Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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.

share|improve this question
up vote 2 down vote accepted

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'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.
share|improve this answer
Thanks, synchronous replication is what I should have been looking for, very comprehensive answer :). – Alex Mar 7 '12 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. – Alex Mar 7 '12 at 5:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.