Results 1 to 10 of 10
  1. #1

    MySQL Synchronization

    Hello,

    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.

    Is there any idea for that?

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Location
    Austin, TX
    Posts
    934
    MySQL replication can do master-to-master. As long as your server-id and auto_increment variables are set correctly.
    SysAdmin.xyz
    Having severs with customer data on it without proper monitoring is like having one night stand without using protections - eventually, there will be an 'oh s**t!' moment.

  3. #3
    Thanks a lot for the idea I'm searching for it now.

  4. #4
    Join Date
    Mar 2011
    Posts
    41
    You could look into mysql cluster as well. People used to avoid it because older versions were all in memory storage. With the newer 7.1 version, you can do disk based storage.

    You could also look at an active/passive red hat cluster as well.

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

    ~Chris

  6. #6
    Join Date
    Mar 2011
    Posts
    41
    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.

  7. #7
    Thanks Nafirici and Aquatix, I never used MySQL clustering before.
    My DB for now about 700MB, the two servers in different networks, So I don't know if clustering will be good or not.

  8. #8
    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. :-)

    ~Chris

  9. #9
    Join Date
    Mar 2011
    Posts
    41
    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.

  10. #10
    Join Date
    Feb 2004
    Posts
    634
    zngr,

    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.

    http://lists.mysql.com/cluster/7887

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

Similar Threads

  1. Replies: 7
    Last Post: 02-15-2011, 12:12 AM
  2. DNS synchronization
    By its_joy in forum Hosting Security and Technology
    Replies: 4
    Last Post: 05-04-2008, 01:07 AM
  3. Replies: 13
    Last Post: 03-18-2006, 08:45 PM
  4. Directory Synchronization
    By prymorsk in forum Hosting Security and Technology
    Replies: 2
    Last Post: 10-14-2005, 12:19 AM
  5. Time synchronization
    By Mike the newbie in forum WHT Announcements, Feedback and Questions
    Replies: 9
    Last Post: 04-07-2002, 01:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •