Notes on MySQL replication terminology, how to interpret the results of SHOW SLAVE STATUS
and SHOW MASTER STATUS
, and how to correctly configure replication for new slave and master hosts in your MySQL clusters.
Note that the technical details of e.g. cloning a MySQL server aren’t covered; let’s consider them left as exercises for the reader.
To start, here’s MySQL’s own definition of the threads involved in replication:
MySQL replication capabilities are implemented using three threads, one on the master server and two on the slave:
Binlog dump thread. The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of
SHOW PROCESSLIST
on the master as the Binlog Dump thread.The binary log dump thread acquires a lock on the master’s binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.
Slave I/O thread. When a
START SLAVE
statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.The slave I/O thread reads the updates that the master’s Binlog Dump thread sends (see previous item) and copies them to local files that comprise the slave’s relay log.
The state of this thread is shown as
Slave_IO_running
in the output ofSHOW SLAVE STATUS
or asSlave_running
in the output ofSHOW STATUS
.Slave SQL thread. The slave creates an SQL thread to read the relay log that is written by the slave I/O thread and execute the events contained therein.
– MySQL 5.5 Reference Manual, 17.2.1 Replication Implementation Details
SHOW MASTER STATUS
tells us where the Binlog dump thread has written up to:
root@db01 $ mysql -e 'SHOW MASTER STATUS\G'
*************************** 1. row ***************************
File: master.014884
Position: 968257120
Binlog_Do_DB:
Binlog_Ignore_DB: monitor
If we cloned this server at this point and wanted the clone to slave off this server, we should:
CHANGE MASTER TO MASTER_HOST='db01'
MASTER_LOG_FILE='$File'
MASTER_LOG_POS=$Position;
SHOW SLAVE STATUS
can tell us – amongst other things – where the Slave I/O and SQL threads are up to:
root@db02 $ mysql -e 'SHOW SLAVE STATUS\G'
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db01
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master.014884
Read_Master_Log_Pos: 968223858
Relay_Log_File: relay.000320
Relay_Log_Pos: 968223335
Relay_Master_Log_File: master.014884
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: 968223192
Relay_Log_Space: 968224187
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
The Slave I/O thread has read up to Read_Master_Log_Pos
in Master_Log_File
from Master_Host
’s binlogs.
The Slave SQL thread has executed events up to Exec_Master_Log_Pos
in Relay_Master_Log_File
from Master_Host
’s binlogs.
In theory, when Seconds_Behind_Master
is 0
, all these values should line
up with the values from SHOW MASTER STATUS
on $Master_Host
.
If we create a clone of this slave, and want it to slave off the same $Master_Host
, we should:
CHANGE MASTER TO MASTER_HOST='$Master_Host'
MASTER_LOG_FILE='$Relay_Master_Log_File'
MASTER_LOG_POS=$Exec_Master_Log_Pos;
All fairly simple so far.
So!
Let’s say we have a slave db02
.
db02
is happily slaving off its master, db01
.
But we want to shoot db01
and replace it with a doppelgänger while db02
is distracted! For reasons.
So we clone db01
as new-db01
and set it up to slave off db01
.
Now the tricky bits:
Stop replication on
db02
– we do this first as we want it to fall a little behinddb01
andnew-db
root@db02 $ mysql -e 'STOP SLAVE;' root@db02 $ mysql -e 'SHOW SLAVE STATUS\G' | grep Running Slave_IO_Running: No Slave_SQL_Running: No
Ensure that all writes that made it to
db01
are flushed to disk, and explicitly lock it against any further writesroot@db01 $ mysql mysql> FLUSH TABLES WITH READ LOCK;
N.B. Leave this MySQL session running! “If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session”, so if you close this session writes can resume.
Once
new-db01
is in sync with the stalleddb01
, stop replication onnew-db01
root@new-db01 # mysql -e 'STOP SLAVE;' root@new-db01 # mysql -e 'SHOW SLAVE STATUS\G' | grep Running Slave_IO_Running: No Slave_SQL_Running: No
Get the SQL thread state on
new-db01
root@new-db01 # mysql -e 'SHOW SLAVE STATUS\G' | grep -E "Exec_Master_Log_Pos|Relay_Master_Log_File" Relay_Master_Log_File: master.014891 Exec_Master_Log_Pos: 184690632
Get the binlog thread state on
new-db01
root@new-db01 # mysql -e 'SHOW MASTER STATUS\G' *************************** 1. row *************************** File: master.000115 Position: 1073640720 Binlog_Do_DB: Binlog_Ignore_DB: monitor
This gives us a pair of binlog coordinates on
db01
(fromSHOW SLAVE STATUS
) andnew-db01
that match.Have
db02
resuming slaving offdb01
until it’s caught up to the same point asnew-db01
root@db02 # mysql -e "START SLAVE UNTIL MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Exec_Master_Log_Pos;" root@db02 # mysql -e 'SHOW SLAVE STATUS\G' | grep -E "_Running|Until_" Slave_IO_Running: Yes Slave_SQL_Running: Yes Until_Condition: ? Until_Log_File: ? Until_Log_Pos: ?
Stop replication on
db02
root@db02 # mysql -e 'STOP SLAVE;' root@db02 # mysql -e 'SHOW SLAVE STATUS\G' | grep Running Slave_IO_Running: No Slave_SQL_Running: No
Slave
db02
offnew-db01
starting at the end ofnew-db01
’s binlogs (i.e. theFile
andPosition
values fromSHOW MASTER STATUS
above)root@db02 # mysql -e "CHANGE MASTER TO MASTER_HOST='new-db01', MASTER_LOG_FILE='$File', MASTER_LOG_POS=$Position;"
Start replication on
db02
root@db02 # mysql -e 'START SLAVE;'
Check that replication on
db02
is happyroot@db02 # mysql -e 'SHOW SLAVE STATUS\G'
Do all the other things that replace
db01
withnew-db01
– update its hostname, its IP addresses, etc. etc. etc.
Et voilà, db02
barely has to know that we killed and replaced its master.