How to check MASTER / SLAVE MySQL nodes status – Check MySQL Replication Status

Thursday, 19th April 2012

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 statusG;
*************************** 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 statusG;

Here is a sample returned output:
 

mysql> show slave statusG;*************************** 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: 0Master_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.

Share this on:

Download PDFDownload PDF

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

4 Responses to “How to check MASTER / SLAVE MySQL nodes status – Check MySQL Replication Status”

  1. Thibs says:
    Firefox 11.0 Firefox 11.0 Windows 7 x64 Edition Windows 7 x64 Edition
    Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0

    Hello,
    If your are interrested, here is the script I've done a few years ago to monitor the replication into my monitoring system
     
    #!/usr/bin/perl -w
    use strict;
    use DBI;
    # Script to test MySQL replication by TRD 08/01/2008

    if (scalar(@ARGV)==3) {
            #Variables
            my $dbServer="DBI:mysql:database=syslog;host=$ARGV[0]";
            my $dbUser=$ARGV[1];
            my $dbPwd=$ARGV[2];

            #Connect to DB
            my $db=DBI->connect($dbServer,$dbUser,$dbPwd) || exit 2;

            my $rs=$db->prepare('show slave status');
            $rs->execute();
            my $dr=$rs->fetchrow_hashref();
            my $Slave_IO_Running=$dr->{'Slave_IO_Running'};
            my $Slave_SQL_Running=$dr->{'Slave_SQL_Running'};
            $rs->finish();
            $db->disconnect();
            if (($Slave_IO_Running eq 'Yes') && ($Slave_SQL_Running eq 'Yes')) {
                    exit 0;
            }
            else {
                    exit 2;
            }
    }
    else {
            print "Missing parameter\n";
            exit 1;
    }
     

    View CommentView Comment
    • admin says:
      Firefox 3.6.3 Firefox 3.6.3 Windows 7 Windows 7
      Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3

      Hi Thibs,

      Long time no hear you. This is nice script.

      Only I need to change the name of the monitored database, cause right now I got error;

      # perl monitor-replication.pl localhost root password

      DBI connect(‘database=syslog;host=localhost’,’root’,…) failed: Unknown database ‘syslog’ at monitor-replication.pl line 15

      I’ve made minor modifications to your script and added some text to clear up the script is written by you as well as a link to your site http://qmailrocks.thibs.com for further reference.
      The script is here: http://www.pc-freak.net/bshscr/not-mine/monitor-replication.pl

      As it was recently Easter the passed sunday and we have the habit to great each other with the paschal Greeting. I would like to great you with our paschal Greeting Jesus is Risen!

      Best
      Georgi

      View CommentView Comment
      • Thibs says:
        Firefox 11.0 Firefox 11.0 Windows 7 x64 Edition Windows 7 x64 Edition
        Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0

        Thanks for all ! Great andholy Easter to you !

        View CommentView Comment
      • Thibs says:
        Firefox 11.0 Firefox 11.0 Windows 7 x64 Edition Windows 7 x64 Edition
        Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0

        Thanks for all ! Great and holy Easter to you !
        For the script, I can adapt it o allow database name beeing passed as parameter.
        Note that according to my experience, it's useless to check replication for all replicated database .. one is enough to check the whole replication status

        View CommentView Comment

Leave a Reply

CommentLuv badge