Database Reference
In-Depth Information
These two SQL statements grant the temporary with thenecessary
SELECT
privilege on
all of the tables in the
rookery
database, and
ALL
privilegesfor the
conserva-
tion_status
table. When you restore the database dump file containing all of the
tables in the
rookery
database, using the
admin_restore_temp
user account, only
con-
servation_status
will be replaced.
When you execute the dump file with this user account, MySQL will generate errors when
it tries to replace the other tables. Normally, that might stop execution of the dump file. To
overlook the errors and to proceed with the restoration of data for tables for which no er-
rors are generated, use the
--force
option withthe
mysql
client.
Let's restore the table now. Enter the following at the command line:
mysql --user
admin_restore_temp
--password --force <
rookery.sql
This should work without a problem. To verify that the
conservation_status
table
has been restored, log into MySQL and check. Then execute the
SELECT
statement again
to see whether the row you entered for Big Bird from the command line in the
birds
table is still there. If it is, that means the
birds
table wasn't overwritten when you re-
stored the dump file. Everything elseshould be fine.
Restoring Only Rows or Columns
You'll rarely needto restore an entire database or even an entire table. It's not often that a
database or a table is dropped, or that the data in all of the rows in a table are changed ac-
cidentally. It's more common that someone deletes a single row in a table or data in a
single column and can't undo what they did. In such a situation, if the table has many oth-
er rows that were changed correctly since the last backup was made, you wouldn't want to
restore the whole table to fix one small mistake. Instead, you will want to restore only one
row or column.
This can be done easily using the method covered in
Restoring with a temporary database
.
That section described how to modify the dump file for the
rookery
database so that
MySQL imports the database into a new, temporary database (
rookery_backup
). If
you use that method, you can then use the
INSERT...SELECT
statement with a
WHERE
clause to select only the row or rows you want to restore. Let's walk through this process.
Suppose that someone accidentally deleted one of the members (e.g., Lexi Hollar) and the
email address of another member (e.g., Nina Smirnova) from the
humans
table in the
birdwatchers
table. To be able to follow along and to set the stage, make a backup of
just the
birdwatchers
database, delete the entry for Lexi Hollar, and Nina Smirnova's
email address by executing the following from the command line:
