Database Reference
In-Depth Information
OPTION
privilege. As root, you will have that privilege. Let's go through the steps in-
volved in this method, using the previous example in which we want to restore the
con-
servation_status
table.
WARNING
There is a risk in this method. If you're not precise about what privileges you grant the user account, or
if you restore data from the dump file inadvertently using the root user account instead of the limited
user account, you will overwrite all of the databases that were backed up to the dump file. So be careful.
Before you start to restore your data, delete the
conservation_status
table and
change some data in one of the other tables so that you can see how well this method
works. You can run something like the following from the command line, using the
ad-
min_boss
user account you should have created in the
Chapter13
exercises:
mysql --user=
admin_boss
--password \
--execute "DROP TABLE rookery.conservation_status;
INSERT INTO rookery.birds (common_name,description)
VALUES('Big Bird','Large yellow bird found in New
York');
SELECT LAST_INSERT_ID();"
That should delete the
conservation_status
table. To test our restore, we've also
added a row to the
birds
table, which we want to make sure has not been lost when we
do our restore. The last statement returns the
bird_id
for the row inserted. Log into
MySQL and verify that the
conservation_status
table has been deleted and use
the
SELECT
statement to view the row inserted into
birds
, where the
bird_id
equals
the number you were given when you executed the command. If everything looks as it
should, you're ready to proceed.
Now you need to create the limited administrative user. Enter the
GRANT
statementon
your server like this:
GRANT SELECT
ON
rookery
.* TO '
admin_restore_temp
'@'localhost'
IDENTIFIED BY 'its_pwd';
GRANT ALL ON
rookery.conservation_status
TO '
admin_restore_temp
'@'localhost';
