MySQL Logical backup Weapon-mydumper

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 whileloading shared libraries:libmysqlclient.so.  or directory   

Workaround:

# locate libmysqlclient.so.18/usr/local/mysql/lib/libmysqlclient.so. 18 /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 number of queries executed per execution, default 1000-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 records backup start and end times, and Binlog log file location. 
                                                                                                                            table data: one file per table table< Span style= "COLOR: #000000" > schemas: Table structure file binary logs: Enable  --binlogs option, the binary file is stored in the Binlog_snapshot directory Daemon mode: In this mode, there are five directories 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.           /span> 
                                                                                                                                

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# Restore backup data to the new database Broker_biz_log Myloader -T 6  -V 3 --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"

-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 &       

"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 ' &            

< Span class= "hljs-string" > "It is noteworthy that: The Myloader is a. Delimiter to intercept the name of the library, so if the name of the library itself contains., it will fail "

MySQL logical backup weapon-mydumper