Restore A Table / Database From Full Backup – Yet Another Way

Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.
In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario.

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.

As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain.

We had to come-up with a solution that works on Windows as well. During this discussion, my Pythian colleague, Romuller, suggested a cool but simple trick which enlightens us and offers one more way of exporting or recovering a table from a full mysqldump.

So the trick here is as follows:

– Create a user that has very specific grants, limited to one or more table(s) or database(s) that we need to restore.
– Add SELECT ON *.* lets us use other databases though we will not be able to load the tables. (Source: comment from Pythian blog)
– Load mysqldump into the database with that user provide with –force. The option –force will ignore all the errors that will occur due to lack of privileges of the new user we created specifically for restore.

Easy right? Database Consultants like to KISS ;).

Let’s give it a try.

I selected a table “stories” & create the “bad” situation by dropping that table.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------+
| Tables_in_test      |
+------------------------+
...
| stories             |
...
+------------------------+

mysql> select count(*) from stories;
+----------+
| count(*) |
+----------+
|   881 |
+----------+
1 row in set (0.02 sec)

mysql> drop table stories;
Query OK, 0 rows affected (0.29 sec)

Let’s begin the recovery phase now following the grants method.

1. Create the user with limited grants only on test.stories table.

mysql> grant all privileges on test.stories to 'stories'@localhost identified by 'X';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Wait, there is a warning. We see this warning in MySQL 5.7.6 onward and it says GRANT commands will be deprecated in favour of CREATE USER statement to create new users. So, we shall have following practice to be ready for MySQL 8 

CREATE USER 'stories'@’localhost’ identified with mysql_native_password by ‘X';
grant all privileges on test.stories to 'stories'@'localhost';
grant select on *.* to 'stories'@'localhost';

2. Load the mysqldump using the same user with –force.

[root@mysql1c ~]# cat fuldump.sql | mysql -ustories -pX test --force
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000) at line 22: Access denied for user 'stories'@'localhost' to database 'archive'
...
ERROR 1142 (42000) at line 420: ALTER command denied to user 'stories'@'localhost' for table 'emp_new'
...
ERROR 1142 (42000) at line 1966: ALTER command denied to user 'stories'@'localhost' for table 'user_address'

3. Verify table is restored:

mysql> show tables;
+------------------------+
| Tables_in_test      |
+------------------------+
...
| stories             |
...
+------------------------+

mysql> select count(*) from stories;
+----------+
| count(*) |
+----------+
|   881 |
+----------+
1 row in set (0.00 sec)

Conclusion:
When you compare the table that is being restored to the other one, mysqldump is smaller. This method may take a lot of time just ignoring errors due to –force option. Of course, in most cases you will end up reading the whole file. If our table appears early in the mysqldump, we may monitor the progress and kill the process as well. Otherwise, it may make more sense to try and install Cygwin or move the backup to a Linux Box to extract a database object from the backup file.