Backing Up and Restoring
Whatever kind of data you are storing in your database it must have some value to you, even if it’s only the cost of the time required for reentering it should the hard disk fail. Therefore it’s important that you keep backups to protect your investment. Also there will be times when you have to migrate your database over to a new server; the best way to do this is usually to back it up first. It is also important that you test your backups from time to time to ensure that they are valid and will work if they need to be used.
Thankfully, backing up and restoring MySQL data is easy using the
mysqldump
command.
Using mysqldump
With mysqldump
, you can dump
a database or collection of databases into one or more files
containing all the instructions necessary to recreate all your tables
and repopulate them with your data. It can also generate files in CSV
(Comma-Separated Values) and other delimited text formats, or even in
XML format. Its main drawback is that you must make sure that no one
writes to a table while you’re backing it up. There are various ways
to do this, but the easiest is to shut down the MySQL server before
mysqldump
and start up the server
again after mysqldump
finishes.
Or you can lock the tables you are backing up before running
mysqldump
. To lock tables for
reading (as we want to read the data), from the MySQL command line
issue the command:
LOCK TABLES tablename1 READ, tablename2
READ, ...;
Then, to release the lock(s), enter:
UNLOCK TABLES; ...
Get Learning PHP, MySQL, and JavaScript now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.