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?