Use MYSQLDUMP and CRON to backup Databases (MySql)

BradTrupp.com -- Tags -- Old Articles -- Use MYSQLDUMP and CRON to backup Databases (MySql)

Use MYSQLDUMP and CRON to backup Databases (MySql)
(2006/05/23)

Use MYSQLDUMP and CRON to backup Databases
By Brad Trupp (c) 2007

If you are keeping any web site data in MYSQL databases, are you making regular backups?

"The mysqldump client can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create the table and/or populate the table."-- from the MySQL Reference Manual.

Technique 1

I wrote a short PERL script to backup my database and compress the results into a unique name based on current date.

Sample PERL script --

#!/usr/bin/perl

($Second, $Minute, $Hour, $Day, $Month, $Year, $WeekDay, $DayOfYear, $IsDST) = localtime(time) ; 

$Year += 1900 ; $Month += 1;

$dt = sprintf("%04d%02d%02d", $Year, $Month, $Day, ) ;

exec "/usr/local/bin/mysqldump --opt -hHOSTNAME -uUSERID -pPASSWORD DATABASE_NAME |gzip > PATHNAME/$dt.gz";

The $dt variable gets resolved to the current date in YYYYMMDD format.

You will need to customize the exec statements where:

Change /usr/local/bin/ to the correct location for mysqldump (or take it out if it is on your path)
Substitute the correct names for HOSTNAME, USERID, PASSWORD, and DATABASE_NAME.
Put in the correct path name in the PATHNAME variable for the output file location.
If gzip is not in your path then put in the proper location too.

So each time you run the perl script, it will backup your database and compress the results into a unique name.

As for running it regularly, just set up a CRON job to run it daily, weekly, or whatever. I'm no CRON expert but I use pair hosting so CRON's can be set up from a control panel without any real knowledge.

Enjoy this gem of wisdom -- it took me far longer than it should of to figure out what goes on the EXEC command.

Technique 2

The wizards at Pair networks sent out this gem of wisdom in one of their monthly newsletters.

To back up a particular database, enter this command (all on one line):

/usr/local/bin/mysqldump -hDBXX.PAIR.COM -uDB_USERNAME -pDB_PASSWORD USERNAME_DATABASENAME > 
usr/home/USERNAME/backup/DATABASENAME.`/bin/date +\%Y\%m\%d`

Here are the replacement values for the above command:

DBXX.PAIR.COM = The hostname of the database server the database resides on
DB_USERNAME = The MySQL username for the database in question
DB_PASSWORD = The MySQL password for the username above
USERNAME_DATABASENAME = The full name of the database
USERNAME = Your pair Networks username

These commands will generate a file in the "backup" directory off of the home directory called DATABASENAME.DATE where DATE is the date the backup was made. Make sure that a "backup" directory exists off of your home directory when creating these cron jobs.

Just set this up to run regularly with cron and you now have regular backups.

Tags: Old Articles