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.
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.
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.
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 >.
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.
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.