Database Backup and Recovery

MySQL can be backed up using the mysqldump command - http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

Tip: The replicated database server should be used for backups. To set up for it, see Configuring MySQL to Run on a Separate Server

Standard Database Backup

Dump the database

mysqldump –uroot –pxxx –all-databases –quick –routines –result-file=dumpfile.sql

Dump the database at regular intervals, using a Linux cron job

• Put the mysqldump command in a shell script - say, xyz.sh
• Setup cron job e.g. every day at 6 pm

 
0 18 * * * /yourscriptlocation/xyz.sh > /somedirectory/xyz.out 2>&1

• Use tar to compress the resulting dump file, to save space.
• Maintain daily backups for ten days or so, to reclaim the space they use.
• Maintain monthly backup. Save the dump of last day of the month, for example, and retain the dump file for a year or so.
• Store long-term backups offsite.

Standard Database Restore

Restoring all data from a dump file

1. Drop all the databases.
2. Restart mysqld
3. Start the restore process and run in background:

mysql –uroot –pxxxx < dumpfile.sql > dumpfile.out 2>&1 &

Restoring a single database or table from a dump file

• Drop the old database/table:

mysql –uroot –pxxxx $database < dumpfile.sql > dumpfile.out 2>&1 &

Learn More

For more detailed information on database backup and recovery in MySQL, see:

• http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
• http://www.mydigitallife.info/2007/07/21/
  how-to-backup-and-restore-export-and-import-mysql-databases-tutorial