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,--passwordUser 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,--verboseVerbosity 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 , 同时结合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-locksDo 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-lockingMinimize 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-savepointsUse savepoints toreduce metadata locking issues, needs SUPER privilege
该选项含义是, 尽快释放元数据锁, 其它过程和1相同.
1601611 Query SAVEPOINT mydumper
1601611 Query ROLLBACK TO SAVEPOINT mydumper
5.--lock-all-tablesUse 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-onlyTransactionalconsistency 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-guardSet 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"中介绍.
页:
[1]