MySQL import database but ignore specific table
Solution 1
mysqlimport
is not the right tool for importing SQL statements. This tool is meant to import formatted text files such as CSV. What you want to do is feed your sql dump directly to the mysql
client with a command like this one:
bash > mysql -D your_database < your_sql_dump.sql
Neither mysql
nor mysqlimport
provide the feature you need. Your best chance would be importing the whole dump, then dropping the tables you do not want.
If you have access to the server where the dump comes from, then you could create a new dump with mysqldump --ignore-table=database.table_you_dont_want1 --ignore-table=database.table_you_dont_want2 ...
.
Check out this answer for a workaround to skip importing some table
Solution 2
The accepted answer by RandomSeed could take a long time! Importing the table (just to drop it later) could be very wasteful depending on size.
For a file created using
mysqldump -u user -ppasswd --opt --routines DBname > DBdump.sql
I currently get a file about 7GB, 6GB of which is data for a log table that I don't 'need' to be there; reloading this file takes a couple of hours. If I need to reload (for development purposes, or if ever required for a live recovery) I skim the file thus:
sed '/INSERT INTO `TABLE_TO_SKIP`/d' DBdump.sql > reduced.sql
And reload with:
mysql -u user -ppasswd DBname < reduced.sql
This gives me a complete database, with the "unwanted" table created but empty. If you really don't want the tables at all, simply drop the empty tables after the load finishes.
For multiple tables you could do something like this:
sed '/INSERT INTO `TABLE1_TO_SKIP`/d' DBdump.sql | \
sed '/INSERT INTO `TABLE2_TO_SKIP`/d' | \
sed '/INSERT INTO `TABLE3_TO_SKIP`/d' > reduced.sql
There IS a 'gotcha' - watch out for procedures in your dump that might contain "INSERT INTO TABLE_TO_SKIP".
Solution 3
For anyone working with .sql.gz files; I found the following solution to be very useful. Our database was 25GB+ and I had to remove the log tables.
gzip -cd "./mydb.sql.gz" | sed -r '/INSERT INTO `(log_table_1|log_table_2|log_table_3|log_table_4)`/d' | gzip > "./mydb2.sql.gz"
Thanks to the answer of Don and comment of Xosofox and this related post: Use zcat and sed or awk to edit compressed .gz text file
Solution 4
Little old, but figure it might still come in handy...
I liked @Don's answer (https://stackoverflow.com/a/26379517/1446005) but found it very annoying that you'd have to write to another file first...
In my particular case this would take too much time and disc space
So I wrote a little bash script:
#!/bin/bash
tables=(table1_to_skip table2_to_skip ... tableN_to_skip)
tableString=$(printf "|%s" "${tables[@]}")
trimmed=${tableString:1}
grepExp="INSERT INTO \`($trimmed)\`"
zcat $1 | grep -vE "$grepExp" | mysql -uroot -p
this does not generate a new sql script but pipes it directly to the database
also, it does create the tables, just doesn't import the data (which was the problem I had with huge log tables)
Solution 5
Unless you have ignored the tables during the dump with mysqldump --ignore-table=database.unwanted_table
, you have to use some script or tool to filter out the data you don't want to import from the dump file before passing it to mysql
client.
Here is a bash/sh function that would exclude the unwanted tables from a SQL dump on the fly (through pipe):
# Accepts one argument, the list of tables to exclude (case-insensitive).
# Eg. filt_exclude '%session% action_log %_cache'
filt_exclude() {
local excl_tns;
if [ -n "$1" ]; then
# trim & replace /[,;\s]+/ with '|' & replace '%' with '[^`]*'
excl_tns=$(echo "$1" | sed -r 's/^[[:space:]]*//g; s/[[:space:]]*$//g; s/[[:space:]]+/|/g; s/[,;]+/|/g; s/%/[^\`]\*/g');
grep -viE "(^INSERT INTO \`($excl_tns)\`)|(^DROP TABLE (IF EXISTS )?\`($excl_tns)\`)|^LOCK TABLES \`($excl_tns)\` WRITE" | \
sed 's/^CREATE TABLE `/CREATE TABLE IF NOT EXISTS `/g'
else
cat
fi
}
Suppose you have a dump created like so:
MYSQL_PWD="my-pass" mysqldump -u user --hex-blob db_name | \
pigz -9 > dump.sql.gz
And want to exclude some unwanted tables before importing:
pigz -dckq dump.sql.gz | \
filt_exclude '%session% action_log %_cache' | \
MYSQL_PWD="my-pass" mysql -u user db_name
Or you could pipe into a file or any other tool before importing to DB.
![DanielAttard](https://i.stack.imgur.com/gh8dR.png?s=256&g=1)
DanielAttard
I am a tax lawyer by training, but I am more interested in learning how to code.
Updated on July 09, 2022Comments
-
DanielAttard almost 2 years
I have a large SQL file with one database and about 150 tables. I would like to use
mysqlimport
to import that database, however, I would like the import process to ignore or skip over a couple of tables. What is the proper syntax to import all tables, but ignore some of them? Thank you. -
DanielAttard about 11 yearsThanks for the comment. My problem seems to be that when I use the import command from within MySQL Workbench, this process works but only imports about a third of my tables and then hangs on a specific table. I can't get all the tables imported because of the problem table.
-
RandomSeed about 11 yearsTry to import your dump directly from the command line. Are you getting the same issue? At least you might be able to see some error message.
-
The Humble Rat over 9 yearsThis is genius, 1 hour down to 30 seconds.
-
Castro Roy over 9 yearswow!!! awesome, i've just reduced a backup from 1GB to 72MB, save me a lot of time, thanks
-
RandomSeed over 8 yearsThis makes sense, absolutely. You might want to trim the
CREATE TABLE
statements too. -
Xosofox about 8 yearsConsider the
sed -r
option and a RegExp in case you want to eliminate more tables in one go, like:sed -r '/INSERT INTO `(TABLE1_TO_SKIP|TABLE2_TO_SKIP)`/d' DBdump.sql > reduced.sql
-
res almost 8 yearsjust FLY:
TABLE_TO_SKIP
is case-sensitive, it's usuallytable_to_skip
:) -
gsziszi about 5 yearsfor windows find for "sed for windows" - now it can be found here: gnuwin32.sourceforge.net/packages/sed.htm - and use double quot instead of single quote for the command
-
DUzun almost 5 yearsIf
sed
is not an option or you need case-insensitive ignoring:grep -viE 'INSERT INTO `(TABLE1_TO_SKIP|TABLE2_TO_SKIP)` DBdump.sql > reduced.sql
-
Igorzovisk almost 5 yearsI am still running the import. Don't know if that worked yet. If it works, I could not express how thankful I would be.
-
abax almost 5 yearsFYI: the
sed
command using thed
flag is used for deleting lines which match the pattern between/../
-
CT. over 4 yearsjust beware that
-- Table structure for table <TABLE_TO_SKIP> DROP TABLE IF EXISTS <TABLE_TO_SKIP>; CREATE TABLE <TABLE_TO_SKIP> ( -- Dumping data for table <TABLE_TO_SKIP> LOCK TABLES <TABLE_TO_SKIP> WRITE;
are not removed with this sed command if they exist. Some were not aware, but im not gonna be naming myself -
GTsvetanov over 4 yearsWow, that's amazing it save me a lot of time! Thank you!
-
Don about 4 yearsYou might want to watch out for a situation where your unwanted_table_name is 'SALES' but you have another table called 'BIGSALES' or maybe a foreign key field called, maybe
another_table_SALES_fk
. Your answer as it stands could lead to a world of pain. ALSO if you are going to go down this route, surely fgrep would be much faster than grep? -
Kamil Dąbrowski almost 4 years"sed '/INSERT INTO
TABLE_TO_SKIP
/d' DBdump.sql > reduced.sql" after this i import and the TABLE_TO_SKIP has been deleted from db already exists after import!!! ----------- it shouldn't becouse this line deleted only INSERT not CREATE Table with DELETE IF EXISTS which is standard included.. in mysqldump. So this scrip makes bad bad bad way!! <---------- read tthis important note -
Shekhar over 3 yearsThank you. This is awesome!
-
Joshua Pinter over 3 years@CT I can concur that some were not aware, including myself. Cheers for that.