The Primary thing you need to do was left in a comment:
Follow the suggestion in Duplicate entry in MySQL slave:
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;
However, here is how you can start off at the better position with the CHANGE MASTER TO
command.
Let's take a look at a sample SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.16.245
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001527
Read_Master_Log_Pos: 554619670
Relay_Log_File: relay-bin.004561
Relay_Log_Pos: 554619815
Relay_Master_Log_File: mysql-bin.001527
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: phpmyadmin
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 554619670
Relay_Log_Space: 554620007
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Please take note of Relay_Master_Log_File
and Exec_Master_Log_Pos
. These represent the log file and the position of the last SQL statement that was completed on the Master that is next in line to be executed on the Slave.
So, the CHANGE MASTER TO
in this case would be:
CHANGE MASTER TO master_log_file='mysql-bin.001527',master_log_pos=554619670;
For your particular case, here is what you need to do on the Slave Server:
Step 01) Start mysql up with replication disabled at startup
$ service mysql restart --skip-slave-start
Step 02) Login to mysql and show the slave status:
mysql> SHOW SLAVE STATUS\G
Step 03) Get Relay_Master_Log_File
and Exec_Master_Log_Pos
from SHOW SLAVE STATUS\G
Step 04) Run CHANGE MASTER TO
command using Relay_Master_Log_File
and Exec_Master_Log_Pos
This will erase any relay logs collected and start collecting with a fresh, empty relay log.
Step 05) mysql> START SLAVE;
Step 06) mysql> SHOW SLAVE STATUS\G
repeatedly to watch Seconds_Behind_Master
go to 0
Step 07) If replication breaks due to Error 1062 (Duplicate Key), now you can implement @DTest's suggestion. Then, goto Step 06. Repeat this until Seconds_Behind_Master
go to 0.
We stopped at log 'mysql1-bin.000362' position 3384732.
That's what you use:
CHANGE MASTER TO master_log_file='mysql1-bin.000362'. master_log_pos=3384732;
If you ran RESET SLAVE
on the Slave, do not despair. Just run the full syntax version of the CHANGE MASTER TO
command:
CHANGE MASTER TO
MASTER_HOST='IP Address of Master',
MASTER_PORT=3306,
MASTER_USER='Replication Username',
MASTER_PASSWORD='Replication Password',
MASTER_LOG_FILE='mysql1-bin.000362',
MASTER_LOG_POS=3384732;
Best Answer
There is no mechanism in MySQL for a master to recapture "lost" (missing, corrupt) data from a slave. Unless replication is circular (or master/master, where each machine is both master of, and slave to, the other... don't try this at home) then the interaction between master and slave is strictly one-way. Even in a circular configuration, there's no "recovery" or resynchronization mechanism.
There is also no mechanism for data to be hidden away and reappear. The comment referencing InnoDB recovering data from log files seems to overlook the fact that this sort of recovery occurs at start-up, and until it is complete, the server doesn't start accepting connections.
The answer should be discoverable in the master's binary logs (you archive those, right?) and the daily backups from master and slave (you archive those, too, right?).
Absent frequent periodic backups and historical binlogs, it seems difficult to speculate further.