Use XtraBackupp to achieve master-slave synchronization without stopping master services

Innobackupex keeps tracking InnoDB log files in the background thread, and then copies InnoDB data files. After the data file is copied, the log replication thread ends.

Innobackupex keeps tracking InnoDB log files in the background thread, and then copies InnoDB data files. After the data file is copied, the log replication thread ends.

MySQL master-slave synchronization principle:

MySQL Master-Slave synchronization is implemented on the basis of MySQL Master-Slave Replication by setting the binlog on the Master MySQL (enabling it ), slave MySQL reads the binlog from the Master MySQL through an I/O thread, and then transmits it to the Slave MySQL relay log. Then the SQL thread of Slave MySQL reads the relay log from the relay log, then it is applied to the Slave MySQL database. This achieves the master-slave data synchronization function.

XtraBackup backup principle:

Innobackupex keeps tracking InnoDB log files in the background thread, and then copies InnoDB data files. After the data file is copied, the log replication thread ends. In this way, the data copies at different time points and the transaction logs after the backup start are obtained. After completing the preceding steps, you can use the InnoDB crash recovery code to execute the transaction log (redo log) to achieve data consistency.

There are two backup processes:

Backup, backup phase, tracking transaction logs and copying data files (physical backup ).

Preparing: replays transaction logs, so that all data is at the same time point to achieve consistency.

Advantages of XtraBackup:

1. Install xtrabackup:

Yum-y install perl-devel 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

Ii. Check mysql version:

Use xtrabackup for full backup and recovery. The 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

3. 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/is the backup directory;

Then, use scp to mount the master

4. perform operations on the server Load balancer instance;

1) shut down the mysql service;

Then clear the files in the mysql data directory. For example, the data directory of mysql on the local machine is/webser/mysql55/var/

2) Start data recovery

Assume that the path of the backup file is/webser/2015-08-12_13-54-56.

Restore log files:

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

Restore data files:

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

The above operations must be performed in order. The operation order is reversed, which may cause the mysql server to fail to start!

3) modify data directory permissions:

Chown-R mysql: mysql/webser/mysql55/var

4) Start mysql in a secure mode to check for exceptions;

/Webser/mysql55/bin/mysqld_safe -- defaults-file =/webser/mysql55/etc/my. cnf &

If no exception is reported, disable mysql.

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

5) view the Location Value of the binlog file and synchronize the data from the following sources:

Cat/tmp/backup/2015-08-12_13-49-32/xtrabackup_binlog_info

Wwwmaster.000458 1022022953

6) perform the following operations on the server Load balancer instance:

CHANGE MASTER

MASTER_HOST = '1970. 168.1.xxx ',

MASTER_USER = 'rep ',

MASTER_PASSWORD = 'xxxxxx ',

MASTER_PORT = 3306,

MASTER_LOG_FILE = 'wwwmaster. 100 ',

MASTER_LOG_POS = 1022022953;

Start master-slave synchronization:

Start slave;

View the slave status:

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

This article permanently updates the link address:

,