Mysql 5.7How to realize multi-master and one-slave multi-source replication
This article mainly introduces mysql 5.7 how to achieve multi-master and one-slave multi-source replication, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.
Master server creates user
Master 1
Multi-source synchronization parameters
# multi replication
Master_info_repository=TABLE
Relay_log_info_repository=TABLE
Turn on GTID
[mysqld]
# GTID parameter
Log-slave-updates=true
Gtid-mode=on
Enforce-gtid-consistency=true
Report-port=3306
Report-host=192.168.56.212
Master-info-repository=TABLE
Relay-log-info-repository=TABLE
Sync-master-info=1
Slave-parallel-workers=10
Binlog-checksum=CRC32
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log_events=1
[mysql@ray] $/ usr/local/mysql/bin/mysql-uroot-p123456-S / data/3306/soket/mysql.sock
Mysql > GRANT REPLICATION SLAVE ON *. * TO 'rep'@'%' IDENTIFIED BY' 123456'
Query OK, 0 rows affected (0.00 sec)
Mysql > flush privileges
Query OK, 0 rows affected (0.05 sec)
Master 2
Multi-source synchronization parameters
# multi replication
Master_info_repository=TABLE
Relay_log_info_repository=TABLE
Turn on GTID
[mysqld]
# GTID parameter
Log-slave-updates=true
Gtid-mode=on
Enforce-gtid-consistency=true
Report-port=3307
Report-host=192.168.56.212
Master-info-repository=TABLE
Relay-log-info-repository=TABLE
Sync-master-info=1
Slave-parallel-workers=10
Binlog-checksum=CRC32
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log_events=1
[mysql@ray] $/ usr/local/mysql/bin/mysql-uroot-p123456-S / data/3307/soket/mysql.sock
Mysql > GRANT REPLICATION SLAVE ON *. * TO 'rep'@'%' IDENTIFIED BY' 123456'
Query OK, 0 rows affected (0.00 sec)
Mysql > flush privileges
Query OK, 0 rows affected (0.05 sec)
Connect to the master server from the slave server
Multi-source synchronization parameters
# multi replication
Master_info_repository=TABLE
Relay_log_info_repository=TABLE
Turn on GTID
[mysqld]
# GTID parameter
Log-slave-updates=true
Gtid-mode=on
Enforce-gtid-consistency=true
Report-port=3308
Report-host=192.168.56.212
Master-info-repository=TABLE
Relay-log-info-repository=TABLE
Sync-master-info=1
Slave-parallel-workers=10
Binlog-checksum=CRC32
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log_events=1
Connect Master 1:
Change master to
Master_host='192.168.56.212'
Master_user='rep'
Master_password='123456'
Master_port=3306
Master_auto_position = 1
For channel 'm1'
Connect Master 2
Change master to
Master_host='192.168.56.212'
Master_user='rep'
Master_password='123456'
Master_port=3307
Master_auto_position = 1
For channel 'm2'
Start the slave process
Mysql > start slave
View the copy process
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.212
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ray-bin.000005
Read_Master_Log_Pos: 194
Relay_Log_File: localhost-relay-bin-m1.000004
Relay_Log_Pos: 403
Relay_Master_Log_File: ray-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 194
Relay_Log_Space: 1260
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 97e8847a-ffdf-11e6-87ed-08002736c224
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2
Executed_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2
97e8847a-ffdf-11e6-87ed-08002736c224:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: m1
Master_TLS_Version:
* 2. Row * *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.212
Master_User: rep
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: ray-bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: localhost-relay-bin-m2.000005
Relay_Log_Pos: 403
Relay_Master_Log_File: ray-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 194
Relay_Log_Space: 1083
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 517e04ac-ffe3-11e6-a7ea-08002736c224
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2
Executed_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2
97e8847a-ffdf-11e6-87ed-08002736c224:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: m2
Master_TLS_Version:
2 rows in set (0.00 sec)
Start and stop separate slave processes
Mysql > start slave for channel 'm1'
Mysql > stop slave for channel 'm1'
MariaDB:
CHANGE MASTER 'm1' TO
MASTER_HOST='192.168.56.91'
MASTER_USER='rep'
MASTER_PASSWORD='123456'
MASTER_PORT=3306
MASTER_LOG_FILE='ray-bin.000007'
MASTER_LOG_POS=396
Mysql:
CHANGE MASTER TO
MASTER_HOST='192.168.56.91'
MASTER_USER='rep'
MASTER_PASSWORD='123456'
MASTER_PORT=3306
MASTER_LOG_FILE='ray-bin.000007'
MASTER_LOG_POS=396
For channel= "M1"
Thank you for reading this article carefully. I hope the article "mysql 5.7 how to achieve multi-master, one-slave, multi-source replication" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!
