Getting duplicate record errors like “Duplicate Entry for key 'PRIMARY', error code 1062” when importing a full backup into slave

Question

We're restoring our slave with a full backup from our master, and getting a bunch of duplicate key errors. Once the slave gets caught up, it does not throw any errors (only when it is behind the master). We replicate all of the database tables. We use a MariaDB Galera cluster as the master, and just a single MariaDB instance as the slave.

These are the steps that are done:

1. Get the GTID of the master by checking the variable "gtid_binlog_position". Save this value.

2. Take backup of master with the command:

   mysql -u -p --routines --triggers --single-transaction --gtid --master-data --dump-slave --add-drop-database <dbnames> | gzip > /tmp/backup.sql.gz.

   This backup takes around 20 minutes to complete.

3. Source the database into the slave.

4. Run RESET SLAVE ALL, and set the GTID position to the value taken in step one. This is done with the command SET GLOBAL gtid_slave_pos = "<gtid from step 1>";, and then CHANGE MASTER TO ...

After this, I receive the "Duplicate Entry for key 'PRIMARY', error code 1062" errors.

These are the settings that are used:

 [mysqld]     
 innodb_buffer_pool_size = 6G
 gtid_domain_id=100
 log-slave-updates=true
 open_files_limit=1000000
 innodb_large_prefix = on
 innodb_file_format = barracuda
 innodb_file_per_table = on


 [galera]
 binlog_format=row
 default_storage_engine=InnoDB
 innodb_autoinc_lock_mode=2
 log_slave_updates=1
 ## Below was added
 sync_binlog=1
 innodb_doublewrite=1
 query_cache_size=0'
 wsrep_provider_options="gcache.size = 5G"
 wsrep_retry_autocommit=4

I read through the post MySQL replication: 'Duplicated entry for PRIMARY key, but it doesn't seem like the solution applies in this case since I don't have those settings in question.

What would cause these duplicate key errors to appear?

Answer 1

From your steps, you are most likely backing up and starting replication from a different position:

Get the GTID of the master by checking the variable "gtid_binlog_position". Save this value.

Do not use that value, between that time and the time the backup was taken (even if seconds), more rows could have been changed, which means you will not get an accurate representation of the start position- being the most probable cause of the duplicate key errors. You are taking the backup with:

--single-transaction --gtid --master-data

You must be using InnoDB for single transaction to work properly, otherwise, your other tables will not be consistent. Then, use the binary log coordinates or the gtid position written near the start of the dump .sql file to kickstart your slave.

The mistake may be because on the official guide it mentions the SELECT of the variable, but that would only work on a running replication if the master is not being written or all the tables are locked. The same guide mentions that using the feature integrated into mysqldump or xtrabackup is a better option.

Here it is a full guide for GTIDs on MySQL (some options and features change, but it may be a better overview for the overal procedure): https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/