MySQL settings double 1 ensure database consistency

In MySQL, after a transaction commits, it is necessary to write undo, write Redo, write binlog, write data files, and so on. In this process, crash may occur at a certain step, which may result in inconsistent master-slave data. To avoid this situation, we need to adjust the master-slave configuration to ensure that the data loss of master-slave replication does not occur even if crash occurs.

1. Modify the configuration on master

Innodb_flush_log_at_trx_commit = 1sync_binlog = 1

The effect of the above two options is: to ensure that each transaction commits, can be flushed to disk in real-time, especially to ensure that each transaction corresponding to the Binlog can be flushed to disk in a timely manner , as long as there is binlog,innodb there is a way to do data recovery, Data loss due to master-slave replication.

2. Modify the configuration on the slave

master_info_repository =  "table" relay_log_info_repository =  "table" Relay_log_recovery  = 1 

The first two options above are: , and the latter option is

With the adjustment of the above several options, you can ensure that the master-slave replication data is not lost. However, this does not guarantee the absolute consistency of the master-slave data , because it is possible to set replication rules such as ignore\do\rewrite, or some of the SQL itself is uncertain, or to artificially modify the data on slave, Eventually leads to inconsistent master-slave data. In this case, the Pt-table-checksum and Pt-table-sync tools can be used to verify and repair the data.

MySQL settings double 1 ensure database consistency