Migrating server, don't wanna lose MySQL data. Is Master-Master setup viable?

Question

I am moving to a new server and thinking about how to keep my 2 MySQL server data consistent is causing me to lose both sleep and hair. I was thinking about using a Master-Master setup to ensure that I lose nothing in the process. How viable is that. Any potential gotchas?

Answer 1

Why does the old server ever need to be aware of data written to the new server? For this reason, make it a master-slave setup.

You do have to deal with the same type of configuration, for instance.. make sure the old server only uses odd id's, and the new server only uses even id's.

As soon as you shut down the old server (master), make sure nobody can write there anymore.

I'm assuming your entire website uses 1 server for both the DB and the webhosting. If this is the case, I want to add the following:

Don't rely on DNS to migrate your site, as this can take a very long time for certain users.

Consider the following:

• old.example.org is the site on the old machine
• new.example.org is the site on the new machine.
• www.example.org is a CNAME to old.example.org.

When you do the cutover, you will perform the following steps:

• The old DB server is shut down, or set to read-only.
• www.example.org becomes a CNAME to new.example.org
• old.example.org should now host a website that automatically redirects people to new.example.org.

This means that your users might for a while browse the url new.example.org directly. When the DNS is fully propagated your users will no longer be redirected, and automatically hit the new server when using www.example.org.

If you have a low-traffic site.. this can be much easier.. Simply point your old application to use the new MySQL database. Sure, it might seem a bit crazy to connect to a mysql server over the net; but if you're not dealing with too much data this is so much easier than any other solution..