mysqldump工具使用详解
工具介绍
mysqldump是MySQL自带的客户端工具，它主要用于对MySQL进行逻辑备份，可实现库、表、行级别的数据备份及库表结构备份需求。应用场景包括不限于如下几种情况：
- 执行DML、DDL类型SQL前进行数据备份（用于回滚需要）。
- 不同云厂商之间数据库的数据迁移。
- 数据库版本升级过程中的数据迁移。
- 导出部分数据到测试环境用于功能和性能测试。
常见用法
| 场景 | 命令举例 | 关键参数 |
|---|---|---|
| 全库导出 | mysqldump -h -P -u -p -A > result.sql | -A, --all-databases |
| 部分库导出 | mysqldump -h -P -u -p -B db1 db2 > result.sql | -B, --databases |
| 单库部分表导出 | mysqldump -h -P -u -p db1 tb1 tb2 > result.sql | |
| 单表部分数据导出 | mysqldump -h -P -u -p db1 tb1 -w "id < 5" > result.sql | -w, --where=name, 注意加引号 |
| 仅导出表结构 | mysqldump -h -P -u -p -A -d > result.sql | -d, --no-data |
| 仅导出数据不包含表结构 | mysqldump -h -P -u -p -A -t > result.sql | -t, --no-create-info |
| 导出数据库事件 | mysqldump -h -P -u -p db1 tb1 -E > result.sql | -E, --events |
| 导出数据库触发器 | mysqldump -h -P -u -p db1 tb1 --triggers > result.sql | --triggers |
| 导出数据库存储过程与函数 | mysqldump -h -P -u -p db1 tb1 -R > result.sql | -R, --routines |
| 指定字符集导出 | mysqldump -h -P -u -p -A --default-character-set=utf8 > result.sql | --default-character-set |
重要参数
-
--add-drop-table
【说明】默认开启，mysqldump导出的SQL文件中包括DROP语句：DROP TABLE IF EXISTS 'XXX'。
【注意】防止将SQL文件直接导入线上实例时drop原数据表，造成数据丢失。可使用参数 --skip-add-drop-table 避免导出DROP语句。
-
--add-locks
【说明】默认开启，在INSERT语句前后分别加 LOCK TABLES 'XXX' WRITE 和 UNLOCK TABLES。
【注意】防止将SQL导入实例时锁表阻塞其他连接写入。可使用参数 --skip-add-locks 规避。
-
--lock-tables
【说明】默认开启，对指定库下的表加读锁（LOCK TABLES
xxxREAD）阻塞写入，保证备份位点一致性。【注意】对于InnoDB引擎表请使用—single-transaction而非--lock-tables。可使用参数 --skip-lock-tables 规避。
-
--lock-all-tables
【说明】默认关闭，通过在备份期前加read、lock锁定所有库的所有表。
【注意】该参数会自动关闭参数选项 --single-transaction 和 --lock-tables。
-
--single-transaction
【说明】mysqldump会自动开启一个可重复读的事务（REPEATABLE READ），然后备份出一致性的数据快照。仅对事务型表如InnoDB引擎的表有用。
【注意】如果数据库中都是InnoDB引擎的表，建议mysqldump导出时开启该参数。具体执行过程是：在备份前设置事务隔离级别为REPEATABLE READ并向server发送 START TRANSACTION 语句，在MySQL的全日志中可以看到如下内容：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ SAVEPOINT SP ... ... ROLLBACK TO SAVEPOINT SP RELEASE SAVEPOINT SP -
--set-gtid-purged
【说明】默认开启，导出的SQL文件中是否包含GTID_PURGED信息。
当设置为ON/AUTO时，导出的SQL文件中包含 SET @@GLOBAL.GTID_PURGED='xxx:1-xxx'。
当设置为OFF时，导出的SQL文件中不包含SET @@GLOBAL.GTID_PURGED='xxx:1-xxx'。
【注意】开启GTID的实例需要设置该参数。
-
--master-data
【说明】用于在主库进行备份时记录备份时刻主库的binlog位点。
取值1：导出的SQL文件中未注释CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX。
取值2：导出的SQL文件中注释CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX。
【注意】开启了binlog，但未开启GTID的情况下，需要设置该参数。
-
--dump-slave
【说明】用于在从库上备份并记录备份时刻对应主库的binlog位点：
取值1：导出的SQL文件中未注释CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX。
取值2：导出的SQL文件中注释CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX。
【注意】从库上执行mysqldump，默认先使用命令 flush table with read lock 获取备份一致性快照，当实例不存在非事务表时建议与--single-transaction 同时使用保证备份过程中不阻塞主从同步线程写入。
导入数据时的注意事项
-
导入数据时误删除数据
未指定--add-drop-table=FALSE 时导出的SQL文件中包含了drop table语句，当需要将备份数据恢复到线上时会先drop table再重建表，期间业务可能会报错提示表不存在并且原表数据丢失。
-
导入数据时锁表
在未指定--add-locks=FALSE时导出的SQL文件中包含了lock table write语句，当需要将备份数据恢复到线上时会进行锁表，期间该表其他连接的写入会被阻塞并且可能造成连接数升高。
-
当5.7开启GTID后，使用--set-gtid-purged参数导出的SQL会包含SET @@SESSION.SQL_LOG_BIN= 0语句，导入时的数据不会记录binlog，因此从库不会同步数据。如果需要主从数据一致，需要去掉mysqldump出来文件中的如下语句：
SET @@SESSION.SQL_LOG_BIN= 0 - 导出的SQL文件中除了包含库表数据外还包含了部分变量设置语句，再导入线上时需要特别注意。