Using Mydumper to prepare MySQL part of the database

existing2DeskDB Server, respectively, forABusiness andBbusiness, whichAthe business is more important and requiresABusiness of1a db (Taeoss) for hot preparation, presumably40Gdata and use the businessBof theDBserver as the standby machine, the server distribution is as follows:

10.137.143.151 A Business

10.137.143.152 B Business

The requirements for development there are:

in the export A Business of DB(taeoss) , you cannot A business has an impact. At the same time in the B Business of the DB Server recovery, also can not have a greater impact, try to control within 1 minutes.

The programme adopted:

1 , mysqldump : is a logical backup, there will be a lock table, but considering the large amount of data, the lock table time will be longer, the business is not allowed, Pass off;

2 xtrabackup : Is a physical backup, there is no lock table, but consider 2 table db use shared tablespace, and in business b when the database is recovering, one is longer and the data is definitely incorrect, pass

3 , Mydumper : is a logical backup, is a multi-threaded, high-performance data logical backup, recovery tools, and lock the table time is very short (40G data, Ten minutes), while recording Binlog File and the POS , the business can be accepted.

Mydumper The main features are as follows:

(1) , the task speed than mysqldump Fast 6 times above;

(2) , transactional, and non-transactional table-consistent snapshots ( Suitable for 0.2.2 version above) ;

(3) , fast file compression;

(4) , export support Binlog ;

(5) , multi-threaded recovery ( Suitable for 0.2.1 version above) ;

(6) , in the way daemons work, timed snapshots and sequential binary logs ( Suitable for 0.5.0 version above) .

Mydumper Installation:

Https://launchpad.net/mydumper/0.6/0.6.2/+download/mydumper-0.6.2.tar.gz

# yum Install glib2-devel mysql-devel zlib-devel pcre-devel

# tar ZXVF mydumper-0.6.2.tar.gz

# CD mydumper-0.6.2

# CMake.

# make

# make Install

The parameters are as follows:

650) this.width=650; "title=" 11.png "alt=" wkiol1sv4t-d3y63aaibfwfpwyi081.jpg "src=" http://s3.51cto.com/wyfs02/M02/ 58/6e/wkiol1sv4t-d3y63aaibfwfpwyi081.jpg "/>

because DB is deployed in older SuSE Linux Ten on the server, install Mydumper rely on more cubby, will be more cumbersome, while using local backup, will also occupy a large number of disks I/O , so we chose another one in the same network segment CentOS 6.4 ( 10.137.143.156 ) server for backup.

The steps are as follows:

1 , in " 10.137.143.151 , 10.137.143.152 " on the " 10.137.143.156 " for temporary Authorization

# MYSQL-UROOT-E "Grant all privileges on * * to ' backup ' @ ' 10.137.143.156 ' identified by ' backup2015 ';"

# MYSQL-UROOT-E "flush privileges;"

2 , in the " 10.137.143.156 "on the right." 10.137.143.151 "of DB ( Taeoss ) for backup

# mydumper-h 10.137.143.151-u backup-p backup2015-b taeoss-t 8-o/data/rocketzhang

3 , restore the backup data to the 10.137.143.152 "

# myloader-h 10.137.143.152-u backup-p backup2015-b taeoss-t 8-o-d/data/rocketzhang

4 and the establishment of master-slave relationship: 10.137.143.151 (master), 10.137.143.152 (from)

in the " 10.137.143.151 "Create an Authorized account:

# mysql-uroot-e "Grant replication Slave on * * to ' repl ' @ ' 10.137.143.152 ' identified by ' repl123456 ';"

# MYSQL-UROOT-E "flush privileges;"

in the " 10.137.143.156 "View the records under the Binlog Information:

650) this.width=650; "title=" 11.png "alt=" wkiom1sv4jjcnfrtaacahfynpsu864.jpg "src=" http://s3.51cto.com/wyfs02/M02/ 58/72/wkiom1sv4jjcnfrtaacahfynpsu864.jpg "/>

in the " 10.137.143.152 "Do the following:

# VIM/ETC/MY.CNF

......

Replicate-do-table = taeoss.%

Replicate-wild-do-table = taeoss.%

......

# Service Mysqld Reload

# mysql-uroot-e "Change master to master_host= ' 10.137.143.151 ', master_user= ' repl ', master_password= ' repl123456 ', Master_log_file= ' mysql-bin.002205 ', master_log_pos=456584891;

# mysql-uroot-e "Start slave;"

# mysql-uroot-e "show slave status\g;"

The following information appears:

650) this.width=650; "title=" 11.png "alt=" wkiol1sv4zpsz-nraacdnegqwi8550.jpg "src=" http://s3.51cto.com/wyfs02/M01/ 58/6e/wkiol1sv4zpsz-nraacdnegqwi8550.jpg "/>

There appears to be a primary key conflict that causes master-slave replication to fail.

Problem Analysis:

in the main DB ( 10.137.143.151 ) on the execution:

# Mysqlbinlog--no-defaults-v-v--base64-output=decode-rows mysql-bin.002205 > Mysql-bin.002205.txt

# grep-c 8 529864938 Mysql-bin.002205.txt

650) this.width=650; "title=" 11.png "alt=" wkiom1sv4pjcepbsaafsarl1ntq638.jpg "src=" http://s3.51cto.com/wyfs02/M01/ 58/72/wkiom1sv4pjcepbsaafsarl1ntq638.jpg "/>

Presumably, there was a primary key conflict when there was an insert operation on the T_evil_detect_uin_blacklist table on the primary DB, and there was a primary key conflict when synchronizing from the slave, which caused the master-slave synchronization to fail.

Temporary solution:

Export a table from the end taeoss.t_evil_detect_uin_blacklist

# mysqldump-uroot--opt taeoss t_evil_detect_uin_blacklist > TaeOss.t_evil_detect_uin_blacklist.sql

Remove TaeOss.t_evil_detect_uin_blacklist.sql where the PRIMARY KEY statement:

650) this.width=650; "title=" 11.png "alt=" wkiol1sv44iwtcy_aaf5wa3pve0742.jpg "src=" http://s3.51cto.com/wyfs02/M02/ 58/6e/wkiol1sv44iwtcy_aaf5wa3pve0742.jpg "/>

And then import:

# Mysql-uroot Taeoss < TaeOss.t_evil_detect_uin_blacklist.sql

# mysql-uroot-e "Stop slave;"

# mysql-uroot-e "Start slave;"

# mysql-uroot-e "show slave status\g;"

650) this.width=650; "title=" 11.png "alt=" wkiol1sv46miafazaae0mfdbnc4862.jpg "src=" http://s3.51cto.com/wyfs02/M02/ 58/6e/wkiol1sv46miafazaae0mfdbnc4862.jpg "/>

This article from "The ideal of life is Perseverance" blog, please be sure to keep this source http://sofar.blog.51cto.com/353572/1601428

Using Mydumper to prepare MySQL part of the database