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:

 innodb_buffer_pool_size = 6G
 innodb_large_prefix = on
 innodb_file_format = barracuda
 innodb_file_per_table = on

 ## Below was added
 wsrep_provider_options="gcache.size = 5G"

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?

  • The most important part is under ... in CHANGE MASTER TO .... Do you set MASTER_USE_GTID there?
    – elenst
    Dec 2, 2016 at 23:07
  • Try MASTER_AUTO_POSITION = 1; instead of specifying a particular gtid to start replication from. The slave should pick up from the last GTID. It appears you are specifying an old GTID that has already been executed by slave, hence duplicate rows.
    – Allen King
    Jul 16, 2017 at 18:54

1 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/

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.