A lot of the time I have found that the only available backup taken from mysqldump contains all databases. This is ok but can be a head ache if you only need to restore one or some of the tables in one database. Below is a good way to restore only that which needs to be restored. I have created three tables in the test database, truncated the third table and now need to restore just that table.

-- Example:

mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tbl1 |
| tbl2 |
| tbl3 |
+----------------+
3 rows in set (0.00 sec)

mysql> select min(anum), max(anum) from test.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 0 | 10000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test.tbl2;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 20001 | 30000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test.tbl3;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 30001 | 40000 |
+-----------+-----------+
1 row in set (0.01 sec)

shell> mysqldump -uroot -S mysql.sock --opt --skip-lock-tables --all-databases > all_dbs.sql

mysql> truncate table tbl3;
Query OK, 0 rows affected (0.05 sec)

mysql> select min(anum), max(anum) from test.tbl3;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| NULL | NULL |
+-----------+-----------+
1 row in set (0.00 sec)

The grants below can be mixed to suit your needs; examples are one table, multiple tables, one table in one database and one database. For simplicity I am only interested in restoring the test.tbl3 table.

The SELECT privilege for all databases is very important to note in this step. If you did not have SELECT on all databases the USE statement generated by mysqldump will fail; furthermore, successive DROP and CREATE statements will execute correctly. This will result in tables from one database being restored to another. If you have identical table names in different databases on the same server you could overwrite with the wrong data.

mysql> grant usage on *.* to 'restoreuser'@'localhost';

mysql> grant select on *.* to 'restoreuser'@'localhost';

mysql> grant all on `test`.`tbl3` to 'restoreuser'@'localhost';


mysql> show grants for 'restoreuser'@'localhost';
+--------------------------------------------------------------------+
| Grants for restoreuser@localhost |
+--------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'restoreuser'@'localhost' |
| GRANT ALL PRIVILEGES ON `test`.`tbl3` TO 'restoreuser'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

shell> mysql -urestoreuser -S mysql.sock --force < all_dbs.sql

Keep in mind that the --force is very important here. If you did not include it the import would fail on the first insert given that this users does not have access to *.*.

mysql> select min(anum), max(anum) from test.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 0 | 10000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test.tbl2;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 20001 | 30000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test.tbl3;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 30001 | 40000 |
+-----------+-----------+
1 row in set (0.01 sec)