Idiotically I rebooted a machine that is used as a MySQL slave without running STOP SLAVE
, FLUSH TABLES
first.
I had thought MySQL automatically handles all of this during a machine reboot, but apparently it doesn't, at least not in the configuration I was using, because the SLAVE doesn't start back up.
mysqld
does start but there is an error message in the logs indicating the slave portion stops due to duplicate primary key issues. Meaning it is trying to insert data that has already been added.
Here are the errors generated in the mysql log:
120104 11:07:54 [Warning] Slave: Duplicate entry '94459' for key 'PRIMARY' Error_code: 1062
120104 11:07:54 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START".
We stopped at log 'mysql1-bin.000362' position 3384732
How do I determine where in the master binary log the process needs to start from to run a CHANGE MASTER
statement? I know I could potentially, skip log entries using the sql_slave_skip_counter
but without knowing how many to skip I would need to go one by one, and that could take all day.
Best Answer
The Primary thing you need to do was left in a comment:
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
Please take note of
Relay_Master_Log_File
andExec_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: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
Step 02) Login to mysql and show the slave status:
Step 03) Get
Relay_Master_Log_File
andExec_Master_Log_Pos
fromSHOW SLAVE STATUS\G
Step 04) Run
CHANGE MASTER TO
command usingRelay_Master_Log_File
andExec_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 watchSeconds_Behind_Master
go to 0Step 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.That's what you use:
If you ran
RESET SLAVE
on the Slave, do not despair. Just run the full syntax version of theCHANGE MASTER TO
command: