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.
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?