Question: How to restore a specific table from a backup dump file containing all databases and tables?
Sometimes you may need to restore specific tables, but the only available backup contains all databases. Restoring everything from a backup may not be an option. Instead, you will need the ability to restore only specific databases or tables. The following procedures can be used to effectively filter the backup as it is applied:
1. The first step is to create a new user account, specifically for restoring the back-up. This can be done with the CREATE USER statement like so:
CREATE USER 'restorative_user'@'localhost' IDENTIFIED BY 'its_password';
In this example, we’ve created a user called restorative_user. Here, any name is fine. You would replace its_password with whatever password you want to use.
2. Once this is done, we need to give this new user account SELECT privileges on all databases and tables. This can be done with the GRANT statement like this:
GRANT SELECT ON *.* TO 'restorative_user'@'localhost';
3. Next you will need to grant the new user ALL PRIVILEGES for the databases and tables that need to be restored. Again, this can be done with the GRANT statement as show below:
GRANT ALL ON db1.table1 TO 'restorative_user'@'localhost';
GRANT ALL ON db2.* TO 'restorative_user'@'localhost';
In this example, we’ve given full privileges to the user restorative_user for only the table, table1 in the database, db1. In the second statement here, we’ve given full privileges to all tables in the database db2. If the full db2 database is all that requires restoration (no specific tables), we could use the –one-database option to filter statements without creating the restorative_user user account.
4. Now we’re ready to restore the backup using the new user account. We’ll do this from the command-line as shown below:
mysql -u restorative_user -pits_password --force < /path/backup.sql
Executing the above will cause the full backup to be replayed. However, only those databases and tables for which privileges the restorative_user has privileges will be restored. The --force option causes the backup to continue to be restored, even after an error. In this case, authorization errors are expected, as the backup statements get filtered by the privileges granted to the user.
The SELECT privilege on all databases is critical, as otherwise the USE statement which changes databases in a mysqldump-generated backup of all databases will fail, but successive DROP and CREATE statements could succeed. This could result in tables from one database being restored to another. Or worse, if the table names from the two databases are identical, one of the tables could be overwritten with the table definition and data from the other.