1

I have a big mySQL database dump named forum.sql. I want to restore only one table, but when I restore the full database, it takes a long time to import the "post" table.

Is there any option to restore this database skipping the "post" table?

KCD
  • 9,337
  • 4
  • 63
  • 72
user724001
  • 11
  • 1
  • 1
  • 2
  • Possible duplicate of http://stackoverflow.com/questions/1013852/can-i-restore-a-single-table-from-a-full-mysql-mysqldump-file – KCD Apr 02 '12 at 00:35

6 Answers6

4

If you are restoring from a dump file, you can easily build a new dumpfile without this table, just by writting down the line numbers.

Initial line

> grep dumpfile.sql -ne "Dumping data for table \`avoid_tablename\`" -m 1
43:-- Dumping data for table `avoid_tablename`

Total lines

> wc -l dumpfile.sql
63 dumpfile.sql

Make a new file

> head -n 43 dumpfile.sql > dumpfile-lite.sql
> tail -n 20 dumpfile.sql >> dumpfile-lile.sql

20 comes from substracting 63 - 43

not clean, but usefull

David Canós
  • 1,630
  • 17
  • 17
  • This can be done without knowing the total lines in the file. tail -n +43 < input_file > output_file It is helpful when dealing with HUGE files – mikeschuld Dec 17 '14 at 22:21
1

Restore a single table

First, do the restore with zero inserts:

cat dump.sql | grep -v '^INSERT INTO' | mysql -u <user> -p<pw> <dbname>

Using grep -v here will exclude any statements matching the pattern. The pattern in this case uses ^ to match at the beginning of a line. The result should be a restored schema with zero data.

Next, restore only your desired INSERT statements:

cat dump.sql | grep '^INSERT INTO \\\`<table>\\\`' | mysql -u <user> -p<pw> <dbname>

That will restore data only for the table named <table>. Note the triple backslashes. You need a backslash to escape a backtick and then you need to escape the backslash with 2 more backslashes.

Restore everything except one table

Another technique I use all the time when I want to restore an entire database but exclude the data from a table or two is this... You can filter out any unwanted INSERT statements by passing your dump through a filter before pushing into the db. Here's an example using grep as the filter:

nohup sh -c "cat dump.sql | grep -v 'INSERT INTO \\\`<table>\\\`' | mysql -u <user> -p<pw> <dbname>" &

The nohup command will keep the sh command running even if you log out of your shell. That can be pretty handy if you have a large dump file that will take quite some time to restore.

The -v flag for grep will exclude anything matching the pattern.

The & at the end will send the command to the background.

marcguyer
  • 113
  • 9
1

I don't think you can do it. But you can dump tables separately when necessary, using --tables myqsldump option. So, you can generate a dump for post table and another dump for the remaining tables.

Example:

mysqldump -u USERNAME -pPASSWORD --tables TABLE_NAME database_name > TABLE_NAME.sql
Doug
  • 6,227
  • 3
  • 27
  • 47
1

Alternatively, extract the table(s) that need to be restored from fulldump.sql using sed:

sed -n -e '/CREATE TABLE.*tableName1/,/CREATE TABLE/p' fulldump.sql > temp.sql
sed -n -e '/CREATE TABLE.*tableName2/,/CREATE TABLE/p' fulldump.sql >> temp.sql

...etc

Now restore from temp.sql.

NullDev
  • 5,940
  • 4
  • 28
  • 47
user12207746
  • 11
  • 1
0

As far as I know, no.

You would have to manually edit the CREATE and INSERT statements of the undesired table out of the dump file.

Pekka
  • 431,103
  • 135
  • 960
  • 1,075
0

You could alter your dump statement so that it uses ignore table? http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_ignore-table

mardala
  • 237
  • 1
  • 4