并行处理：

使用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)