Note: Some of this was cut and pasted from this article by Ian Gilfillan, and is printed here merely for my convenience. If you are Ian and have a problem with this, lmk. Thanks.
Backing up your database
There are two approaches to backing up your mysql database. You can either backup the raw database files themselves (usually found somewhere in the proximity of /var/lib/mysql/), or you can dump the database (or individual tables) to a file that can be used to reconstruct it in the case of a disaster.
The perl script mysqlhotcopy is useful for doing raw copies. For creating a backup .sql script that can be used to recreate your database, I recommend mysqldump.
mysqldump dbname > dbname-bkup.sql
To restore using this backup:
mysql dbname < filepath_filename.sql
Note, before restoring, if a database with this name already exists, you may want to delete it. Alternatively, if you use the switch –add-drop-table to mysqldump when making the backup, all database tables will be deleted before restoring.
Other switches that may be of interest include:
| –add-drop-table | Adds a DROP TABLE statement before each CREATE TABLE. This ensures that when restoring, if the tables already exist, they will first be dropped and not able to interfere with the restoration. |
| –add-locks | Puts a LOCK TABLES statement before each table dump, and an UNLOCK TABLE statement, causing the INSERT statements to be processed much more quickly, as the key buffer is only flushed once per table, after the UNLOCK. |
| –lock-tables, -l | Locks all tables on the server before starting the dump. MySQL uses a READ LOCAL lock, which allows concurrent inserts. |
| –opt |
Use this! Same as –add-drop-table –add-locks –all –extended-insert –quick –lock-tables |
| –all-databases, -A | Dumps all databases |
| –tables | Dumps a list of tables (this overwrites the –databases option) |
|
-u username -p password -h host |
Allows you to specify the username, password or an alternate host to localhost. |
| –where=’condition’ | Allows you to dump results returned according to a specific condition. |
Here is a crontab example:
0 5 * * * mysqldump –user=shortstat –password=NotTelling –opt blogstats > /var/www/blogstats.sql
mysqlhotcopy is a Perl script that physically copies files. MyISAM tables are stored as files, and can therefore be backed up and restored simply by copying the data, index and structure files. This is much quicker than mysqldump, but does not work for InnoDB tables, which are not stored individually as files. When copying files, you need to ensure that either the server is offline, or the tables are locked to ensure a consistent backup. mysqlhotcopy does all of this for you by obtaining a read lock on all the tables to be backed up, then copying the tables and releasing the lock. It is a good option for small, live databases, but not for larger, heavily used databases. mysqlhotcopy works as follows:
$ mysqlhotcopy dbname filepath
There are a number of prerequisites to running mysqlhotcopy.
To restore from mysqlhotcopy, simply copy the files back to the data directory:
$ cp filepath_filenames datadir_filepath