Tap the technical and computer knowledge of other experienced users. Find information on computers and technological topics.

The MySQL Dump Code for Cron

  • Filter
  • Time
  • Show
Clear All
new posts

    The MySQL Dump Code for Cron

    Here is the script in Cron that I use:

    date=`/bin/date "+\%Y-\%m-\%d-\%H-\%M-\%S"` ; /usr/local/bin/mysqldump -u DATABASEUSERNAME -pPASSWORD DATABASENAME > /usr/home/USER/DATABASENAMEbackup_$date.sql ; gzip /usr/home/USER/DATABASENAMEbackup_$date.sql
    You need to replace anything that is in caps with the relevant text. So you need the database name, database username, database password and the server username.

    Explaining the Code

    The first part of the command is using the Unix date function. date=`/bin/date "+\%Y-\%m-\%d-\%H-\%M-\%S"` ;

    We are formating it to show the Year, Month, Day, Hour, Minute and Second. This is then added to the file name so that each file name is unique.

    Then we run the mysqldump command to create the backup itself. /usr/local/bin/mysqldump -u DATABASEUSERNAME -pPASSWORD DATABASENAME > /usr/home/USER/DATABASENAMEbackup_$date.sql ;

    You might need to change the path to mysqldump as this depends on your server setup. If in doubt, ask your hosting provider.

    Finally we gzip the file to compress it.gzip /usr/home/USER/DATABASENAMEbackup_$date.sql

    You need to set the time that this cron job will run. If you want to run it once a day, then it is a wise idea to run it overnight. You can set the hour to midnight, for example.

    Make sure you check that it is running by going to the root folder and checking for new backup files. You can FTP these to your local computer on a regular basis for added piece of mind.

    Backup folder

    If you want the backups to be in one folder, then in your FTP client create a folder called "dbbackups". This should be in the root directory so that it is not accessible from the web. Then add the folder to your path. So the command would become:date=`/bin/date "+\%Y-\%m-\%d-\%H-\%M-\%S"` ; /usr/local/bin/mysqldump -u DATABASEUSERNAME -pPASSWORD DATABASENAME > /usr/home/USER/dbbackups/DATABASENAMEbackup_$date.sql ; gzip /usr/home/USER/dbbackups/DATABASENAMEbackup_$date.sql

    Then you can relax, knowing that a backup of your database is being created on auto pilot.

    This is another option that I later implemented instead of the previous post:

    I wrapped the backup command with a delete command into a single shell script. The deletion ensures the directory containing the backups will not grow beyond seven days of backups by auto deleting older than seven day backups.

    Remember, you must replace the all capital values with your own and add your file path to directory.

    I first created a Cron Job to run every six hours:

    sh /your path to file/mysqldump.sh
    In the home directory I created a file named "mysqldump.sh" that contains the below script with a minor change to the Unix date/time:

    PHP Code:
    date=`/bin/date "+%Y-%m-%d-%H-%M-%S"` ; /usr/local/bin/mysqldump -u DATABASEUSERNAME -pPASSWORD DATABASENAME > /usr/home/USER/DATABASENAMEbackup_$date.sql gzip /usr/home/USER/DATABASENAMEbackup_$date.sql 

    Only adding to the end of it:

    find /your path to backups/ -mtime +7 -exec rm {} \;
    The complete file mysqldump.sh looks like this:

    PHP Code:


    date=`/bin/date "+%Y-%m-%d-%H-%M-%S"` ; /usr/local/bin/mysqldump -u DATABASEUSERNAME -pPASSWORD DATABASENAME > /usr/home/USER/DATABASENAMEbackup_$date.sql gzip /usr/home/USER/DATABASENAMEbackup_$date.sql
    /your path to backups/ -mtime +-exec rm {} \; 
    Articles - News - SiteMap