MySQL Database Backup Methods Season 2 – MySQL Data Dumper
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL Database Backup Methods Season 2 – MySQL Data Dumper，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9208字，纯文字阅读大概需要14分钟。
内容图文
说到 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 语句 , 即常说的逻辑备份 .
若能直接备份数据文件 , 效率是否更高 , 又是否有这样的方法呢 ... 那就是 XtraBackup 了 , 稍后会在 "MySQL Database BackupMethods Season 3 – XtraBackup" 中介绍 .
本文出自 “感动自己” 博客，请务必保留此出处http://coveringindex.blog.51cto.com/12533373/1959683
原文：http://coveringindex.blog.51cto.com/12533373/1959683
内容总结
以上是互联网集市为您收集整理的MySQL Database Backup Methods Season 2 – MySQL Data Dumper全部内容，希望文章能够帮你解决MySQL Database Backup Methods Season 2 – MySQL Data Dumper所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。