Can I restore a single table from a full mysql mysqldump file?

Question

I have a mysqldump backup of my mysql database consisting of all of our tables which is about 440 megs. I want to restore the contents of just one of the tables from the mysqldump. Is this possible? Theoretically, I could just cut out the section that rebuilds the table I want but I don't even know how to effectively edit a text document that size.

FWIW, you could also use [mydumper](https://launchpad.net/mydumper). This creates a logical dump like mysqldump, but it outputs separate files per table, and it can do both the dumping and the loading multi-threaded, so it takes less time. — Bill Karwin, Oct 31 '14 at 21:49
We have https://github.com/kedarvj/mysqldumpsplitter to extract objects you want from the dump. — mysql_user, Apr 04 '23 at 12:16

score 346 · Accepted Answer · edited Aug 29 '20 at 21:13

346

You can try to use sed in order to extract only the table you want.

Let say the name of your table is mytable and the file mysql.dump is the file containing your huge dump:

$ sed -n -e '/CREATE TABLE.*`mytable`/,/Table structure for table/p' mysql.dump > mytable.dump

This will copy in the file mytable.dump what is located between CREATE TABLE mytable and the next CREATE TABLE corresponding to the next table.

You can then adjust the file mytable.dump which contains the structure of the table mytable, and the data (a list of INSERT).

edited Aug 29 '20 at 21:13

Mohammad Naji

5,372
10
54
79

answered Jun 18 '09 at 17:10

uloBasEI

4,167
2
19
13

6

That's even nicer than my answer since it takes care of the CREATE TABLE too, but you should search with the backquotes so as not to get another table called "thisismytabletoo". – JCCyC Jun 18 '09 at 17:14
1

True. I was not sure if the table names in all mysql dumps are always surrounded by backquote or if "CREATE TABLE mytable" could also be possible. We can easily adapt the first regexp if we know how the dump looks like. A second problem could be if the table mytable is not unique (one in the database db1, and another one in the database db2). Both will be exported in the file mytable.dump. If the table is not unique, we can use the same sed command, first with CREATE DATABASE in order to extract only the right database. Then, use the sed command with CREATE TABLE. – uloBasEI Jun 18 '09 at 17:47
Yep, JCCyC. This thing did exactly what I needed with zero monkeybusiness. Thank you and uloBasEl so much! – Mobius Jun 18 '09 at 20:14
15

Sweet! Remember to add DROP TABLE at the top and remove the DROP TABLE [next table] at the bottom of the file. – Nathan May 21 '10 at 17:55
22

For not adding/removing DROP TABLE, it is more useful to match by `Table structure for table` comment, instead of matching by CREATE TABLE. This will ensure that next table would not be dropped if one forgets to remove its DROP TABLE statement, and allows piping for single-command table restore (gzip | sed | mysql). However, this solution is dependent on mysqldump comment syntax (I don't know how standard it is). – Olexa Feb 26 '13 at 09:55
14

With Olexa's modification it's perfect: sed -n -e '/Table structure for.*`mytable/,/Table structure for/p' whole.sql > mytable.sql – deeenes May 26 '13 at 22:28
Brilliant. Really quite nice. – kmarks2 Aug 23 '13 at 19:49
1

This answer is better, it includes the DROP TABLE => http://stackoverflow.com/a/15857815/292408 – Elijah Lynn Oct 31 '14 at 19:38
Remember to add a closing backtick ` after the tablename, otherwise all tables with the same prefix will be matched. – jotrocken Mar 02 '16 at 12:48
It works fine also for postgres `$ sed -n -e '/COPY.*mytable/,/COPY/p' pg.dump > mytable.dump` - Thanks! – gunzapper Mar 30 '16 at 13:09
Anone know how i can swap mytable for a variable in bash? ``` export VAR=my_table sed -n -e '/CREATE TABLE.*`$VAR`/,/Table structure for table/p' backup ``` – Kay Jan 13 '21 at 15:44
Thank you! Worked for a .sql file too. I just had to edit 1 line to make the import work - /*!40101 SET character_set_client = 'utf8' */; instead of /*!40101 SET character_set_client = @saved_cs_client */; (See https://stackoverflow.com/questions/29112716/mysql-error-1231-42000variable-character-set-client-cant-be-set-to-the-val) – Hammurabi Jun 13 '22 at 00:43

score 156 · Answer 2 · edited Aug 10 '16 at 09:55

156

I used a modified version of uloBasEI's sed command. It includes the preceding DROP command, and reads until mysql is done dumping data to your table (UNLOCK). Worked for me (re)importing wp_users to a bunch of Wordpress sites.

sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' mydump.sql > tabledump.sql

edited Aug 10 '16 at 09:55

sjas

18,644
14
87
92

answered Apr 07 '13 at 01:11

bryn

3,155
1
16
15

5

this is actually a much better solution than the one chosen as the answer, above. Can't believe it didnt' get more upvotes. – rICh Jun 21 '13 at 23:40
10

I suggest adding at least backtick quotes to the table name: `\`mytable\`` to avoid matching tables `mytable2` and so on as was in my case. – Feb 19 '14 at 10:16
Maxim, it didn't work because there was no source file in the example nor stdout and destination file. I edited the example to include these (mydump.sql > tabledump.sql) and it should work now. – Elijah Lynn Oct 31 '14 at 19:37
for me sed give wrong file format, must be utf8 but is ansi. text is damaged. – user706420 Nov 25 '14 at 10:20
4

you just saved my ass – Gabriel Alack Dec 16 '14 at 18:34
1

@user706420 Hmm, are you certain your terminal wasn't to blame? `sed` shouldn't interfere with encoding... – bryn Dec 17 '14 at 15:39
@rICh this answer is a bit more difficult to understand and it requires good sql dump strucutre. Also, if you want add DROP and other additions, you can derive this from uloBasEl's answer easily. – d.sergeiev Dec 23 '14 at 16:06
8

For those with SQL files not containing the `DROP TABLE` (my MySQL dump did not have this) may want to use:`sed -n -e '/-- Table structure for table ``/,/UNLOCK TABLES/p' > table.sql` This makes use of the table comments provided before each table in a MySQl dump, and ensures lines like `/*!40101 SET @saved_cs_client = @@character_set_client */;` get included.
– hamx0r Feb 16 '16 at 19:31
1

Thanks @WestonGanger. t I believe you are correct. The command should be ```sed -n -e '/-- Table structure for table ``/,/UNLOCK TABLES/p' > table.sql```
– hamx0r Jul 14 '16 at 00:12
To enchance this ever more: For large dump files, you can tell sed to stop once the match from/to has been found by adding the `q` command like this: ```sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/{p;/UNLOCK TABLES/q}' mydump.sql > tabledump.sql``` – Lifeboy Jul 15 '23 at 21:31

score 62 · Answer 3 · edited Aug 09 '22 at 10:52

62

This can be done more easily? This is how I did it:

Create a temporary database (e.g. restore):

mysqladmin -u root -p create restore

Restore the full dump in the temp database:

mysql -u root -p --one-database restore < fulldump.sql

Dump the table you want to recover:

mysqldump restore mytable > mytable.sql

Import the table in another database:

mysql -u root -p database < mytable.sql

edited Aug 09 '22 at 10:52

Grodriguez

21,501
10
63
107

answered Apr 25 '13 at 12:33

Martijn Kools

661
5
2

4

This is what most people need. – sjas Apr 23 '15 at 08:08
6

According to [this edit suggestion](http://stackoverflow.com/review/suggested-edits/9403385), you should add the "--one-database" parameter, to make sure you restore only **one** database and don't destroy everything else. – S.L. Barth is on codidact.com Sep 05 '15 at 08:33
19

For really huge db it is can very weird - restore 150 GB of data for one table of 100 MB. – Enyby Jul 07 '16 at 21:02
I just ran this without "--one-database" and it still merged tables just fine. It didn't remove my existing tables. – Qasim Jul 26 '16 at 07:05
3

Very bad practice! I had few DBs in dump, and trying to restore only one, deleted all others. – Andrey Dec 01 '16 at 20:01
can be done from shell entirely, very easy. no need for editing files or sql knowledge. – May 15 '17 at 09:52
@AndreyP I'm using mariadb it worked fine for me, sql file originating from table dump didn't have any drop statement for any other table or db itself. Which version of mysql were you using? – May 15 '17 at 09:56
@S.L.Barth I'm wondering if this happens only when there are a multiple db dumps in single file – May 15 '17 at 09:58
This restored a database with a default collocation of latin something, while my original db was UTF-8, I hope I don't run into issues – Freedo Aug 06 '19 at 07:15
The table name could be case sensitive. Check the name of the table in the database by running `use restore;` and `show tables;` in the MySQL Command-Line Client. – Hari Dec 29 '20 at 16:56
This is very dangerous if the backup contains multiple databases or if it contains a full dump for one database with a different name than the one used for the temporary database. The --one-database option should be used always to avoid problems; adding that to the answer. – Grodriguez Aug 09 '22 at 10:49
This is not 'more easily' at all. It requires 3 major steps which are significant if you have a database that is anything but a few records. Using the sed method above is a clever and neat way to achieve the result, unless of course someone is too lazy to figure out how it works? – Lifeboy Jul 15 '23 at 21:38

score 13 · Answer 4 · answered Aug 08 '13 at 09:34

A simple solution would be to simply create a dump of just the table you wish to restore separately. You can use the mysqldump command to do so with the following syntax:

mysqldump -u [user] -p[password] [database] [table] > [output_file_name].sql

Then import it as normal, and it will only import the dumped table.

score 9 · Answer 5 · answered Jun 18 '09 at 16:57

9

One way or another, any process doing that will have to go through the entire text of the dump and parse it in some way. I'd just grep for

INSERT INTO `the_table_i_want`

and pipe the output into mysql. Take a look at the first table in the dump before, to make sure you're getting the INSERT's the right way.

Edit: OK, got the formatting right this time.

answered Jun 18 '09 at 16:57

JCCyC

16,140
11
48
75

I'm dumb for not immediately thinking of grepping it. Grep saves my bacon every day, it seems. The other two suggestions were also bang-up ones and totally what I would have done without the wonders of grep available. Thank you all! – Mobius Jun 18 '09 at 17:06
6

If you think you love grep, when you learn awk you'll become its happy sex slave: http://en.wikipedia.org/wiki/Awk – JCCyC Jun 18 '09 at 17:10

score 9 · Answer 6 · edited Mar 25 '23 at 16:22

You should try bryn's answer, but with the ` delimiter, otherwise you will also extract the tables having a prefix or a suffix, this is what I usually do:

sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' dump.sql > mytable.sql

Also for testing purposes, you may want to change the table name before importing:

sed -n -e 's/`mytable`/`mytable_restored`/g' mytable.sql > mytable_restored.sql

To import you can then use the MySQL command:

mysql -u root -p'password' mydatabase < mytable_restore.sql

score 9 · Answer 7 · edited Feb 23 '15 at 11:10

9

Backup
```
$ mysqldump -A | gzip > mysqldump-A.gz
```

Restore single table

$ mysql -e "truncate TABLE_NAME" DB_NAME
$ zgrep ^"INSERT INTO \`TABLE_NAME" mysqldump-A.gz | mysql DB_NAME

edited Feb 23 '15 at 11:10

vicvicvic

6,025
4
38
55

answered Feb 23 '15 at 08:24

y.tk

91
1
1

score 4 · Answer 8 · answered Jun 18 '09 at 16:57

4

One possible way to deal with this is to restore to a temporary database, and dump just that table from the temporary database. Then use the new script.

answered Jun 18 '09 at 16:57

Tim Hoolihan

12,316
3
41
54

score 4 · Answer 9 · answered Jul 12 '16 at 16:59

sed -n -e '/-- Table structure for table `my_table_name`/,/UNLOCK TABLES/p' database_file.sql > table_file.sql

This is a better solution than some of the others above because not all SQL dumps contain a DROP TABLE statement. This one will work will all kinds of dumps.

score 3 · Answer 10 · edited Sep 21 '18 at 10:11

3

Table should present with same structure in both dump and database.

`zgrep -a ^"INSERT INTO \`table_name" DbDump-backup.sql.tar.gz | mysql -u<user> -p<password> database_name`

or

`zgrep -a ^"INSERT INTO \`table_name" DbDump-backup.sql | mysql -u<user> -p<password> database_name`

edited Sep 21 '18 at 10:11

Madhur Bhaiya

28,155
10
49
57

answered Sep 21 '18 at 09:46

Neminath

95
2
12

score 3 · Answer 11 · answered Dec 14 '11 at 12:41

3

This tool may be is what you want: tbdba-restore-mysqldump.pl

https://github.com/orczhou/dba-tool/blob/master/tbdba-restore-mysqldump.pl

e.g. Restore a table from database dump file:

tbdba-restore-mysqldump.pl -t yourtable -s yourdb -f backup.sql

answered Dec 14 '11 at 12:41

user1097790

41
1

score 2 · Answer 12 · answered Jan 02 '14 at 14:45

This may help too.

# mysqldump -u root -p database0 > /tmp/database0.sql
# mysql -u root -p -e 'create database database0_bkp'
# mysql -u root -p database0_bkp < /tmp/database0.sql
# mysql -u root -p database0 -e 'insert into database0.table_you_want select * from database0_bkp.table_you_want'

score 1 · Answer 13 · answered Jun 18 '09 at 16:58

Most modern text editors should be able to handle a text file that size, if your system is up to it.

Anyway, I had to do that once very quickly and i didnt have time to find any tools. I set up a new MySQL instance, imported the whole backup and then spit out just the table I wanted.

Then I imported that table into the main database.

It was tedious but rather easy. Good luck.

score 1 · Answer 14 · edited Jan 17 '17 at 12:24

The 'sed' solutions mentioned earlier are nice but as mentioned not 100% secure

You may have INSERT commands with data containing: ... CREATE TABLE...(whatever)...mytable...
or even the exact string "CREATE TABLE `mytable`;" if you are storing DML commands for instance!

(and if the table is huge you don't want to check that manually)

I would verify the exact syntax of the dump version used, and have a more restrictive pattern search:

Avoid ".*" and use "^" to ensure we start at the begining of the line. And I'd prefer to grab the initial 'DROP'

All in all, this works better for me:

sed -n -e '/^DROP TABLE IF EXISTS \`mytable\`;/,/^UNLOCK TABLES;/p' mysql.dump > mytable.dump

score 1 · Answer 15 · answered Feb 02 '15 at 07:28

You can use vi editor. Type:

vi -o mysql.dump mytable.dump

to open both whole dump mysql.dump and a new file mytable.dump. Find the appropriate insert into line by pressing / and then type a phrase, for example: "insert into `mytable`", then copy that line using yy. Switch to next file by ctrl+w then down arrow key, paste the copied line with pp. Finally save the new file by typing :wq and quite vi editor by :q.

Note that if you have dumped the data using multiple inserts you can copy (yank) all of them at once using Nyy in which N is the number of lines to be copied.

I have done it with a file of 920 MB size.

score 1 · Answer 16 · edited May 23 '17 at 12:18

1

I tried a few options, which were incredibly slow. This split a 360GB dump into its tables in a few minutes:

How do I split the output from mysqldump into smaller files?

edited May 23 '17 at 12:18

Community

1
1

answered Apr 22 '16 at 14:55

Kohjah Breese

4,008
6
32
48

score 0 · Answer 17 · answered Jun 18 '09 at 17:04

Get a decent text editor like Notepad++ or Vim (if you're already proficient with it). Search for the table name and you should be able to highlight just the CREATE, ALTER, and INSERT commands for that table. It may be easier to navigate with your keyboard rather than a mouse. And I would make sure you're on a machine with plenty or RAM so that it will not have a problem loading the entire file at once. Once you've highlighted and copied the rows you need, it would be a good idea to back up just the copied part into it's own backup file and then import it into MySQL.

This is actually really easy to do and understandable even by beginners. I do not know, why this is downvoted. — Karl Johan Vallner, Oct 04 '17 at 11:58

score 0 · Answer 18 · answered Jun 18 '09 at 18:24

The chunks of SQL are blocked off with "Table structure for table my_table" and "Dumping data for table my_table."

You can use a Windows command line as follows to get the line numbers for the various sections. Adjust the searched string as needed.

find /n "for table `" sql.txt

The following will be returned:

---------- SQL.TXT

[4384]-- Table structure for table my_table

[4500]-- Dumping data for table my_table

[4514]-- Table structure for table some_other_table

... etc.

That gets you the line numbers you need... now, if I only knew how to use them... investigating.

score -4 · Answer 19 · answered Feb 22 '21 at 09:20

-4

You can import single table using terminal line as given below. Here import single user table into specific database.

mysql -u root -p -D my_database_name < var/www/html/myproject/tbl_user.sql

answered Feb 22 '21 at 09:20

Yagnesh bhalala

1,107
1
15
17

1

that imports everything related to that database from the sql file. This question was about extracting a single table from a full database dump. useful ... but not related to this question – TheSatinKnight Aug 02 '21 at 18:30

score -4 · Answer 20 · answered Jun 19 '21 at 12:01

I admire some of the ingenuity here, but there is literally no reason to use sed at all to address the OP's question.

The comment "use --one-database" is the correct answer, built into MySQL/MariaDB. No need for third-party hacks.

mysql -u root -p databasename --one-database < localhost.sql will just import the desired database.

I also found in some cases, when using this to import a series of databases, it would create the next database in the list for me (but not put anything in it). Not sure why it did that, but it made the restore easier.

With this command, enter the password interactively and it will import the requested database.

Except that the OP asked about restoring a single table from a single database dump, not restoring a single database from an 'all databases' dump? — CodeMantle, Aug 02 '21 at 13:19

Can I restore a single table from a full mysql mysqldump file?

20 Answers20

Linked

Related