本文版本 mysql 5.7.26
【mysqldump默认参数】
all-databases FALSE all-tablespaces FALSE no-tablespaces FALSE add-drop-database FALSE add-drop-table TRUE add-drop-trigger FALSE add-locks TRUE allow-keywords FALSE apply-slave-statements FALSE bind-address (No default value) character-sets-dir (No default value) comments TRUE compatible (No default value) compact FALSE complete-insert FALSE compress FALSE create-options TRUE databases FALSE default-character-set utf8 delete-master-logs FALSE disable-keys TRUE dump-slave 0 events FALSE extended-insert TRUE fields-terminated-by (No default value) fields-enclosed-by (No default value) fields-optionally-enclosed-by (No default value) fields-escaped-by (No default value) flush-logs FALSE flush-privileges FALSE force FALSE hex-blob FALSE host (No default value) ignore-error (No default value) include-master-host-port FALSE insert-ignore FALSE lines-terminated-by (No default value) lock-all-tables FALSE lock-tables TRUE log-error (No default value) master-data 0 max-allowed-packet 25165824 net-buffer-length 1046528 no-autocommit FALSE no-create-db FALSE no-create-info FALSE no-data FALSE order-by-primary FALSE port 3306 quick TRUE quote-names TRUE replace FALSE routines FALSE set-charset TRUE single-transaction FALSE dump-date TRUE socket /data/mysql/mysql.sock secure-auth TRUE ssl TRUE ssl-verify-server-cert FALSE ssl-ca (No default value) ssl-capath (No default value) ssl-cert (No default value) ssl-cipher (No default value) ssl-key (No default value) ssl-crl (No default value) ssl-crlpath (No default value) tls-version (No default value) tab (No default value) triggers TRUE tz-utc TRUE user (No default value) verbose FALSE where (No default value) plugin-dir (No default value) default-auth (No default value) enable-cleartext-plugin FALSE
（1.1）mysqldump默认启动的参数(5.7)
我们可以看到 为 true的
add-drop-table TRUE -- 有 drop table if exist add-locks TRUE -- 允许加锁，避免tuncate alter 等ddl comments TRUE -- 导出注释 create-options TRUE -- 创建语句 default-character-set utf8 -- 默认utf8 字符集 disable-keys TRUE -- 使用ALTER TABLE ... DISABLE KEYS要告诉MySQL停止更新非唯一索引 dump-slave 0 -- 是否是在从库备份，开启参数为1 可以获取到主库binlog位置 extended-insert TRUE -- 默认可以把所有数据从每行数据1个insert into，变成insert into values(),() lock-tables TRUE -- 默认运行锁表 master-data 0 -- 默认不做任何binlog情况操作与收集 max-allowed-packet 25165824 -- 每次导出的最大包大小 net-buffer-length 1046528 -- TCP/IP SOCKET连接 缓存大小 port 3306 -- 默认端口 3306 quick TRUE -- 不要用 buffer 缓存mysqldump里的查询，直接转储到标准输出 quote-names TRUE -- 就是把表名、列名等使用 `` 包起来，以免与关键字等冲突 set-charset TRUE -- 增加 SET NAMES default_character_set 到默认输出 dump-date TRUE -- 在标准输出最末尾，加上dump完成时间 socket /data/mysql/mysql.sock -- 默认 mysql socket 位置，这个是我在 my.cnf 中的 [mysql] 里面改了，默认的应该是 /var/local/mysql 下目录 secure-auth TRUE -- 默认拒绝使用 4.1.1之前的老协议连接 ssl TRUE -- 默认支持ssl triggers TRUE -- 默认会导出触发器 tz-utc TRUE -- 允许时区
【2】mysqlpump（5.7）
（2.1）功能特性
-
并行处理数据库和数据库中的对象，以加快转储过程
-
更好地控制要转储的数据库和数据库对象（表、存储的程序、用户帐户）
-
将用户帐户转储为帐户管理语句 (
CREATE USER,GRANT) 而不是插入mysql系统数据库 -
创建压缩输出的能力
-
进度指标（数值为估计值）
-
对于转储文件重新加载，
InnoDB通过在插入行后添加索引来更快地为表 创建二级索引
（2.2）常用参数
|
参数 |
说明 |
|---|---|
|
--default-parallelism=# |
设置并行导出的并发度默认为2,如果为0则不并行，5.7.11后解决了--single-transaction才能与--default-parallelism共用 |
|
--single-transaction |
创建一个单独的事务来导出所有的表，5.7.11后解决了--single-transaction才能与--default-parallelism共用 |
|
--exclude-databases=name |
导出时排除掉某些库，多个库以逗号分隔 |
|
--exclude-tables=name |
导出时排除掉某些表，多个表以逗号分隔 |
|
--include-databases=name |
导出时包含某些库，多个库以逗号分隔 |
|
--include-tables=name |
导出时包含某些表，多个表以逗号分隔 |
|
--users |
导出 create user ,grant 语句 |
（2.3）mysqlpump自带默认参数（5.7）
default-character-set UTF8MB4 -- 默认字符集 default-parallelism 2 -- 默认2线程 defer-table-indexes TRUE -- 在导出所有行之后，再加索引 events TRUE -- 默认导出事件 extended-insert 250 -- 把每行一个 insert into 变成 max-allowed-packet 25165824 -- 默认最大包大小 net-buffer-length 1047552 -- 网络缓存大小 password * -- 密码 port 3306 -- 端口 routines TRUE -- 默认自带存储过程 set-charset TRUE -- set names default-character-set set-gtid-purged AUTO -- 自动，但不写会报错 socket /data/mysql/mysql.sock --socket triggers TRUE -- 默认自带存储过程 tz-utc TRUE -- 支持时区 watch-progress TRUE -- 导出时显示进度
到8.0 默认的 存储过程，触发器等参数 又会因为系统表的改进而默认不备份，所以无论什么时候什么情况，存储过程、事件、触发器等 参数还是加上吧；
【最佳实践】mysqldump与mysqlpupm对比
（1）构造5个100W行数据表
sysbench参考：https://www.cnblogs.com/gered/p/14142946.html#autoid-5-0-0
sysbench oltp_common --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=localhost --mysql-port=3306
--mysql-user=root --mysql-password=bfgame20 --mysql-db=test --threads=4 --table_size=1000000 --tables=5 --mysql-socket=/data/mysql/mysql.sock prepare
（2）实际执行时间对比
mysqldump：
time mysqldump --single_transaction --default-character-set=utf8mb4 --master-data=2 test sbtest1 sbtest2 sbtest3 sbtest4 sbtest5 >/data/tmp.txt Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. real 0m12.329s user 0m10.500s sys 0m1.304s
mysqlpump：
time mysqlpump --default-character-set=utf8mb4 --set-gtid-purged=ON --host=localhost --single-transaction test sbtest1 sbtest2 sbtest3 sbtest4 sbtest5 >/data/tmp1.txt mysqlpump: [WARNING] (1429) Unable to connect to foreign data source: Access denied for user 'leoaprd'@'10.20.53.12' (using password: mysqlpump: [WARNING] (1429) Unable to connect to foreign data source: Access denied for user 'leoaprd'@'10.20.53.12' (using password: Dump progress: 0/5 tables, 250/4932000 rows Dump progress: 0/5 tables, 806250/4932000 rows Dump progress: 0/5 tables, 1700250/4932000 rows Dump progress: 2/5 tables, 2611250/4932000 rows Dump progress: 2/5 tables, 3520000/4932000 rows Dump progress: 4/5 tables, 4204500/4932000 rows Dump progress: 4/5 tables, 4636000/4932000 rows Dump completed in 7608 milliseconds real 0m7.676s user 0m11.274s sys 0m0.937s
执行效率结论：
同500W行，那么 mysqlpump 默认使用了多线程基于表的并行 导出；所以快了不少
500W数据，5个表
mysqldump：12.32s mysqlpump：7.67s
（3）导出内容对比
概述：mysqldump mysqlpump导出的内容影响
MySQL8.0之后，在使用mysqldump和mysqlpump导出数据时候，与之前有了一些不同，主要是以下几点：
之前版本的mysqldump和mysqlpump可以导出mysql系统库中的所有表的内容，8.0之后，只能导出mysql系统库中没有数据的数据字典表。
之前版本当使用 --all-databases 参数导出数据的时候，不加 --routines和 --events选项也可以导出触发器、存储过程等信息；
因为这些信息都存放于proc和event表中，导出所有表即可导出这些信息。
但是在8.0中，proc表和event表都不再使用，并且定义触发器、存储过程的数据字典表不会被导出，所以在8.0中使用mysqldump、mysqlpump导出数据的时候，如果需要导出触发器、存储过程等内容，一定需要加上 --routines和 --events选项。
之前版本中 --routines选项导出的时候，备份账户需要有proc表的SELECT权限，在8.0中需要对所有表的SELECT权限
之前版本中，导出触发器、存储过程可以同时导出触发器、存储过程的创建和修改的时间戳，8.0中不再支持。
（3.1）导出内容的实质区别
| mysqldump | mysqlpump |
| 固定根据max_allow_packet，拆分insert into values个数 | 根据extended-insert ，默认每250行汇成一个Insert |
| 建一个表，插入一个表的数据 | 并行N个线程，会先建N个表，插入N个表的数据 |
| 建表时建好所有索引，然后禁用非唯一索引插入数据 | 建表时只建立唯一索引(主键也算)，插完数据后建其他索引 |
| 可以保证一致性 --single_transaction | 就算不使用 --single_transaction 也可以保证一致性 |
| 可以通过--master-data 来获取binlog位置 | 无法获取binlog 位置，只能获取gtid，且set-gtid-purged必填 |
mysqlpump 还有相关优点：
-
并行处理数据库和数据库中的对象，以加快转储过程
-
更好地控制要转储的数据库和数据库对象（表、存储的程序、用户帐户）
-
将用户帐户转储为帐户管理语句 (
CREATE USER,GRANT) 而不是插入mysql系统数据库 -
创建压缩输出的能力
-
进度指标（数值为估计值）
-
对于转储文件重新加载，
InnoDB通过在插入行后添加索引来更快地为表 创建二级索引
（4）优缺点对比
mysqlpump
- 优点：
-
-
- 基于表并行备份数据库和数据库中对象，加快备份过程。（--default-parallelism）
- 更好地控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
- 备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
- 支持直接压缩导出
- 导出可显示进度（估计值）。
- 导出时可以排除或指定数据库。
- 导入备份文件时，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。
-
- 缺点：
- 官方表示在5.7.11之前无法保证数据的一致性，所以5.7.11之前该工具基本无法使用
- 5.7.11之前，--defaut-parallelism>0时与--single-transaction互斥，无法使用并行。直到5.7.11才解决了--single-transaction和--default-parallelism互斥的问题
- 只能并行到表级别，如果有一个表数据量特别大那么会存在非常严重的短板效应，甚至不如mysqldump。
- 导出的数据保存在一个文件中，导入仍旧是单线程，效率较低。
- 无法获取当前备份对应的binlog位置。
- 无法多版本兼容，不像Mysqldump 什么版本都可以用，yyds啊！
【参考文档】