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:
Get the GTID of the master by checking the variable
"gtid_binlog_position"
. Save this value.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.
Source the database into the slave.
Run
RESET SLAVE ALL
, and set the GTID position to the value taken in step one. This is done with the commandSET GLOBAL gtid_slave_pos = "<gtid from step 1>";
, and thenCHANGE 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?
CHANGE MASTER TO ...
. Do you setMASTER_USE_GTID
there? – elenst Dec 2 '16 at 23:07MASTER_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 '17 at 18:54