How to check MASTER / SLAVE MySQL nodes status -
Check MySQL Replication Status
I'm doing replication for one server. Its not the first time I do
configure replication between two MySQL database nodes, however
since I haven't done it for a few years, my "know how" has mostly
vanished so I had some troubles in setting it up. Once I followed
some steps to configure replication I had to check if the two
MASTER / Slave MySQL db nodes communicate properly. Hence I decided
to drop a short post on that just in case if someone has to do the
same or if I myself forget how I did it so I can check later
on:
1. Check if MASTER MySQL server node is configured
properly
The standard way to check a MySQL master node status info is
with:
mysql> show master status;
+------------------+----------+---------------------------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------------------------------------------------+------------------+
| mysql-bin.000007 | 106 | database1,database2,database3 | |
+------------------+----------+---------------------------------------------------------+------------------+
1 row in set (0.00 sec)
By putting
\G some extra status info is provided:
mysql> show master status\G;
*************************** 1. row
***************************
File: mysql-bin.000007
Position: 106
Binlog_Do_DB: database1,database2,database3
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ERROR:
No query specified
2. Check if Slave MySQL node is configured properly
To check status of the slave the cmd is:
mysql> show slave status;
The command returns an output like:
mysql> show slave status;
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port |
Connect_Retry | Master_Log_File | Read_Master_Log_Pos |
Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File |
Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB |
Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table |
Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno
| Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space
| Until_Condition | Until_Log_File | Until_Log_Pos |
Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path |
Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key |
Seconds_Behind_Master | Master_SSL_Verify_Server_Cert |
Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | HOST_NAME.COM | slave_user |
3306 | 10 | mysql-bin.000007 | 106 | mysqld-relay-bin.000002 | 251
| mysql-bin.000007 | Yes | Yes | database1,database2,database3 | |
| | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No |
0 | | 0 | |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
As you can see the output is not too readable, as there are too
many columns and data to be displayed and this doesn't fit neither
a text console nor a graphical terminal emulator.
To get more readable (more verbose) status for the SQL SLAVE, its
better to use command:
mysql> show slave status\G;
Here is a sample returned output:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event Master_Host:
HOST_NAME.COM Master_User: slave_user Master_Port: 3306
Connect_Retry: 10 Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:
database1,database2,database3 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: 106
Relay_Log_Space: 407 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: 1 row in set (0.00 sec) ERROR: No
query specified
If
show master status or
shwo slave status commands
didn't reveal replication issue, one needs to stare at the mysql
log for more info.