{"id":2467,"date":"2018-06-29T15:43:55","date_gmt":"2018-06-29T15:43:55","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2467"},"modified":"2023-04-22T09:50:57","modified_gmt":"2023-04-22T09:50:57","slug":"restore-a-table-database-from-full-backup-yet-another-way","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/restore-a-table-database-from-full-backup-yet-another-way","title":{"rendered":"Restore A Table \/ Database From Full Backup \u2013 Yet Another Way"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.<\/p>\n\n\n\n

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

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.<\/p>\n\n\n\n

So the trick here is as follows:<\/strong><\/p>\n\n\n\n

Restore single table from full mysqldump<\/strong><\/h2>\n\n\n\n\n\n\n\n


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

Easy right? Database Consultants like to KISS ;).<\/p>\n\n\n\n

Let\u2019s give it a try.<\/p>\n\n\n\n

I selected a table \u201cstories\u201d & create the \u201cbad\u201d situation by dropping that table.<\/p>\n\n\n\n

mysql> use test\nReading table information for completion of table and column names\nYou can turn off this feature to get a quicker startup with -A\n\nDatabase changed\nmysql> show tables;\n+------------------------+\n| Tables_in_test     \t|\n+------------------------+\n...\n| stories            \t|\n...\n+------------------------+\n\nmysql> select count(*) from stories;\n+----------+\n| count(*) |\n+----------+\n|  \t881 |\n+----------+\n1 row in set (0.02 sec)\n\nmysql> drop table stories;\nQuery OK, 0 rows affected (0.29 sec)\n<\/pre>\n\n\n\n
Let\u2019s begin the recovery phase now following the grants method.<\/p>\n\n\n\n
1. Create the user with limited grants only on test.stories table.<\/p>\n\n\n\n
mysql> grant all privileges on test.stories to 'stories'@localhost identified by 'X';\nQuery OK, 0 rows affected, 1 warning (0.03 sec)\n\nmysql> show warnings;\n+---------+------+------------------------------------------------------------------------------------------------------------------------------------+\n| Level   | Code | Message                                                                                                                            |\n+---------+------+------------------------------------------------------------------------------------------------------------------------------------+\n| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |\n+---------+------+------------------------------------------------------------------------------------------------------------------------------------+\n1 row in set (0.00 sec)\n<\/pre>\n\n\n\n
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 \ud83d\ude42<\/p>\n\n\n\n
CREATE USER 'stories'@\u2019localhost\u2019 identified with mysql_native_password by \u2018X';\ngrant all privileges on test.stories to 'stories'@'localhost';\ngrant select on *.* to 'stories'@'localhost';<\/pre>\n\n\n\n
2. Load the mysqldump using the same user with \u2013force.<\/p>\n\n\n\n
[root@mysql1c ~]# cat fuldump.sql | mysql -ustories -pX test --force\nmysql: [Warning] Using a password on the command line interface can be insecure.\nERROR 1044 (42000) at line 22: Access denied for user 'stories'@'localhost' to database 'archive'\n...\nERROR 1142 (42000) at line 420: ALTER command denied to user 'stories'@'localhost' for table 'emp_new'\n...\nERROR 1142 (42000) at line 1966: ALTER command denied to user 'stories'@'localhost' for table 'user_address'\n<\/pre>\n\n\n\n
3. Verify table is restored:<\/p>\n\n\n\n
mysql> show tables;\n+------------------------+\n| Tables_in_test     \t|\n+------------------------+\n...\n| stories            \t|\n...\n+------------------------+\n\nmysql> select count(*) from stories;\n+----------+\n| count(*) |\n+----------+\n|  \t881 |\n+----------+\n1 row in set (0.00 sec)\n<\/pre>\n\n\n\n
Conclusion:<\/strong>
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 \u2013force 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.<\/p>\n\n\n\n
Hope this helps.<\/p>\n\n\n\n
PS: This article was originally published on The Pythian Blog here.<\/a><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"How to Restore A Table \/ Database From Full Backup using MySQL Grants & mysqldump.\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377,6],"tags":[459,427,403,402,76,366,550],"class_list":{"0":"post-2467","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"category-technical","9":"tag-backup-and-restore","10":"tag-mysql","11":"tag-mysql-recovery","12":"tag-mysql-restore","13":"tag-mysqldump","14":"tag-restore","15":"tag-restore-single-table-from-mysql-dump"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2467"}],"version-history":[{"count":3,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2467\/revisions"}],"predecessor-version":[{"id":2813,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2467\/revisions\/2813"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}