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.

• jkavalik: Do you have sed there? You could use it to remove "drop table if exists" and change "create table" to "create table if not exists". Or can you do it on your computer before uploading the dump files?
• Vérace: @jkavalik You should make this an answer.

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.

• András Váczi: What about the CREATE TABLE statements? Furthermore, grep -v ... 1.sql does just the same, no need for cat.
• Vérace: See @jkavalik 's comment. This will fail on two levels. 1) "IF EXISTS" on its own will throw an error. 2) "CREATE TABLE" with an already existing table will burp also.
• mysql_user: Updated to use --force which will ignore errors and let things run.this will work.

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