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 to reenter 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, 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 using
mysqldump and start it up again after
Alternatively, you can lock the tables you are backing up before
mysqldump. To lock tables for
reading (as we want to read the data), from the MySQL command line issue
tablename1 READ, tablename2READ ...
Then, to release the lock(s), enter:
UNLOCK TABLES; ...