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!
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.
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and AI-native businesses
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.