There are not many details on how to add an additional source to a multi-source Mysql 5.7 replication based on GTID. This note helps you to understand the simple steps to add additional sources.
Here we already have a channel running in a multi-source replication configuration.
As you can see below, we have one channel already configured and working
[sudeep@db-slave ~]$ sudo mysql -e "SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.27
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 459719526
Relay_Log_File: mysqld-relay-bin-db@002d01.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.user
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 459719526
Relay_Log_Space: 632
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: 15
Master_UUID: a21bbd99-935e-11ea-a7c0-080027677b11
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:
Executed_Gtid_Set: a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: db-master-1
Master_TLS_Version:
One important thing that we need to make a note in the case of multi-source replication is that the gtid_purged is global to the slave. So if we try to setup gtid_purged value based on the news source, the replication for the existing channel will fail.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.27
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 459719526
Relay_Log_File: mysqld-relay-bin-db@002d01.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.user
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 459719526
Relay_Log_Space: 632
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: 15
Master_UUID: a21bbd99-935e-11ea-a7c0-080027677b11
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:
Executed_Gtid_Set: a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: db-master-1
Master_TLS_Version:
One important thing that we need to make a note in the case of multi-source replication is that the gtid_purged is global to the slave. So if we try to setup gtid_purged value based on the news source, the replication for the existing channel will fail.
The solution is to make sure that we take the value of gtid_executed from the existing source say db-master-1 and make a note of it first
On Master1
----------------
[sudeep@db-master-1 ~]$ sudo mysql -e "show global variables like 'gtid_executed'"
+---------------+------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------+
| gtid_executed | a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484 |
+---------------+------------------------------------------------+
On master2
---------------
Take a mysqldump from db-master-2 , say mysqldump_db-master-2_2020-05-14.sql.gz and then copy it to slave
On slave
------------
[sudeep@db-slave ~]$ unzip mysqldump_master-2_2020-05-14.sql.gz
On Master1
----------------
[sudeep@db-master-1 ~]$ sudo mysql -e "show global variables like 'gtid_executed'"
+---------------+------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------+
| gtid_executed | a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484 |
+---------------+------------------------------------------------+
On master2
---------------
Take a mysqldump from db-master-2 , say mysqldump_db-master-2_2020-05-14.sql.gz and then copy it to slave
On slave
------------
[sudeep@db-slave ~]$ unzip mysqldump_master-2_2020-05-14.sql.gz
[sudeep@db-slave ~]$ cat mysqldump_db-master-2_$(date +%F).sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''
475d926a-9309-11ea-9dee-080027cfdd46:1-2493391
[sudeep@db-slave ~]$ sed '/GTID_PURGED/d' mysqldump_db-master-2_$(date +%F).sql > mysqldump_db-master-2_gtid_nopurge_$(date +%F).sql
[sudeep@db-slave ~]$ sudo mysql -e "source mysqldump_db-master-2_gtid_nopurge_$(date +%F).sql"
[sudeep@db-slave ~]$ sudo mysql -e "RESET MASTER"
[sudeep@db-slave ~]$ sudo mysql -e 'SET @@GLOBAL.gtid_purged = "a21bbd99-935e-11ea-a7c0-080027677b11:1-3369484,475d926a-9309-11ea-9dee-080027cfdd46:1-2493391"'
[sudeep@db-slave ~]$ sudo mysql -e "CHANGE MASTER TO MASTER_HOST='172.16.1.28',MASTER_USER='repl',MASTER_PASSWORD='Password',MASTER_AUTO_POSITION = 1 FOR CHANNEL 'db-master-2'"
[sudeep@db-slave ~]$ sudo mysql -e "START SLAVE FOR CHANNEL 'db-master-2'"
[sudeep@db-slave ~]$ sudo mysql -e "SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.27
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 459719703
Relay_Log_File: mysqld-relay-bin-db@002d01.000002
Relay_Log_Pos: 591
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.user
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 459719703
Relay_Log_Space: 809
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: 15
Master_UUID: a21bbd99-935e-11ea-a7c0-080027677b11
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: a21bbd99-935e-11ea-a7c0-080027677b11:3369485
Executed_Gtid_Set: 475d926a-9309-11ea-9dee-080027cfdd46:1-2493391,
a21bbd99-935e-11ea-a7c0-080027677b11:1-3369485
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: db-master-1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.28
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 365
Relay_Log_File: mysqld-relay-bin-db@002d02.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema,sys
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.user
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 365
Relay_Log_Space: 632
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: 28
Master_UUID: 475d926a-9309-11ea-9dee-080027cfdd46
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:
Executed_Gtid_Set: 475d926a-9309-11ea-9dee-080027cfdd46:1-2493391,
a21bbd99-935e-11ea-a7c0-080027677b11:1-3369485
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: db-master-2
Master_TLS_Version:
We have added a second source channel db-master-2.
Posts
No comments:
Post a Comment