I've a website running on two servers, I wan to synchronize MySQL on both servers if any change happened to one of them it should be applied to the other one. I know MySQL Replication but it uses one server as a master and the other one as a slave, if the master is down for any reason my website wan't be abel to access the DB, so I'm looking for a way to synchronize the two servers.
Depending on how large your database is, I would recommend MySQL clustering - I have it running here for some projects and it works like a charm. Just google for 'mysql ndb cluster', there's some excellent documentation out there.
I should also mention that if you do look into mysql clustering, you need to consider how your application is written and how far out you cluster. For example, lets say you set noOfReplicas = 2 and you have 4 data nodes, this will give you 2 node groups and mysql cluster will automatically partition your data. You'll have 2 data nodes in node group 1 and 2 data nodes in node group 2. If your application does a lot of big joins, than there is a good chance that it'll need to pull data from both node groups. This can slow down your queries if you have not properly networked between the node groups and api node(s). I'm not trying to scare you away from mysql cluster, I just want to make sure you're aware of it.
700 MB should be just fine for an NDB cluster. We had a bit of trouble here getting an NDB cluster running for a customer where the database had a total size of like 400 gigs - needed servers with loads of RAM to accomodate. :-)
You'll also need to have at least 3 total servers to accomplish HA/Scalability. Also, for the data nodes, you'll want them to be the same set up, same RAM and CPU for sure because cluster will only use as much as the smallest data node has.
A couple things to keep in mind regarding MySQL Cluster:
- it runs much, much better on a gigabit private network than 100Base
- I would not run a cluster over the WAN, unless you're doing cluster-to-cluster geographic replication, which is two separate cluster installations replicating to each other asynchronously. I've run it over the WAN (using an encrypted VPN tunnel on a gigabit uplink at both locations) for a short period during a datacenter relocation, but the datacenters were pretty close to each other in proximity (<10ms away). Performance was definitely worse during this time, but it worked.
- As nafirici mentioned, you'll need a minimum of 3 servers, though one is only used as the arbitrator node (in case of a split brain situation where the two data nodes can't talk to each other but can talk to the arbitrator node, the arbitrator will elect one node as the primary) and to fetch the initial configuration at node startup, and therefore can be a very minimal configuration (a 512MB or 1GB single proc box is fine).
- Regarding join performance, this has traditionally been a weak spot of MySQL Cluster in comparison to the other MySQL storage engines, but there is a lot of optimization work being done in that area (Push Down Joins, or SPJ). Right now the code for that is in beta, but some reports I've read have shown 50x performance increase in certain types of joins.
- certain schema changes are not "online", meaning that the whole table has to be locked, copied to another temp table, the schema change made, copied back as the original table, after which the table lock is released. In situations such as this, you'll need 2x the memory of the table being copied, 1x for the current table and 1x for the copy. This used to be a major limitation of older versions, where even something as simple as adding a column was an offline operation. With current versions, online schema changes are possible for many typical situations.
These caveats aside, MySQL Cluster is very reliable and performant assuming it fits your workload (the target has been telco level reliability, with is generally considered 5 nines - 99.999% uptime). I've been working off and on with an e-commerce project that has been using MySQL Cluster for about 6 years since version 4.1 (which had a lot of limitations in comparison to the current versions), and--knock on wood--they've had no database outages during that time. I've also familiar with several other installations that have had excellent uptime over a couple year period. Aside from HA, one nice benefit to using MySQL Cluster is that it natively supports online backups (which is not the case with other MySQL storage engines), meaning that you don't have to lock tables in order to get a consistent backup.