MySQL backup command tips

To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. See Section 13.4.5, ?LOCK TABLES and UNLOCK TABLES Syntax?, and Section 13.5.5.2, ?FLUSH Syntax?. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

To make an SQL-level backup of a table, you can use SELECT INTO ... OUTFILE. For this statement, the output file cannot already exist because allowing files to be overwritten would constitute a security risk. See Section 13.2.7, 'sELECT Syntax?.

Another technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script. See Section 8.12, ?mysqldump - A Database Backup Program?, and Section 8.13, ?mysqlhotcopy - A Database Backup Program?.

1. Create a full backup of your database:

   shell> mysqldump --tab=/path/to/some/dir --opt db_name

   Or:

   shell> mysqlhotcopy db_name /path/to/some/dir