Scenario: Activating existing slave again to sync with master server after cluster dissolved task happened in the master servers (group replication Innodb Cluster).
Steps:
1.Stop
slave to ensure no process to master server.
mysql>
STOP SLAVE;
The STOP
SLAVE command in MySQL is used to stop the slave replication process on a slave
server in a master-slave replication setup. When executed on the slave server,
it halts the replication process and stops the slave from applying any new
changes received from the master.
Query OK, 0
rows affected, 1 warning (0.00 sec)
2.Change
master command to set the new master server ip and credentials details.
->
MASTER_PASSWORD='Password',MASTER_DELAY = 0,MASTER_AUTO_POSITION=1;
Query OK, 0
rows affected, 1 warning (0.05 sec)
3.Then
start slave to sync master copy to slave.
mysql>
START SLAVE;
Query OK, 0
rows affected (0.00 sec)
The START SLAVE command in MySQL is used to start the replication process on a slave server in a master-slave replication setup. When executed on the slave server, it initiates the connection to the master server and begins replicating the changes received from the master.
4.Check
show slave status results.
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTERDBSERVERIP
Master_User: db_repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MASTERDBSERVERNAME-bin.001763
Read_Master_Log_Pos: 843694033
Relay_Log_File: SLAVEDBSERVERNAME-relay-bin.000002
Relay_Log_Pos: 10832949
Relay_Master_Log_File: MASTERDBSERVERNAME-bin.001763
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: 843694033
Relay_Log_Space: 10833158
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: 13
Master_UUID: 08538d8c-ed98-11ea-852d-005056844240
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: 08538d8c-ed98-11ea-852d-005056844240:942835-942878
Executed_Gtid_Set: 08538d8c-ed98-11ea-852d-005056844240:1-942878,
28179efb-72c5-11e4-b496-0050569242d9:1-13995703,
29a42f08-7c8e-11ed-969f-005056846111:1-69949262:70421923-70422753,
329fcb1a-c0dd-11eb-82b1-005056847352:1-66160,
6a6ae23d-183f-11eb-ae83-005056846111:1-114606407:114931406-114935574,
ae4d4435-ecbc-11ea-a835-0050568415e0:1-348529,
d1f71bf8-c0dd-11eb-82b1-005056847352:1-258121803,
d9014b6b-1812-11eb-976b-005056846111:1-292573,
dfc8fe40-336a-11ed-a8ea-005056847352:1-48770981
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
Interpret the Output: The SHOW SLAVE STATUS command provides a result set with various columns containing information about the replication status. Some of the important columns and their meanings include:
- Slave_IO_State: The current state of the slave I/O thread.
- Master_Host and Master_Port: The hostname and port of the master server.
- Slave_IO_Running and Slave_SQL_Running: Whether the slave I/O thread and slave SQL thread are running (Yes) or stopped (No).
- Seconds_Behind_Master: The replication lag in seconds, indicating how far behind the slave is compared to the master.
- Last_Error: Any error message related to replication.
- Master_Log_File and Read_Master_Log_Pos: The binary log file and position from which the slave is currently reading.
- Relay_Log_File and Relay_Log_Pos: The relay log file and position, indicating the last processed event on the slave.
- Exec_Master_Log_Pos: The position of the last executed event on the slave.
These columns provide insights into the replication status, lag, error messages, and positions within the replication process.