DigitalOcean Kubernetes: new control plane is faster and free, enable HA for 99.95% uptime SLA

Question

How to restore slave server in MySQL master/slave replication

  • Posted October 6, 2014

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.

Subscribe
Share
Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Andrew SB • 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.