本文主要给大家介绍MySQL数据库备份常用工具之MySQL Data Dumper简析，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在开发云行业资讯里面关注我的更新文章的。说到MySQL数据库的备份, MySQL Data Dumper(项目)也是常用的工具, 其有两个可执行程序: mydumper, 负责导出数据; myloader, 负责导入数据. mydumper相对于mysqldump, 多了些特性, 在下面分析选项的过程中能体会到.由于是第三方工具, 先来看下安装, 及可能遇到的问题.a. mydumper需要依赖一些开发库, 使用yum安装即可.root@db01: ~# yum install glib* zlib* pcre* -yb. 添加连接MySQL需要的动态链接库.root@db01: ~# cat /etc/ld.so.conf.d/mysql.conf/opt/mysql/libroot@db01: ~#ldconfigroot@db01: ~#ldconfig –print-cache | grep ‘mysql’ libmysqlclient.so.18 (libc6,x86-64)=> /opt/mysql/lib/libmysqlclient.so.18root@db01: ~# ls -l /opt/mysql/lib/libmysqlclient.so.18lrwxrwxrwx 1 rootroot 26 Aug 25 14:21 /opt/mysql/lib/libmysqlclient.so.18 ->libmysqlclient_r.so.18.1.0c. 编译安装.root@db01: ~# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mydumper
root@db01: ~# make install添加可执行命令的路径到环境变量PATH中.mysql@db01: ~$grep ‘PATH’ .bash_profilePATH=/usr/local/mydumper/bin:/opt/mysql/bin/:$PATH:$HOME/binexport PATHd. 在命令行敲入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: ~$mydumpermydumper: 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 connectionDebug 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 2Filtering 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 functionsTransactional Options 组该组主要涉及到备份时如何加锁, 下面使用该命令行进行测试mydumper –regex ‘^(?!(mysql))’–threads=1 [Option], 同时结合general log, 看mydumper是如何工作的. 1. 先看不加选项时, 是什么情况.Master线程, 获取GLOBAL READ LOCK, 开启一致性读事物, 得到二进制日志的坐标.1587512Query FLUSH TABLES WITH READ LOCK1587512Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */1587512Query SHOW MASTER STATUSDump线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物进行非事物数据表的备份.1587513Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ1587513Query 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 STATUSDump线程, 设置事物隔离级别为REPEATABLE READ, 开启开发云主机域名一致性读事物进行数据表的备份.1586767Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ1586767Query 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 LOCK1588054Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */1588054Query SHOW MASTER STATUSDump2线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物.1588056Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ1588056Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */Dump1线程, 锁定非事物数据表.1588055 Query LOCK TABLES `product`.`pr1` READ LOCAL,`product`.`pr2` READ LOCALMaster线程, 释放锁.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 mydumper1601611 Query ROLLBACK TO SAVEPOINT mydumper5.–lock-all-tables Use LOCK TABLE forall, instead of FTWRLMaster线程, 获取有那些数据库和数据库表, 然后把需要备份的数据表加锁, 开启一致性读事物, 再后得到二进制日志的坐标.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` READ1586979Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */1586979Query SHOW MASTER STATUSDump线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物进行非事物数据表的备份.1586980Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ1586980Query 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 tablesMaster线程, 获取GLOBAL READ LOCK, 开启一致性读事物, 得到二进制日志的坐标.1588315Query FLUSH TABLES WITH READ LOCK1588315Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */1588315Query SHOW MASTER STATUSDump线程, 设置事物隔离级别为REPEATABLE READ, 开启一致性读事物.1588316Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ1588316Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */Master线程, 释放锁.1588315Query UNLOCK TABLES /* trx-only */Dump线程, 备份数据表.此方式, 从加锁到释放锁, 时间最短, 效率最高.经上面的分析, 可得到加锁过程影响大小顺序如下:–lock-all-tables> 不加该组选项 = –use-savepoints >–less-locking > –trx-consistency-only > –no-locksPerformance 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=32. 备份全部数据库.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小时售前售后，随时帮您解答问题的。
use in in mysql,if the field is indexed,the index is still useful,but when the data quantity is small relative to the param in “in…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
