MySQL Reference Manual

Disaster Prevention and Recovery

Database Backups

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables followed by FLUSH TABLES for the tables. See Section 6.7.2, and Section 4.5.3. You only need a read lock; this allows other threads to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLE is needed to ensure that all the active index pages are written to disk before you start the backup.

If you want to make a SQL-level backup of a table, you can use SELECT INTO OUTFILE or BACKUP TABLE. See Section 6.4.1, and Section 4.4.2.

Another way to back up a database is to use the mysqldump program or the mysqlhotcopy script, and Section 4.8.5. See Section 4.8.6.

1. Do a full backup of your databases:

   shell> mysqldump --tab=/path/to/some/dir --opt --full
   
   or
   
   shell> mysqlhotcopy database /path/to/some/dir

   You can also simply copy all table files (*.frm, *.MYD, and *.MYI files) as long as the server isn’t updating anything. The script mysqlhotcopy does use this method.

2. Stop mysqld if it’s running, then start it with the --log-update[=file_name] option. See Section 4.9.3. The update log file(s) provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

If you have to restore something, try to recover your tables using REPAIR TABLE or myisamchk -r first. That should work ...