MySQL Backup mydumper

2019-01-02 14:16:50来源:cnblogs.com作者:H_Johnny人点击

分享

正文

生产环境中有一实例每天使用mysqldump备份时长达到了2个小时53分钟,接近3个小时,还不算上备份文件归档的时间,这个时间对于逻辑备份来说有点久。为了提高逻辑备份效率,打算替换为使用mydumper。

对比mysqldump,mydumper具有如下特点:

  1. 多线程备份
  2. 备份执行速度更快
  3. 支持备份文件压缩
  4. 支持行级别切块备份

更多关于mydumper的说明,可以查看官方GitHubhttps://github.com/maxbube/mydumper

安装

之前在测试mydumper时有使用过早期版本,是通过编译进行安装的,而mydumper是C语言写的,编译过程中出现了一系列的依赖问题。为了避免出现安装依赖问题,官方从0.9.3版本开始提供了编译后的安装包,建议采用RPM包的方式进行安装。同样通过官方GitHub获取安装包。

本文使用的RPM安装包为:

mydumper-0.9.5-2.el7.x86_64.rpm

安装完成后验证:

# rpm -qa |grep mydumpermydumper-0.9.5-2.x86_64# rpm -ql mydumper-0.9.5-2.x86_64/usr/bin/mydumper/usr/bin/myloader

mydumper:用来备份数据。
myloader:用来还原数据。

本文主要讨论mydumper,查看版本信息:

# mydumper -Vmydumper 0.9.5, built against MySQL 5.7.21-21

主要选项

# mydumper --helpUsage:  mydumper [OPTION?] multi-threaded MySQL dumpingHelp Options:  -?, --help                  Show help optionsApplication Options:  -B, --database              Database to dump  -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)  -O, --omit-from-file        File containing a list of database.table entries to skip, one per line (skips before applying regex option)  -o, --outputdir             Directory to output files to  -s, --statement-size        Attempted size of INSERT statement in bytes, default 1000000  -r, --rows                  Try to split tables into chunks of this many rows. This option turns off --chunk-filesize  -F, --chunk-filesize        Split tables into chunks of this output file size. This value is in MB  -c, --compress              Compress output files  -e, --build-empty-files     Build dump files even if no data available from table  -x, --regex                 Regular expression for 'db.table' matching  -i, --ignore-engines        Comma delimited list of storage engines to ignore  -N, --insert-ignore         Dump rows with INSERT IGNORE  -m, --no-schemas            Do not dump table schemas with the data  -d, --no-data               Do not dump table data  -G, --triggers              Dump triggers  -E, --events                Dump events  -R, --routines              Dump stored procedures and functions  -W, --no-views              Do not dump VIEWs  -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups  --no-backup-locks           Do not use Percona backup locks  --less-locking              Minimize locking time on InnoDB tables.  -l, --long-query-guard      Set long query timer in seconds, default 60  -K, --kill-long-queries     Kill long running queries (instead of aborting)  -D, --daemon                Enable daemon mode  -I, --snapshot-interval     Interval between each dump snapshot (in minutes), requires --daemon, default 60  -L, --logfile               Log file name to use, by default stdout is used  --tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.  --skip-tz-utc  --use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege  --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist  --lock-all-tables           Use LOCK TABLE for all, instead of FTWRL  -U, --updated-since         Use Update_time to dump only tables updated in the last U days  --trx-consistency-only      Transactional consistency only  --complete-insert           Use complete INSERT statements that include column names  -h, --host                  The host to connect to  -u, --user                  Username with the necessary privileges  -p, --password              User password  -a, --ask-password          Prompt For User password  -P, --port                  TCP/IP port to connect to  -S, --socket                UNIX domain socket file to use for connection  -t, --threads               Number of threads to use, default 4  -C, --compress-protocol     Use compression on the MySQL connection  -V, --version               Show the program version and exit  -v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2  --defaults-file             Use a specific defaults file  --ssl                       Connect using SSL  --key                       The path name to the key file  --cert                      The path name to the certificate file  --ca                        The path name to the certificate authority file  --capath                    The path name to a directory that contains trusted SSL CA certificates in PEM format  --cipher                    A list of permissible ciphers to use for SSL encryption
  • -B, --database
    指定dump数据库

  • -T, --tables-list
    指定dump表,多个表用逗号分隔(不排除正则匹配)

  • -O, --omit-from-file
    指定dump需要跳过包含一行或多行database.table格式的文件,跳过dump的优先级大于dump正则匹配

  • -o, --outputdir
    指定dump文件保存目录

  • -s, --statement-size
    指定dump生成insert语句的大小,单位字节,默认是1000000

  • -r, --rows
    把表多少行分割成chunks,这个选项会关闭选项 --chunk-filesize

  • -F, --chunk-filesize
    表分割成chunks的大小,单位为MB,这个指定大小默认为加1MB,如果想切割成每个3MB大小的文件,则指定 -F 2,如果指定 -F 1,则不进行切割,不清楚为什么这么设置

  • -c, --compress
    压缩输出文件

  • -e, --build-empty-files
    表中如果没有数据也创建dump文件

  • -x, --regex
    正则匹配,如'db.table'

  • -i, --ignore-engines
    忽略存储引擎,如有多个用逗号分隔

  • -N, --insert-ignore
    dump文件中不使用INSERT语句

  • -m, --no-schemas
    dump文件中只有表数据而没有表结构信息

  • -d, --no-data
    dump文件中只有表结构而没有表数据

  • -G, --triggers
    dump触发器

  • -E, --events
    dump事件

  • -R, --routines
    dump存储过程和函数

  • -W, --no-views
    不要dump视图

  • -k, --no-locks
    不执行临时的共享读锁,这有可能会导致不一致的备份

  • --no-backup-locks
    不使用Percona备份锁

  • --less-locking
    最小化对InnoDB表的锁定时间

  • -l, --long-query-guard
    设置长查询的时间, 单位秒,默认60秒

  • -K, --kill-long-queries
    Kill长时间执行的查询 (instead of aborting)

  • -D, --daemon
    指定为守护进程模式

  • -I, --snapshot-interval
    每次dump的快照间隔,单位分钟, 需要开启 --daemon,默认60分钟

  • -L, --logfile
    指定输出日志文件名,默认为屏幕标准输出

  • --tz-utc
    在dump一开始加入时区timestamp,数据移动或恢复至不同时区上的数据库适用,默认通过选项 --skip-tz-utc 来禁用

  • --skip-tz-utc
    用法如上

  • --use-savepoints
    通过使用savepoints来避免元数据锁的产生,需要SUPER权限

  • --success-on-1146
    不统计增量错误和警告,除非是表不存在的错误

  • --lock-all-tables
    使用LOCK TABLE锁定所有表,代替FTWRL

  • -U, --updated-since
    指定Update_time天数来dump只在过去几天内更新的表

  • --trx-consistency-only
    事务一致性备份导出

  • --complete-insert
    dump文件中包含完整的INSERT语句,语句中包含所有字段的名称

  • -h, --host
    指定连接host

  • -u, --user
    指定连接用户,需有相应的权限

  • -p, --password
    指定用户密码

  • -a, --ask-password
    指定用户密码提示输入

  • -P, --port
    指定连接port

  • -S, --socket
    指定本地socket文件连接

  • -t, --threads
    指定dump线程数, 默认是4

  • -C, --compress-protocol
    在mysql连接时使用压缩协议

  • -V, --version
    显示程序版本并退出

  • -v, --verbose
    显示更详细的输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认是2

  • --defaults-file
    指定默认参数文件

  • --ssl
    使用SSL连接

  • --key
    指定key file的文件路径

  • --cert
    指定证书文件路径

  • --ca
    指定证书授权文件路径

  • --capath
    指定所有CA颁发的PEM格式文件路径

  • --cipher
    指定允许使用SSL连接加密的密码列表

备份流程

测试MySQL版本为官方社区版5.7.24

(root@localhost) [test] > select version();+------------+| version()  |+------------+| 5.7.24-log |+------------+1 row in set (0.00 sec)

通过开启mysql的general log观察下mydumper在备份过程中做了哪些操作。

  • 开启general log
(root@localhost) [(none)] > show global variables like '%general%';+------------------+---------------------------------+| Variable_name    | Value                           |+------------------+---------------------------------+| general_log      | OFF                             || general_log_file | /data/mysql/3306/data/dbabd.log |+------------------+---------------------------------+2 rows in set (0.00 sec)(root@localhost) [(none)] > set global general_log = 1;Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)] > show global variables like '%general%';+------------------+---------------------------------+| Variable_name    | Value                           |+------------------+---------------------------------+| general_log      | ON                              || general_log_file | /data/mysql/3306/data/dbabd.log |+------------------+---------------------------------+2 rows in set (0.01 sec)
  • 备份test库
# mydumper -h 192.168.58.3 -u root -a -P 3306 -B test -o /data/test/

备份文件结构(以test.t1表为例):

# ll /data/test/total 66728-rw-r--r--. 1 root root      136 Dec 27 16:02 metadata-rw-r--r--. 1 root root       63 Dec 27 16:02 test-schema-create.sql-rw-r--r--. 1 root root      278 Dec 27 16:02 test.t1-schema.sql-rw-r--r--. 1 root root 18390048 Dec 27 16:02 test.t1.sql

通过以上信息可知,备份所有文件都存放在一个目录当中,可以指定。如果没有指定路径,则在运行mydumper命令的当前目录下,生成一个新的目录,名称命名规则为:export-yyyymmdd-HHMMSS 。每个备份目录中主要产生的备份文件为:

  • metadata文件

metadata:备份元数据信息。包含备份开始和备份结束时间,以及MASTER LOG FILEMASTER LOG POS。如果是在从库进行备份,则记录的是 SHOW SLAVE STATUS 中同步到的主库binlog文件及binlog位置。

# cat metadataStarted dump at: 2018-12-27 16:02:06SHOW MASTER STATUS:        Log: mysql-bin.000034        Pos: 154        GTID:Finished dump at: 2018-12-27 16:02:35
  • 库创建语句文件

test-schema-create.sql:test库的创建语句。

# cat test-schema-create.sqlCREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
  • 每张表两个备份文件

test.t1-schema.sql:t1表的创建语句。

test.t1.sql:t1表数据文件,以INSERT语句存储。

如果涉及到大表进行表切片备份的话,会有多个表数据文件。

  • 查看general log
-- 主线程连接数据库,设置临时session级别参数 7   Connect   admin@dbabd on test using TCP/IP 7   Query     SET SESSION wait_timeout = 2147483 7   Query     SET SESSION net_write_timeout = 2147483 7   Query     SHOW PROCESSLIST-- 主线程执行FTWRL获取全局读锁,并开启一致性快照事务,记录当前binlog文件及位置 7   Query     FLUSH TABLES WITH READ LOCK 7   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 7   Query     /*!40101 SET NAMES binary*/ 7   Query     SHOW MASTER STATUS 7   Query     SHOW SLAVE STATUS-- 产生了4个子进程,并且设置会话级事务隔离级别为REPEATABLE READ,4个子线程同时进行dump操作 8   Connect   admin@dbabd on  using TCP/IP 8   Query     SET SESSION wait_timeout = 2147483 8   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 8   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 8   Query     /*!40103 SET TIME_ZONE='+00:00' */ 8   Query     /*!40101 SET NAMES binary*/ 9   Connect   admin@dbabd on  using TCP/IP 9   Query     SET SESSION wait_timeout = 2147483 9   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 9   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 9   Query     /*!40103 SET TIME_ZONE='+00:00' */ 9   Query     /*!40101 SET NAMES binary*/10   Connect   admin@dbabd on  using TCP/IP10   Query     SET SESSION wait_timeout = 214748310   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ10   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */10   Query     /*!40103 SET TIME_ZONE='+00:00' */10   Query     /*!40101 SET NAMES binary*/11   Connect   admin@dbabd on  using TCP/IP11   Query     SET SESSION wait_timeout = 214748311   Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ11   Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */11   Query     /*!40103 SET TIME_ZONE='+00:00' */11   Query     /*!40101 SET NAMES binary*/-- 主线程获取备份库语句和表状态  7   Init DB   test 7   Query     SHOW TABLE STATUS 7   Query     SHOW CREATE DATABASE `test`-- 4个子进程备份库中所有的表 8   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='course' and extra like '%GENERATED%'11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t' and extra like '%GENERATED%'10   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t2' and extra like '%GENERATED%' 9   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1' and extra like '%GENERATED%' 8   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`course` 7   Query     UNLOCK TABLES /* FTWRL */ 7   Quit11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t` 9   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t1`10   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t2`11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t3' and extra like '%GENERATED%'11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`t3`11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='teacher' and extra like '%GENERATED%'11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`teacher` 8   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='teachercard' and extra like '%GENERATED%' 8   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`teachercard` 8   Query     SHOW CREATE TABLE `test`.`course` 8   Query     SHOW CREATE TABLE `test`.`t` 8   Query     SHOW CREATE TABLE `test`.`t1` 8   Query     SHOW CREATE TABLE `test`.`t2` 8   Query     SHOW CREATE TABLE `test`.`t3` 8   Query     SHOW CREATE TABLE `test`.`teacher` 8   Query     SHOW CREATE TABLE `test`.`teachercard` 8   Query     SHOW CREATE TABLE `test`.`v9_pic_tag_content`11   Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='v9_pic_tag_content' and extra like '%GENERATED%' 8   Quit11   Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`v9_pic_tag_content` 9   Quit10   Quit11   Quit  

总结下mydumper的工作流程:

  • 主线程连接MySQL,查询当前服务线程状态确定是否中止dump或是KILL长查询;
  • 通过FTWRL获取全局读锁,确保dump一致性,开启一致性快照事务,查询当前binlog信息写入metadata文件;
  • 创建多个子线程(默认4个),开启一致性快照事务,将session级事务隔离级别设置成REPEATABLE READ;
  • 子线程备份非事务引擎表(non-InnoDB tables);
  • 待子线程备份完非事务引擎表后,主线程执行UNLOCK TABLES释放全局读锁;
  • 子线程备份事务引擎表(InnoDB tables);
  • (如有)子线程备份函数、存储过程、触发器和视图;
  • dump过程结束。

用法示例

  • 备份全库

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -o /data/backupdir
  • 备份某个库

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -o /data/test/
  • 备份多个库(可使用正则匹配)

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -x '^(test/.|test2/.)' -o /data/
  • 不备份某(几)个库

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -x '^(?!(mysql/.|sys/.))' -o /data/
  • 备份某(几)张表

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -T test.t1,test2.t3 -o /data/
  • 不备份某(几)张表

    通过正则匹配

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -x '^(?!test.t2)' -o /data/test/ 

    通过选项 -O, --omit-from-file

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -O nodump.file -o /data/test/
  • 切割表数据文件,指定每份文件包含行数

    test.t2表有100万行:

    (root@localhost) [test] > select count(*) from t2;+----------+| count(*) |+----------+|  1000000 |+----------+1 row in set (0.45 sec)

    现在指定备份test.t2表分割成每个chunks包含的行数为10万行:

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -T test.t2 -r 100000 -o /data/test/

    查看表备份文件:

    # ls /data/test/metadata           test.t2.00001.sql  test.t2.00003.sql  test.t2.00005.sql  test.t2.00007.sql  test.t2.00009.sqltest.t2.00000.sql  test.t2.00002.sql  test.t2.00004.sql  test.t2.00006.sql  test.t2.00008.sql  test.t2-schema.sql
  • 切割表数据文件,指定每份文件大小

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -T test.t2 -F 2 -o /data/test/

    查看表备份文件:

    # ll -h /data/test/total 18M-rw-r--r--. 1 root root  141 Dec 27 16:32 metadata-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00001.sql-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00002.sql-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00003.sql-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00004.sql-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00005.sql-rw-r--r--. 1 root root 2.9M Dec 27 16:32 test.t2.00006.sql-rw-r--r--. 1 root root 381K Dec 27 16:32 test.t2.00007.sql-rw-r--r--. 1 root root  278 Dec 27 16:32 test.t2-schema.sql
  • 对备份文件进行压缩

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -c -o /data/test/

    没压缩之前的大小:

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -o /data/test/# du /data/test  --max-depth=1 -h53M     /data/test

    压缩之后的大小:

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -c -o /data/test/# du /data/test/  --max-depth=1 -h22M     /data/test/
  • 对空表备份也生成数据文件

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -e -o /data/test/

    这样即使是张空表,不仅备份会生成table-schema.sql文件,也会生成table.sql文件。

  • 指定备份子线程数

    # mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test -e -t 8 -o /data/test/

    没指定 -t 选项默认是4个子线程,可以根据机器配置进行适当增加子线程数加快备份效率。

参考

  • https://github.com/maxbube/mydumper
  • http://www.ywnds.com/?p=7267
  • http://blog.51cto.com/l0vesql/2057223

☆〖本人水平有限,文中如有错误还请留言批评指正!〗☆

相关文章

    无相关信息

微信扫一扫

第七城市微信公众平台