Right way to mirror a PHP/MySQL setup

Question

I just created my very first PHP/MySQL site and was looking into creating a mirror of the db for backup. This is of course, in case some hacker manages to go snooping and wrecks the main db. Is there a right or wrong way of doing this?

Edit: Yes, I have a hosting plan in some other host that I'd like the mirror to be placed in.

Answer 1

There are mainly two ways for backing up your mysql databases: cold(static/offline) backup & hot(dynamic/online) backup.

1 Using a crontab script backup your db every day/week/month, etc. We call it cold backup. The script maybe like this:

!#/bin/sh
mysqldump -usample_name -S/tmp/sample.sock --databases db1 db2 > db_backup_time.sql

you can find manual of mysqldump here. Actually, the whole chp.6 is talking about database backup and recovery. After this, you'll get the mirror of your data(eg. 6am.) every day.

2 Using mysql replication solutions(master-slave structure) for online backup. All the queries on master leading to data modifications will also performed on slave.

comparation:

Basicly, cold backup is easier. But when bad things happen, first method can only recover the data to the time you dumping the mirror. With hot backup and mysqldump tool, you can recover the data to anytime.

In my experience, we always compose these two methods together:

1. write mirror every morning, and,
2. build a slave of master db on another physical machine.

that would be safe.

Answer 2

As far as backing up the database in the case of a hacker, It seems that mysqldump is the way to go; you can read up on it here. You could then ftp that mysql dump over to another server. Personally, I have a chron doing this backup every day and then I copy the backup onto another server with a ftp synchronize, but there are other ways of automating it too. If you are on a linux server, using webmin may make the process easier, it has tools for backing up mysql databases. Hope this helps.

Answer 3

mysqldump is a handy utility that lets you backup your database as a text file.

To backup your database:

mysqldump -u username -p database_name > backup.sql

Now to restore from a backup:

mysql -u username -p database_name < backup.sql

It's best to create a script that does this periodically so that you always have a fresh restoring point.

You can learn more about the syntax for mysqldump here

Answer 4

If you back up your database by mirroring it to another host, say once a day, your data will still be lost if it gets corrupted/trashed and you don't catch it before it gets mirrored. The correct way is to keep dumps periodically. I make a dump of my database every day, and keep daily backups for 4 weeks, then weekly backups for 6 months.

This works best if your database and backup servers are next to each other (in network distance). But if your database is hosted elsewhere and isn't too large, it should still work fine (many hosts will dump your database daily, but you're on your own to fetch it).

Answer 5

You can setup a mysql Master/Slave replication:

First create a user that you are going to use for replicating the databases: replUser

On the master server you want the following settings:

[mysqld]
log-bin=mysql-bin
server-id=1

On the slave server you want the following settings in your my.cnf

## Replication
server-id                       = 2
relay-log-space-limit           = 16G
expire-logs-days                = 7
master-host = 10.10.10.10
master-user = replUser
master-password = yourpassword
master-connect-retry=60
replicate-do-db = database_name

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