MySQL Database

ID #1004

How can I backup my database using cron?

Applies to: Grid System

There are many ways to use cron to backup your database(s). One of the easiest ways is using a bash shell script:

  1. Create the folder /dbbackup/ in the root directory.

  2. Create a text file, /dbbackup/backup.sh, with the following bash commands:

    #!/bin/sh
    FILENAME="/dbbackup/db-´/bin/date +\%m-\%d´.sql.gz"
    /usr/bin/mysqldump -h DB_SERVER -u DB_USERNAME -pDB_PASSWORD --opt DB_NAME | gzip > $FILENAME;
    

    Note: The lack of a space between the "-p" flag and your database password is intentional and necessary.

    The above command will create a zipped dump file of your database called db-05-28.sql.gz in the folder /dbbackup/. The "-05-28" section is determined by the month and the day the backup is made (May 28th in this example).

  3. Alter the permissions of the file so that you (the owner) has execute pemissions. This shell command will make the changes:

    chmod u+x /dbbackup/backup.sh
  4. Now create a daily cron job tha runs in off hours (to run it at 2:15 AM MST use the following: 15 2 * * *).  With this cron job, run the command: /dbbackup/backup.sh. You can create a cron job via Modwest OnSite here, or we have information on creating a cron job via the shell here here.

  5. You can connect to your account via FTP and download your sql dump files from the folder /dbbackup/.  Or, you can restore your database from a backup by importing one of the sql dumps.


Last update: 2011-12-22 16:40
Author: Walt Javins
Revision: 1.10

Digg it! Share on Facebook Print this record Send FAQ to a friend Show this as PDF file
Please rate this FAQ:

Average rating: 5 (2 Votes)

completely useless 1 2 3 4 5 most valuable

You can comment this FAQ