2

I'm trying to figure out how to exclude or include specific tables when doing mysqldump command on AWS via terminal.

Background: I have a WordPress multisite but only need the tables with the first 3 characters "wp_" all of the other tables with prefixes like "wp_1", "wp_2", etc. I don't need in the dump file.

Here is the code I am using to generate the dump file which works but grabs all the tables in the database:

mysqldump -h RDS instance endpoint \ -u user \ -p databasename \ --port=3306 \ --single-transaction \ --routines \ --triggers \ --databases databasename > path/rds-dump.sql

Thanks!

Improve this question
2
  • It's not clear what is difference between - 'tables with the first 3 characters "wp_"' and "wp_1", "wp_2". Could you clarify more detailed. It would be better with some real examples. Commented Feb 19, 2016 at 21:42
  • 1
    Possible duplicate of mysqldump ignore table with wildcard Commented Feb 20, 2016 at 4:36

3 Answers 3

Reset to default
2

One thing that I have implemented when I faced same issue is that I created a file with all the required table names. Now I just have to Iterate through the file and take the dump of each table and append it in the same dump file. For example:

for i in $(cat requiredtables.txt);do mysqldump -h RDS_instance -u user -p'password' db_name $i >> db_dump.sql;done
Improve this answer
1
  • 1
    That appears to work, but the problem is that you don't necessarily get a backup where all the tables are consistent with each other. If you catch a parent table too early and a child table too late, you could have a stranded foreign key issue, for example. Dumping all the desired tables in a single execution of mysqldump is a better/safer approach, particularly when you use --single-transaction (as you always should) so that all the tables are consistent with the snapshot of the database as it appeared when the backup first started. Commented Feb 20, 2016 at 4:40
0

I looked at this a while ago, when moving my own websites, and my conclusion is it's not possible. There's probably a way to process the dump file after export, but I probably wouldn't bother without a really good reason.

Improve this answer
0

This is not possible, but you can always dump the entire db and create an SQL query that drops the unwanted tables. This however will not shorten mysqldump time.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.