mysql中mysqldumper怎么用
这篇文章主要为大家展示了“mysql中mysqldumper怎么用”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“mysql中mysqldumper怎么用”这篇文章吧。
并行处理：
使用mydumper命令的局限在与他是一个单线程进程。但开源的mydumper是一个很好的替代。
mydumper（http://www.mysqldumper.org/）是一个在GNU GPLv3许可下发布的高性能MySQL备份和恢复工具集。mydumper是多线程的，他创建一个mysql备份就比随mysql发布的mysqldump工具要快得多。mydumper也有从源端服务器恢复二进制日志的能力。
mydumper的优点：
多线程，可以是转存数据快很多。
mydumper的输出已于管理和分析，因为他的表和元数据是分开的单独文件。
所有线程都维护有一直的快照，这边提供了精准的主从位置。
Mydumper支持Perl正则表达式，这样就既可以包括是数据库名和报名的模式匹配，也可以配置这种匹配。
通过名为myloader的多线程工具，mydumper工具集也可以从mydumper备份中恢复数据。
mydumper必须在源代码上进行编辑。这就需要带有C++编辑器的系统。另外还需要如下组件：
Cmake、带有开发包的Glib2、带有开发包的PCRE、mysql的客户端库和开发工具。
安装步骤如下：
依赖包：Fedora, RedHat and CentOS:yum install glib2-devel* mysql-devel* zlib-devel* pcre-devel* openssl-devel* -y
tar -zxvf mydumper-0.2.3.tar.gz
cd mydumper-0.2.0.3/
cmake .
make
./mydumper -help
sudo cp mydumper /usr/local/bin
简单用法：
mkdir /mysql/bakcup/mydumper
cd /mysql/backup/mydumper
time mydumper
[root@d4jtarmsvurd01mydumper_bak]#pwd
/mysql/mydumper_bak
[root@d4jtarmsvurd01mydumper_bak]#lsex*
metadatamysql.proc-schema.sql
mysql-schema-create.sqlmysql.procs_priv-schema.sql
mysql.columns_priv-schema.sqlmysql.servers-schema.sql
mysql.db-schema.sqlmysql.tables_priv-schema.sql
mysql.db.sqlmysql.time_zone-schema.sql
mysql.event-schema.sqlmysql.time_zone_leap_second-schema.sql
mysql.func-schema.sqlmysql.time_zone_name-schema.sql
mysql.help_category-schema.sqlmysql.time_zone_transition-schema.sql
mysql.help_category.sqlmysql.time_zone_transition_type-schema.sql
....................
当在冗长模式中运行时，会产生额外的输出，单所提供的信息中并不包括完整的输出目录：
[root@d4jtarmsvurd01mydumper_bak]#mydumper-v3
**Message:ConnectedtoaMySQLserver
**Message:Starteddumpat:2016-07-0515:16:56
**Message:Writtenmasterstatus
**Message:Thread1connectedusingMySQLconnectionID1367
**Message:Thread2connectedusingMySQLconnectionID1368
**Message:Thread3connectedusingMySQLconnectionID1369
**Message:Thread4connectedusingMySQLconnectionID1370
**Message:Thread2dumpingdatafor`mysql`.`db`
**Message:Thread1dumpingdatafor`mysql`.`columns_priv`
**Message:Thread3dumpingdatafor`mysql`.`event`
**Message:Emptytablemysql.event
**Message:Emptytablemysql.columns_priv
**Message:Thread2dumpingdatafor`mysql`.`func`
**Message:Thread1dumpingdatafor`mysql`.`help_category`
**Message:Thread3dumpingdatafor`mysql`.`help_keyword`
**Message:Thread1dumpingdatafor`mysql`.`help_relation`
**Message:Emptytablemysql.func
**Message:Thread2dumpingdatafor`mysql`.`help_topic`
**Message:Thread3dumpingdatafor`mysql`.`host`
**Message:Thread1dumpingdatafor`mysql`.`ndb_binlog_index`
**Message:Emptytablemysql.ndb_binlog_index
**Message:Thread1dumpingdatafor`mysql`.`plugin`
**Message:Emptytablemysql.plugin
**Message:Thread1dumpingdatafor`mysql`.`proc`
**Message:Emptytablemysql.proc
**Message:Thread1dumpingdatafor`mysql`.`procs_priv`
**Message:Emptytablemysql.host
**Message:Thread3dumpingdatafor`mysql`.`servers`
**Message:Emptytablemysql.servers
**Message:Thread3dumpingdatafor`mysql`.`tables_priv`
**Message:Emptytablemysql.procs_priv
**Message:Thread1dumpingdatafor`mysql`.`time_zone`
**Message:Emptytablemysql.time_zone
**Message:Thread1dumpingdatafor`mysql`.`time_zone_leap_second`
**Message:Emptytablemysql.time_zone_leap_second
**Message:Thread1dumpingdatafor`mysql`.`time_zone_name`
**Message:Emptytablemysql.time_zone_name
**Message:Thread1dumpingdatafor`mysql`.`time_zone_transition`
**Message:Emptytablemysql.tables_priv
**Message:Thread3dumpingdatafor`mysql`.`time_zone_transition_type`
**Message:Emptytablemysql.time_zone_transition
**Message:Thread1dumpingdatafor`mysql`.`user`
**Message:Thread1dumpingdatafor`sanxing`.`sanxing`
**Message:Emptytablemysql.time_zone_transition_type
**Message:Thread3dumpingdatafor`test`.`guijian`
**Message:Thread3dumpingschemafor`mysql`.`columns_priv`
**Message:Thread1dumpingschemafor`mysql`.`db`
**Message:Thread1dumpingschemafor`mysql`.`event`
**Message:Thread1dumpingschemafor`mysql`.`func`
**Message:Thread1dumpingschemafor`mysql`.`help_category`
**Message:Thread1dumpingschemafor`mysql`.`help_keyword`
**Message:Thread3dumpingschemafor`mysql`.`help_relation`
**Message:Thread1dumpingschemafor`mysql`.`help_topic`
**Message:Thread3dumpingschemafor`mysql`.`host`
**Message:Thread1dumpingschemafor`mysql`.`ndb_binlog_index`
**Message:Thread1dumpingschemafor`mysql`.`plugin`
**Message:Thread3dumpingschemafor`mysql`.`proc`
**Message:Thread1dumpingschemafor`mysql`.`procs_priv`
**Message:Thread1dumpingschemafor`mysql`.`servers`
**Message:Thread1dumpingschemafor`mysql`.`tables_priv`
**Message:Thread3dumpingschemafor`mysql`.`time_zone`
**Message:Thread1dumpingschemafor`mysql`.`time_zone_leap_second`
**Message:Thread3dumpingschemafor`mysql`.`time_zone_name`
**Message:Thread1dumpingschemafor`mysql`.`time_zone_transition`
**Message:Thread3dumpingschemafor`mysql`.`time_zone_transition_type`
**Message:Thread3dumpingschemafor`mysql`.`user`
**Message:Thread3dumpingschemafor`sanxing`.`sanxing`
**Message:Thread3dumpingschemafor`test`.`guijian`
**Message:Non-InnoDBdumpcomplete,unlockingtables
**Message:Thread3shuttingdown
**Message:Thread1shuttingdown
**Message:Thread4shuttingdown
**Message:Thread2shuttingdown
**Message:Finisheddumpat:2016-07-0515:16:56
[root@d4jtarmsvurd01mydumper_bak]#ls
export-20160705-151255export-20160705-151656
[root@d4jtarmsvurd01mydumper_bak]#
用法：
[root@d4jtarmsvurd01mydumper_bak]#mydumper--help
Usage:
mydumper[OPTION...]multi-threadedMySQLdumping
HelpOptions:
-?,--helpShowhelpoptions
ApplicationOptions:
-B,--databaseDatabasetodump
-T,--tables-listCommadelimitedtablelisttodump(doesnotexcluderegexoption)
-o,--outputdirDirectorytooutputfilesto
-s,--statement-sizeAttemptedsizeofINSERTstatementinbytes,default1000000
-r,--rowsTrytosplittablesintochunksofthismanyrows.Thisoptionturnsoff--chunk-filesize
-F,--chunk-filesizeSplittablesintochunksofthisoutputfilesize.ThisvalueisinMB
-c,--compressCompressoutputfiles
-e,--build-empty-filesBuilddumpfilesevenifnodataavailablefromtable
-x,--regexRegularexpressionfor'db.table'matching
-i,--ignore-enginesCommadelimitedlistofstorageenginestoignore
-m,--no-schemasDonotdumptableschemaswiththedata
-d,--no-dataDonotdumptabledata
-G,--triggersDumptriggers
-E,--eventsDumpevents
-R,--routinesDumpstoredproceduresandfunctions
-k,--no-locksDonotexecutethetemporarysharedreadlock.WARNING:Thiswillcauseinconsistentbackups
--less-lockingMinimizelockingtimeonInnoDBtables.
-l,--long-query-guardSetlongquerytimerinseconds,default60
-K,--kill-long-queriesKilllongrunningqueries(insteadofaborting)
-D,--daemonEnabledaemonmode
-I,--snapshot-intervalIntervalbetweeneachdumpsnapshot(inminutes),requires--daemon,default60
-L,--logfileLogfilenametouse,bydefaultstdoutisused
--tz-utcSETTIME_ZONE='+00:00'attopofdumptoallowdumpingofTIMESTAMPdatawhenaserverhasdataindifferenttimezonesordataisbeingmovedbetweenserverswithdifferenttimezones,defaultstoonuse--skip-tz-utctodisable.
--skip-tz-utc
--use-savepointsUsesavepointstoreducemetadatalockingissues,needsSUPERprivilege
--success-on-1146NotincrementerrorcountandWarninginsteadofCriticalincaseoftabledoesn'texist
--lock-all-tablesUseLOCKTABLEforall,insteadofFTWRL
-U,--updated-sinceUseUpdate_timetodumponlytablesupdatedinthelastUdays
--trx-consistency-onlyTransactionalconsistencyonly
-h,--hostThehosttoconnectto
-u,--userUsernamewithprivilegestorunthedump
-p,--passwordUserpassword
-P,--portTCP/IPporttoconnectto
-S,--socketUNIXdomainsocketfiletouseforconnection
-t,--threadsNumberofthreadstouse,default4
-C,--compress-protocolUsecompressionontheMySQLconnection
-V,--versionShowtheprogramversionandexit
-v,--verboseVerbosityofoutput,0=silent,1=errors,2=warnings,3=info,default2
[root@d4jtarmsvurd01mydumper_bak]#
通过正则表达式选项排除mysql和测试的模式对象：
mydumper --user root --regex '^(?!(mysql|test))'
压缩：默认情况下，所有处处文件都是不压缩的。但是通过使用-c选项，所有文件都可以被压缩。
[root@d4jtarmsvurd01mydumper_bak]#mydumper-c
[root@d4jtarmsvurd01mydumper_bak]#ls-lrt
total12
drwx------2rootroot4096Jul515:12export-20160705-151255
drwx------2rootroot4096Jul515:16export-20160705-151656
drwx------2rootroot4096Jul515:20export-20160705-152048
[root@d4jtarmsvurd01mydumper_bak]#cdexport-20160705-152048/
[root@d4jtarmsvurd01export-20160705-152048]#ls
metadatamysql.proc-schema.sql.gz
mysql-schema-create.sql.gzmysql.procs_priv-schema.sql.gz
mysql.columns_priv-schema.sql.gzmysql.servers-schema.sql.gz
mysql.db-schema.sql.gzmysql.tables_priv-schema.sql.gz
mysql.db.sql.gzmysql.time_zone-schema.sql.gz
mysql.event-schema.sql.gzmysql.time_zone_leap_second-schema.sql.gz
................
mydumper生产多个与元数据、表数据、表模式和二进制日志相关的文件。
.metadata文件中保存着转存的开始和结束时间以及主二进制日志的位置。当执行转存时，一个.metadata文件边被创建到输出目录中：
备份目录中的素有文件：
[root@d4jtarmsvurd01export-20160705-151656]#ls
metadatamysql.proc-schema.sql
mysql-schema-create.sqlmysql.procs_priv-schema.sql
mysql.columns_priv-schema.sqlmysql.servers-schema.sql
mysql.db-schema.sqlmysql.tables_priv-schema.sql
mysql.db.sqlmysql.time_zone-schema.sql
mysql.event-schema.sqlmysql.time_zone_leap_second-schema.sql
mysql.func-schema.sqlmysql.time_zone_name-schema.sql
mysql.help_category-schema.sqlmysql.time_zone_transition-schema.sql
mysql.help_category.sqlmysql.time_zone_transition_type-schema.sql
mysql.help_keyword-schema.sqlmysql.user-schema.sql
mysql.help_keyword.sqlmysql.user.sql
mysql.help_relation-schema.sqlsanxing-schema-create.sql
mysql.help_relation.sqlsanxing.sanxing-schema.sql
mysql.help_topic-schema.sqlsanxing.sanxing.sql
mysql.help_topic.sqltest-schema-create.sql
mysql.host-schema.sqltest.guijian-schema.sql
mysql.ndb_binlog_index-schema.sqltest.guijian.sql
mysql.plugin-schema.sql
[root@d4jtarmsvurd01export-20160705-151656]#moremetadata
Starteddumpat:2016-07-0515:16:56
SHOWMASTERSTATUS:
Log:mysql-bin.000002
Pos:106
GTID:(null)
Finisheddumpat:2016-07-0515:16:56
[root@d4jtarmsvurd01export-20160705-151656]#pwd
/mysql/mydumper_bak/export-20160705-151656
[root@d4jtarmsvurd01export-20160705-151656]#
在使用mydumper的时候可以通过show processlist来监控线程。
可以使用两种不同的方式存储表数据：将所有表数据村委一个文件或者将一个表的数据块存为多个文件，如果未指定--row选项，则将为每个表创建一个文件，命令规则类似于database.table.sql。
关于mydumper生成文件的类型如下：
db_name.table_name-schema_name.sql ---表结构文件
db_name.table_name.sql ---表数据文件
db_name-schema-create.sql ---数据库创建脚本
mydumper的还原工具为：myloader，使用说明如下：
[root@d4jtarmsvurd01mydumper_bak]#myloader--help
Usage:
myloader[OPTION...]multi-threadedMySQLloader
HelpOptions:
-?,--helpShowhelpoptions
ApplicationOptions:
-d,--directoryDirectoryofthedumptoimport
-q,--queries-per-transactionNumberofqueriespertransaction,default1000
-o,--overwrite-tablesDroptablesiftheyalreadyexist
-B,--databaseAnalternativedatabasetorestoreinto
-s,--source-dbDatabasetorestore
-e,--enable-binlogEnablebinaryloggingoftherestoredata
-h,--hostThehosttoconnectto
-u,--userUsernamewithprivilegestorunthedump
-p,--passwordUserpassword
-P,--portTCP/IPporttoconnectto
-S,--socketUNIXdomainsocketfiletouseforconnection
-t,--threadsNumberofthreadstouse,default4
-C,--compress-protocolUsecompressionontheMySQLconnection
-V,--versionShowtheprogramversionandexit
-v,--verboseVerbosityofoutput,0=silent,1=errors,2=warnings,3=info,default2
恢复测试：
1、备份数据库：
[root@d4jtarmsvurd01mydumper_bak]#mydumper-uroot-proot123-v3
2、删除其中的某一个数据库：
mysql>dropdatabasesanxing;
QueryOK,2rowsaffected(0.06sec)
3、开始恢复其中的一个数据库：
[root@d4jtarmsvurd01mydumper_bak]#myloader-d/mysql/mydumper_bak/export-20170224-151158-o-Bsanxing-uroot-p'root123'
4、检查恢复情况：
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|guijian|
|mysql|
|sanxing|
|test|
+--------------------+
5rowsinset(0.00sec)
注意原本有两个表的数据库，在恢复时指定了所有数据库备份的目录，此时所有的数据库表都被恢复到了，删除的库，（故此，在恢复的时候注意要使用单个数据库的备份，即什么样的备份能恢复什么样的数据库）
mysql>usesanxing;
Databasechanged
mysql>showtables;
+---------------------------+
|Tables_in_sanxing|
+---------------------------+
|columns_priv|
|db|
|event|
|func|
|guijian|
|guijian01|
|help_category|
|help_keyword|
|help_relation|
|help_topic|
|host|
|jiehun|
|ndb_binlog_index|
|plugin|
|proc|
|procs_priv|
|sanxing|
|servers|
|tables_priv|
|time_zone|
|time_zone_leap_second|
|time_zone_name|
|time_zone_transition|
|time_zone_transition_type|
|user|
+---------------------------+
25rowsinset(0.00sec)
mysql>
单独测试恢复：
mysql>useguijian;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>showtables;
+-------------------+
|Tables_in_guijian|
+-------------------+
|test|
|test01|
+-------------------+
2rowsinset(0.00sec)
mysql>exit
Bye
[root@d4jtarmsvurd01mydumper_bak]#mydumper-uroot-proot123-Bguijian-v3
**Message:ConnectedtoaMySQLserver
**Message:Starteddumpat:2017-02-2415:28:18
**Message:Writtenmasterstatus
**Message:Thread1connectedusingMySQLconnectionID2807
**Message:Thread2connectedusingMySQLconnectionID2808
**Message:Thread3connectedusingMySQLconnectionID2809
**Message:Thread4connectedusingMySQLconnectionID2810
**Message:Thread1dumpingdatafor`guijian`.`test`
**Message:Thread3dumpingschemafor`guijian`.`test`
**Message:Thread2dumpingdatafor`guijian`.`test01`
**Message:Thread4dumpingschemafor`guijian`.`test01`
**Message:Non-InnoDBdumpcomplete,unlockingtables
**Message:Thread4shuttingdown
**Message:Thread1shuttingdown
**Message:Thread3shuttingdown
**Message:Thread2shuttingdown
**Message:Finisheddumpat:2017-02-2415:28:18
[root@d4jtarmsvurd01mydumper_bak]#ls-lrt
总用量4
drwx------2rootroot40962月2415:28export-20170224-152818
[root@d4jtarmsvurd01mydumper_bak]#cdexport-20170224-152818/
[root@d4jtarmsvurd01export-20170224-152818]#ls-lrt
总用量24
-rw-r--r--1rootroot682月2415:28guijian-schema-create.sql
-rw-r--r--1rootroot11102月2415:28guijian.test.sql
-rw-r--r--1rootroot28172月2415:28guijian.test-schema.sql
-rw-r--r--1rootroot11122月2415:28guijian.test01.sql
-rw-r--r--1rootroot28192月2415:28guijian.test01-schema.sql
-rw-r--r--1rootroot1432月2415:28metadata
[root@d4jtarmsvurd01export-20170224-152818]#
mysql>dropdatabaseguijian;
QueryOK,2rowsaffected(0.01sec)
mysql>exit
[root@d4jtarmsvurd01mydumper_bak]#myloader-d/mysql/mydumper_bak/export-20170224-152818-o-Bguijian-uroot-p'root123'
[root@d4jtarmsvurd01mydumper_bak]#
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| guijian |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use guijian;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_guijian |
+-------------------+
| test |
| test01 |
+-------------------+
2 rows in set (0.00 sec)
以上是“mysql中mysqldumper怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
