所有文档

          云数据库 RDS

          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 xxx READ)阻塞写入,保证备份位点一致性

            【注意】对于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 同时使用保证备份过程中不阻塞主从同步线程写入。

          导入数据时的注意事项

          1. 导入数据时误删除数据

            未指定--add-drop-table=FALSE 时导出的SQL文件中包含了drop table语句,当需要将备份数据恢复到线上时会先drop table再重建表,期间业务可能会报错提示表不存在并且原表数据丢失。

          2. 导入数据时锁表

            在未指定--add-locks=FALSE时导出的SQL文件中包含了lock table write语句,当需要将备份数据恢复到线上时会进行锁表,期间该表其他连接的写入会被阻塞并且可能造成连接数升高。

          3. 当5.7开启GTID后,使用--set-gtid-purged参数导出的SQL会包含SET @@SESSION.SQL_LOG_BIN= 0语句,导入时的数据不会记录binlog,因此从库不会同步数据。如果需要主从数据一致,需要去掉mysqldump出来文件中的如下语句:

            SET @@SESSION.SQL_LOG_BIN= 0;
          4. 导出的SQL文件中除了包含库表数据外还包含了部分变量设置语句,再导入线上时需要特别注意!
          上一篇
          utf8mb4字符集在云上的使用
          下一篇
          time_zone参数使用详解