UnixServerAdmin

Server Administration & Management

How to reconfigure MySQL Slave server after Break

mysql_slave

When you get the SQL Running “NO” error, do not use slave skip counter as shown below.

mysql> stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;  

The above statement will skip one sql statement and execute the next statement found in the binary log file. When you get the following “Could not parse relay log event entry” error, it means that Relay log file is corrupt. The IO thread is running and SQL thread is stopped. SQL thread may be broken due to 2 reasons.

1) SQL statement can not be executed.
2) OR slave is stopped due to Relay log event entry.

1.1) In case of first case use skip counter statement.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

1.2) In case of second case when SQL relay log is corrupt you need to restart the slave by using “change master to” statement as shown below.

mysql> stop slave;
mysql> reset slave;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.X.X’, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_user’, MASTER_LOG_FILE=’mysql-bin.000xxx’, MASTER_LOG_POS=503102260;
mysql> start slave;

Master log file and Master log position can be found in the show slave status output.
Relay_Master_Log_File: mysql-bin.000xxx
Exec_Master_Log_Pos: 503102260

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.x.x
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000300
        Read_Master_Log_Pos: 328331704
             Relay_Log_File: mysqld-relay-bin.000024
              Relay_Log_Pos: 108519259
      Relay_Master_Log_File: mysql-bin.000xxx
           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: 0
                 Last_Error: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.
               Skip_Counter: 1
        Exec_Master_Log_Pos: 503102260
            Relay_Log_Space: 872754335
            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)

The master IP address along with username, password can be found in master.info file. Relay log info file has the binary log file name and position. These 2 files are found in MySQL data directory. In this case these files can be found in /var/lib/mysql/ folder.

[root@localhost]# cat /var/lib/mysql/master.info
14
mysql-bin.000300
327270768
192.168.x.x
slave_user
slave_user
3306
60
0

In the following case skip slave counter command was used to skip the SQL error and go to the next command from binary.

mysql> show slave status\G  
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.X.X
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000300
        Read_Master_Log_Pos: 397589316
             Relay_Log_File: mysqld-relay-bin.000004
              Relay_Log_Pos: 254511006
      Relay_Master_Log_File: mysql-bin.000300
           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: 1064
                 Last_Error: Error ‘You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1’ on query. Default database: ‘freshnew1’. Query: ‘insert into audit_trail(PROCESS, PAGE_NAME, TABLE_NAME, TA��’
               Skip_Counter: 0
        Exec_Master_Log_Pos: 462435
            Relay_Log_Space: 397590023
            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> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; start slave;  

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

June 21, 2013 Posted by | MySQL | , | Leave a comment