mysql - import only data from full dump?

Question

I have a full dump from database A. I want to import only the data of this dump into database B. Database B's structure is the same as database A only it misses some tables. How can I do this?

The reason that I don't import the full dump and delete the extra tables is that database B is on RDS and when I import the dump file into it, the importing job will stop in the middle of it. I tried three times and the database is quite large. Every three times, it stops at the same place. RDS log is not showing anything. So I decided to only import the data from the dump file.

Answer 1

You could filter out the DDL statements in a dump file using sed or perl or something. But this would take some time to write the code and test it.

It'd be easier if you could prepare another dump file that skips the DDL statements. Read the documentation on mysqldump, there's an option --no-create-info that does this.

However, if the problem is that restoring your large dump file can't complete, I am not sure how it would help to import data only. Omitting the DDL statements won't reduce the size of the dump file by much.

Here's another option: don't use a dump file at all. Recently RDS announced support for importing physical backups made with Percona XtraBackup: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.html

You'd create a backup of your original database using Percona XtraBackup, then upload that to S3, then invoke the import of that backup to an RDS instance. It's far faster and more reliable, but the caveat is that it will overwrite whatever tables you have on your RDS instance.