Database Backup and Recovery

From AgileApps Support Wiki
Revision as of 11:58, 13 July 2017 by imported>Aeric
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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

Thumbsup.gif

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: