Re: [tlug] wiki

["Keith Bawden" <keith@example.com>]

On 11/19/06, Walter Hansen <gandalf@example.com> wrote:
> I use this little script for mysql. Mysqldump takes a snapshot of
> current data at that moment and saves it as a very large series of SQL
> statemnts in a plain text file. The thing is if you backup the files of
> a database you may inadvertently catch them mid update. This can mean
> you're backing up corrupted data. Mysqldump works with Mysql so it
> doesn't have this problem. The statement before this repairs the
> database. Once this is done it's whisked off the computer by the user
> backup.
>
> #!/bin/sh

# SNIP

The way I do this is to run a master and slave MySQL server (you can
run them on the same machine) and to backup the slave.

I then have a script that does the following (and more :)

1. Checks replication status
2. Stops replication
3. Dumps DB to text file.
4. Re-enables replication
5. Checks if slave catches up to master.
6 Logs and reports backup status.

This way there are no issues with backing up a live server, or the
problems associated with locking tables or records during a backup.
Another bnus is that you can also take the slave completely offline
and do a backup of the binary tables and log files if need be.

Regards, Keith