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