MySQL : How to restore mysql dump (.sql) ignoring DROP TABLE statement?

Question

I am trying to restore multiple mysql dump (.sql) files. Data in each dump file are from the same table, but with different ID (primary key) range.

For example,

> 1.sql (0 < ID && ID <= 100)
> 2.sql (100 < ID && ID <= 200)
> ...

I have 100 files like the above, each containing 'DROP TABLE IF EXISTS' and 'CREATE TABLE' statements. Because of the two statements, I can't simply automate the mysql restore command.

> root$> mysql < dump.sql

Since every time I execute the sql, it will drop the table and delete all data restored. So I need something that I can ignore the DROP and CREATE statements while doing ...

> root$> mysql < dump.sql

Has anyone done this before?

FYI, my OS is Amazon Linux AMI.

Answer 1

cat 1.sql | grep -v "DROP TABLE" | mysql --force

Assuming no other string contains drop commands this should work..

Updated: use --force to ignore errors by CREATE TABLE.

Answer 2

If you're running on Linux (any with awk), you could run:

awk '!/^DROP TABLE IF EXISTS/{print}' db_dump_sql | mysql -u $user -p --force --database=$db

PD: I added --force that means:

--force, -f

Continue even if an SQL error occurs.

Test:

root@onare:/home/onare# touch db_dump_sql
root@onare:/home/onare# echo "DROP TABLE IF EXISTS/" >> db_dump_sql 
root@onare:/home/onare# echo "Test 1" >> db_dump_sql 
root@onare:/home/onare# echo "DROP TABLE IF EXISTS/" >> db_dump_sql 
root@onare:/home/onare# echo "Test 2" >> db_dump_sql 
root@onare:/home/onare# awk '!/^DROP TABLE IF EXISTS/{print}' db_dump_sql 
Test 1
Test 2
root@onare:/home/onare# awk '!/^DROP TABLE IF EXISTS/{print}' db_dump_sql | mysql -u $user -p --database=$db