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怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云技术行业资讯频道！

相关推荐: Oracle与PostgreSQL的区别有哪些

这篇文章主要讲解了“Oracle与PostgreSQL的区别有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle与PostgreSQL的区别有哪些”吧！Oracle输出参数分别是number、var…