Using Xtrabackup to do MySQL does not stop re-starting from copying

Do not stop to do MySQL master-slave synchronization, you can use mysqldump, but this low efficiency, the use of xtrabackup to achieve this, high efficiency!

One Xtrabackup installation:

Yum-y Install Perl perl-devel libaio libaio-devel perl-time-hires perl-dbd-mysql

RPM-IVH percona-xtrabackup-2.2.12-1.el6.x86_64.rpm

Install Libenv:

wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm

Xtrabackup Software:

Software: Https://www.percona.com/software/mysql-database/percona-xtrabackup

Two check the MySQL version:

With Xtrabackup for full and recovery, MySQL version is best consistent!

Check the MySQL version:

/webser/mysql55/bin/mysql-v

/webser/mysql55/bin/mysql Ver 14.14 Distrib 5.5.21, for Linux (x86_64) using ReadLine 5.1

Three. Back up the database online on the master server:

Innobackupex--defaults-file=/webser/mysql55/etc/my.cnf--user=root--password=xxxx/tmp/

To back up the specified database:

Innobackupex--defaults-file=/webser/mysql55/etc/my.cnf--user=root--password=xxxxx --include= ' abc.*|salt.* ' /tmp/

Notice that the "|" is used between multiple libraries Symbol separated!

ps:/tmp/for backup directory;

And then through the SCP, the master

Four. operation on slave machine;

1) Turn off the MySQL service;

Then empty the MySQL data directory files, such as the local MySQL data directory is:/webser/mysql55/var/

2) Start recovering data

The path of the fake device files in:/tmp/2016-04-29_16-00-54

Recovery log File:

Innobackupex--defaults-file=/webser/mysql55/etc/my.cnf--user=root--password=xxxx--apply-log/tmp/2016-04-29_ 16-00-54/

To recover a data file:

Innobackupex--defaults-file=/webser/mysql55/etc/my.cnf--user=root--password=xxxx--copy-back/tmp/2016-04-29_ 16-00-54/

3) Modify Data directory permissions:

Chown-r Mysql:mysql/webser/mysql55/var

4) Safe way to start MySQL to see if there is an exception;

/webser/mysql55/bin/mysqld_safe--DEFAULTS-FILE=/WEBSER/MYSQL55/ETC/MY.CNF &

If there is no abnormal error, turn off MySQL

/webser/mysql55/bin/mysqladmin-uroot-p shutdown

5) View the location value of the Binlog log file, from Sync:

Cat/tmp/2016-04-29_16-00-54/xtrabackup_binlog_info
wwwmaster.000458 1022022953

6) on the slave machine from the relevant operation:

Change MASTER to
Master_host= ' 192.168.3.xxx ',
Master_user= ' Mysql_rep ',
master_password= ' xxxx ',
master_port=33066,
Master_log_file= ' wwwmaster.000458 ',
master_log_pos=1022022953;

To start master-slave synchronization:

Start slave;

Reference Links:

Http://blog.chinaunix.net/uid-20682026-id-3319204.html

http://wsgzao.github.io/post/xtrabackup/

The problem encountered:

1) error after starting:

[[email protected] etc]#/etc/init.d/mysql55 start

Starting MySQL (Percona Server) ... The server quit without U[failed]pid file (/data/mysql55/var/guoletaotest02.pid).

To view the error log:

160429 16:47:12 [ERROR] Can ' t open the Mysql.plugin table. Please run Mysql_upgrade to create it.

Workaround:

./mysql_install_db--user=mysql--basedir=/webser/mysql55/--datadir=/data/mysql55/var/

Execute the above statement, the problem can be solved!

This article is from the "Shine_forever blog" blog, make sure to keep this source http://shineforever.blog.51cto.com/1429204/1769084

Using Xtrabackup to do MySQL does not stop re-starting from copying