On restoring a single table from mysqldump

ゝ一世哀愁。 2022-08-11 00:41 24阅读 0赞

December 1, 2009

Following [Restore one table from an ALL database dump][] and [Restore a Single Table From mysqldump][], I would like to add my own thoughts and comments on the subject.

I also wish to note performance issues with the two suggested solutions, and offer improvements.

#### Problem relevance ####

While the problem is interesting, I just want to note that it is relevant in very specific database dimensions. Too small - and it doesn't matter how you solve it (e.g. just open vi/emacs and copy+paste). Too big - and it would not be worthwhile to restore from *mysqldump* anyway. I would suggest that the problem is interesting in the whereabouts of a few dozen GB worth of data.

#### Problem recap ####

Given a dump file (generated by mysqldump), how do you restore a single table, without making any changes to other tables?

Let's review the two referenced solutions. I'll be using the [employees db][] on [mysql-sandbox][] for testing. I'll choose a very small table to restore:**departments** (only a few rows in this table).

#### Security based solution ####

[**Chris**][Restore one table from an ALL database dump] offers to create a special purpose account, which will only have write (CREATE, INSERT, etc.) privileges on the particular table to restore. Cool hack! But, I'm afraid, not too efficient, for two reasons:

1.  MySQL needs to process all irrelevant queries (ALTER, INSERT, ...) only to disallow them due to access violation errors.
2.  Assuming restore is from remote host, we overload the network with all said irrelevant queries.

Just how inefficient? Let's time it:

>     mysql> grant usage on *.* to 'restoreuser'@'localhost';
>     mysql> grant select on *.* to 'restoreuser'@'localhost';
>     mysql> grant all on employees.departments to 'restoreuser'@'localhost';
>     
>     $ time mysql --user=restoreuser --socket=/tmp/mysql_sandbox21701.sock --force employees < /tmp/employees.sql
>     ...
>     ERROR 1142 (42000) at line 343: INSERT command denied to user 'restoreuser'@'localhost' for table 'titles'
>     ERROR 1142 (42000) at line 344: ALTER command denied to user 'restoreuser'@'localhost' for table 'titles'
>     ...
>     (lot's of these messages)
>     ...
>     
>     real    0m31.945s
>     user    0m6.328s
>     sys     0m0.508s

So, at about **30** seconds to restore a 9 rows table.

#### Text filtering based solution. ####

[**gtowey**][Restore a Single Table From mysqldump] offers parsing the dump file beforehand:

*  First, parse with *grep*, to detect rows where tables are referenced within dump file
 *  Second, parse with *sed*, extracting relevant rows.

Let's time this one:

>     $ time grep -n 'Table structure' /tmp/employees.sql
>     23:-- Table structure for table `departments`
>     48:-- Table structure for table `dept_emp`
>     89:-- Table structure for table `dept_manager`
>     117:-- Table structure for table `employees`
>     161:-- Table structure for table `salaries`
>     301:-- Table structure for table `titles`
>     
>     real    0m0.397s
>     user    0m0.232s
>     sys     0m0.164s
>     
>     $ time sed -n 23,48p /tmp/employees.sql | ./use employees
>     
>     real    0m0.562s
>     user    0m0.380s
>     sys     0m0.176s

Much faster: about **1** second, compared to **30** seconds from above.

Nevertheless, I find two issues here:

1.  A correctness problem: this solution somewhat assumes that there's only a single table with desired name. I say "somewhat" since it leaves this for the user.
2.  An efficiency problem: it reads the dump file *twice*. First parsing it with *grep*, then with *sed*.

#### A third solution ####

*sed* is much stronger than presented. In fact, the inquiry made by *grep* in gtowey's solution can be easily handled by *sed*:

>     $ time sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p" /tmp/employees.sql | ./use employees
>     
>     real    0m0.573s
>     user    0m0.416s
>     sys     0m0.152s

So, the **"/^-- Table structure for table \\\`departments\\\`/,/^-- Table structure for table/p"** part tells *sed* to only print those rows starting from the **departments** table structure, and ending in the next table structure (this is for clarity: had department been the last table, there would not be a next table, but we could nevertheless solve this using other anchors).

And, we only do it in **0.57** seconds: about half the time of previous attempt.

Now, just to be more correct, we only wish to consider the **employees.department** table. So, *assuming* there's more than one database dumped (and, by consequence, **USE** statements in the dump-file), we use:

>     cat /tmp/employees.sql | sed -n "/^USE \`employees\`/,/^USE \`/p" | sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p" | ./use employees

#### Further notes ####

*  All tests used warmed-up caches.
 *  The sharp eyed readers would notice that **departments** is the first table in the dump file. Would that give an unfair advantage to the parsing-based restore methods? The answer is no. I've created an **xdepartments** table, to be located at the end of the dump. The difference in time is neglectful and inconclusive; we're still at ~0.58-0.59 seconds. The effect will be more visible on really large dumps; but then, so would the security-based effects.

\[**UPDATE**: see also following similar post: [Extract a Single Table from a mysqldump File][]\]

#### Conclusion ####

[![classic-shell-scripting][]][classic-shell-scripting 1]Its is always best to test on large datasets, to get a feel on performance.

It's best to save MySQL the trouble of parsing & ignoring statements. Scripting utilities like *sed*, *awk* & *grep* have been around for ages, and are well optimized. They excel at text processing.

I've used *sed* many times in transforming dump outputs; for example, in converting MyISAM to InnoDB tables; to convert Antelope InnoDB tables to Barracuda format, etc. grep & awk are also very useful.

May I recommend, at this point, reading [Classic Shell Scripting][classic-shell-scripting 1], a very easy to follow book, which lists the most popular command line utilities like *grep*, *sed*, *awk*, *sort*, (countless more) and shell scripting in general. While most of these utilities are well known, the book excels in providing suprisingly practical, simple solution to common tasks.

*  [Facebook][]
 *  [Twitter][]
 *  [LinkedIn][]
 *  [Google][]
 *  [Email][]
 *  [Print][]
 *  [More][]
 *

tags: [Backup][], [Books][], [mysqldump][], [Performance][], [scripts][]  
posted in [MySQL][] by shlomi

« [questions or queries?][questions or queries] | [Useful temporal functions & queries][Useful temporal functions _ queries] »

Vote on  [Planet MySQL][] [![thumbs_up_blue.jpg][]][thumbs_up_blue.jpg 1]

Follow comments via the [RSS Feed][] | [Leave a comment][] | [Trackback URL][]

December 1, 2009

Following [Restore one table from an ALL database dump][] and [Restore a Single Table From mysqldump][], I would like to add my own thoughts and comments on the subject.

I also wish to note performance issues with the two suggested solutions, and offer improvements.

#### Problem relevance ####

#### Problem recap ####

Given a dump file (generated by mysqldump), how do you restore a single table, without making any changes to other tables?

#### Security based solution ####

Just how inefficient? Let's time it:

So, at about **30** seconds to restore a 9 rows table.

#### Text filtering based solution. ####

[**gtowey**][Restore a Single Table From mysqldump] offers parsing the dump file beforehand:

*  First, parse with *grep*, to detect rows where tables are referenced within dump file
 *  Second, parse with *sed*, extracting relevant rows.

Let's time this one:

Much faster: about **1** second, compared to **30** seconds from above.

Nevertheless, I find two issues here:

#### A third solution ####

*sed* is much stronger than presented. In fact, the inquiry made by *grep* in gtowey's solution can be easily handled by *sed*:

And, we only do it in **0.57** seconds: about half the time of previous attempt.

>     cat /tmp/employees.sql | sed -n "/^USE \`employees\`/,/^USE \`/p" | sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p" | ./use employees

#### Further notes ####

\[**UPDATE**: see also following similar post: [Extract a Single Table from a mysqldump File][]\]

#### Conclusion ####

[![classic-shell-scripting][]][classic-shell-scripting 1]Its is always best to test on large datasets, to get a feel on performance.

*  [Facebook][]
 *  [Twitter][]
 *  [LinkedIn][]
 *  [Google][]
 *  [Email][]
 *  [Print][]
 *  [More][]
 *

tags: [Backup][], [Books][], [mysqldump][], [Performance][], [scripts][]  
posted in [MySQL][] by shlomi

« [questions or queries?][questions or queries] | [Useful temporal functions & queries][Useful temporal functions _ queries] »

Vote on  [Planet MySQL][] [![thumbs_up_blue.jpg][]][thumbs_up_blue.jpg 1]

Follow comments via the [RSS Feed][] | [Leave a comment][] | [Trackback URL][]

备注：该篇技术文档是与MySQL数据库中的“备份恢复”主题相关的技术主题。  
  
  
本文转载自：http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump

December 1, 2009

Following [Restore one table from an ALL database dump][] and [Restore a Single Table From mysqldump][], I would like to add my own thoughts and comments on the subject.

I also wish to note performance issues with the two suggested solutions, and offer improvements.

#### Problem relevance ####

#### Problem recap ####

Given a dump file (generated by mysqldump), how do you restore a single table, without making any changes to other tables?

#### Security based solution ####

Just how inefficient? Let's time it:

So, at about **30** seconds to restore a 9 rows table.

#### Text filtering based solution. ####

[**gtowey**][Restore a Single Table From mysqldump] offers parsing the dump file beforehand:

*  First, parse with *grep*, to detect rows where tables are referenced within dump file
 *  Second, parse with *sed*, extracting relevant rows.

Let's time this one:

Much faster: about **1** second, compared to **30** seconds from above.

Nevertheless, I find two issues here:

#### A third solution ####

*sed* is much stronger than presented. In fact, the inquiry made by *grep* in gtowey's solution can be easily handled by *sed*:

And, we only do it in **0.57** seconds: about half the time of previous attempt.

>     cat /tmp/employees.sql | sed -n "/^USE \`employees\`/,/^USE \`/p" | sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p" | ./use employees

#### Further notes ####

\[**UPDATE**: see also following similar post: [Extract a Single Table from a mysqldump File][]\]

#### Conclusion ####

[![classic-shell-scripting][]][classic-shell-scripting 1]Its is always best to test on large datasets, to get a feel on performance.

*  [Facebook][]
 *  [Twitter][]
 *  [LinkedIn][]
 *  [Google][]
 *  [Email][]
 *  [Print][]
 *  [More][]
 *

tags: [Backup][], [Books][], [mysqldump][], [Performance][], [scripts][]  
posted in [MySQL][] by shlomi

« [questions or queries?][questions or queries] | [Useful temporal functions & queries][Useful temporal functions _ queries] »

Vote on  [Planet MySQL][] [![thumbs_up_blue.jpg][]][thumbs_up_blue.jpg 1]

Follow comments via the [RSS Feed][] | [Leave a comment][] | [Trackback URL][]

[Restore one table from an ALL database dump]: http://everythingmysql.ning.com/profiles/blogs/restore-one-table-from-an-all
[Restore a Single Table From mysqldump]: http://gtowey.blogspot.com/2009/11/restore-single-table-from-mysqldump.html
[employees db]: http://dev.mysql.com/doc/employee/en/employee.html
[mysql-sandbox]: https://launchpad.net/mysql-sandbox
[Extract a Single Table from a mysqldump File]: http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html
[classic-shell-scripting]: /images/20220810/a236737a68454218be98784d0a025a7f.png
[classic-shell-scripting 1]: http://www.amazon.com/Classic-Shell-Scripting-Arnold-Robbins/dp/0596005954/ref=sr_1_1
[Facebook]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump?share=facebook&nb=1
[Twitter]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump?share=twitter&nb=1
[LinkedIn]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump?share=linkedin&nb=1
[Google]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump?share=google-plus-1&nb=1
[Email]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump?share=email&nb=1
[Print]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump#print
[More]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump#
[Backup]: http://code.openark.org/blog/tag/backup
[Books]: http://code.openark.org/blog/tag/books
[mysqldump]: http://code.openark.org/blog/tag/mysqldump
[Performance]: http://code.openark.org/blog/tag/performance
[scripts]: http://code.openark.org/blog/tag/scripts
[MySQL]: http://code.openark.org/blog/category/mysql
[questions or queries]: http://code.openark.org/blog/mysql/questions-or-queries
[Useful temporal functions _ queries]: http://code.openark.org/blog/mysql/useful-temporal-functions-queries
[Planet MySQL]: http://planet.mysql.com/
[thumbs_up_blue.jpg]: /images/20220810/eb8c81ed95374ee2aeea175727d3760b.png
[thumbs_up_blue.jpg 1]: http://planet.mysql.com/entry/vote/?apivote=1&vote=1&url=http%3A%2F%2Fcode.openark.org%2Fblog%2Fmysql%2Fon-restoring-a-single-table-from-mysqldump
[RSS Feed]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump/feed
[Leave a comment]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump#respond
[Trackback URL]: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump/trackback