Skip to Main Content
Learning PHP, MySQL, and JavaScript
book

Learning PHP, MySQL, and JavaScript

by Robin Nixon
July 2009
Beginner content levelBeginner
526 pages
14h 24m
English
O'Reilly Media, Inc.
Content preview from Learning PHP, MySQL, and JavaScript

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; ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Web Database Applications with PHP and MySQL, 2nd Edition

Web Database Applications with PHP and MySQL, 2nd Edition

Hugh E. Williams, David Lane
Learning PHP

Learning PHP

David Sklar

Publisher Resources

ISBN: 9780596803605Supplemental ContentErrata Page