//

2010/09/03

Use MYSQLDUMP and CRON to backup Databases (MySql)

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

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.

No comments:

IP address