How to skip a table when restoring mysql database?

I have a big mySQL database dump named forum.sq开发者_运维百科l. 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?

---

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

---

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.

---

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.

---

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.

---

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

---

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