链接：https://pan.baidu.com/s/1pFEljz17jMWMwbFqWNlccQ 

提取码：vmc0 

 

mysql运维必须熟练掌握备份和恢复技术

 

一、 备份恢复策略

进行备份或恢复操作时需要考虑一些因素：

1、确定要备份的表的存储引擎是事务型还是非事务型，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。

2、确定使用全备份还是增量备份。全备份的优点是备份保持最新备份，恢复的时候可以花费更少的时间；缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。增量备份相反，只需要备份每天的增量日志，备份时间少，对负载压力也小；缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间长一些。

3、可以考虑采用复制的方法来做异地备份，但不能代替备份，它对数据库的误操作也无能为力。

4、要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行

5、确保 MySQL 打开 log-bin 选项，有了 binlog，MySQL 才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。

6、经常做备份恢复测试，确保备份是有效的，是可以恢复的。

二、 逻辑备份和恢复

在 MySQL 中，逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法，因此，对于不同存储引擎混合的数据库，逻辑备份会简单一点。

1. 备份

MySQL 中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在 MySQL 中，可以使用 mysqldump 工具来完成逻辑备份：

// 备份指定的数据库或者数据库中的某些表   shell> mysqldump [options] db_name [tables]   // 备份指定的一个或多个数据库   shell> mysqldump [options] --database DB1 [DB2,DB3...]   // 备份所有数据库   shell> mysqldump [options] --all-database

如果没有指定数据库中的任何表，默认导出所有数据库中的所有表。

示例：

1. 备份所有数据库：

shell>mysqldump -uroot -p --all-database > all.sql

2. 备份数据库 test

shell>mysqldump -uroot -p test > test.sql

3. 备份数据库 test 下的表 emp

shell> mysqldump -uroot -p test emp > emp.sql

4. 备份数据库 test 下的表 emp 和 dept

shell> mysqldump -uroot -p test emp dept > emp_dept.sql

5. 备份数据库test 下的所有表为逗号分割的文本，备份到 /tmp:

shell> mysqlddump -uroot -p -T /tmp test emp --fields-terminated-by ','shell> more emp.txt  1,z12,z23,z34,z4

注意：为了保证数据备份的一致性， myisam存储引擎在备份时需要加上-l参数,表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。但是对于事务存储引擎来说，可以采用更好的选项 --single-transaction，此选项使得 innodb 存储引擎得到一个快照(snapshot)，使得备份的数据能够保证一致性。

2. 完全恢复

mysqldump 的恢复也很简单，将备份作为输入执行即可：

mysql -uroot -p db_name < backfile

注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做：

mysqlbinlog binlog-file | mysql -uroot -p

完整的 mysqldump 备份与恢复示例：

1. 凌晨 2:00，备份数据库：

root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp Enter password: 

其中 -l参数表示给所有表加读锁，-F表示生成一个新的日志文件，此时，t2 中 emp 表的数据如下：

# 为了便于测试，执行 reset master 删除所有 binlog。 MySQL [(none)]> reset master; Query OK, 0 rows affected (0.00 sec) # 此时只有一个 binlog 日志文件   mysql-bin.000001 MySQL [t2]> select * from test; +------+------+ | id   | name | +------+------+ |    1 | a    | |    2 | b    | +------+------+ 2 rows in set (0.00 sec)

2. 备份完毕后，插入新的数据：

# 因为上一步执行是加入了 -F 选项, 所以接下来的操作会被记录到新的二进制文件，即名为 mysql-bin.000002 的文件 MySQL [t2]> insert into test values (3,'c'); Query OK, 1 row affected (0.00 sec) MySQL [t2]> insert into test values (4,'d'); Query OK, 1 row affected (0.00 sec)

3. 数据库突然故障(其实是小伙伴没事儿删库练手玩儿)，数据无法访问。需要恢复备份：

删库跑路：

# 这里为了便于测试，不把删库操作记入日志，当前 session 设置 sql_log_bin 为 off。 # 删库后，执行 flush logs，让后续的 binlog 到新的文件中，即名为 mysql-bin.000003中 MySQL [t2]> set sql_log_bin = 0; Query OK, 0 rows affected (0.00 sec) MySQL [t2]> show variables like "%sql_log_bin%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin   | OFF   | +---------------+-------+ 1 row in set (0.00 sec) MySQL [t2]> drop database t2; Query OK, 1 row affected (0.01 sec) MySQL [(none)]> flush logs; Query OK, 0 rows affected (0.22 sec) MySQL [t2]> drop database t2; Query OK, 3 rows affected (0.23 sec) MySQL [(none)]> exit; Bye