8.3. Backing Up and Restoring DataEven 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 FilesYou 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 CommandIt'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 upWe'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
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 backupThe 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 formatsAlthough 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 commandWhen 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
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 practicesDepending 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. |