Showing posts with label Mysql REPLICATION. Show all posts
Showing posts with label Mysql REPLICATION. Show all posts

Tuesday, 11 September 2018

PHP script to check MySQL replication status

If you have MySQL replication running in either a master-slave or master-master type setup then don't assume everything is running perfectly all the time. Power cuts and other disasters do happen so you need to check periodically to ensure it's all working. This post has a PHP script to check for any MySQL replication errors and then emails if there are any issues

MySQL Slave Status

Get the current MySQL Slave status by running the command "SHOW SLAVE STATUS" on the slave. This will spit out all sorts of information including whether the following are running ("Yes" or "No"):
  • Slave_IO_Running
  • Slave_SQL_Running
and then an error number and message for each of the above like so:
  • Last_IO_Errno
  • Last_IO_Error
  • Last_SQL_Errno
  • Last_SQL_Error

PHP script to check MySQL slave status

The following script should be run periodically. Modify it to suit your own purposes as you see fit.
Enter values for the username and password to log in as (my script assumes the same login and password is used for all servers, and that all servers can be logged into from the current server) and a list of MySQL servers to check.
$username = '[username]';
$password = '[password]';
$servers = array(
 '[server1]',
 '[server2]',
 '[serverN]',
);

$errors = '';

foreach($servers as $server) {
 $link = mysql_connect($server, $username, $password);
 if($link) {
  $res = mysql_query("SHOW SLAVE STATUS", $link);
  $row = mysql_fetch_assoc($res);
  if($row['Slave_IO_Running'] == 'No') {
   $errors .= "Slave IO not running on $server\n";
   $errors .= "Error number: {$row['Last_IO_Errno']}\n";
   $errors .= "Error message: {$row['Last_IO_Error']}\n\n";
  }
  if($row['Slave_SQL_Running'] == 'No') {
   $errors .= "Slave SQL not running on $server\n";
   $errors .= "Error number: {$row['Last_SQL_Errno']}\n";
   $errors .= "Error message: {$row['Last_SQL_Error']}\n\n";
  }
  mysql_close($link);
 }
 else {
  $errors .= "Could not connect to $server\n\n";
 }
}

if($errors) {
 mail('[email address]', 'MySQL slave errors', $errors);
}

Useful links for dealing with errors

I'm not going to help you fix errors myself as I'd simply be copying more or less the information I've found on other websites (and I don't know how accurate or successful all of the solutions are) so will link to some solutions here.
HowtoForge has a post called "how to repair MySQL replication" which shows examples of the SHOW SLAVE STATUS command and covers fixing issues when Slave_SQL_Running is "No". I've used the solution presented a couple of times to fix issues, but do be aware if you've got something like a primary key violation issue and you skip the error then your databases may not actually be accurately in sync.
I personally got a Slave_IO_Running = "No" error which resulted in the message "Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log".
I did the change master log file solution which worked for me, but again be careful as you may lose data. Note you don't need to also set the master host, user and password too, just the file and position.


Related posts:

How To Repair MySQL Replication

If you have set up MySQL replication, you probably know this problem: sometimes there are invalid MySQL queries which cause the replication to not work anymore. In this short guide I explain how you can repair the replication on the MySQL slave without the need to set it up from scratch again.
I do not issue any guarantee that this will work for you!

1 Identifying The Problem

To find out whether replication is/is not working and what has caused to stop it, you can take a look at the logs. On Debian, for example, MySQL logs to /var/log/syslog:
grep mysql /var/log/syslog
server1:/home/admin# grep mysql /var/log/syslog
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
May 29 09:56:08 http2 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views
May 29 09:56:08 http2 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001079' position 203015142
server1:/home/admin#
You can see what query caused the error, and at what log position the replication stopped.
To verify that the replication is really not working, log in to MySQL:
mysql -u root -p
On the MySQL shell, run:
mysql> SHOW SLAVE STATUS \G
If one of Slave_IO_Running or Slave_SQL_Running is set to No, then the replication is broken:
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 1.2.3.4
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.001079
        Read_Master_Log_Pos: 269214454
             Relay_Log_File: slave-relay.000130
              Relay_Log_Pos: 100125935
      Relay_Master_Log_File: mysql-bin.001079
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: mydb
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1146
                 Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'.
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
        SET thread.views = thread.views + aggregate.views
        WHERE thread.threadid = aggregate.threadid'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 203015142
            Relay_Log_Space: 166325247
            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: NULL
1 row in set (0.00 sec)

mysql>

2 Repairing The Replication

Just to go sure, we stop the slave:
mysql> STOP SLAVE;
Fixing the problem is actually quite easy. We tell the slave to simply skip the invalid SQL query:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
This tells the slave to skip one query (which is the invalid one that caused the replication to stop). If you'd like to skip two queries, you'd use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.
That's it already. Now we can start the slave again...
mysql> START SLAVE;
... and check if replication is working again:
mysql> SHOW SLAVE STATUS \G
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 1.2.3.4
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.001079
        Read_Master_Log_Pos: 447560366
             Relay_Log_File: slave-relay.000130
              Relay_Log_Pos: 225644062
      Relay_Master_Log_File: mysql-bin.001079
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: mydb
        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: 447560366
            Relay_Log_Space: 225644062
            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
1 row in set (0.00 sec)

mysql>
As you see, both Slave_IO_Running and Slave_SQL_Running are set to Yes now.
Now leave the MySQL shell...
mysql> quit;
... and check the log again:
grep mysql /var/log/syslog
server1:/home/admin# grep mysql /var/log/syslog
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
May 29 09:56:08 http2 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views
May 29 09:56:08 http2 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001079' position 203015142
May 29 11:42:13 http2 mysqld[1380]: 080529 11:42:13 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.001079' at position 203015142, relay log '/var/lib/mysql/slave-relay.000130' position: 100125935
server1:/home/admin#
The last line says that replication has started again, and if you see no errors after that line, everything is ok.

Subscribe to: Posts (Atom)