MySQL数据库备份常用工具之MySQL Data Dumper简析

本文主要给大家介绍MySQL数据库备份常用工具之MySQL Data Dumper简析，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在亿速云行业资讯里面关注我的更新文章的。

说到MySQL数据库的备份, MySQL Data Dumper(项目)也是常用的工具, 其有两个可执行程序: mydumper, 负责导出数据; myloader,  负责导入数据. mydumper相对于mysqldump, 多了些特性, 在下面分析选项的过程中能体会到.

由于是第三方工具, 先来看下安装, 及可能遇到的问题.

a. mydumper需要依赖一些开发库, 使用yum安装即可.

root@db01: ~# yum install glib* zlib* pcre* -y

b. 添加连接MySQL需要的动态链接库.

root@db01: ~# cat /etc/ld.so.conf.d/mysql.conf

/opt/mysql/lib

root@db01: ~#ldconfig

root@db01: ~#ldconfig --print-cache | grep 'mysql'

         libmysqlclient.so.18 (libc6,x86-64)=> /opt/mysql/lib/libmysqlclient.so.18

root@db01: ~# ls -l /opt/mysql/lib/libmysqlclient.so.18

lrwxrwxrwx 1 rootroot 26 Aug 25 14:21 /opt/mysql/lib/libmysqlclient.so.18 ->libmysqlclient_r.so.18.1.0

c. 编译安装.

root@db01: ~# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mydumper

root@db01: ~# make install

添加可执行命令的路径到环境变量PATH中.

mysql@db01: ~$grep 'PATH' .bash_profile

PATH=/usr/local/mydumper/bin:/opt/mysql/bin/:$PATH:$HOME/bin

export PATH

d. 在命令行敲入mydumper回车, 看下面的返回信息, 安装是正常的.

mysql@db01: ~$mydumper

**(mydumper:723): CRITICAL **: Error connecting to database: Access denied foruser 'root'@'localhost' (using password:NO)

mysql@db01: ~$myloader

**(myloader:5288): CRITICAL **: a directory needs to be specified, see --help

若出现如下报错, 可能是步骤b有问题.

mysql@db01: ~$mydumper

mydumper: errorwhile loading shared libraries: libmysqlclient.so.18: cannot open shared objectfile: No such file or directory

下面是演示用到的数据库数据表的信息:

(root@localhost)[(none)]> SELECT table_schema, table_name, engine FROM information_schema.tables WHERE (engine = 'InnoDB' OR engine = 'MyISAM') AND table_schema NOT IN('mysql', 'performance_schema' ,'information_schema');

+--------------+------------+--------+

| table_schema |table_name | engine |

+--------------+------------+--------+

| product      | pr1        | MyISAM |

| product      | pr2        | MyISAM |

| product      | pr3        | InnoDB |

| stage        | st1        | InnoDB |

| stage        | st2        | InnoDB |

| test         | tb1        | InnoDB |

| test         | tb2        |InnoDB |

+--------------+------------+--------+

7 rows in set(0.01 sec)

mydumper的选项也不少, 按照分析mysqldump一样, 将其分成若干组, 看看重点选项的含义.

Connection Options组

该组选项指明了如何连接数据库.

-h, --host      The host to connect to

-u, --user      Username with privileges to run the dump

-p,--password  User password

-P, --port       TCP/IPport to connect to

-S, --socket     domainsocket file to use for connection

Debug Options 组

改组指明了日志放在哪里, 以及日志的级别.

-L,--logfile   Log file name to use, by defaultstdout is used

-v,--verbose  Verbosity of output, 0 =silent, 1 = errors, 2 = warnings, 3 = info, default 2

Filtering Options组

改组指明了备份哪些数据库对象, 以及对备份文件做什么附加处理(压缩, 分割等).

-B,--database        Database to dump

-T,--tables-list        Comma delimitedtable list to dump (does not exclude regex option)

-o,--outputdir        Directory to outputfiles to

-s,--statement-size    Attempted size ofINSERT statement in bytes, default 1000000

-r, --rows            Try to split tables into chunks ofthis many rows. This option turns off --chunk-filesize

-F,--chunk-filesize      Split tables into chunks of this output filesize. This value is in MB

-c,--compress         Compress output files

-e,--build-empty-files   Build dump files even if no data availablefrom table

-x, --regex             Regular expression for 'db.table'matching

-m,--no-schemas       Do not dump tableschemas with the data

-d,--no-data           Do not dump tabledata

-G,--triggers           Dump triggers

-E, --events            Dump events

-R, --routines           Dump stored procedures and functions

Transactional Options 组

该组主要涉及到备份时如何加锁, 下面使用该命令行进行测试mydumper --regex '^(?!(mysql))'--threads=1 [Option], 同时结合general log, 看mydumper是如何工作的.

1. 先看不加选项时, 是什么情况.

Master线程, 获取GLOBAL READ LOCK, 开启一致性读事物, 得到二进制日志的坐标.

1587512Query   FLUSH TABLES WITH READ LOCK

1587512Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1587512Query   SHOW MASTER STATUS

Dump线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物进行非事物数据表的备份.

1587513Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1587513Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1587513Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr1`

1587513Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr2`

Master线程, 待Dump线程备份完非事物数据表后, 释放锁.

1587512Query   UNLOCK TABLES /* FTWRL */

Dump线程, 继续其它事物数据表的备份.

2. -k,--no-locks  Do not execute the temporaryshared read lock.  WARNING: This willcause inconsistent backups

使用该选项时, mydumper会有如下类似提示:

**(mydumper:4095): WARNING **: Executing in no-locks mode, snapshot will notbeconsistent

其主要作用过程如下:

Master线程, 开启一致性读事物, 得到二进制日志的坐标.

1586766Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1586766Query   SHOW MASTER STATUS

Dump线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物进行数据表的备份.

1586767Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1586767Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

该过程由于未执行FLUSH TABLES WITH READ LOCK, 得到的二进制日志坐标可能不准确; (多个)线程开启一致性读事物时, 数据表可能会有变动, 这两点会造成备份数据不一致.                                           

3.--less-locking  Minimize locking time onInnoDB tables.

Master线程, 获取GLOBAL READ LOCK, 开启一致性读事物, 得到二进制日志的坐标.

1588054Query   FLUSH TABLES WITH READ LOCK

1588054Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1588054Query   SHOW MASTER STATUS

Dump2线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物.

1588056Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1588056Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

Dump1线程, 锁定非事物数据表.

1588055 Query   LOCK TABLES `product`.`pr1` READ LOCAL,`product`.`pr2` READ LOCAL

Master线程, 释放锁.

1588054Query   UNLOCK TABLES /* FTWRL */

Dump1线程, 备份非事物数据表.

1588055Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr1`

1588055Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr2`

Dump1线程, 备份完成后, 释放锁.

1588055Query   UNLOCK TABLES /* Non Innodb */

Dump2线程, 继续其它事物数据表的备份.

4.--use-savepoints  Use savepoints toreduce metadata locking issues, needs SUPER privilege

该选项含义是, 尽快释放元数据锁, 其它过程和1相同.

1601611 Query         SAVEPOINT mydumper

1601611 Query         ROLLBACK TO SAVEPOINT mydumper

5.--lock-all-tables  Use LOCK TABLE forall, instead of FTWRL

Master线程, 获取有那些数据库和数据库表, 然后把需要备份的数据表加锁, 开启一致性读事物, 再后得到二进制日志的坐标.

1586979Query   SELECT TABLE_SCHEMA, TABLE_NAMEFROM information_schema.TABLES WHERE TABLE_TYPE ='BASE TABLE' AND TABLE_SCHEMANOT IN ('information_schema', 'performance_schema', 'data_dictionary') AND NOT(TABLE_SCHEMA = 'mysql' AND (TABLE_NAME = 'slow_log' OR TABLE_NAME ='general_log'))

1586979Query   LOCK TABLE `product`.`pr1` READ,`product`.`pr2` READ, `product`.`pr3` READ, `stage`.`st1` READ, `stage`.`st2`READ, `test`.`tb1` READ, `test`.`tb2` READ

1586979Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1586979Query   SHOW MASTER STATUS

Dump线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物进行非事物数据表的备份.

1586980Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1586980Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1586980Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr1`

1586980Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr2`

Master线程, 待Dump线程备份完非事物数据表后, 释放锁.

1586979Query   UNLOCK TABLES /* FTWRL */

Dump线程, 继续其它事物数据表的备份.

此种加锁方式, 若数据库数据表比较多时, 加锁效率不高.

6.--trx-consistency-only  Transactionalconsistency only 

使用该选项时, mydumper会有如下类似提示:

**(mydumper:2573): WARNING **: Using trx_consistency_only, binlog coordinateswill not be accurate if you are writing to non transactional tables

Master线程, 获取GLOBAL READ LOCK, 开启一致性读事物, 得到二进制日志的坐标.

1588315Query   FLUSH TABLES WITH READ LOCK

1588315Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1588315Query   SHOW MASTER STATUS

Dump线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物.

1588316Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1588316Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

Master线程, 释放锁.

1588315Query   UNLOCK TABLES /* trx-only */

Dump线程, 备份数据表.

此方式, 从加锁到释放锁, 时间最短, 效率最高.

经上面的分析, 可得到加锁过程影响大小顺序如下:

--lock-all-tables> 不加该组选项 = --use-savepoints >--less-locking > --trx-consistency-only > --no-locks

Performance Options 组

该组指定了线程数量, 和如何处理长查询.

-t,--threads          Number of threads touse, default 4

-l,--long-query-guard  Set long query timerin seconds, default 60

-K,--kill-long-queries   Kill long runningqueries (instead of aborting)

参数了解完了, 看两个实际工作中例子.

1. 备份除数据库mysql之外的其它数据库.

mysql@db01:~/dbbackup$ mydumper --outputdir=20170826 --compress --build-empty-files--regex '^(?!(mysql))' --triggers --events --routines --logfile=error.txt--use-savepoints --trx-consistency-only --threads=4 --verbose=3

2. 备份全部数据库.

mysql@db01:~/dbbackup$ mydumper --outputdir=20170826 --compress --build-empty-files--triggers --events --routines --long-query-guard=60 --kill-long-queries--logfile=error.txt --use-savepoints --trx-consistency-only --threads=4--verbose=3

经过选项分析和实践过程, 总结下mydumper的特点:

1. 多线程备份, 可指定线程数量, 其也是速度优于mysqldump的关键.

2. 对于备份数据一致性方面考虑较多, 主要体现在非事物数据表的备份上.

3. 分析选项时, 没有指定字符集的, 查看general log后, 发现是这样处理的/*!40101 SET NAMES binary*/, 即省去了转换字符集的开销.

4. 提供了如何应对长查询的选项.

myloader并没有太多需要说明的, 看下选项解释, 实践下即可.

mydumper在备份时, 效率有了很大提升, 但其终究还是将数据转化为SQL语句, 即常说的逻辑备份.

看了以上关于MySQL数据库备份常用工具之MySQL Data Dumper简析，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。