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;
Using replicate-wild-do-table
=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:
The replicate-*
options are restrictive by their presence. The *-do-*
options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-*
configuration variables means "replicate everything."
In the simplest case, when there are no --replicate-*
options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html
That, I think, is the point you are needing. It holds true for all MySQL 5.x.
Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ...
, and then START SLAVE
, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.
If you started out with identical data sets, everything should behave exactly as you would expect it to.
What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."
When you use the --replicate-*
options, the document cited above also offers this tip:
it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options
You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.
Best Answer
slave-skip-errors
can hide even serious errors; don't do it.Turn on the "general log" briefly. This should capture a little more info about where the duplicate
DELETE
is coming from. One of two things will happen:DELETEs
in the general log, too. This will point at the application for issuing the delete twice.Do you have the binlog for replication? If so, turn on the general log on a Slave to see if the duplicate stuff in the binlog really represents two
DELETEs
, not just some kind of stuttering inmysqlbinlog
.In any case file a bug report at bugs.mysql.com; the different paths give you different ways to complain.