并行处理：
使用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@d4jtarmsvurd01 mydumper_bak]# pwd
/mysql/mydumper_bak
[
root@d4jtarmsvurd01 mydumper_bak]# ls ex*
metadata mysql.proc-schema.sql
mysql-schema-create.sql mysql.procs_priv-schema.sql
mysql.columns_priv-schema.sql mysql.servers-schema.sql
mysql.db-schema.sql mysql.tables_priv-schema.sql
mysql.db.sql mysql.time_zone-schema.sql
mysql.event-schema.sql mysql.time_zone_leap_second-schema.sql
mysql.func-schema.sql mysql.time_zone_name-schema.sql
mysql.help_category-schema.sql mysql.time_zone_transition-schema.sql
mysql.help_category.sql mysql.time_zone_transition_type-schema.sql
....................
当在冗长模式中运行时，会产生额外的输出，单所提供的信息中并不包括完整的输出目录：
** Message: Connected to a MySQL server
** Message: Started dump at: 2016-07-05 15:16:56
** Message: Written master status
** Message: Thread 1 connected using MySQL connection ID 1367
** Message: Thread 2 connected using MySQL connection ID 1368
** Message: Thread 3 connected using MySQL connection ID 1369
** Message: Thread 4 connected using MySQL connection ID 1370
** Message: Thread 2 dumping data for `mysql`.`db`
** Message: Thread 1 dumping data for `mysql`.`columns_priv`
** Message: Thread 3 dumping data for `mysql`.`event`
** Message: Empty table mysql.event
** Message: Empty table mysql.columns_priv
** Message: Thread 2 dumping data for `mysql`.`func`
** Message: Thread 1 dumping data for `mysql`.`help_category`
** Message: Thread 3 dumping data for `mysql`.`help_keyword`
** Message: Thread 1 dumping data for `mysql`.`help_relation`
** Message: Empty table mysql.func
** Message: Thread 2 dumping data for `mysql`.`help_topic`
** Message: Thread 3 dumping data for `mysql`.`host`
** Message: Thread 1 dumping data for `mysql`.`ndb_binlog_index`
** Message: Empty table mysql.ndb_binlog_index
** Message: Thread 1 dumping data for `mysql`.`plugin`
** Message: Empty table mysql.plugin
** Message: Thread 1 dumping data for `mysql`.`proc`
** Message: Empty table mysql.proc
** Message: Thread 1 dumping data for `mysql`.`procs_priv`
** Message: Empty table mysql.host
** Message: Thread 3 dumping data for `mysql`.`servers`
** Message: Empty table mysql.servers
** Message: Thread 3 dumping data for `mysql`.`tables_priv`
** Message: Empty table mysql.procs_priv
** Message: Thread 1 dumping data for `mysql`.`time_zone`
** Message: Empty table mysql.time_zone
** Message: Thread 1 dumping data for `mysql`.`time_zone_leap_second`
** Message: Empty table mysql.time_zone_leap_second
** Message: Thread 1 dumping data for `mysql`.`time_zone_name`
** Message: Empty table mysql.time_zone_name
** Message: Thread 1 dumping data for `mysql`.`time_zone_transition`
** Message: Empty table mysql.tables_priv
** Message: Thread 3 dumping data for `mysql`.`time_zone_transition_type`
** Message: Empty table mysql.time_zone_transition
** Message: Thread 1 dumping data for `mysql`.`user`
** Message: Thread 1 dumping data for `sanxing`.`sanxing`
** Message: Empty table mysql.time_zone_transition_type
** Message: Thread 3 dumping data for `test`.`guijian`
** Message: Thread 3 dumping schema for `mysql`.`columns_priv`
** Message: Thread 1 dumping schema for `mysql`.`db`
** Message: Thread 1 dumping schema for `mysql`.`event`
** Message: Thread 1 dumping schema for `mysql`.`func`
** Message: Thread 1 dumping schema for `mysql`.`help_category`
** Message: Thread 1 dumping schema for `mysql`.`help_keyword`
** Message: Thread 3 dumping schema for `mysql`.`help_relation`
** Message: Thread 1 dumping schema for `mysql`.`help_topic`
** Message: Thread 3 dumping schema for `mysql`.`host`
** Message: Thread 1 dumping schema for `mysql`.`ndb_binlog_index`
** Message: Thread 1 dumping schema for `mysql`.`plugin`
** Message: Thread 3 dumping schema for `mysql`.`proc`
** Message: Thread 1 dumping schema for `mysql`.`procs_priv`
** Message: Thread 1 dumping schema for `mysql`.`servers`
** Message: Thread 1 dumping schema for `mysql`.`tables_priv`
** Message: Thread 3 dumping schema for `mysql`.`time_zone`
** Message: Thread 1 dumping schema for `mysql`.`time_zone_leap_second`
** Message: Thread 3 dumping schema for `mysql`.`time_zone_name`
** Message: Thread 1 dumping schema for `mysql`.`time_zone_transition`
** Message: Thread 3 dumping schema for `mysql`.`time_zone_transition_type`
** Message: Thread 3 dumping schema for `mysql`.`user`
** Message: Thread 3 dumping schema for `sanxing`.`sanxing`
** Message: Thread 3 dumping schema for `test`.`guijian`
** Message: Non-InnoDB dump complete, unlocking tables
** Message: Thread 3 shutting down
** Message: Thread 1 shutting down
** Message: Thread 4 shutting down
** Message: Thread 2 shutting down
** Message: Finished dump at: 2016-07-05 15:16:56
[
root@d4jtarmsvurd01 mydumper_bak]# ls
export-20160705-151255 export-20160705-151656
[
root@d4jtarmsvurd01 mydumper_bak]#
用法：
[
root@d4jtarmsvurd01 mydumper_bak]# mydumper --help
Usage:
mydumper [OPTION...] multi-threaded MySQL dumping
Help Options:
-?, --help Show help options
Application Options:
-B, --database Database to dump
-T, --tables-list Comma delimited table list to dump (does not exclude regex option)
-o, --outputdir Directory to output files to
-s, --statement-size Attempted size of INSERT statement in bytes, default 1000000
-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
-c, --compress Compress output files
-e, --build-empty-files Build dump files even if no data available from table
-x, --regex Regular expression for 'db.table' matching
-i, --ignore-engines Comma delimited list of storage engines to ignore
-m, --no-schemas Do not dump table schemas with the data
-d, --no-data Do not dump table data
-G, --triggers Dump triggers
-E, --events Dump events
-R, --routines Dump stored procedures and functions
-k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups
--less-locking Minimize locking time on InnoDB tables.
-l, --long-query-guard Set long query timer in seconds, default 60
-K, --kill-long-queries Kill long running queries (instead of aborting)
-D, --daemon Enable daemon mode
-I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default 60
-L, --logfile Log file name to use, by default stdout is used
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
--skip-tz-utc
--use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
--lock-all-tables Use LOCK TABLE for all, instead of FTWRL
-U, --updated-since Use Update_time to dump only tables updated in the last U days
--trx-consistency-only Transactional consistency only
-h, --host The host to connect to
-u, --user Username with privileges to run the dump
-p, --password User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression on the MySQL connection
-V, --version Show the program version and exit
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
[
root@d4jtarmsvurd01 mydumper_bak]#
通过正则表达式选项排除mysql和测试的模式对象：
mydumper --user root --regex '^(?!(mysql|test))'
压缩：默认情况下，所有处处文件都是不压缩的。但是通过使用-c选项，所有文件都可以被压缩。
[
root@d4jtarmsvurd01 mydumper_bak]# mydumper -c
[
root@d4jtarmsvurd01 mydumper_bak]# ls -lrt
total 12
drwx------ 2 root root 4096 Jul 5 15:12 export-20160705-151255
drwx------ 2 root root 4096 Jul 5 15:16 export-20160705-151656
drwx------ 2 root root 4096 Jul 5 15:20 export-20160705-152048
[
root@d4jtarmsvurd01 mydumper_bak]# cd export-20160705-152048/
[
root@d4jtarmsvurd01 export-20160705-152048]# ls
metadata mysql.proc-schema.sql.gz
mysql-schema-create.sql.gz mysql.procs_priv-schema.sql.gz
mysql.columns_priv-schema.sql.gz mysql.servers-schema.sql.gz
mysql.db-schema.sql.gz mysql.tables_priv-schema.sql.gz
mysql.db.sql.gz mysql.time_zone-schema.sql.gz
mysql.event-schema.sql.gz mysql.time_zone_leap_second-schema.sql.gz
................
mydumper生产多个与元数据、表数据、表模式和二进制日志相关的文件。
.metadata文件中保存着转存的开始和结束时间以及主二进制日志的位置。当执行转存时，一个.metadata文件边被创建到输出目录中：
备份目录中的素有文件：
[
root@d4jtarmsvurd01 export-20160705-151656]# ls
metadata mysql.proc-schema.sql
mysql-schema-create.sql mysql.procs_priv-schema.sql
mysql.columns_priv-schema.sql mysql.servers-schema.sql
mysql.db-schema.sql mysql.tables_priv-schema.sql
mysql.db.sql mysql.time_zone-schema.sql
mysql.event-schema.sql mysql.time_zone_leap_second-schema.sql
mysql.func-schema.sql mysql.time_zone_name-schema.sql
mysql.help_category-schema.sql mysql.time_zone_transition-schema.sql
mysql.help_category.sql mysql.time_zone_transition_type-schema.sql
mysql.help_keyword-schema.sql mysql.user-schema.sql
mysql.help_keyword.sql mysql.user.sql
mysql.help_relation-schema.sql sanxing-schema-create.sql
mysql.help_relation.sql sanxing.sanxing-schema.sql
mysql.help_topic-schema.sql sanxing.sanxing.sql
mysql.help_topic.sql test-schema-create.sql
mysql.host-schema.sql test.guijian-schema.sql
mysql.ndb_binlog_index-schema.sql test.guijian.sql
mysql.plugin-schema.sql
[
root@d4jtarmsvurd01 export-20160705-151656]#
more metadata
Started dump at: 2016-07-05 15:16:56
SHOW MASTER STATUS:
Log: mysql-bin.000002
Pos: 106
GTID:(null)
Finished dump at: 2016-07-05 15:16:56
[
root@d4jtarmsvurd01 export-20160705-151656]# pwd
/mysql/mydumper_bak/export-20160705-151656
[
root@d4jtarmsvurd01 export-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@d4jtarmsvurd01 mydumper_bak]# myloader --help
Usage:
myloader [OPTION...] multi-threaded MySQL loader
Help Options:
-?, --help Show help options
Application Options:
-d, --directory Directory of the dump to import
-q, --queries-per-transaction Number of queries per transaction, default 1000
-o, --overwrite-tables Drop tables if they already exist
-B, --database An alternative database to restore into
-s, --source-db Database to restore
-e, --enable-binlog Enable binary logging of the restore data
-h, --host The host to connect to
-u, --user Username with privileges to run the dump
-p, --password User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression on the MySQL connection
-V, --version Show the program version and exit
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
恢复测试：
1、备份数据库：
[
root@d4jtarmsvurd01 mydumper_bak]# mydumper -u root -p root123 -v 3
2、
删除其中的某一个数据库：
mysql> drop database sanxing;
Query OK, 2 rows affected (0.06 sec)
3、开始恢复其中的一个数据库：
[
root@d4jtarmsvurd01 mydumper_bak]# myloader -d /mysql/mydumper_bak/export-20170224-151158 -o -B sanxing -u root -p 'root123'
4、检查恢复情况：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| guijian |
| mysql |
| sanxing |
| test |
+--------------------+
5 rows in set (0.00 sec)
注意原本有两个表的数据库，在恢复时指定了所有数据库备份的目录，此时所有的数据库表都被恢复到了，删除的库，（故此，在恢复的时候注意要使用单个数据库的备份，即什么样的备份能恢复什么样的数据库）
mysql> use sanxing;
Database changed
mysql> show tables;
+---------------------------+
| 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 |
+---------------------------+
25 rows in set (0.00 sec)
mysql>
单独测试恢复：
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> exit
Bye
[
root@d4jtarmsvurd01 mydumper_bak]# mydumper -u root -p root123 -B guijian -v 3
** Message: Connected to a MySQL server
** Message: Started dump at: 2017-02-24 15:28:18
** Message: Written master status
** Message: Thread 1 connected using MySQL connection ID 2807
** Message: Thread 2 connected using MySQL connection ID 2808
** Message: Thread 3 connected using MySQL connection ID 2809
** Message: Thread 4 connected using MySQL connection ID 2810
** Message: Thread 1 dumping data for `guijian`.`test`
** Message: Thread 3 dumping schema for `guijian`.`test`
** Message: Thread 2 dumping data for `guijian`.`test01`
** Message: Thread 4 dumping schema for `guijian`.`test01`
** Message: Non-InnoDB dump complete, unlocking tables
** Message: Thread 4 shutting down
** Message: Thread 1 shutting down
** Message: Thread 3 shutting down
** Message: Thread 2 shutting down
** Message: Finished dump at: 2017-02-24 15:28:18
[
root@d4jtarmsvurd01 mydumper_bak]# ls -lrt
总用量 4
drwx------ 2 root root 4096 2月 24 15:28 export-20170224-152818
[
root@d4jtarmsvurd01 mydumper_bak]# cd export-20170224-152818/
[
root@d4jtarmsvurd01 export-20170224-152818]# ls -lrt
总用量 24
-rw-r--r-- 1 root root 68 2月 24 15:28 guijian-schema-create.sql
-rw-r--r-- 1 root root 1110 2月 24 15:28 guijian.test.sql
-rw-r--r-- 1 root root 2817 2月 24 15:28 guijian.test-schema.sql
-rw-r--r-- 1 root root 1112 2月 24 15:28 guijian.test01.sql
-rw-r--r-- 1 root root 2819 2月 24 15:28 guijian.test01-schema.sql
-rw-r--r-- 1 root root 143 2月 24 15:28 metadata
[
root@d4jtarmsvurd01 export-20170224-152818]#
mysql> drop database guijian;
Query OK, 2 rows affected (0.01 sec)
mysql> exit
[
root@d4jtarmsvurd01 mydumper_bak]# myloader -d /mysql/mydumper_bak/export-20170224-152818 -o -B guijian -u root -p 'root123'
[
root@d4jtarmsvurd01 mydumper_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)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28612416/viewspace-2134233/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28612416/viewspace-2134233/
9358
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
