Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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

Your Answer

 
discard

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.