How I Backup MySQL

I’m documenting this mostly for my benefit, but I figured it may be of use to others. This is how I backup my mysql servers.

1. Create a backup directory. I use /backup
2. Verify mysqldump exists under /usr/bin
3. Verify user account with rights in MYSQL to the database you want to backup. In this example I will backup a data base called bluedb.  For this demo I’ll use the username “Joe” and the password “Shmoe”
4. Create a script directory under /backup
5. Create the backup script. Here’s mine (largely written by my friend Jason):


#:

BACKUP_DIR=/backup
DUMP=/usr/bin/mysqldump
DATE=`date +%Y%m%d`

# DATABASE INFO
DB=bluedb

# First we will backup the structure of the database
${DUMP} --user=Joe --password=Shmoe --no-data ${DB} > ${BACKUP_DIR}/${DATE}_${DB}_backup_structure.sql

# Now we will backup the database itself
${DUMP} --user=Joe --password=Shmoe --add-drop-table ${DB} > ${BACKUP_DIR}/${DATE}_${DB}_backup.sql

# Now we will remove files that are older than 3 days
find ${BACKUP_DIR} -type f -mtime +3 -exec rm {} \;


*Note: There is some serious line wrapping going on above.


6. Name the script something like mysqlbackup.sh and drop it in /backup/script
7. Change rights on the mysqlbackup.sh so it has rights to execute (chmod 755 mysqlbackup.sh)
8. Now you can test it by running:  /backup/script/mysqlbackup.sh
9. Next I generally edit my cron jobs (crontab –e) and add the line   0 0 * * * /backup/script/mysqlbackup.sh

That’s it . The script dumps both the data and the structure of the database and it keeps 3 days worth of backups.  From here my main backup script for the box picks these up during it’s normal daily file level backup.

Enjoy the backup goodness!!!