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!

Christian
  • 123
  • 3
  • 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. – ALex_hha Feb 19 '16 at 21:42
  • 1
    Possible duplicate of [mysqldump ignore table with wildcard](http://serverfault.com/questions/511333/mysqldump-ignore-table-with-wildcard) – Michael - sqlbot Feb 20 '16 at 04:36

3 Answers3

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
Sunny
  • 73
  • 5
  • 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. – Michael - sqlbot Feb 20 '16 at 04: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.

Tim
  • 30,383
  • 6
  • 47
  • 77
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.

Alex
  • 330
  • 3
  • 8