Database Backup and Recovery
From AgileApps Support Wiki
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
- Drop all the databases.
- Restart mysqld
- 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: