Question
How to restore slave server in MySQL master/slave replication
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.
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.
×