Xtrabackup Online migration MySQL and build mutual master from sync

I. BACKGROUND

There is a need to migrate the database in the work, but can not stop the service, can not lock the library lock table affect the normal operation of the business, so use Xtrabackup

Second, the environment:

Operating system: CentOS Linux release 7.4.1708 (Core)

mysql:5.6.35

ip:192.168.0.131

Xtrabackup version: percona-xtrabackup-24-2.4.2-1.el7.x86_64.rpm

xtrabackup:https://www.percona.com/downloads/xtrabackup/latest/

Third, installation Xtrabackup

Yum Install Perl Perl Perl perl-dbd-~]# rpm-uvh percona-xtrabackup--2.4. 2-1. el7.x86_64.rpm

Iv. Backup and Recovery

1, the use of the environment: I test is used for the environment of the new Mysql_multi multi-instance mode, specific installation steps see: https://www.cnblogs.com/01-single/p/9051412.html

MY.CNF configuration:

[Email protected] ~]#Cat/usr/local/mysql/My.cnf[client][mysqld][mysqld_multi]user=Mysqlpassword=Mysqlmysqld=/usr/local/mysql/bin/Mysqld_safemysqladmin=/usr/local/mysql/bin/Mysqladmin[mysqld3307]socket=/tmp/Mysql3307.sockport=3307User=Mysqlpid-file=/alidata1/mysql_multi/mysql3307/Mysqld.piddatadir=/alidata1/mysql_multi/mysql3307/Mydatalog-bin=/alidata1/mysql_multi/mysql3307/log/Binlogserver-ID=3307001innodb_buffer_pool_size=256mlog_error=/alidata1/mysql_multi/mysql3307/log/log-errexpire_logs_days=5Bind-address =192.168.0.131Skip-name-Resolve[mysqld3308]socket=/tmp/Mysql3308.sockport=3308User=Mysqlpid-file=/alidata1/mysql_multi/mysql3308/Mysqld.piddatadir=/alidata1/mysql_multi/mysql3308/Mydatalog-bin=/alidata1/mysql_multi/mysql3308/log/Binlogserver-ID=3308001innodb_buffer_pool_size=256mlog_error=/alidata1/mysql_multi/mysql3308/log/log-errexpire_logs_days=5Bind-address =192.168.0.131Skip-name-resolve

Must be aware of the need to master from the Server-id can not be the same, or will error

Innobackupex is usually used directly, as it can back up the tables of the InnoDB and MyISAM engines at the same time

Focus on whether the status of Slave_io_running and Slave_sql_runningd is yes

2. Backup:

[Email protected] ~]# Innobackupex--socket=/tmp/mysql3307.sock--user=root--password= 123456 --defaults- file=/usr/local/mysql/my.cnf   /~]# innobackupex--defaults-file=/usr/local/mysql/ MY.CNF--socket=/tmp/mysql3307.sock--user=root--password=123456  --apply-log/mysqlbackup/2018 --21_10--/#保持事务一致性

If you use another server as the master slave, you need to transfer the backed up data:

SCP 192.168. 0.131:/mysqlbackup/2018--21_10--/tmp/backup

3. Recovery:

[Email protected] mysql3308]#pwd/alidata1/mysql_multi/Mysql3308[[email protected] mysql3308]#MVmydata Mydatabak #备份原有的数据库 [[email protected] mysql3308]#mkdirMyData #新建数据库目录
#恢复数据库:
[Email protected] mysql3308]# Innobackupex--defaults-file=/USR/LOCAL/MYSQL/MY.CNF--datadir=/alidata1/mysql_multi/mysql3308/mydata/--socket=/tmp/mysql3308.sock--user= Root--password=123456--copy-back/mysqlbackup/2018- .-21_10- *- the/[email protected] mysql3308]#Chown-R mysql:mysql mydata #还原权限 [[email protected] mysql3308]# CD/usr/local/mysql/bin/
#重新启动3308数据库 [[email protected] bin]#./mysqld_multi--defaults-file=.. /my.cnf stop3308--user=root--password=123456[email protected] bin]#./mysqld_multi--defaults-file=/USR/LOCAL/MYSQL/MY.CNF start3308[email protected] bin]# netstat-nlpt |grepmysqltcp0      0 192.168.0.131:3307      0.0.0.0:* LISTEN35205/mysqld TCP0      0 192.168.0.131:3308      0.0.0.0:* LISTEN37161/mysqld

V. Turn on master/slave synchronization:

First,#主: 192.168.0.131 3307MySQL> GRANT REPLICATION SLAVE on * * to'Slaveuser'@'%'Identified by'slave123'; MySQL>Show Master Status \g#从: 192.168.0.131 3308[[Email protected]~]#Cat/mysqlbackup/2018- .-21_10- *- the/xtrabackup_binlog_info Binlog.000001    32399093MySQL> Change MASTER to Master_host='192.168.0.131', master_user='Slaveuser', master_password='slave123', master_port=3307, master_log_file='binlog.000001', master_log_pos=32399093; MySQL>Start Slave;mysql>Show Slave Status\g***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.0.131master_user:slaveuser Master_port:3307................  Slave_io_running:yes slave_sql_running:yes ... ...... .........1RowinchSet (0.00sec) Two,#主: 192.168.0.131 3308MySQL> GRANT REPLICATION SLAVE on * * to'Slaveuser'@'%'Identified by'slave123'; MySQL>Show Master Status \g***************************1. Row ***************************File:binlog.000003Position:592Binlog_Do_DB:Binlog_Ignore_DB:Executed_Gtid_Set:1RowinchSet (0.00sec) #从: 192.168.0.131 3307MySQL> Change MASTER to Master_host='192.168.0.131', master_user='Slaveuser', master_password='slave123', master_port=3308, master_log_file='binlog.000003', master_log_pos=592; MySQL>Start Slave;mysql>Show Slave Status\g***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.0.131master_user:slaveuser Master_port:3308................  Slave_io_running:yes slave_sql_running:yes ... ...... .........1RowinchSet (0.00Sec

At this point each of the main from the configured, two libraries can perform read and write operations, and synchronization with each other

If the server is two different IP from the master, just change the IP on the line, operating the same way

Xtrabackup Online migration MySQL and build mutual master from sync