Shulou(Shulou.com)06/01 Report--

There are two DB servers for business An and business B respectively. Business An is more important. A DB (TaeOss) of business An is needed for hot backup. There is about 40G of data, and the DB server of business B is used as the backup machine. The server distribution is as follows:

10.137.143.151 A service

10.137.143.152 B Business

The requirements of the developer side are:

When exporting the DB (TaeOss) of A business, it cannot affect A business. At the same time, when the recovery is carried out on the DB server of B business, it can not have a big impact and should be controlled within 1 minute as far as possible.

The options adopted are:

1. Mysqldump: it belongs to logical backup, and the table will be locked. However, considering the large amount of data, it will take a long time to lock the table, and business does not allow it, so pass will be dropped.

2. Xtrabackup: it belongs to physical backup, and there are no locked tables, but considering that both DB use shared tablespaces, and when restoring the database of Business B, it takes a long time, and the data is definitely incorrect. Pass is dropped (tested).

3. Mydumper: belongs to logical backup, which is a multi-threaded, high-performance tool for logical backup and recovery of data. The locking time of the table is very short (40G data, less than 10 minutes), and binlog file and pos are recorded at the same time. The business is acceptable.

Mydumper has the following main features:

(1) the task speed is more than 6 times faster than mysqldump

(2), consistent snapshots of transactional and non-transactional tables (for version 0.2.2 and above)

(3) Fast file compression

(4) support exporting binlog

(5), multithreaded recovery (applicable to version 0.2.1 or above)

(6) regular snapshots and continuous binary logs (applicable to version 0.5.0 or above) in the way of daemons.

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:

Because DB is deployed on the older SuSE Linux 10 server, the installation of mydumper depends on more libraries, which will be more tedious. At the same time, if you use local backup, it will also take up a large amount of disk Ipex O, so we choose another centos 6.4 (10.137.143.156) server on the same network segment for backup.

The steps are as follows:

1. Temporarily authorize "10.137.143.156" on "10.137.143.151" and "10.137.143.152"

# mysql-uroot-e "grant all privileges on *. * to 'backup'@'10.137.143.156' identified by' backup2015';"

# mysql-uroot-e "flush privileges;"

2. Back up the DB (TaeOss) of "10.137.143.151" on "10.137.143.156"

# mydumper-h 10.137.143.151-u backup-p backup2015-B TaeOss-t 8-o / data/rocketzhang

3. Restore the backup data to "10.137.143.152"

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

4. Master-slave relationship establishment: 10.137.143.151 (master), 10.137.143.152 (slave)

Set up an authorized account at "10.137.143.151":

# mysql-uroot-e "grant replication slave on *. * to 'repl'@'10.137.143.152' identified by' repl123456';"

# mysql-uroot-e "flush privileges;"

View the recorded binlog information at "10.137.143.156":

At "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 message appears:

It seems that there is a primary key conflict, which leads to the failure of master-slave replication.

Problem analysis:

Execute on the primary DB (10.137.143.151):

# mysqlbinlog-- no-defaults-v-- base64-output=DECODE-ROWS mysql-bin.002205 > mysql-bin.002205.txt

# grep-C 8 529864938 mysql-bin.002205.txt

It roughly means that when there is an insert operation on the t_evil_detect_uin_blacklist table on the master DB, there is a primary key conflict, and when synchronization is performed on the slave side, there is also a primary key conflict, resulting in a master-slave synchronization failure.

Temporary solution:

Export the slave-end table TaeOss.t _ evil_detect_uin_blacklist

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

Remove the primary key statement from TaeOss.t_evil_detect_uin_blacklist.sql:

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;"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.