本文主要给大家介绍MySQL数据库备份常用工具之MySQL Data Dumper简析，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在创新互联行业资讯里面关注我的更新文章的。
创新互联是一家专注于网站制作、网站设计与策划设计,栖霞网站建设哪家好?创新互联做网站,专注于网站建设十年,网设计领域的专业建站公司;建站业务涵盖:栖霞等地区。栖霞做网站价格咨询:18982081108
说到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小时售前售后，随时帮您解答问题的。
