Mirroring Databases in MySQL

Question

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?

• Jocelyn: stackoverflow.com/questions/3402794/mysql-replication
• DeveloperInToronto: Setting up Replication in MySQL is quite easy. You can find a full guide here.
• spencer7593: 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.html

Answer 1

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

Answer 2

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.

Answer 3

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