MySQL semisynchronous replication provides improved data integrity because when a commit returns successfully, it’s known that the data exists in at least two places – the master and its slave. In this blog post, we review some of the MySQL configurations that influence the data integrity and performance aspects of semisynchronous replication. We’ll be using InnoDB storage engine and GTID-based replication in a 3-node replica set (master and 2 slaves), which will ensure there is redundancy in the slaves. This means that if there are issues with one slave, we can fall back on the other.
Read the original post: Data Integrity and Performance Considerations in MySQL Semisynchronous Replication
Configurations Applicable to Both Master and Slave Nodes
innodb_flust_log_at_trx_commit = 1
sync_binlog = 1
These configurations guarantee high durability and consistency settings for data. That is, each committed transaction is guaranteed to be present in binary logs and also the logs are flushed to the disk. Hence, in the case of a power failure or operating system crash, the data consistency of MySQL is always preserved.
Configurations on the Master Node
This option is used to configure the number of slaves that must send an acknowledgment before a semisynchronous master can commit the transaction. In the 3-node replica set, we recommend setting this to …