I'm trying to build a replica from an active database with no downtime for replication purposes.

I run the following statement to get a backup, avoid locking the tables, and flush the logs so that the replica can pick up replication from when the dump started:

mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --master-data=2 -A > dump.sql

I then transfer this file over to my fresh replica with no database tables, and run mysql -uroot -p < dump.sql, yet I receive a duplicate key error somehow about halfway through the import:

Duplicate entry '<misc text here>' for key 'floats':36

I'm struggling to figure out why this is happening. Does this indicate duplicate keys in my primary active database? In this case, wouldn't the database be broken and spitting out errors?

  • So, when you query the source database for '<misc text here>' in that column, how many rows do you get? It would also help to see the definitions of table and index(es) in question.
    – mustaccio
    Commented May 25, 2022 at 23:16
  • 1
    Check character sets and collations. Pleasse provide SHOW CREATE TABLE. Can you find "'floats':36" in the dump? Twice? What datatypes are the column(s) involved?
    – Rick James
    Commented May 26, 2022 at 1:59
  • Are you replicating from the right spot, master-data=2 is a commented position.
    – danblack
    Commented May 26, 2022 at 3:54
  • While comparing MySQL server removes trailing spaces on 5.x and takes them into account on 8+. dbfiddle.uk/…
    – Akina
    Commented May 26, 2022 at 7:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.