Question
How to restore slave server in MySQL master/slave replication
- Posted on October 6, 2014
Asked by Jay Prakash
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.
This textbox defaults to using Markdown to format your 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.
Want to learn more? Join the DigitalOcean Community!
Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.
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:
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.