Shulou(Shulou.com)06/01 Report--

In MySQL, after a transaction is committed, you need to write undo, write redo, write binlog, write data files, and so on. In this process, crash may occur at some step, which may lead to inconsistency between master and slave data. In order to avoid this situation, we need to adjust the master-slave option configuration above to ensure that even if crash occurs, the data loss of master-slave replication can not occur.

1. Modify the configuration on master innodb_flush_log_at_trx_commit = 1sync_binlog = 1

The function of the above two options is to ensure that after each transaction is committed, it can be refreshed to the disk in real time, especially to ensure that the binlog corresponding to each transaction can be refreshed to the disk in a timely manner. As long as there is binlog,InnoDB, there is a way to restore data, so as to avoid the loss of master-slave replicated data.

two。 Modify the configuration master_info_repository = "TABLE" relay_log_info_repository = "TABLE" relay_log_recovery = 1 on slave

The first two options above are used to ensure that the metadata tables related to replication on slave also use the InnoDB engine and are protected by InnoDB transactions, while the latter option enables the automatic repair mechanism of relay log. When crash occurs, it automatically determines which relay log needs to be crawled back from the master and applied again, so as to avoid the possibility of partial data loss.

By adjusting the above options, you can ensure that the master-slave replication data will not be lost. However, this does not guarantee the absolute consistency of master-slave data, because it is possible to set replication rules such as ignore\ do\ rewrite, or there are uncertainties in some SQL itself, or artificial modification of data on slave, resulting in inconsistency between master and slave data. In this case, pt-table-checksum and pt-table-sync tools can be used for data checksum repair.

Http://edu.51cto.com/center/course/lesson/index?id=184082

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.