云数据库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参数使用详解