這篇文章主要為大家展示了“mysql中mysqldumper怎么用”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“mysql中mysqldumper怎么用”這篇文章吧。
并行處理：
使用mydumper命令的局限在與他是一個(gè)單線程進(jìn)程。但開源的mydumper是一個(gè)很好的替代。
mydumper（http://www.mysqldumper.org/）是一個(gè)在GNU GPLv3許可下發(fā)布的高性能MySQL備份和恢復(fù)工具集。mydumper是多線程的，他創(chuàng)建一個(gè)mysql備份就比隨mysql發(fā)布的mysqldump工具要快得多。mydumper也有從源端服務(wù)器恢復(fù)二進(jìn)制日志的能力。
mydumper的優(yōu)點(diǎn)：
多線程，可以是轉(zhuǎn)存數(shù)據(jù)快很多。
mydumper的輸出已于管理和分析，因?yàn)樗谋砗驮獢?shù)據(jù)是分開的單獨(dú)文件。
所有線程都維護(hù)有一直的快照，這邊提供了精準(zhǔn)的主從位置。
Mydumper支持Perl正則表達(dá)式，這樣就既可以包括是數(shù)據(jù)庫(kù)名和報(bào)名的模式匹配，也可以配置這種匹配。
通過(guò)名為myloader的多線程工具，mydumper工具集也可以從mydumper備份中恢復(fù)數(shù)據(jù)。
mydumper必須在源代碼上進(jìn)行編輯。這就需要帶有C++編輯器的系統(tǒng)。另外還需要如下組件：
Cmake、帶有開發(fā)包的Glib2、帶有開發(fā)包的PCRE、mysql的客戶端庫(kù)和開發(fā)工具。
安裝步驟如下：
依賴包：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
簡(jiǎn)單用法：
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
....................
當(dāng)在冗長(zhǎng)模式中運(yùn)行時(shí)，會(huì)產(chǎn)生額外的輸出，單所提供的信息中并不包括完整的輸出目錄：
[root@d4jtarmsvurd01 mydumper_bak]# mydumper -v 3
** 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]#
通過(guò)正則表達(dá)式選項(xiàng)排除mysql和測(cè)試的模式對(duì)象：
mydumper --user root --regex '^(?!(mysql|test))'
壓縮：默認(rèn)情況下，所有處處文件都是不壓縮的。但是通過(guò)使用-c選項(xiàng)，所有文件都可以被壓縮。
[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生產(chǎn)多個(gè)與元數(shù)據(jù)、表數(shù)據(jù)、表模式和二進(jìn)制日志相關(guān)的文件。
.metadata文件中保存著轉(zhuǎn)存的開始和結(jié)束時(shí)間以及主二進(jìn)制日志的位置。當(dāng)執(zhí)行轉(zhuǎn)存時(shí)，一個(gè).metadata文件邊被創(chuàng)建到輸出目錄中：
備份目錄中的素有文件：
[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的時(shí)候可以通過(guò)show processlist來(lái)監(jiān)控線程。
可以使用兩種不同的方式存儲(chǔ)表數(shù)據(jù)：將所有表數(shù)據(jù)村委一個(gè)文件或者將一個(gè)表的數(shù)據(jù)塊存為多個(gè)文件，如果未指定--row選項(xiàng)，則將為每個(gè)表創(chuàng)建一個(gè)文件，命令規(guī)則類似于database.table.sql。
關(guān)于mydumper生成文件的類型如下：
db_name.table_name-schema_name.sql ---表結(jié)構(gòu)文件
db_name.table_name.sql ---表數(shù)據(jù)文件
db_name-schema-create.sql ---數(shù)據(jù)庫(kù)創(chuàng)建腳本
mydumper的還原工具為：myloader，使用說(shuō)明如下：
[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
恢復(fù)測(cè)試：
1、備份數(shù)據(jù)庫(kù)：
[root@d4jtarmsvurd01 mydumper_bak]# mydumper -u root -p root123 -v 3
2、刪除其中的某一個(gè)數(shù)據(jù)庫(kù)：
mysql> drop database sanxing;
Query OK, 2 rows affected (0.06 sec)
3、開始恢復(fù)其中的一個(gè)數(shù)據(jù)庫(kù)：
[root@d4jtarmsvurd01 mydumper_bak]# myloader -d /mysql/mydumper_bak/export-20170224-151158 -o -B sanxing -u root -p 'root123'
4、檢查恢復(fù)情況：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| guijian |
| mysql |
| sanxing |
| test |
+--------------------+
5 rows in set (0.00 sec)
注意原本有兩個(gè)表的數(shù)據(jù)庫(kù)，在恢復(fù)時(shí)指定了所有數(shù)據(jù)庫(kù)備份的目錄，此時(shí)所有的數(shù)據(jù)庫(kù)表都被恢復(fù)到了，刪除的庫(kù)，（故此，在恢復(fù)的時(shí)候注意要使用單個(gè)數(shù)據(jù)庫(kù)的備份，即什么樣的備份能恢復(fù)什么樣的數(shù)據(jù)庫(kù)）
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>
單獨(dú)測(cè)試恢復(fù)：
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)
以上是“mysql中mysqldumper怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)-成都網(wǎng)站建設(shè)公司行業(yè)資訊頻道！
重慶分公司
重慶分公司
