MySQL Replication Status

About replication

MySQL replication involves propogating changes on the master database server to one or more slave database servers. It does this by transferring the SQL statements that modify data from master to slave. It is important to understand that replication uses SQL statements, not changes to data files (like Oracle ODG.)
There are 2 threads in MySQL that do replication: the IO Thread and the SQL Thread. The IO Thread is responsible for talking to the master, reading the master's binary logs and writing the SQL statements to the relay logs (they use the same format as binary logs) on the slave server. The SQL Thread is responsible for reading the relay logs and applying the SQL statements to the local data files, if appropriate.

Getting the status

The status of MySQL replication is checked on the slave itself, not from the master. To get the status, issue the command:

(Note: use '\G' instead of ';' on the end of the statement if you want to read it vertically. Otherwise, it gets printed on one long line across the screen.)
The output will look similar to this:
Let's go over this one line at a time.

• Slave_IO_State: This is what the IO Thread is currently doing.
• Master_Host: This is the master server the slave is replicating from.
• Master_User: This is the database user account used for replication. This account requires certain privileges to perform replication properly.
• Master_Port: This is the port on the master server the IO Thread connects to.
• Connect_Retry: If the IO Thread looses contact with the master server, it will try to re-establish connection every Connect_Retry seconds.
• Master_Log_File: This is the binary log on the master server the IO Thread is currently reading from.
• Read_Master_Log_Pos: This is the postition in the Master_Log_File the IO Thread has read up to.
• Relay_Log_File: This is the relay log the IO Thread is currently writing SQL statements to that it read from Master_Log_File.
• Relay_Log_Pos: This is the position in the relay log the IO Thread has written to.
• Relay_Master_Log_File: As SQL statements are replicated, this tells up to which binary log on the master replication is in sync with. In other words, the slave would be identical if the master stopped with the Relay_Master_Log_File binary log.
• Slave_IO_Running: Whether the I/O thread for reading the master's binary log is running. Normally, you want this to be Yes unless you have not yet started replication or have explicitly stopped it with STOP SLAVE.
• Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be Yes.
• Replicate_Do_DB: This is a list of databases to replicate. If blank, all databases get replicated.
• Replicate_Ignore_DB: This is a list of databases to not replicate.
• Replicate_Do_Table: This is a list of tables to replicate. If blank, all tables get replicated.
• Replicate_Ignore_Table: This is a list of table to not replicate.
• Replicate_Wild_Do_Table: If you used a wildcard to specify tables for replication, this is the list of tables that match the wildcard when expanded.
• Replicate_Wild_Ignore_Table: If yuo used a wildcard to specify table to not replicate, this is the list of tables that match the waldcard when expanded.
• Last_Errno: If replication encounters an error, this is the error number you can use to look up the error.
• Last_Error: If replication encoutners an error, this is the text of the error, including the SQL statement that caused the error.
• Skip_Counter: The Skip_Counter is used to skip statements in the relay log.
• Exec_Master_Log_Pos: This is the position in the Relay_Master_Log_File binary log replication has synced to.
• Relay_Log_Space: This is the amount of disk space the IO Thread is allowed to use before it pauses to let the SQL Thread catch up.
• Until_Condition: You can set up replication to stop on certain conditions. If set, they are listed here.
• Until_Log_File: You can replicate up to a certain binary log on the master.
• Until_Log_Pos: You can replicate up to a certain position in the Until_Log_File binary log.
• Master_SSL_Allowed: This will be a "Yes" or "No" depending on if the master allows SSL connections.
• Master_SSL_CA_File: This is the CA file used for SSL connections.
• Master_SSL_CA_Path: This is the CA path used for verifying SSL certificates.
• Master_SSL_Cert: This is the SSL certificate used for the SSL connection.
• Master_SSL_Cipher: This is the cryptographic cipher used for SSL encryption.
• Master_SSL_Key: This is the key used by the cipher to encrypt communications.
• Seconds_Behind_Master: This is the number of seconds the slave is behind the master. Ideally, this should be zero.
• Last_IO_Error, Last_SQL_Error: The last errors registered by the I/O and SQL threads when processing the relay log. Ideally these should be blank, indicating no errors.

Quick Tip to verify the Replication Status

• If either of these is NO, like this, then replication is stopped:
  Slave_IO_Running: No
  Slave_SQL_Running: No

• The Last_Errno and Last_Error might give you a clue as to what went wrong.
• If all seems OK, you can also confirm further that everything is working by logging into the master and executing SHOW MASTER STATUS and comparing the binary log and exec position.