MySQL Database Backup Methods Season 2 – 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>　　-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语句, 即常说的逻辑备份.
　　若能直接备份数据文件, 效率是否更高, 又是否有这样的方法呢... 那就是XtraBackup了, 稍后会在"MySQL Database BackupMethods Season 3 – XtraBackup"中介绍.