- MySQL Master version: 5.5.16-1
- MySQL Slave version: 5.5.18-1
The master's snapshot is created by:
mysql> FLUSH TABLES WITH READ LOCK;
shell> mysqldump --all-databases --master-data > dbname_`date +%F`.sql
This dump file is imported on the slave (which is started with --skip-slave-start option) without error:
shell> pv dbname_`date +%F`.sql | mysql -u root -p
But I got the following error when executing the mysql> start slave;:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '115846' for key
'PRIMARY'' on query. Default database: 'db'. Query: 'INSERT INTO
request_posted (id, user_id, channel, message, link, picture, name, ...
There is only one record with ID 115846 on the master:
mysql> select count(*) from request_posted where id=115846;
Current database: db
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)
Try to skip some queries with:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> START SLAVE;
didn't help. I don't want to skip those errors by adding:
slave-skip-errors = 1062
to my.cnf file because it may bring slave inconsistent.
What may be the reason for this error?
UPDATE
This is not how I usually setup mySQL replication
Which steps that you think I don't follow the document?
I wonder if you will encounter the same problem if you were to setup the entire configuration rather that passing the mysqldump command.
No, it works as normally if I also change the master to corresponding coordinates.
I would try dropping the database on the slave, make sure the binlogs are clear, and start again. Also check the table in question on the master to assure the indexes do not have errors.
Is delete (move) all the datadir enough? I did that and get the same result.
Reply to @Dmytro Leonenko
'show slave status\G' on slave to ensure that it is properly configured, MASTER_LOG_POS is 0
Only 'show slave statug\G' after import but before 'start slave;' can give us the answer
I backed up the datadir, delete all and run mysql_install_db, import the dump file, execute change master to and here's the results:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: x.x.x.x
Master_User: xx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 106
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
I'm wondering why Master_Log_Pos is 4?
