Best way to back up mysql database

[Paul Schmehl]

--On September 30, 2008 6:18:35 PM -0400 John Almberg 
<jalmberg at identry.com> wrote:

> First, I wanted to say how great this list is. I'm a newbie FreeBSD
> admin and, besides the Handbook and "Absolute FreeBSD" (which never
> seems to leave my desk), this list is the best resource I have.
>
> I just had a huge scare today... One of the websites on my server uses a
> large Mysql database. Somehow, one of the tables got corrupted today.
>
> I have been blithely backing up mysql with a simple cron script that ran
> mysqldump every night. Simple, reliable, and I've never needed it.
>
> Today, when I realized the database was corrupted, I scrambled for my
> backup, and realized that if I hadn't caught the problem today, tomorrow
> my backup would have been overwritten, and I would have been... well,
> not a happy camper.
>
> Again, I have run into a problem which is stupidly obvious to
> experienced admins, I'm sure. I want to slap myself, but don't have
> time. I'll do that after I have a better backup system in place.
>
> I am just about to dive into Google in search of a solution, but thought
> I would fire off a quick request, in case there is an obvious solution
> that everyone uses. If there is, a name or URL will do. I'll figure out
> the rest.
>
> Any hints much appreciated. Not going home until this is fixed...

Found this on the mysql documentation site:

#!/bin/sh
date=`date -I`
mysqldump --opt --all-databases | bzip2 -c
> /var/backup/databasebackup-$date.sql.bz2

The date must be something from linux, but you can do it like this in FSBD:

#!/bin/sh
date=`date "+%Y-%m-%d.%H:%M:%S"`
mysqldump --opt --all-databases | bzip2 -c
> /var/backup/databasebackup-$date.sql.bz2

Using this makes every dump uniquely named, even if you run several a day, 
so you would need to edit newsyslog.conf to rotate the dumps after a 
number of dumps that you choose so you don't keep writing dumps until the 
hard drive is full.

Paul Schmehl, If it isn't already
obvious, my opinions are my own
and not those of my employer.
******************************************
WARNING: Check the headers before replying