Web Database Applications with PHP and MySQL, 2nd Edition

Backup and Recovery

It's happened to all of us: your hard disk crashes, your machine dies, somebody steals your box, or you get horribly hacked. In these cases, the only way to recover your application is to restore a backup copy of your database (after you've reinstalled MySQL!). There are also less catastrophic events that can occur from which you need to recover: indexes and tables can become corrupt because of a power failure or MySQL unexpectedly dying, your operating system might crash, or a disk may become unreliable.

To protect against catastrophic events, you should make regular backups and store these offsite. There are many different ways you can backup your MySQL installation, and different ways you can automate the process. The simplest technique is to automate the dumping of your database as SQL statements into a file using the mysqldump utility, and this is the approach we focus on in this section.

In a Unix environment, you can also use mysqlhotcopy to do backups; it's a Perl script that works only for MyISAM tables and is described in more detail in Section 4.8.7 of the MySQL manual. Other approaches you can use in all environments are the BACKUP TABLE and RESTORE TABLE statements from within the command interpreter or a PHP script, and simply copying the database files when the database is offline. We don't discuss these approaches here.

All backup techniques have in common that they result in one or more files that are the backup of the database. You could burn ...