Data Integrity and Performance Considerations in MySQL Semisynchronous Replication

Article posted on : link to source

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

rpl_semi_sync_master_wait_for_slave_count:

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 …

Read More on Datafloq