Mirroring a MySQL database and web site

Melville

I have a mission-critical website and database that I would like to mirror in another location so that I have a backup in case the primary goes down.

Two questions:

1) What is the best way to mirror?
2) How do I get my clients to the mirrored site when the primary site goes down? How would they even know to go there, given that it takes about 48 hours for a DNS change to propogate?

Thanks,

Mel🙂

drew010

as far as the dns goes, just set the nameservers in the order that you want them.

for example.

ns1.mainsite.com
ns2.mainsite.com
ns1.backupsite.com
ns2.backupsite.com
ns1.thirdbackup.com
ns1.fourthbackup.com

if the dns fails on the first two, it will rely on the 3rd, and so on. as to mirror your site, you may manually need to zip up the specific folders and dump the database content and have it remotely downloaded by the other site and unzipped and then databases updated. you could accomplish this with a shell script or even a php script.

Melville

Thank you, Drew. You just made my life a lot easier!
Mel

piersk

If you have access to cron on *nix or task scheduler on windows, then you could try the following to mirror the mysql db:

use [man]mysqldump()[/man] to create a dump of the db

next, use the ftp functions to copy the sql file to the other mysql servers.

finally, run a script that will populate the databases with the file that you've just copied over

Obviously these scripts wouldn't be run too often because of the amount of bandwidth to keep copying the file over, but it could be run once a day to backup the db and also make sure all dbs are mirrored.

Take a look here for more details on this

marky2coats

Even better use the replication function of mysql. This way your sencondary database is constantly kept uptodate by the primary.

Mark