For a brief introduction and download of Mydumper, please visit: https://launchpad.net/mydumper
In short, Mydumper is a multithreaded logical backup, and it is recommended to use Mydumper to improve backup efficiency, much faster than mysqldumper for large tables and data volumes.
Why should I use Mydumper? Is it based on a requirement, such as I need to back up and import all table-by-date tables under a library to a new instance? How is it implemented?
table_20170101table_20170102......table_20171231 altogether more than 300 sheets
At first I went to try mysqldump, see parameters:-B [table1,table2......table N], how brazen to say we support multiple specified table backup, 5 below fully accepted, but I want to back up 1000 tables, you want me in that column 1000? I still do not believe, mysqldump how can not support table name fuzzy matching it? So I'm going to use the following notation:
Mysqldump--- B xx table_*>backup. sql
Execution no error, surprise, I said, how can not support the table of fuzzy matching it!! When I opened the Backup.sql file, my bladder blew up, and I backed up all the tables in a database directly. Ignore my fuzzy match. Since then I have no good impression of mysqldump, (of course, it may be my limited ability, has not found mysqldump multi-table backup method).
Switch to Mydumper, when I see the-x parameter, it is also a happy! Look at others, write how clear, so began to use:
First, Mydumper installation:
# yum Install Glib2-Devel MySQL-Devel zlib-Devel pcre-devel# wget http://Launchpad.net/Mydumper/0.9/0.9.1/+Download/Mydumper-0.9.1. tar.gz# Tar zxvf mydumper-0.9.1. tar.gz-C../Software/# cmake. # make# make install
Second, the use of Mydumper
1, just use the Times wrong:
/usr/local/bin/ while loading shared libraries:libmysqlclient.so. - Open file file or directory
Workaround:
# locate libmysqlclient.so. - /Usr/Local/Mysql/Lib/Libmysqlclient.so. - /Usr/Local/Mysql/Lib/Libmysqlclient.so.18.1.0# echo "/Usr/Local/Mysql/Lib>> /etc/ld.so.conf# Ldconfig
2. Introduction of important parameters:
-B--Database needs to be backed up -T--tables-list The tables to be backed up, separated by -O--OutputDir Output Directory -S--Statement-size attempted size of INSERT statement in bytes, default 1000000 -R--rows tries to split into many block tables -C--Compress compressed output file -E--Build-empty-files Even if the table has no data, it produces an empty file -X--regex supports regular expressions -I--Ignore-engines ignored by the storage engine, separated by -M--no-schemas do not export table structure -K--No-locks does not perform a temporary shared read lock warning: This will result in inconsistent backups -L--long-query-guard long query, default 60s --kill-long-queries Kill long executed queries (instead of aborting) -B--binlogs Export Binlog -D--Daemon Enable daemon mode -I,--snapshot-interval Dump snapshot interval, default 60s, required in daemon mode -L--logfile log Files -H--Host -U--User -P--Password -P--Port -S--Socket -T--threads number of threads used, default 4 -C--compress-protocol using compression on MySQL connection -V--version -V--verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2myloader parameter Description:-D--Directory import Backup catalog -Q--queries-per-transaction The number of queries executed per execution, the default -O--overwrite-tables If a table exists to delete a table -B--library that database needs to restore -E--enable-binlog Enabling binary recovery data -H--Host -U--User -P--Password -P--Port -S--Socket -T--threads number of threads used, default 4 -C--using compression on Compress-protocol connections -V--version -V--verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 mydumper Output File Description:metadata: Metadata record backup start and end time, and Binlog log file location. Tabledata: One file per tableTableSchemas: Table structure filebinaryLogs: Enabled--binlogs option, binary files are stored in the Binlog_snapshot directoryDaemon mode: In this model, there are five directory 0,1, Binlogs,binlog_snapshot,last_dump. The backup directory is 0 and 1, the interval backup, if mydumper for some reason fails and still have a good snapshot, when the snapshot is complete, last_dump points to the backup.
3. Use Cases
# Back up the daily table of a library mydumper-X "New_broker.broker_action_log_*"-O/Data/Dbbackup/Tmp/ --host= ' xxxx '--user= ' xxxx '--password= ' xxx '--port= ' 3306 '--logfile= '/data/dbbackup/tmp/mydumper.log '--threads =6-v 3#将备份数据恢复到新数据库broker_biz_log中myloader-T6 -V3 --host= ' xxxx '--user= ' xxxx '--password= ' xxxx '--port= ' 3306 '-b broker_biz_log--directory= '/data/dbbackup/tmp '
4, the use of misunderstanding
Requirement 1: Back up all tables under multiple databases
"Error case, partial library export failed"
nohup mydumper - x "(Test_db. * ) | (Test. * ) | (MySQL. * ) " - o / data/ dbbackup/ mydumper/ -- host= ' 192.168.1.20 '--user= ' secret '--password= ' xx '--port= ' 3306 '--logfile= '/data/dbbackup/mydumper/mydumper.log '--threads=6-v 3 &
"The right wording."
-X "(test_db| Test| MySQL). * - /Data/dbbackup/mydumper/--host= ' 192.168.1.20 '-- User= ' secret '--password= ' xx '--port= ' 3306 '--logfile= '/data/dbbackup/mydumper/mydumper.log '--threads=6-v 3 &
Requirement 2: Back up all tables under all databases
- /Data/dbbackup/mydumper/--host= ' IP '--user= ' secret '--password= ' xx '--port= ' 3306 '--logfile= '/data/dbbackup/mydumper/mydumper.log '--threads=6-v 3
< Span class= "hljs-string" >[ Myloader]
< Span class= "hljs-string" >--restore all libraries on/data/dbbackup/mydumper
< Span class= "hljs-string" > ' Correct wording "
nohup myloader - t 6 - v 3 - u secret - p xxx - S / tmp/ mysql.sock -- directory= '/data/dbbackup/mydumper ' &
"It is worth noting that the Myloader is a. Delimiter to intercept the name of the library, so if the name of the library itself contains., then it will fail."
MySQL logical backup weapon-mydumper