Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Tweeted twitter.com/StackDBAs/status/1005950852459302912
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
3 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

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

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?

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?

    Bumped by Community user
2 Improve formatting
source | link

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

  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 - httpMySQL replication://dba.stackexchange.com/questions/45518/mysql-replication-duplicated-entry-for-primary-key 'Duplicated entry for PRIMARY key, but it doesntdoesn't seem like htethe solution applies in this case since I don't have those settings in question.

What would cause these duplicate key errors to appear?

Thanks.

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 - http://dba.stackexchange.com/questions/45518/mysql-replication-duplicated-entry-for-primary-key, but it doesnt seem like hte solution applies in this case since I don't have those settings in question.

What would cause these duplicate key errors to appear?

Thanks.

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?

1
source | link

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

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 - http://dba.stackexchange.com/questions/45518/mysql-replication-duplicated-entry-for-primary-key, but it doesnt seem like hte solution applies in this case since I don't have those settings in question.

What would cause these duplicate key errors to appear?

Thanks.