Section 8.3. Backing Up and Restoring Data

8.3. Backing Up and Restoring Data

Even the best maintained databases occasionally develop problems. Hardware failures, in particular, can really throw a monkey wrench into your web pages. Now that you're using a database, just backing up the files (HTML, PHP, and images) on your web server isn't enough. There's nothing worse than informing your web users that they have to reenter information, such as their accounts, or have to recreate your catalog items. Having a complete backup can make the difference between an hour of down time and having to recreate the wheel. There are a couple of tactics that we'll discuss for backing up your database data.

8.3.1. Copying Database Files

You can also do a simple file backup of your MySQL database's datafiles, in the same way that you can back up your HTML and PHP files. If you can back up files, you can back up the MySQL database files.

We don't recommend this tactic for moving a database from one machine to another server, since different versions of MySQL may expect these files to be in a different format. MySQL stores its datafiles in a special data directory that is usually located in C:\Program Files\MySQL\MySQL Server 4.1\data\[database_name] on Windows and in /var/lib/mysql on Unix variants such as Linux and Mac OS X.

To fully back up and restore a MySQL database using your current datafiles, all the files must be replaced in the same directory from which they were backed up. Then, the database must be restarted.

8.3.2. The mysqldump Command

It's better to use the MySQL command-line tool for making complete database backups. The same tools you'll use to back up and restore can also be used to change platforms or move your database from one server to another; mysqldump creates a text file containing the SQL statements required to rebuild the database objects and insert the data. The mysqldump command is accessible from the command line and takes parameters for backing up a single table, a single database, or everything. The command's syntax is:

 mysqldump -u user -p objects_to_backup 

The default mode for mysqldump is to export to backup and then to standard output, which is usually the screen.

8.3.2.1. Backing up

We're going to show you the commands to back up a database called test from the shell prompt.

 mysqldump -u root -p test > my_backup.sql 

This tells mysqldump to log into the database as the root user with a password of barney, and to back up the test database. The output of the command is saved to a file called my_backup.sql with the help of the redirect character also known as the greater-than symbol (>).

Example 8-1 shows the first portion of the output mysqldump creates.

Example 8-1. The contents of the my_backup.sql file

 -- MySQL dump 10.9 -- -- Host: localhost    Database: test -- ------------------------------------------------------ -- Server version       4.1.11-Debian_4-log -- -- Table structure for table `authors` -- DROP TABLE IF EXISTS `authors`; CREATE TABLE `authors` (   `author_id` int(11) NOT NULL auto_increment, `title_id` int(11) NOT NULL default '0',   `author` varchar(125) default NULL,   PRIMARY KEY  (`author_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `authors` -- /*!40000 ALTER TABLE `authors` DISABLE KEYS */; LOCK TABLES `authors` WRITE; INSERT INTO `authors` VALUES (1,1,'Ellen Siever'),(2,1,'Aaron Weber'),(3,2,'Arno ld Robbins'),(4,2,'Nelson  Beebe'); UNLOCK TABLES; /*!40000 ALTER TABLE `authors` ENABLE KEYS */; 

The two major sections in Example 8-1 are creating the authors table and populating the data for the table.

To back up only a single table from a database, simply add the table name after the database name. For example, the command below illustrates how to back up only the authors table:

 $ mysqldump -u root -p test authors > authors.sql 

Most of the time, you'll just want to back up everything in the database. To do this, use the --all-databases command-line switch. The resulting database backup file will contain the commands necessary to create the databases and users, making a complete database restore a snap. Here's how to use this parameter:

 $ mysqldump -u root -p --all-databases > my_backup.sql 

To create an empty copy of your databasejust the structurefor testing, use the --no-data switch:

 $ mysqldump -u root -p --no-data test > structure.sql 

You can also do the opposite and just back up the data with the --no-create-info switch like this:

 $ mysqldump -u root -p --no-create-info test > data.sql 

Of course, having a backup of your database doesn't do you much good if you don't know how to restore the database from it.

8.3.2.2. Restoring a MySQL backup

The good news is it's not difficult to recreate your database from a mysqldump file. As you saw in Example 8-1, the contents of the backup file are simply SQL statements and can therefore be processed by the mysql command-line client to restore the backed-up data.

If you did a backup of your database using mysqldump --all-databases to a file called my_backup.sql, you could restore your database like this:

 mysql -u root -p < my_backup.sql 

If you did a selective backup of only one database, it's a bit more complex. To restore that type of backup file, use the -D command-line switch:

 mysql -u root -p -D test < my_backup.sql 

Now that you know how to restore default dump files, we can move on to some other applications regarding exporting and importing data.

8.3.2.3. Working with other formats

Although working with SQL-based files is convenient, there may be times when you want to save your data in other formats. For example, a common method of representing a list of data is in CSV (comma-separated values) format. The mysqldump command supports this format. All you need to do is specify the --no-create-info, --tab, and --fields-terminated-by arguments like this:

 mysqldump -u root -p --no-create-info --tab=/home/jon --fields-terminated-by=',' test 

This tells mysqldump to generate separate files for each table in the test database. They'll all be placed in the directory /home/jon. Each file's name will be the name of the table that is being exported. Each file contains the records in the respective table separated by the comma character (,) that was specified on the command line.

8.3.2.4. The mysqlimport command

When you're setting up your database, you may need to bring in data from another database or a spreadsheet in CSV format. For example, if you're offering books for sale, you may bring in the existing catalog of books. To import the data displayed in Example 8-2, use the mysqlimport command.

Example 8-2. Book titles in CSV format

 1,Linux in a Nutshell,476 2,Classic Shell Scripting,256 

Like this:

 mysqlimport -u root -p --fields-terminated-by=',' test /home/jon/books.txt 

The main portion of the filename (not including the path or the file extension) determines the name of the table. In the example above, the table name is books. The table must already exist or an error displays. Another useful keyword is ENCLOSED BY char;, which allows you to specify characters, such as double quotes (") that enclose each field in the file. This is useful for avoiding the problem with a book title like Classic Shell Scripting, Second Edition, which would otherwise cause mysqlimport to process the "Second Edition" portion of the title as the start of the next field.

8.3.2.5. Backup best practices

Depending on how critical your data is and how often it changes, you can determine how often to back it up. As a rule, weekly, bi-weekly, and monthly are the most common schedules. If your business is completely dependent on your database, you should do a weekly backup schedule, if not backing up daily. Also, keeping a copy of the data in a separate location is a good idea in the event of large scale disasters, such as a fire. A client of ours keeps bi-monthly backups in a fire safe at the office, whereas another client sends the data to a backup service. A backup service can use physical hard drives, tapes, or CDs, or can log into your server and perform the backup electronically.