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 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 using
mysqldump and start it up again after
mysqldump finishes.
Alternatively, 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:
Get Learning PHP, MySQL, JavaScript, and CSS, 2nd Edition 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.
