How to restore slave server in MySQL master/slave replication

October 6, 2014 14.8k views
jaymailbox2012
By:
jaymailbox2012

I had MySQL master/slave replication working properly but suddenly it stopped syncing and then accidentally someone changed MASTER_LOG_POSS and MASTER_LOG_FILE so I got this when i do this in slave

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.138.220
                  Master_User: slaveRoot
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000862
          Read_Master_Log_Pos: 68619802
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000862
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              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: 
                 Skip_Counter: 1
          Exec_Master_Log_Pos: 68619802
              Relay_Log_Space: 1206
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000862' at 68619802, the last event read from '/var/log/mysql/mysql-bin.000862' at 4, the last byte read from '/var/log/mysql/mysql-bin.000862' at 4.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

So i checked master log and updated latest log file in slave and it started syncing but after one day new log_file generated in master so slave started giving same error.

Can anyone tell me proper fix for it without downtime.

Log In to Comment
1 Answer
asb MOD October 6, 2014

It sounds like you are restoring correctly if it started syncing again. Normally all you need to do is tell the slave about the new binlog file and position using something like:

CHANGE MASTER TO MASTER_LOG_FILE='binlog-name', MASTER_LOG_POS=4;

I think it's more important to figure out why it's happening in the first place. Usually this is because the master has crashed and restarted. Have you reviewed the log files on the master? One common reason for MySql to crash is that it has ran out of memory. Beside the MySql logs, I'd also check the syslog for mentions of "OOM" or "Out of Memory" If you haven't done so, try adding a swap file to give yourself a little breathing room on the memory front.

How To Add Swap on Ubuntu 14.04
by Justin Ellingwood
Swap space can be used as an "overflow" area for your system when you run out of RAM. The operating system can store data that would normally be kept in RAM on the hard drive in a specially formatted file. In this guide, we'll demonstrate how to create and use one of these files in Ubuntu 14.04.
Reply
  • jaymailbox2012 October 7, 2014

    I did same when i saw first fatal error 1236 . But it came again as soon as master created new binlog file on server.

  • jaymailbox2012 October 7, 2014

    In master 

    mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000870
        Position: 114247240
    Binlog_Do_DB: tm_production
Binlog_Ignore_DB: 
1 row in set (0.00 sec)
Have another answer? Share your knowledge.