mysql 备份恢复.txt - 墨天轮文档

mysql 备份恢复.txt

天之道

373

9页

2次

2022-09-02

25墨值下载

### Mysql 备份恢复

#### 一、备份分类、备份策略

##### 1.1 造成数据丢失的原因：

- 程序错误

- 人为操作错误

- 运算错误

- 磁盘故障

- 灾难（如火灾、地震）和盗窃

##### 1.2 数据库备份的分类

1）物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。

冷备份(脱机备份) ：是在关闭数据库的时候进行的

热备份(联机备份) ：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

2）逻辑备份

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份。

即以 sql 语句的形式，把库、表结构、表数据保存下来。

3）数据库的备份策略

完全备份（全量备份）：每次对数据库进行完整的备份。

差异备份：备份自从上次完全备份之后被修改过的文件。

增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份。

##### 1.3 常见备份方法

1）物理冷备：（完全备份）

备份时数据库处于关闭状态，直接打包数据库文件。

至少包括 data 目录，my.cnf，binlog 一般都放在 data 目录中。所以在 mysql 安装时规划好目录，

利于后期备份，迁移，升级等操作。

备份速度快，恢复时也是最简单的

2）专用备份工具 mydump 或 mysqlhotcopy （完全备份，逻辑备份）

mysqldump 常用的逻辑备份工具（导出为 sql 脚本）

mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表

3）启用二进制日志进行增量备份（增量备份）

进行增量备份，需要刷新二进制日志

4）第三方工具备份

免费的 MySQL 热备份软件 Percona XtraBackup；阿里云的工具 dts，支持热迁移；ogg；Pentaho（习

惯称为 Kettle）；Canal；percona-toolkit 等

#### 二、mysqldump

mysqldump 是常用的逻辑备份工具。

mysqldump 可以将指定的库、表导出为 SQL 脚本。

mysqldump 的备份流程大致如下：

==对某个库下所有表加读锁；==刷新 binlog 日志；

备份表数据；

释放读锁；

循环上面三个步骤；直到备份完毕。

由于 mysqldump 备份流程对每个库加锁进行导出的循环方式，所以并不能保证各库之间的数据一致性。

要保证完整的数据一致性使用--lock-all-tables 参数，在备份开始时会对 mysql 加全局读锁，直至整

个备份结束。但此种方式对于 PXC 或业务连续性高的场景不适用。对于 mysqldump 方式数据一致性细化要

求，需要了解**—lock-all-tables；—single-transaction；—lock-tables**三个参数。一般情

况下--single-transaction 参数，通过将导出行为放入一个事务执行，从而可以在保证数据一致性情况

下，不影响业务导出数据。--single-transaction 只能用在 innodb 引擎，导出过程中不能有任何人执

行 alter table，drop table，rename table，truncate table 等 DDL 语句。DDL 语句会被导

出事务的 metadata lock 阻塞。参见 myusqldump 常用参数。

mysqldump 导出数据是备份开始时的数据，而非备份结束时的数据。

##### 1）备份命令

```mysql

mysqldump -uroot -p[密码] --databases 库名 1 [库名 2].. >/备份路径/备份文件名.sql

mysqldump -uroot -p[密码] --all-databases > /备份路径/备份文件名.sql

mysqldump -uroot -p --all-databases --routines > ymt`date +'%Y%m%d%H'`.sql

```

备份出来的文件就是文本的 sql 语句，可以直接查看。

##### 2）恢复命令

可按全库恢复，单独库恢复，表恢复

```mysql

##删除数据库 yuji##

mysql -u root -p -e 'drop database yuji;'

#"-e"选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出

mysql -u root -p -e 'SHOW DATABASES;' #查看当前有哪些数据库

##恢复全部数据库##

mysql -uroot -p < ymt2022082308.sql

##恢复数据库 yuji##

mysql -u root -p < /work/mysql_bak/ymt2022082308.sql #重定向输入库文件

mysql -u root -p -e 'SHOW DATABASES;' #查看当前有哪些数据库

##备份 yuji 库中的 class 表##

mysqldump -uroot -p yuji class > /work/mysql_bak/ymt_class.sql

##删除 yuji 库中的 class 表##

mysql -u root -p -e 'drop table yuji.class;'

mysql -u root -p -e 'show tables from yuji;' #查看 yuji 库中的表，已无 class 表

##恢复 yuji 库中的 class 表##

mysql -u root -p yuji < /work/mysql_bak/ymt_class.sql #重定向导入备份文件，必须指

定库名，且目标库必须存在

mysql -u root -p -e 'show tables from yuji;'

```

异机恢复只需初始化一个空库，并启动 mysql 服务

```mysql

初始化一个空库：

./bin/mysqld --initialize --user=mysql --datadir=/work/mysql/data

--basedir=/work/mysql

启动数据库，查初始密码

### Mysql 备份恢复

#### 一、备份分类、备份策略

##### 1.1 造成数据丢失的原因：

- 程序错误

- 人为操作错误

- 运算错误

- 磁盘故障

- 灾难（如火灾、地震）和盗窃

##### 1.2 数据库备份的分类

1）物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。

冷备份(脱机备份) ：是在关闭数据库的时候进行的

热备份(联机备份) ：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

2）逻辑备份

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份。

即以 sql 语句的形式，把库、表结构、表数据保存下来。

3）数据库的备份策略

完全备份（全量备份）：每次对数据库进行完整的备份。

差异备份：备份自从上次完全备份之后被修改过的文件。

增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份。

##### 1.3 常见备份方法

1）物理冷备：（完全备份）

备份时数据库处于关闭状态，直接打包数据库文件。

至少包括 data 目录，my.cnf，binlog 一般都放在 data 目录中。所以在 mysql 安装时规划好目录，

利于后期备份，迁移，升级等操作。

备份速度快，恢复时也是最简单的

2）专用备份工具 mydump 或 mysqlhotcopy （完全备份，逻辑备份）

mysqldump 常用的逻辑备份工具（导出为 sql 脚本）

mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表

3）启用二进制日志进行增量备份（增量备份）

进行增量备份，需要刷新二进制日志

4）第三方工具备份

免费的 MySQL 热备份软件 Percona XtraBackup；阿里云的工具 dts，支持热迁移；ogg；Pentaho（习

惯称为 Kettle）；Canal；percona-toolkit 等

#### 二、mysqldump

mysqldump 是常用的逻辑备份工具。

mysqldump 可以将指定的库、表导出为 SQL 脚本。

mysqldump 的备份流程大致如下：

==对某个库下所有表加读锁；==刷新 binlog 日志；

备份表数据；

释放读锁；

循环上面三个步骤；直到备份完毕。

由于 mysqldump 备份流程对每个库加锁进行导出的循环方式，所以并不能保证各库之间的数据一致性。

要保证完整的数据一致性使用--lock-all-tables 参数，在备份开始时会对 mysql 加全局读锁，直至整

个备份结束。但此种方式对于 PXC 或业务连续性高的场景不适用。对于 mysqldump 方式数据一致性细化要

求，需要了解**—lock-all-tables；—single-transaction；—lock-tables**三个参数。一般情

况下--single-transaction 参数，通过将导出行为放入一个事务执行，从而可以在保证数据一致性情况

下，不影响业务导出数据。--single-transaction 只能用在 innodb 引擎，导出过程中不能有任何人执

行 alter table，drop table，rename table，truncate table 等 DDL 语句。DDL 语句会被导

出事务的 metadata lock 阻塞。参见 myusqldump 常用参数。

mysqldump 导出数据是备份开始时的数据，而非备份结束时的数据。

##### 1）备份命令

```mysql

mysqldump -uroot -p[密码] --databases 库名 1 [库名 2].. >/备份路径/备份文件名.sql

mysqldump -uroot -p[密码] --all-databases > /备份路径/备份文件名.sql

mysqldump -uroot -p --all-databases --routines > ymt`date +'%Y%m%d%H'`.sql

```

备份出来的文件就是文本的 sql 语句，可以直接查看。

##### 2）恢复命令

可按全库恢复，单独库恢复，表恢复

```mysql

##删除数据库 yuji##

mysql -u root -p -e 'drop database yuji;'

#"-e"选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出

mysql -u root -p -e 'SHOW DATABASES;' #查看当前有哪些数据库

##恢复全部数据库##

mysql -uroot -p < ymt2022082308.sql

##恢复数据库 yuji##

mysql -u root -p < /work/mysql_bak/ymt2022082308.sql #重定向输入库文件

mysql -u root -p -e 'SHOW DATABASES;' #查看当前有哪些数据库

##备份 yuji 库中的 class 表##

mysqldump -uroot -p yuji class > /work/mysql_bak/ymt_class.sql

##删除 yuji 库中的 class 表##

mysql -u root -p -e 'drop table yuji.class;'

mysql -u root -p -e 'show tables from yuji;' #查看 yuji 库中的表，已无 class 表

##恢复 yuji 库中的 class 表##

mysql -u root -p yuji < /work/mysql_bak/ymt_class.sql #重定向导入备份文件，必须指

定库名，且目标库必须存在

mysql -u root -p -e 'show tables from yuji;'

```

异机恢复只需初始化一个空库，并启动 mysql 服务

```mysql

初始化一个空库：

./bin/mysqld --initialize --user=mysql --datadir=/work/mysql/data

--basedir=/work/mysql

启动数据库，查初始密码

of 9

25墨值下载

关注

评论