What causes the MySQL error 1062 - duplicate entry when starting slave?

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?

What to try to fix your problem:

You should remove master.info on slave first and restart mysql
issue CHANGE MASTER TO MASTER_HOST='XX.XX.XX.XX', MASTER_USER='repl', MASTER_PASSWORD='slavepass';
do mysqldump with '--flush-logs' option on master
'mysql -u user -p < dump.sql' on slave
'show slave status\G' on slave to ensure that it is properly configured, MASTER_LOG_POS is 0
'start slave;' on slave.

What to check also:

Binlog format: MIXED
server_ids are different on master and slave

The problem is caused by setting up the master on a running production server BEFORE doing the dump (as far as I can tell). So, there are queries written in the master_log that have already been executed on the data residing on the slave. I never actually saw a solution on the mysql website or mailing list. So, I came up with the following solution that solved my problem.

on slave:

mysql> STOP SLAVE;
mysql> FLUSH PRIVILEGES;  # dump likly included users too

on master:

mysql> RESET MASTER;

on slave:

mysql> RESET SLAVE;
mysql> START SLAVE;

by the way, I ran my dump with the following on the slave:

mysqldump -uROOTUSER -pROOTPASSWORD -hMYSQLMASTER.EXAMPLE.COM --all-databases --delete-master-logs | mysql -uROOTUSER -pROOTPASSWORD

I hope this helps someone else.

http://dev.mysql.com/doc/refman/5.0/en/reset-master.html

http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html

What causes the MySQL error 1062 - duplicate entry when starting slave?

Related

Recent Posts