If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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.

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.

Users who have thanked Old Pedant for this post:

*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 ;)

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.

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.

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 ;)

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.