0

We're building a new MySQL server pair for high availability, and of course it's not like managing a single server. We're working with Ubuntu 14.04 LTS and MySQL 5.5.43

I had master-master replication working just fine yesterday after following the guide at https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication, but now the pair isn't sending new inserts or updates to the other server. They work fine on either server, but don't get copied.

The troubleshooting I've done so far has shown this from the SLAVE STATUS command:

Master 1:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: REDACTED
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 2049
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 606
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'dummy2'' on query. Default database: 'postfixadmin'. Query: 'DROP TABLE `dummy2` /* generated by server */'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 576
              Relay_Log_Space: 4195
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1051
               Last_SQL_Error: Error 'Unknown table 'dummy2'' on query. Default database: 'postfixadmin'. Query: 'DROP TABLE `dummy2` /* generated by server */'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

Master 2:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: REDACTED
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 379
               Relay_Log_File: mysqld-relay-bin.000008
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1396
                   Last_Error: Error 'Operation CREATE USER failed for 'addmail'@'10.0.0.254'' on query. Default database: 'postfixadmin'. Query: 'create user 'addmail'@'10.0.0.254' identified by 'Password''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 682
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1396
               Last_SQL_Error: Error 'Operation CREATE USER failed for 'addmail'@'10.0.0.254'' on query. Default database: 'postfixadmin'. Query: 'create user 'addmail'@'10.0.0.254' identified by 'Password''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

I need to know not only what I can do about this sort of situation, but how I can detect it automatically so that when it does happen (as frequently as this? I hope not!) so that I am at least made aware of the situation so I can fix it again.

  • Do you have any DB replicating on my.cnf?. If you should see there's not DBs replicating; Replicate_Do_DB: . – oNare Jun 30 '15 at 0:38
1

Slave_SQL_Running: No

The above indicates that although the replica is connected to the master it is no longer applying SQL statements to the dataset. This is due to the problems exposed in the 'Last_Error' field. You should resolve these issues either by aligning the data or skipping the transaction and syncing the data thereafter. You've likely arrived at this scenario because of data or database object inconsistencies between the nodes.

You should explore monitoring the replication health using any of the popular methods for example using Nagios and Percona's Monitoring Plugins for MySQL. There is a broad choice in this space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.