Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts

    synchronise mysql on two servers

    My problem is simple to explain.
    If server one goes down instantly switch the site to server two.

    The DNS will change instantly so all requests will be directed to the new server. This used to work well in the past when all the MYSQL database had static content in them. When we e-Commerced our sites, the databases became dynamic. Orders are stored in the orders database. What i want to do is have the orders database running on both servers and somehow have any query that was run on that database on server one also be run on server two.

    I know i could literally run the queries on both servers by connecting to the other server. However if i constantly connecting between both servers for every little query the speed of the website will be compromised.

    I know i could use mysqldump and just dump the database and run the dump on the other server. However this isn't real time. What i don't want to happen is two orders be put through with the same order id.

    I either need server2 to know the next autoincrement value of the order id from server1 in realtime. So even if we lose access to the orders database at least any new orders will have a unique order id. Then still use mysqldump each night. Not ideal as we will lose any transactions between the last dump and the server going down.

    Or ideally every transaction that was run on server one be also run on server two as quickly as possibly with out compromising the speed of the site.

    What would you suggest?
    You can not say you know how to do something, until you can teach it to someone else.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Read The Fantastic Manual (a.k.a., RTFM):

    http://dev.mysql.com/doc/refman/5.6/en/replication.html

    No way you should consider trying to do something like this using your own coding. Let MySQL do it for you.

    And note, please, that the replication types described there are the *simple* types supported by MySQL. MySQL also supports clustered replication, but you wouldn't likely use that if you only have two servers.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    timgolding (09-19-2013)

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    *Hand up*
    I've *never* done this before, and don't know if Pedant has either, so I'm curious (and I'm also not well versed on it ).
    What of a clustered MySQL environment and using of a federated environment for the each of the servers assigned via ip? In the event that a machine is no longer reachable, shouldn't both machines if set up with federated tables simply be contacting the same mysql anyway (with the mysql clustered for some redundancy as well)?
    Would such a thing work (I've never done anything more than a quick peek at federation where I go "neat, but nothing I need it for atm"). Would this be a situation where federated would actually have some value?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Yes, indeed, FouLu, but at a cost. The simple replication (two servers, one master, one slave) is part of the free version of MySQL (or at least it used to be! haven't checked in years) and would work just fine for a simple situation such as he posits.

    I worked on a project where we were planning to move to clustered/federated (but then the company cut the funding for the project), but we started with the simple replication and it worked just fine for a medium-sized system.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Thats great thanks for stearing me in the right direction. I was dreaming up all sorts of fantastic ways of doing it. At least this way mysql can take control of it and from what i am reading will handle the fail over nicely. So i guess i got of reading to do about replication
    You can not say you know how to do something, until you can teach it to someone else.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Neat.
    I didn't realize that federated had a cost associated. Pity, the idea is pretty neat.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Assuming we are talking about the same thing:
    http://www.mysql.com/products/

    The Cluster Carrier Grade Edition starts at $10,000 per year.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Clustering wise, definitely.
    I'd probably look at using a single device with multiple connections and as much redundancy I could set up on the mysql server though. Put that on a nix machine and it'd hum.
    Federated wise, I'm not sure. They are a table type, but I'm not sure if they're available in standard version: http://dev.mysql.com/doc/refman/5.0/...ge-engine.html
    The idea is neat, but unless the remove (and even origin for that matter) servers have good connectivity, I can see issues with performance. Assuming no issues with performance (), the idea would be useful in having many offloaded webservers without needing replicated copies.

    Not that I have a problem with replication mind you.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    timgolding (09-20-2013)

  • #9
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    16.3.6. Switching Masters During Failover

    There is currently no official solution for providing failover between master and slaves in the event of a failure. With the currently available features, you would have to set up a master and a slave (or several slaves), and to write a script that monitors the master to check whether it is up. Then instruct your applications and the slaves to change master in case of failure.
    From what i am reading this solution is not a dead certainty that there won't be some lag thus conflicts in order ids. I wonder if the once failover has taken place and the slave becomes the new master i should auto increment the Order ID if i can to avoid conflicts.

    Promoting a slave to master isn't a process that can be reversed. When the failed master comes back up, it's no longer useful. It should be rebuilt as a new slave of the new master.
    I guess there will be some manual work involved then. If failover occurs i will have to promote the old master as a slave. Sometimes the DNS failover kicks in when it doesn't need to. DNS made easy monitors the primary server IP with PING. if it doesn't get a packet back failover kicks in. Sometimes this happens for a few seconds then failover switches the DNS back when the PING recieves packets from the primary IP address. I can configure DNS made easy to use the 'auto off' feature that prevents the DNS changing back to the primary IP address. But i am worried if i go ahead with this mysql replication i am constantly going to be promoting the old master to a new slaves. I guess i need to know how time consuming this process will be?
    You can not say you know how to do something, until you can teach it to someone else.


  •  

    Tags for this Thread

    Posting Permissions

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