mysqldump常用参数

首先，给出我的MySQL版本

SELECT  VERSION();
5.7.25-log

mysqldump的三种调用形式

1. dump一张或者多张表

   mysqldump [options] db_name [tbl_name ...]
   

2. dump一个或者多个库

   mysqldump [options] --databases db_name ...
   

3. dump所有库

   mysqldump [options] --all-databases
   

调用说明

1. 只有在后两种调用模式下，才会出现建库语句，所以，--add-drop-database也只会出现在后两种调用模式下
2. 全库导出只会导出用户库和mysql库

全量备份所有库(基于binlog)

mysqldump \
    --all-databases \
    --set-charset \
    --default-character-set=utf8 \
    --create-options \
    --triggers \
    --routines \
    --events \
    --add-drop-database \
    --add-drop-table \
    --add-drop-trigger \
    --add-locks \
    --disable-keys \
    --extended-insert \
    --set-gtid-purged=OFF \
    --master-data=2 \
    --single-transaction \
    --quick \
    --flush-privileges \
    --flush-logs \
    --skip-opt \
    --result-file=/var/local/mysql/dump.sql \
    --log-error=/var/log/mysql/dump_error_log \
    -S /var/local/mysql/mysql.sock \
    -p \

参数说明

1. --skip-opt
--opt相当于
   --add-drop-table,
   --add-locks,
   --create-options,
   --disable-keys,
   --extended-insert,
   --lock-tables,
   --quick,
   --set-charset
   其中，--lock-tables和--single-transaction互斥，为了避免歧义，这里禁用--opt，除了--lock-tables的其他选项手工指定
   经过测试，这样虽然不报错，但是，导出的备份文件不对。明显的两个问题是没有添加DROP TABLE语句，也就是说，--add-drop-table无效，还有，SOURCE这个导出文件的时候，会报错，mysql.user表不存在。
   那就不要用这种自创参数，给成如下格式

   mysqldump \
       --all-databases \
       --default-character-set=utf8 \
       --triggers \
       --routines \
       --events \
       --add-drop-database \
       --add-drop-trigger \
       --set-gtid-purged=OFF \
       --master-data=2 \
       --single-transaction \
       --flush-privileges \
       --flush-logs \
       --opt \
       --result-file=/var/local/mysql/dump.sql \
       --log-error=/var/log/mysql/dump_error_log \
       -S /var/local/mysql/mysql.sock \
       -p \
   

2. --set-gtid-purged
   为ON会在导出的备份文件中加入
   SET @@GLOBAL.GTID_PURGED和SET @@SESSION.SQL_LOG_BIN= 0
   三种情况：

   1. 导出的文件用于本地备份，给成OFF
   2. 导出的文件用于从库导入数据，给成ON
   3. 导出的文件用于其他不相关节点，给成OFF

3. --master-data=2
   会在导出的备份文件中加入
   -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1474;
   这样的注释。

4. --single-transaction
   将隔离级别设为RR，并在进行导出前，执行START TRANSACTION。
   但是对于非事务性存储引擎，还是会出现不一致性，
   所以，在备份过程中，禁止出现以下语句
   ALTER TABLE,
   CREATE TABLE,
   DROP TABLE,
   RENAME TABLE,
   TRUNCATE TABLE
   这个选项和--lock-tables互斥

5. 不要使用--compact选项

   Produce more compact output. This option enables the --skip-add-drop-table，--skip-add-locks， --skip-comments，--skip-disable-keys，and --skip-set-charset options.

遗留问题

1. 关于锁
   经过试验，--add-locks会在导出的备份文件中的每个表的数据插入前后分别执行
   LOCK TABLES table_name WRITE；和UNLOCK TABLES;
   那么，如何查看出--lock-all-tables和--lock-tables申请是什么样的锁呢？
   根据文档，--master-data和--single-transaction一起使用的时候会申请全局锁FLUSH TABLES WITH READ LOCK。
   在这种情况下，在dump开始的时候，进程会申请锁，一旦获取锁，就会得到the binary log coordinates (file name and position)，同时，释放锁。所以，并不会hang住数据库。
   --flush-logs和--lock-all-talbes，--master-data或者--single-transaction一起使用的时候，也会申请全局锁FLUSH TABLES WITH READ LOCK。如果想要保证dump文件和the binary log coordinates的一致性，要做到上述的--flush-logs和--lock-all-talbes，--master-data或者--single-transaction一起使用。
   推测--lock-all-tables申请的锁也是FLUSH TABLES WITH READ LOCK，但是在没有--single-transaction的情况下，由于无法做到RR的隔离性，所以这个锁会保持到备份完成，所以，在此期间，数据库会hang住。而有了--single-transaction选项，因为通过隔离性保证了一致性读，所以，一旦获得了the binary log coordinates，锁会立即释放。
   至于--lock-tables，推测是申请的表级锁，因为与--single-transaction互斥，所以并不推荐使用。

参考文档

4.5.4 mysqldump — A Database Backup Program