Introduction to MySQL-Master-Slave Cascade replication of MySQL replication Technology

Master-Slave Cascade replication of MySQL replication Technology

Master database: mysqld1 on 192.168.12.181

Relay mysqld1 on slave:192.168.12.182

Mysqld4 on cascaded slave:192.168.12.181

The Master database synchronizes the data to the relay slave, and then the relay slave acts as the master database of the cascaded slave for data replication. Under the configured master-slave structure, the master configuration does not need to be changed, only the relay slave and cascading slave need to be configured. The implementation steps are as follows:

(1) Relay slave configuration

a. Configure my.cnf

# / etc/my.cnf

Log_slave_updates=1

Log_bin=/data/mysql/log/vm2-bin.log # the above two are key parameters that must be configured

Read_only=ON # optional

Once configured, restart mysqld

b. Query master status

On the relay slave, query master status and record file and position as the synchronization starting point for cascading slave

[mysql] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | vm2-bin.000009 | 450 | |

+-+

1 row in set (0.00 sec)

c. Backup Relay slave

Backup relay slave as a restore data source for cascading slave

[root@vm2] # mysqldump-uroot-p-- all-databases > slave_bak.sql

[root@vm2 ~] # scp slave_bak.sql vm1:/data/ # to cascade slave

(2) prepare to cascade slave

a. Configuration of cascaded slave

# / etc/my.cnf

[mysqld4]

Port=3309

Basedir=/usr

Datadir=/data/mysql/data4

Socket=/var/lib/mysql/mysql4.sock

Pid-file=/var/run/mysqld/mysqld4.pid

Log-error=/data/mysql/log4/mysqld4.log

User=mysql

Relay-log=/data/mysql/log4/vm4-slave-bin.log

Server_id=4

Slave-skip-errors=all

Start mysqld4, and the database will be initialized at startup through the mysqld_multi command

# mysqld_multi start 4

b. Restore the backup of the relay database

[root@vm1 data] # mysql-uroot-p-S / var/lib/mysql/mysql4.sock