Database Reference
In-Depth Information
Solution
Use
mysqldump
to back up your databases.
Discussion
The
mysqldump
program provides an easy way to back up database contents. This pro‐
gram is discussed elsewhere, but primarily as a means of copying individual files (see
Recipe 4.6
). For administrative purposes, you're likely more interested in backing up
entire databases, including nontable objects such as stored programs. This section shows
some simple techniques for backup and recovery.
The
mysqldump
commands shown here include the
--routines
and
--events
options
so that dump output includes definitions for stored functions and procedures and
scheduled events. (There is also a
--triggers
option, but it's not used here because
mysqldump
dumps triggers with their associated tables by default.) To omit stored rou‐
tine or scheduled event definitions from dump output, omit the
--routines
or
--
events
option. To omit trigger definitions, use
--skip-triggers
.
To back up a single database:
%
mysqldump --routines --events db1 > dump.sql
To reload the dump file:
%
mysql db1 < dump.sql
That command reloads the file into the database from which it was dumped (
db1
),
thereby restoring it to its state at the time of the dump. To make a
copy
of the original
database, specify the name of a different database. (Create the database first if it doesn't
exist.)
To back up multiple databases:
%
mysqldump --routines --events --databases db1 db2 db3 > dump.sql
Normally,
mysqldump
treats nonoption arguments following the first as table names.
The
--databases
option causes
mysqldump
to treat all such arguments as database
names. That option also causes dump output to include
CREATE
DATABASE
and
USE
statements for each database. This causes the reload operation to create each database
as necessary, and makes it the default database so the following contents reload into it.
To reload the dump file:
%
mysql < dump.sql
In this case, no database name is needed on the command line (and in fact is ignored
if given) due to the
USE
statements in the dump file.
To back up all databases:
