2

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.

  • 1
    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? – jkavalik Jul 14 '15 at 5:49
  • @jkavalik You should make this an answer. – Vérace Jul 14 '15 at 8:11
2
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.

  • What about the CREATE TABLE statements? Furthermore, grep -v ... 1.sql does just the same, no need for cat. – dezso Jul 14 '15 at 8:06
  • 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. – Vérace Jul 14 '15 at 8:10
  • Updated to use --force which will ignore errors and let things run.this will work. – mysql_user Jul 14 '15 at 9:22
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.