Jump to content

The Protestant Community

Welcome to Christforums the Protestant Community. You'll need to register in order to post your comments on your favorite topics and subjects. You'll also enjoy sharing media across multiple platforms. We hope you enjoy your fellowship here! God bless, Christforums' Staff
Register now

Christforums

Christforums is a Protestant Christian forum, open to Bible- believing Christians such as Presbyterians, Lutherans, Reformed, Baptists, Church of Christ members, Pentecostals, Anglicans. Methodists, Charismatics, or any other conservative, Nicene- derived Christian Church. We do not solicit cultists of any kind, including Mormons, Jehovah's Witnesses, Eastern Lightning, Falun Gong, Unification Church, Aum Shinrikyo, Christian Scientists or any other non- Nicene, non- Biblical heresy. God bless, Christforums' Staff
Register now
Sign in to follow this  
William

The MySQL Dump Code for Cron

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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:

 

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:

 

#!/bin/bash

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
find /your path to backups/ -mtime +7 -exec rm {} \;

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×