XtraBackup enables MySQL to redo master-slave replication without stopping services

XtraBackup enables MySQL to redo master-slave replication without stopping services

You can use mysqldump to implement MySQL master-slave synchronization without stopping services, but this efficiency is relatively low. XtraBackup is used to achieve this, which is more efficient!

Xtrabackup installation:
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

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

Ii. Check the 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/

Back up the specified database:
Innobackupex -- defaults-file =/webser/mysql55/etc/my. cnf -- user = root -- password = xxxxx -- include = 'abc. * | salt. * '/tmp/

Note that multiple databases are separated by the "|" symbol!

PS:/tmp/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/tmp/2016-04-29_16-00-54.

Restore log files:
Innobackupex -- defaults-file =/webser/mysql55/etc/my. cnf -- user = root -- password = xxxx -- apply-log/tmp/2016-04-29_16-00-54/
Restore data files:
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) 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/2016-04-29_16-00-54/xtrabackup_binlog_info
Wwwmaster.000458 1022022953
6) perform the following operations on the server Load balancer instance:
CHANGE MASTER
MASTER_HOST = '1970. 168.3.xxx ',
MASTER_USER = 'mysql _ rep ',
MASTER_PASSWORD = 'xxxx ',
MASTER_PORT = 33066,
MASTER_LOG_FILE = 'wwwmaster. 100 ',
MASTER_LOG_POS = 1022022953;
Start master-slave synchronization:
Start slave;

Problems encountered:
1) An error is reported after startup:
[Root @ GuoletaoTest02 etc] #/etc/init. d/mysql55 start
Starting MySQL (Percona Server)... The server quit without u [FAILED] PID file (/data/mysql55/var/GuoletaoTest02.pid ).
View error logs:
160429 16:47:12 [ERROR] Can't open the mysql. plugin table. Please run mysql_upgrade to create it.
Solution:
./Mysql_install_db -- user = mysql -- basedir =/webser/mysql55/-- datadir =/data/mysql55/var/
Run the preceding statements to solve the problem!

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: