Use binary hot backup to create MySQL master-slave Replication

Set up the environment description machine Master190: 192.168.1.190 (enable binlog, server-id1) Slave191: 192.168.1.191Slave192: 192.168.1

Build Environment Description machine description Master 190: 192.168.1.190 (enable binlog, server-id = 1) Slave 191: 192.168.1.191Slave 192: 192.168.1

Environment setup

Machine description

Master 190: 192.168.1.190 (enable binlog, server-id = 1)
Slave 191: 192.168.1.191
Slave 192: 192.168.1.192
MySQL 5.5.29 and Percona XtraBackup 2.06 are installed on all the above three machines.

Create a new Slave based on the Master binary Hot Backup

Create the account MySQL> create user 'newrepl' @ '190 for mysql replication on Master 191. 168.1.191 'identified by '20170101'; mysql> grant replication slave on *. * to 'newrepl' @ '192. 168.1.191 '; mysql> flush privileges;

Remote hot backup of MySQL Data from Master 190 to Slave 191 [root @ CentOS190 ~] # Innobackupex -- user = backup -- password = 123456 -- parallel = 4 -- stream = tar./| ssh root@192.168.1.191 "tar-ixf--C/var/lib/mysql/data"

Apply the backup on Slave 191 and set the permission for the backup data directory [root @ centos191 ~] # Innobackupex -- apply-log -- use-memory = 4G/var/lib/mysql/data [root @ centos191 ~] # Chown-R mysql: mysql/var/lib/mysql/data

Slave 191 stop MySQL, MySQL configuration, and then start MySQL [root @ centos191 mysql] # service mysql stop [root @ centos191 mysql] # scp root@192.168.1.190:/etc/my. cnf/etc/my. cnf

Modify/etc/my. cnf:

... [Mysqld] datadir =/var/lib/mysql/dataserver-id = 2 # Master server-id = 1relay-log = slave-relay-binrelay-log-index = slave-relay-bin.index...

After configuration, start MySQL:

[Root @ centos191 mysql] # service mysql startStarting MySQL... [OK]

Establish a replication connection to start replication and check the running status of slave.

View the binlog file name and offset information of the Hot Backup:

[Root @ centos191 mysql] # cat/var/lib/mysql/data/xtrabackup_binlog_info master-bin.000005107

Create a replication connection and check whether the connection is successful:

Mysql> change master to-> MASTER_HOST = '2017. 168.1.190 ',-> MASTER_USER = 'newrepl',-> MASTER_PASSWORD = '000000',-> MASTER_PORT = 123456,-> MASTER_LOG_FILE = 'master-bin.000005 ', -> MASTER_LOG_POS = 107; Query OK, 0 rows affected (0.96 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show global status like 'slave _ running '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Slave_running | ON | + --------------- + ------- + 1 row in set (0.00 sec)

Create a new Slave based on Slave binary Hot Backup

Create the account [root @ centos191 MySQL] # mysql-uroot-p-h192.168.1.190.0. for mysql replication on Master 190... mysql> create user 'newrepl' @ '192. 168.1.192 'identified by '20170101'; mysql> grant replication slave on *. * to 'newrepl' @ '192. 168.1.192 '; mysql> flush privileges;

Remote hot backup of MySQL Data from Slave 191 to Slave 192 # innobackupex -- user = backup -- password = 123456 -- parallel = 4 -- slave-info -- safe-slave-backup -- stream = tar. /| ssh root@192.168.1.192 "tar-ixf--C/var/lib/mysql/data"

Note: here the backup uses -- slave-info to record the file name and offset of the binary log of the Master to the xtrabackup_slave_info file. using -- safe-slave-backup will suspend the SQL thread of Slave until the backup is completed, so as to ensure consistent replication status.

Apply the backup on the new Slave 192 and set the permission for the backup data directory

[Root @ centos192 ~] # Innobackupex-apply-log-use-memory = 4G/var/lib/mysql/data
[Root @ centos192 ~] # Chown-R mysql: mysql/var/lib/mysql/data

New Slave 192 stop MySQL, MySQL configuration, and then start MySQL [root @ centos192 mysql] # service mysql stop [root @ centos192 mysql] # scp root@192.168.1.191:/etc/my. cnf/etc/my. cnf

Modify/etc/my. cnf:

... [Mysqld] server-id = 3skip-slave-start...

Start MySQL:

[Root @ centos192 mysql] # service mysql startStarting MySQL... [OK]

Establish a replication connection to start replication and check the running status of slave.

View the binlog file name and offset information of the Slave Hot Backup:

[Root @ centos192 mysql] # cat/var/lib/mysql/data/xtrabackup_slave_info change master to MASTER_LOG_FILE = 'master-bin.000005 ', MASTER_LOG_POS = 614672

Create a replication connection and check whether the connection is successful:

Mysql> change master to-> MASTER_HOST = '2017. 168.1.190 ',-> MASTER_USER = 'newrepl',-> MASTER_PASSWORD = '000000',-> MASTER_PORT = 123456,-> MASTER_LOG_FILE = 'master-bin.000005 ', -> MASTER_LOG_POS = 614672; Query OK, 0 rows affected (0.33 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \ G ;... slave_IO_Running: YesSlave_ SQL _Running: Yes... seconds_Behind_Master: 0...

Modify/etc/my. cnf, comment out "skip-slave-start", and restart MySQL.

# Sed-I's/skip-slave-start/# skip-slave-start/G'/etc/my. cnf # service mysql restart

Recommended reading:

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server