Database Reference
In-Depth Information
Restoring Backups
If data is lost in MySQL, butyou've been using
mysqldump
to make regular backups of the
data, you can use the dump files to restore the data. This is the point of the back-ups, after
all. Restoring a dump file madewith
mysqldump
is just a matter of using the
mysql
clientto
execute all of the SQL statements contained in the dump file. You can restore all of the
databases, a single database, individual tables, or even specific rows of data. We'll cover all
of these in this section.
Restoring a Database
Let's look athow to restore an entire database. To be safe, as part of experimenting, we'll
make a fresh backup of the
rookery
database and then restore it. Execute the following
from the command line on your server:
mysqldump --user=
admin_backup
--password --lock-tables \
--databases
rookery
>
rookery.sql
Before proceeding, check the contents of the dump file. Make sure it contains the SQL
statements for restoring the
rookery
database. If everything looks OK, delete the
rook-
ery
database from the server. This may seem scary, but you just made a good back-up.
There will come a time when a database is deleted or corrupted unintentionally. So it's bet-
ter to develop confidence in your ability to restore a database with a test database like
rookery
. To get rid of the database, you can execute the following from the command
line:
mysql --user=
admin_maintenance
--password --execute "DROP DATABASE
rookery
;"
Here we're using the
mysql
client at the command line toexecute the
DROP DATABASE
statement. You could have done this from within the
mysql
client, though. It's done here on
the command line with the
--execute
option. You'llhave to specify an administrative
user that has privileges to drop a database. Here we're using the
admin_restore
user we
created in the previous chapter. After you've dropped the
rookery
database, execute
SHOW DATABASES
statementwith the
mysql
client to confirm that
rookery
has been
deleted.
We're now ready to restore the
rookery
database. To do this, execute the following from
the command line:
mysql --user=
admin_restore
--password <
rookery.sql
