【MySQL】删库别着急跑路（二）--mysqldump的使用

上篇文章我们一起学习了Xtrabackup的备份原理和使用方法，今天一起聊下mysqldump的使用。mysqldump是MySQL数据库自带的一个备份命令，它支持数据库全备也可以指定库进行备份，它备份的文件以SQL语句的形式存储。它在备份时会先从buffer中寻找需要备份的数据，如果buffer中没有就要去磁盘查找并调回buffer里面在进行备份。

1. mysqldump参数

+

下面我们介绍几个mysqldump的核心参数，

1. --single-transaction:用于保证innodb备份数据时的一致性，配合RR隔离级别一起使用，当发起一个事务时，会读取当前的数据快照，知道备份结束，都不会读取到本事务开始之后提交的数据。

2. --all-databases(-A):备份所有数据库。

3. --master-data:该参数为1时，表示备份出来的文件中添加一个CHANGE MASTER语句，该值为2时，表示会在CHANGE MASTER命令前端增加注释信息。

4. --database(-B)：备份多个数据库。

5. --no-create-info(-t):备份过程中，只备份数据，不备份表结构。

6. --no-data(-d):备份过程中，只备份表结构，不备份表数据。

7. --dump-slave:该参数用于在从库端备份，在线搭建新的从库时使用。该参数也有两个值1和2，该参数为1时，表示备份出来的文件中添加一个CHANGE MASTER语句，该值为2时，表示会在CHANGE MASTER命令前端增加注释信息。

8. --add-drop-database:在CREATE DATABASE语句前添加DROP DATABASE语句。

9. --add-drop-table:在CREATE TABLE语句前添加DROP TABLE。

10. --complete-insert(-c):使用包括列名的完整的INNSERT语句。

11. --flush-log(-F):开始转储前刷新MySQL的日志。该选项要求RELOAD权限。

12. --default-character-set:字符集选项，MySQL目前默认的字符集是UTF-8(MySQL8.0默认 utf8mb4)，要与备份出来的表的字符集保持一致。

13. --quick(-q):不缓冲查询，直接输出到stdout。

14. --where=name(-w):按条件备份想要的数据。

2. mysqldump备份演示

+

（1） 备份所有数据库：

shell> mysqldump -uuser_name -p --single-transaction --all-databases > all.sql

（2） 备份数据库 test：

shell> mysqldump -uuser_name -p --single-transaction test > test.sql

（3） 备份数据库 test 下的表 emp：

shell>mysqldump -uuser_name -p --single-transaction test emp > emp.sql

（4） 备份存储过程

shell>mysqldump -uuser_name -p -R -d -t database_name>triggers.sql

（5） 只导出数据库结构

shell>mysqldump -uuser_name -p -d  database_name > outfile.sql

（6） 备份后压缩

shell> mysqldump -uuser_name -p --single-transaction --all-databases|gzip >all.gz

（7） 备份test数据库t表中id>3的记录

shell> mysqldump -uuser_name -p --single-transaction test t –where=”id>3” > test.sql

3. 数据恢复

+

mysqldump工具备份的数据可以通过以下方式恢复。

1. 使用mysql命令还原

mysql –uuser_name –p dbname <outfile.sql

其中dbname是数据库名，outfile.sql是我们用mysqldump命令备份出来的文件。

2. 通过source命令还原

首先要登录到mysql服务器，选择要恢复的数据库，然后执行source命令。

Mysql>use dbname
Mysql>source outfile.sql

4. 小结

+

mysqldump速度较慢，针对数据不是太大的库或者表可以选择采用mysqldump的方式备份，如果数据量比较大建议采用Xtrabackup等物理备份方式。mysqldump备份时会从磁盘读取数据到buffer中，占用内存及磁盘I/O，不建议日间业务高峰期进行此操作。