Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.