3

I've created two MySQL 5.1 servers, replicating off each other (log-bin) in a low latency environment (different AZs within an EC2 region). All tables that are being replicated are using the InnoDB storage engine.

It appears to work as expected in a number of scenarios I have tested (not brimming with confidence, though), but I am worried that I cannot find any answers on whether this setup guarantees ACID for both nodes on transactions made to either node.

If not, are there steps I can take to meet this requirement?

I've seen various bits and pieces around the mysql.com site such as:

... For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1, sync_binlog=1 ...

but I find it rather worrying that I cannot find a definitive answer (or even guide) on the matter.

Improve this question

1 Answer 1

Reset to default
2

MySQL replication is not ACID compliant since it is asynchronous. There are no guarantees that queries executed on one server will be committed on the other. In fact, the master doesn't even know about the slave except that it's pulling binary logs. The binary logs themselves "might" be considered ACID compliant when sync_binlog=1 is on, but one server does not know if the other server has committed the change.

If you truly need something close to ACID compliance in your replication, what you want is synchronous replication, which is provided by products like Tungsten Replicator, Galera, and Percona Xtradb Cluster (which is Galera + Percona Server), and MySQL Cluster. I'm not sure if they fit a strict definition of ACID compliance, but they are certainly a lot closer. MySQL 5.5 has something called semi-synchronous replication, but don't be fooled...it's still asynchronous.

You can improve the reliability of MySQL replication by doing a few things:

  1. Use Row-based Replication (RBR) by setting binlog_format=ROW in my.cnf. This will replicate the entire row for every change made instead of replicating the SQL statement that made the change (as with statement-based replication, [SBR]). That minimizes the chances that a statement executes differently between servers if there are small discrepancies already.
  2. Run pt-table-checksum frequently to verify the consistency between master and slaves. If there are certain tables that are more important, run it on those tables more frequently.
  3. Set up monitoring that checks the output of pt-table-checksum and alerts you when there are discrepancies. You can use pmp-check-pt-table-checksum from the Percona MySQL Nagios Plugins if you use nagios. Otherwise, it's just a simple query to determine this information.
  4. If you have discrepancies, use pt-table-sync to resolve them. Read the documentation carefully, as you can shoot yourself in the foot relatively easily with this tool.
Improve this answer
2
  • Thanks, synchronous replication is what I should have been looking for, very comprehensive answer :). Commented Mar 7, 2012 at 3:09
  • I've made a small Galera cluster (sql+sql+arbitrator), and it's working simply great - will probably go with this option. Cheers. Commented Mar 7, 2012 at 5:57

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.