MySQL数据备份和恢复

一、两种备份工具的特点

1.1、逻辑备份mysqldump

• mysqldump是MySQL自带的备份工具，不需要下载安装；
• 通过mysqldump备份出来的是以文本格式保存的SQL语句，可读性高，便于备份处理；
• 通过mysqldump备份的数据压缩比高，节省磁盘空间；
• mysqldump依赖于数据库存储引擎，需要把数据从磁盘读出，然后转换成SQL进行转储。这个过程会耗费服务器资源，数据量很大时，备份效率低；
• 在备份TB级别的数据时，也可能会选择mysqldump，主要考虑数据压缩比高，能节省磁盘空间。

1.2、物理备份xtrabackup(XBK)

• 备份时会直接拷贝数据文件，性能较高；
• 备份文件可读性差；
• 数据压缩比低，占用磁盘空间。

二、mysqldump数据备份和恢复

2.1、备份

备份常用命令：

• 全量备份：

# mysqldump -h 127.0.0.1 -P 3306 -u root -p -A > backup/full_bak.sql

-A选项：–all-databases，备份所有库。

• 备份指定库：

# mysqldump -h 127.0.0.1 -B test1 test2 > backup/test.sql

-B选项：–databases，备份指定的库，可以同时指定多个库。

• 备份指定表：

# mysqldump -h 127.0.0.1 test1 sbtest1 > backup/sbtest1.sql

test1：指定库名；
sbtest1：指定表名，可以同时指定库中的多个表。

• 只备份表结构，而不备份数据：

# mysqldump -h 127.0.0.1 -d test1 > backup/test1.sql

-d选项：–no-data，只导出表结构。

• 备份存储过程、函数和触发器：

# mysqldump -h 127.0.0.1 -A -R --triggers > backup/full.sql

-R选项：备份存储过程和函数；
--triggers选项：备份触发器。

• 备份时记录binlog的状态信息：

# mysqldump -h 127.0.0.1 -A -R --triggers --master-data=2 --single-transaction > backup/full.sql

--master-data=0：默认值；
--master-data=1：以“CHANGE MASTER TO”命令的形式记录binlog的状态信息；
--master-data=2：以注释的形式记录binlog的状态信息，指备份时刻的文件名和position号；
--single-transaction：对支持事务的表，会对该表创建一致性快照，从快照备份数据而不必锁表。

2.2、恢复

在恢复时，通过source命令导入备份的SQL文件即可。
需要注意的是：

• 恢复数据时，最好停止业务，避免对表中数据的破坏；
• 因为备份的SQL中包含了“DROP”语句，最好在测试库中恢复；
• 在测试库确认无误后，再将数据导入线上库；
• 导入到线上时，最好再将线上的正常数据做备份，避免恢复失败影响正常数据；
• 在执行source命令前，可以将“sql_log_bin”参数置为0，不记录恢复时产生的二进制