Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 352

You'll see that the tables and data are all there. Now you're ready to restore the table you
need.
There are a couple of ways you can restore a table at this point. Let's try both. First, let's
delete the conservation_status table in the rookery database. To do this, ex-
ecute the following within the mysql client:
DROP TABLE rookery . conservation_status ;
Now create a new conservation_status table in rookery . You can do this based
on the backup copy by using a CREATE TABLE...LIKE statement, covered in Essen-
tial Changes . Enter the following on your server:
CREATE TABLE rookery . conservation_status
LIKE rookery_backup . conservation_status ;
Next, you need to copy the data from the backup table to the newly created table. You can
do that by entering this SQL statement on your server:
INSERT INTO rookery . conservation_status
SELECT * FROM rookery_backup . conservation_status ;
The INSERT...SELECT syntax is covered in Other Possibilities . It will insert into the
original database's table all of the rows selected from the backup table. When that's fin-
ished, execute a SELECT statement to see that all of the data is in the conserva-
tion_status table. If everything is fine, delete the temporary database by entering the
following on your server:
DROP DATABASE rookery_backup ;
This method of restoring a single table works nicely. For a large database, though, it could
take a long time to temporarily import the entire database into MySQL. However, if you
have a database this large, you should make backups based on tables or batches of tables
to make restoration more manageable. This method requires CREATE and DROP priv-
ileges,which allow the user account to create new databases and drop them.
There is another method for restoring a single table that doesn't require editing the dump
file. That method is explained in the nextsection.
Using a limited user account
A simple way torestore only one table is to create a temporary user account that has only
privileges for the table you want to restore. When you run the dump file, the SQL state-
ments for other tables will fail and not be executed — only the table for which the user ac-
count has privileges will be restored. To create such a user account, youneed the GRANT