Restore a table / database from full backup – Yet another way

Posted in: MySQL, Technical Track

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.
– 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';

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.

Hope this helps.

email

Interested in working with Kedar? Schedule a tech call.

About the Author

MySQL Database Consultant
Kedar is a MySQL Consultant at Pythian since Oct 2012. He's experienced in MySQL related technologies and constantly working on improving skills. Kedar also enjoys sharing on his blog.

4 Comments. Leave new

You could use a tool like MobaXterm which provides a cygwin env but its less invasive and comes as a portable install if you need it.

Reply

This user should also have the privilege SELECT ON *.*
Otherwise it’s possible to get the wrong table restored.

USE test; — ok, default database now `test`
DROP TABLE stories; CREATE TABLE stories…– ok
USE trial; — fails; default database still `test`
DROP TABLE stories; CREATE TABLE stories… — ok, restored into the default `test` database. Wrong result!

By adding SELECT ON *.*, the “USE trial;” succeeds, and the table which follows is now properly ignored.

Reply
Kedar Vaijanapurkar
May 2, 2018 11:27 am

Hi Scott,

Thanks for your comment and I completely agree with you. I’d look to update the post to reflect the change of grants.

Thanks.

Reply

Hi Scott & Kedar,

Thanks for the scripts.
It was very helpful.

Dov.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *