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:
UNLOCK TABLES;
By default, the output from mysqldump is simply printed out, but you can
capture it in a file through the >
redirect symbol.
The basic format of the mysqldump command is:
mysqldump -uuser-ppassworddatabase
However, if you want to dump the contents of a database, you must
make sure that mysqldump is in your
path, or that you specify its location as part of your command. Table 9-13 shows the likely
locations of the program for the different installations and operating
systems covered in Chapter 2.
If you have a different installation, it may be in a slightly different
location.
Operating system & program | Likely folder location |
Windows 32-bit Zend Server CE | C:\Program Files\zend\MySQL51\bin |
Windows 64-bit Zend Server CE | C:\Program Files (x86)\zend\MySQL51\bin |
OS X Zend Server CE | /usr/local/zend/mysql/bin |
Linux Zend Server CE | /usr/local/zend/mysql/bin |
So, to dump the contents of the publications database that you created in
Chapter 8 to the screen, enter mysqldump (or the full path if necessary) and
the command in Example 9-7.
mysqldump -uuser-ppasswordpublications
Make sure that you replace user and
password with the correct details for your
installation of MySQL. If there is no password set for the user, you can
omit that part of the command, but the -u user part is
mandatory—unless you have root access without a password and are
executing as root (not recommended). The result of
issuing this command will look something like the screen grab in Figure 9-4.
Creating a Backup File
Now that you have mysqldump
working and have verified that it outputs correctly to the screen, you
can send the backup data directly to a file using the > redirect symbol. Assuming that you wish
to call the backup file publications.sql, type in
the command in Example 9-8 (remembering to
replace user and
password with the correct details).
mysqldump -uuser-ppasswordpublications > publications.sql
Note
The command in Example 9-8 stores the backup file into the current directory. If you need it to be saved elsewhere, you should insert a file path before the filename. You must also ensure that the directory you are backing up to has the right permissions set to allow the file to be written.
If you echo the backup file to the screen or load it into a text editor, you will see that it comprises sequences of SQL commands such as the following:
DROP TABLE IF EXISTS `classics`; CREATE TABLE `classics` ( `author` varchar(128) default NULL, `title` varchar(128) default NULL, `category` varchar(16) default NULL, `year` smallint(6) default NULL, `isbn` char(13) NOT NULL default '', PRIMARY KEY (`isbn`), KEY `author` (`author`(20)), KEY `title` (`title`(20)), KEY `category` (`category`(4)), KEY `year` (`year`), FULLTEXT KEY `author_2` (`author`,`title`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
This is smart code that can be used to restore a database from a backup even if it currently exists, because it will first drop any tables that need to be recreated, thus avoiding potential MySQL errors.
Backing up a single table
To back up only a single table from a database (such as the
classics table from the publications database), you should first
lock the table from within the MySQL command line by issuing a command
such as the following:
LOCK TABLES publications.classics READ;
This ensures that MySQL remains running for read purposes, but writes cannot be made. Then, while keeping the MySQL command line open, use another terminal window to issue the following command from the operating system command line:
mysqldump -u user -ppassword publications classics > classics.sql
You must now release the table lock by entering the following command from the MySQL command line in the first terminal window, which unlocks all tables that have been locked during the current session:
UNLOCK TABLES;
Backing up all tables
If you want to back up all your MySQL databases at once
(including the system databases, such as mysql), you can use a command such as the
one in Example 9-9,
which will make it possible to restore an entire MySQL database
installation. Remember to use locking where required.
mysqldump -uuser-ppassword--all-databases > all_databases.sql
Note
Of course, there’s a lot more than just a few lines of SQL code in backed-up database files. I recommend that you take a few minutes to examine a couple in order to familiarize yourself with the types of commands that appear in backup files and how they work.
Restoring from a Backup File
To perform a restore from a file, call the
mysql executable, passing it the file to restore
from using the < symbol. So, to
recover an entire database that you dumped using the --all-databases option, use a command such as
that in Example 9-10.
To restore a single database, use the -D option followed by the name of the
database, as in Example 9-11,
where the publications database is
being restored from the backup made in Example 9-8.
mysql -uuser-ppassword-D publications < publications.sql
To restore a single table to a database, use a command such as
that in Example 9-12,
where just the classics table is
being restored to the publications
database.
Dumping Data in CSV Format
As previously mentioned, the mysqldump program is very flexible and
supports various types of output, such as the CSV format. Example 9-13 shows how you can dump the
data from the classics and customers tables in the publications database to the files classics.txt and customers.txt in the folder c:/temp. By default, on Zend Server CE the
user should be root and no password is used. On OS
X or Linux systems, you should modify the destination path to an
existing folder.
mysqldump -uuser-ppassword--no-create-info --tab=c:/temp --fields-terminated-by=',' publications
This command is quite long and is shown here wrapped over two lines, but you must type it all in as a single line. The result is the following:
Mark Twain (Samuel Langhorne Clemens)','The Adventures of Tom Sawyer','Classic Fiction', '1876','9781598184891 Jane Austen','Pride and Prejudice','Classic Fiction','1811','9780582506206 Charles Darwin','The Origin of Species','Non-Fiction','1856','9780517123201 Charles Dickens','The Old Curiosity Shop','Classic Fiction','1841','9780099533474 William Shakespeare','Romeo and Juliet','Play','1594','9780192814968 Mary Smith','9780582506206 Jack Wilson','9780517123201
Planning Your Backups
The golden rule to backing up is to do so as often as you find practical. The more valuable the data, the more often you should back it up, and the more copies you should make. If your database gets updated at least once a day, you should really back it up on a daily basis. If, on the other hand, it is not updated very often, you can probably get by with backing up less frequently.
Note
You should also consider making multiple backups and storing them in different locations. If you have several servers, it is a simple matter to copy your backups between them. You would also be well advised to make physical backups of removable hard disks, thumb drives, CDs or DVDs, and so on, and to keep these in separate locations—preferably somewhere like a fireproof safe.
Once you’ve digested the contents of this chapter, you will be proficient in using both PHP and MySQL; the next chapter will show you how to bring these two technologies together.