0

I want to solve a data replication/data sync problem between two MySQL server instances. Both of them have the same database structure (databases, tables, columns, everyting ...) but each of them has a different priority when it comes to storing data. One receives data from a website, which is the frontend in the solution, the other one gets its data from the backend. This is some sort of load balancing here, because the backend isnt well designed, and its connection dies if it cant write its data within some seconds.

To make it more precise, I have to mention, that backend and frontend does write not into the same tables. Currently the synchronisation between these databases is managed by triggers and stored procedures, and this should be changed into a more stable solution.
I found an article on the web ( http://www.howtoforge.com/mysql_database_replication ) that looks promising, but it threw up a question: Is it possible to make both servers Master and Slave at the same time? It would be perfect if yes. Btw: if yes, i might ask how its done (the settings for master and slave on each servers config?).

Id like to ask if there are other solutions for that kind of replication too.

Thanks

2 Answers 2

1

Percona XtraDB Cluster is designed for exactly this use case.

  • Write-to-any,Read-from-any operation.
  • Synchronous multi-master replication.
  • Based on the Galera project.
  • Complete, up-to-date, high-performance Mysql Fork.
  • Minimal changes to your app's database code (unlike Mysql Cluster).
  • Fully API compatible with your distribution's (recent)Mysql. Just uninstall your existing Mysql and install XDC; Single-node operation is the same as Vanilla Mysql.
  • Free

Be sure to check out the Limitations too. You need a minimum of 3 nodes for a High-availability cluster configuration.

Here are examples for using AWS EC2 and handling lock edge-cases .

0

You could try with master-master replication.

There is also the option to limit replication at table level, but I think for your case this is optional.

Master-Master replication (also knowns as Circular replication) works also if both server modify the same tables.

1
  • The article you're referring to is seriously outdated (look at its date). Not that it is incorrect, its just that considerable progress hes been done in the field since then. Heed vimalg2's advice. If still not convinced, check the note on this site: mysql-mmm.org
    – ayurchen
    Jan 27, 2013 at 14:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .