Using Xtrabackup to achieve non-stop master service from synchronization

MySQL master-Slave synchronization principle:

MySQL master-slave synchronization is implemented on the basis of MySQL master-slave replication (Master-slave Replication), by setting the Binlog on master MySQL (leaving it open), Slave MySQL through a i/ The o thread reads binlog from master MySQL and then transmits it to slave MySQL's trunk log, then slave mysql's SQL thread to read the relay log from the log and then apply it to the slave MySQL database. This realizes the master-slave data synchronization function.

Xtrabackup Backup principle:

Innobackupex keeps track of InnoDB log files in the background thread, and then copies the InnoDB data files. After the data file copy is complete, the replication thread for the log ends. This gives you a copy of the data not at the same point in time and the transaction log after the start of the backup. After completing the above steps, you can use the InnoDB crash recovery code to execute the transaction log (redo log) to achieve data consistency.

A backup is divided into two processes:

Backup, trace the transaction log and copy the data file (physical backup).

Preparing, replay the transaction log so that all data is at the same point in time, reaching a consistent state.

Advantages of Xtrabackup:

Data backup can be done quickly and reliably (copying data files and tracking transaction logs)

Transaction processing is not interrupted during data backup (hot backup)

Conserve disk space and network bandwidth

Auto-Complete backup authentication

Increased online time due to faster recovery times

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

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/backup/

ps:/tmp/backup/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:/webser/2015-08-12_13-54-56

Recovery log File:

Innobackupex--defaults-file=/webser/mysql55/etc/my.cnf--user=root--password=xxxx--apply-log/tmp/backup/ 2015-08-12_13-49-32/

To recover a data file:

Innobackupex--defaults-file=/webser/mysql55/etc/my.cnf--user=root--password=xxxx--copy-back/tmp/backup/ 2015-08-12_13-49-32/

Above, please be sure to follow the order, the order of operation is reversed, will cause the MySQL server can not boot!

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/backup/2015-08-12_13-49-32/xtrabackup_binlog_info

wwwmaster.000458 1022022953

6) on the slave machine from the relevant operation:

Change MASTER to

Master_host= ' 192.168.1.xxx ',

Master_user= ' rep ',

Master_password= ' xxxxxx ',

master_port=3306,

Master_log_file= ' wwwmaster.000458 ',

master_log_pos=1022022953;

To start master-slave synchronization:

Start slave;

View slave Status:

Using Xtrabackup to achieve non-stop master service from synchronization