"20180719" records one time mariadb master-slave replication due to tokudb a primary key 1062 error issue

Remember to build the MARIADB master-slave Replication environment:

• System: CentOS Release 6.3
• Kernel: 2.6.32-431.23.3.el6.centos.plus.x86_64
• Database version:
  • MASTER:10.1.16-MARIADB MariaDB Server
  • SLAVE:10.1.34-MARIADB MariaDB Server

Problem Description:

• Use the Mysqldump Backup tool to complete the master above, then the Binlog file and position based on the backup to build the master-slave environment, but after the start slave error, there are 1062 errors.

• Mysqldump backup command:

   mysqldump -S/var/lib/mysql/mysql.sock  -uroot -p --single-transaction --master-data=2 zst > 20180719_zst.sql

• At the beginning of the thought is a very small part of the data or because the slave above has a write cause 1062 primary key conflict error, and then directly on the slave based on the primary key of the error to delete the row, and then the start slave, but found that there are many such data, The direct query then causes this.

Guess:

• Because the data on the line is mixed with the InnoDB and TOKUDB engines, I suspect that the TOKUDB engine does not support snapshot backups, but mysqldump only the database engine that supports MVCC supports snapshot backups, and the TOKUDB engine supports MVCC and transactions. Finally I asked the others to get the TOKUDB engine does not support mysqldump consistent snapshot backups. So I'm going to experiment with it myself.

Experiment:

2. Lab Environment:

   • master:172.16.3.5
   • slave:172.16.3.7
   • sysbench_host:172.16.3.15

4. Install mariadb (both master and slave need to be installed)

   shell> wget https://downloads.mariadb.com/MariaDB/mariadb-10.1.34/yum/rhel/mariadb-10.1.34-rhel-6-x86_64-rpms.tarshell> tar -xf mariadb-10.1.34-rhel-6-x86_64-rpms.tarshell> cat sys/kernel/mm/transparent_hugepage/enabled ##需要关闭大页传输always madvise [never] ## 是never是正确的shell> echo never > /sys/kernel/mm/transparent_hugepage/enabledshell> echo never > /sys/kernel/mm/transparent_hugepage/defragshell> cd mariadb-10.1.34-rhel-6-x86_64-rpmsshell> ./setup_repositoryshell> yum install Mariadb-Server

6. See if TOKUDB is supported

   ......*************************** 5. row ***************************  Engine: TokuDB Support: DEFAULT Comment: Percona TokuDB Storage Engine with Fractal Tree(tm) TechnologyTransactions: YES      XA: YESSavepoints: YES*************************** 6. row ***************************......

8. Build Master-Slave

   mysql> change master to-> master_host=‘172.16.3.5‘,-> master_user=‘rpl‘,-> master_password=‘new_password‘,-> master_port=3306,-> master_log_file=‘mysql-bin.000010‘,-> master_log_pos=258482739;

10. Sysbench Test (run on sysbench_host)

    • Verify the idea: Because my purpose is to verify that TOKUDB does not support snapshot backups, so first I just need to keep writing data in the database, And make sure I have data written during the backup. So as far as possible the value of table size is larger, so that the time to write data is longer, when writing data in the master above the mysqldump consistent snapshot backup, the obtained data will be imported into slave, and according to the backup file Binlog File and position build master-Slave, if there is no 1062 primary key conflict when start slave, then the TOKUDB is to support consistent snapshot backup, if there is a 1062 error (not to say only one, but many, the specific validation can be set Sql_ Slave_skip_counter set an empty transaction, you can see that you need to skip a lot of empty transactions, or the appeal said to delete slave above the primary key conflict Row,start slave. The Gtid was not opened because it was for experimentation. )

      sysbench oltp_insert.lua--threads=10--report-interval=5--db-driver=mysql--mysql-host= 172.16.3.5--mysql-port=3306--mysql-user=root--mysql-password= ' new_password '--mysql-db=zst--mysql_storage_ Engine=tokudb--tables=10--table_size=2000000 Prepare  

12. Experimental results

    ............Last_SQL_Errno: 1062           Last_SQL_Error: Could notexecute Write_rows event on table zst.sbtest1; Duplicate entry ‘8‘ for key‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event‘smaster log mysql-bin.000005, end_log_pos 2678............

Summarize

• It is clear that the TOKUDB engine does not support transactional consistent snapshots.

• Backup Tools for Tokudb

  • Using the Percona version of Mysqldump, you can back up tokudb data and provide an additional parameter lock-for-backup to ensure data consistency. (Https://www.percona.com/doc/percona-server/LATEST/management/backup_locks.html#mysqldump)

  mysqldump has also been extended with a new option, lock-for-backup (disabled by default). When used together with the --single-transaction option, the option makes mysqldump issue LOCK TABLES FOR BACKUP before starting the dump operation to prevent unsafe statements that would normally result in an inconsistent backup.When used without the single-transaction option, lock-for-backup is automatically converted to lock-all-tables.Option lock-for-backup is mutually exclusive with lock-all-tables, i.e. specifying both on the command line will lead to an error.If the backup locks feature is not supported by the target server, but lock-for-backup is specified on the command line, mysqldump aborts with an error.

  • A physical backup tool, also a compiled xtrabackup

  https://github.com/XeLabs/tokudb-xtrabackup

  • It is recommended that you use a physical backup when using Tokudb backup, because TOKUDB is a compression engine that uses 33G on the physical disk of the InnoDB and Tokudb hybrid engines on the wire, but uses mysqldump to back up 88G of data. So one disk and one efficiency issue is not recommended to use logical backup TOKUDB, as much as possible with physical backups.

Problem solving

• Although it is verified that TOKUDB does not support mysqldump consistent snapshot backups, the actual problem is not resolved.
• Finally, set the value of Slave_skip_errors to 1062 and then restart Slave, after a period of time and so on (all the data of the primary key conflict is synchronized), turn off this parameter, and then restart the slave. Finally, the Percona toolset Pt-table-cheksum and Pt-table-sync are validated for synchronization.

"20180719" records one time mariadb master-slave replication due to tokudb a primary key 1062 error issue