mysqldump 与 mysqlpump

简介：

mysqldump 与 mysqlpump都是mysql自带的备份工具。mysqlpump 是 mysqldump 的一个衍生，本身也参考了 mydumper 的思路，支持了并行导出数据，因此导出数据的效率比 mysqldump 会高很多。

使用：

mysqldump

mysqldump常用选项

-A, --all-databases 　　　　  　　Dump all the databases. This will be same as --databases with all databases selected.
-Y, --all-tablespaces           Dump all the tablespaces.(似乎没什么用处)
-y, --no-tablespaces 　　　　　　 Do not dump any tablespace information.(似乎没什么用处)
-B, --databases     　　　　　　  Dump several databases.
-E, --events        　　　　　　  Dump events.(默认不导出events与routines，默认导出triggers)
-R, --routines      　　　　　　　Dump stored routines (functions and procedures).
--triggers 　　　　　　　　　　　　Dump triggers for each dumped table.
　　　　　　　　　　　　　　     　(Defaults to on; use --skip-triggers to disable.)

--ignore-error=name 　　　　　　　A comma-separated list of error numbers to be ignored if encountered during dump.
--ignore-table=name 　　　　　　　Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times, oncefor each table.  
　　　　　　　　　　　　　　　　　　  Each table must be specified with both database and table names, e.g.,--ignore-table=database.table.
--master-data[=#] 　　　　　　　　This causes the binary log position and filename to be appended to the output. 
　　　　　　　　　　　　　　　　　　　If equal to 1, will print it as a CHANGE MASTER command; 
　　　　　　　　　　　　　　　　　　  if equal to 2, that command will be prefixed with a comment symbol.
 　　　　　　　　　　　　　　　　　　 此选项要配合选项 --single-transactinon一起使用，否则整个dump期间所有表都会被锁。

-t, --no-create-info 　　　　　　Don't write table creation info.
-d, --no-data 　　　　　　　　　　 No row information.

-q, --quick 　　　　　　　　　　　　Don't buffer query, dump directly to stdout.

　　　　　　　　　　　　　　　　　　　　(Defaults to on; use --skip-quick to disable.)

--set-gtid-purged[=name]　　　　　　Add 'SET @@GLOBAL.GTID_PURGED' to the output.

　　　　　　　　　　　　　　　　　　　　Possible values for this option are ON, OFF and AUTO. 默认值为AUTO.

没有启用gtid，此选项设置为on，会报错。

　　　　　　　　　　　　　　　　　　　　设置为AUTO,会根据是否启用了gtid来决定是否输出'SET @@GLOBAL.GTID_PURGED' 到备份文件中。

--single-transaction　　　　　　　　Creates a consistent snapshot by dumping all tables in a single transaction.

在备份期间，确保没有alter table，drop table，rename table，truncate table这些语句发生。

-F, --flush-logs 　　　　　　 Flush logs file in server before starting dump.

--tables 　　　　　　　Overrides option --databases (-B).

-w, --where=name 　　　　　　　Dump only selected records. Quotes are mandatory.

--users　　　　　　　　　　　　　　导出用户权限

--compact 　　　　　　　　　　　　 Give less verbose output (useful for debugging).

　　　　　　　　　　　　　　　　　　　　Disables structure comments and header/footer constructs.

　　　　　　　　　　　　　　　　　　　 Enables options --skip-add-drop-table --skip-add-locks --skip-comments

--skip-disable-keys --skip-set-charset.

mysqldump常用举例：

指定表导出：(本例子导出test库的表tt与tt1) （--tables）

mysqldump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --compact --flush-logs --master-data=2 --tables test tt tt1 > /mysql/backup/3308/mysqldump.$(date +%F).sql

指定库导出 （--databases）

mysqldump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --flush-logs --master-data=2 --databases test benchmark  > /mysql/backup/3308/mysqldump.$(date +%Y%m%d_%H%M%S).sql

表结构导出
mysqldump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --flush-logs --master-data=2 --databases test benchmark --no-data  > /mysql/backup/3308/mysqldump.$(date +%Y%m%d_%H%M%S).sql

表数据导出（不带表结构）

mysqldump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --flush-logs --master-data=2 --databases test --no-create-info  > /mysql/backup/3308/mysqldump.$(date +%Y%m%d_%H%M%S).sql

整个实例导出（包含用户权限）（分两步，先导出用户，再导出业务表）

（1）/mysql/svr/mysql/bin/mysqlpump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --set-gtid-purged=OFF --exclude-databases=% --users >/mysql/backup/3308/mysql_user.sql
（2）mysqldump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --flush-logs --master-data=2 --databases test  --events --routines > /mysql/backup/3308/mysqldump.$(date +%Y%m%d_%H%M%S).sql


导入使用mysql客户端即可
mysql -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock < /mysql/backup/3308/mysqldump.$(date +%Y%m%d_%H%M%S).sql

mysqlpump

mysqlpump 的绝大多数参数与 mysqldump 是一样的，整体的使用方法和 mysqldump 没有太多的差异。

并行导出的架构为：队列+线程。允许有多个队列（--parallel-schemas），每个队列下有多个线程（N），每个队列可以绑定1个或者多个数据库（逗号分隔）。mysqlpump的备份是基于表并行的，对于每张表的导出只能是单个线程的。

mysqlpump的选项

mysqlpump 没有选项 --tables --flush-logs --compact --master-data

--default-parallelism=#        设置并行导出的默认并发度

--parallel-schemas=name　　　　[N:]<list of: schema_name separated with ','>.

　　　　　　　　　　　　　　　　　　　 Process tables in specified schemas using separate queue

　　　　　　　　　　　　　　　　　　　　handled by --default-parallelism threads or N threads,

　　　　　　　　　　　　　　　　　　　　if N is specified. Can be used multiple times to specify more parallel processes.

--single-transaction　　　　　　 创建一个单独的事务来导出所有的表

--exclude-databases=name　　　　导出时排除掉某些库，多个库以逗号分隔

--exclude-tables=name　　　　   导出时排除掉某些表，多个表以逗号分隔

--include-databases=name　　　　导出时包含某些库，多个库以逗号分隔

--include-tables=name　　　　　　导出时包含某些表，多个表以逗号分隔

-A, --all-databases 　　　　　　Dump all databases. This is default behaviour if no positional options are specified.

　　　　　　　　　　　　　　　　　　 Specifying this option is mutually exclusive with --databases.

-B, --databases 　　　　　　　　Dump selected databases, specified in positional options.

　　　　　　　　　　　　　　　　　　 Specifying this option is mutually exclusive with --all-databases.

　-d, --skip-dump-rows 只导表结构.

--no-create-db 　　　　　　　　不导出create database语句
-t, --no-create-info 不导出create table语句。只导数据。

　　-C, --compress 　　　　　　　　Use compression in server/client protocol.
　　--compress-output=name　　　　Compresses all output files with LZ4 or ZLIB compression algorithm.

使用中注意点：

（1）5.7.11版本之前无法保证数据的一致性，所以5.7.11之前该工具基本无法使用，

5.7.11之前，--defaut-parallelism>0时与--single-transaction互斥，无法使用并行。直到5.7.11才解决了--single-transaction和--default-parallelism互斥的问题

（2）使用 default-parallelism 并行导出，配合使用选项与 --single-transaction ，可以保证数据的一致性。在并行导出之前，有一个线程加上了全局读锁，然后等所有的并发线程打开事务之后才解锁了表，因此并行导出的时候也是数据一致的。

（3）mysqlpump 的“并行导出”实际上只是基于表级别的并行导出，当存在单个大表的时候，导出的时间会被严重的影响，存在短板效应。

mysqlpump使用举例：

指定表导出：(本例子导出test库的表tt与tt1) （--include-databases --include-tables） (只指定--include-tables 不指定--include-dtaabases，会把其他库create database语句导出来)
mysqlpump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --include-databases test --include-tables test.tt,test.tt1> /mysql/backup/3308/mysqlpump.$(date +%Y%m%d_%H%M%S).sql
指定库导出 （--databases）
     使用默认并行度
mysqlpump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --default-parallelism=6 --single-transaction --set-gtid-purged=OFF --include-databases test,benchmark  > /mysql/backup/3308/mysqlpump.$(date +%Y%m%d_%H%M%S).sql

     指定不同的库使用不同的并行度
mysqlpump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --parallel-schemas=8:benchmark --parallel-schemas=2:test --single-transaction --set-gtid-purged=OFF --include-databases test,benchmark  > /mysql/backup/3308/mysqlpump.$(date +%Y%m%d_%H%M%S).sql


表结构导出
mysqlpump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --databases test benchmark -d  > /mysql/backup/3308/mysqldump.$(date +%Y%m%d_%H%M%S).sql

表数据导出（不带表结构）
mysqldump -uroot -pabc123 -S /mysql/dbdata/data3308/data/mysql3308.sock --single-transaction --set-gtid-purged=OFF --flush-logs --master-data=2 --databases test --no-create-info  > /mysql/backup/3308/mysqldump.$(date +%Y%m%d_%H%M%S).sql

mysqlpump优缺点

优点：

　　　　并行备份数据库和数据库中的对象，比 mysqldump 更高效。

　　　　更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。

备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。

　　　　备份进度可视化。

　　　　导入备份文件时，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度

缺点：

　　只能并行到表级别，如果有一个表数据量特别大那么会存在非常严重的短板效应。

　　导出的数据保存在一个文件中，导入仍旧是单线程，效率较低。

参考博客：

MySQL官方导出工具mysqlpump的使用(导出并行) (yingsoo.com)

MySQL并行导入导出工具——mysqlpump_Hehuyi_In的博客-CSDN博客_mysqlpump

posted on 2023-02-02 09:49 JennyYu 阅读(528) 评论(0) 编辑收藏举报