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.

Is there a way to mirror databases in MySQL? In SQL Server, you have the ability to link 2 databases in a master to master relationship. One DB will accept transactions and the transactions will be committed on both DBs at the same time. Is there a a similar concept in MySQL?

share|improve this question
    
    
Setting up Replication in MySQL is quite easy. You can find a full guide here. –  DeveloperInToronto Aug 23 '12 at 20:32
    
MySQL has a "Multi-Master" replication feature in it's Cluster NDB engine. http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-multi-master.h‌​tml –  spencer7593 Aug 23 '12 at 22:35
add comment

migrated from stackoverflow.com Aug 25 '12 at 18:00

This question came from our site for professional and enthusiast programmers.

3 Answers

its called MySQL Replication:

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default - slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

share|improve this answer
add comment
up vote 1 down vote accepted

I think the other answer missed the point of the question; which is that in SQL Server mirroring, a transaction that commits to both servers A and B are committed at the same time (in atomicity). If the transaction fails on one server, it fails on both. It doesn't seem like there is a similar feature on MySQL

share|improve this answer
add comment

If you want to setup a DB Cluster using MySQL that commits DB Changes on a Master and a Slave at the same time, your best setup would have to involve PXC (Percona XtraDB Cluster).

PXC uses the WriteSet Replication package from Galera (made by CoderShip)

It works on the premise that a transaction committed on a Master is then committed on the Slave. If the Galera code detects that the Slave could not commit and rolls back, then the Master rolls back. This process repeated if there are more than two DB Servers using PXC.

Upon installation, Percona Server has Galera integrated. Don't worry about interoperability. Percona's server binaries are a complete drop-in replacement for MySQL's server binaries.

Read documentation because there are some limitations.Such limitations include

  • All Data should be InnoDB
  • No Replication of MyISAM data
  • Slowest Performing Node slows down Cluster

IMHO you can reasonably live with those limitations.

If you are looking for simple mirroring, PXC would be just fine because all DB writes would go to just one server and synchronous replication would take place at COMMIT time.

I have evaluated this product and it seems very sound so far...

share|improve this answer
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.