Database Reference
In-Depth Information
Restoring with a temporary database
Another way to restore asingle table from a dump file that contains a database with many
tables is simply to change the name of the database in the dump file. The dump file gener-
allycontains a
CREATE DATABASE
statement. If you change the name of the database
to a unique name that's not already used on the server, a new database will be created on
the server when the dump file is run. Then you can copy the table you want from this tem-
porary database within MySQL to the original database. When you're finished, you can
delete the temporary database. Let's look at an example.
Returning to the previous scenario, suppose that you have a dump file containing the
rookery
database, from which you need to restore only the
conservation_status
table. So that you can participate, if you don't have a current dump file of
rookery
, use
mysqldump
to make one.
First, run
SHOW DATABASES
onthe server to see the names of the database so that you
don't by chance give the temporary database a name that's already in use. Next, open the
dump file in a text editor and look for the lines near the top that creates the database. Edit
that section to change the name of the database. Here's how that section of the dump file
might look after you edit it:
--
...
-- Current Database: `rookery`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `
rookery_backup
`
/*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */;
USE `
rookery_backup
`;
...
In this excerpt, you can see that I changed the name of
rookery
to
rookery_backup
in two places: the
CREATE DATABASE
statement andthe
USE
statement. That's all that
you need to change. You can save the dump file now and execute it. Using an administrat-
ive user that has the
CREATE
privilege,enter something like this from the command line:
mysql --user=
admin_restore
--password <
rookery.sql
Once you've executed this, there should be a new database called
rookery_backup
.
Log into MySQL through the
mysql
client and set the default database to
rook-
ery_backup
. Run the
SHOW TABLES
statement and a couple of
SELECT
statements.
