【MySQL】删库别着急跑路——mysqldump的使用

今天一起聊下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，不建议日间业务高峰期进行此操作。

文章来源：墨天轮 https://www.modb.pro/db/25125
文章转载自 Mysql technology 公众号

相关文章：
【生产篇】_mysql逻辑备份mysqldump如何实时跟踪备份过程
mysqldump学习总结
MySQL DBA学习指南卷II

你有技术问题，到墨天轮问答平台或微信小程序 “ DBASK ”提问吧，无门槛提问，技术专家免费答~