Friday, May 22, 2009

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):

  6. #:

    DATE=`date +%Y%m%d`


    # 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.

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

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!!!



No comments: