How would I exclude a group of tables based on a name prefix foo_* from a mysqldump?

by codecowboy   Last Updated August 14, 2019 00:06 AM

I'd like to exclude a group of tables from a mysqldump command (from a bash script). I might not know the exact table names ahead of time but they will be named with the prefix foo_ or might contain another known suffix like _BAK_[%Y%m%d]

Answers 1

This is based on answers from How do you mysqldump specific table(s)?

To exclude all tables from a mysqldump that starts with foo_, here is the shell script to do it

SQL="SET group_concat_max_len = 102400;"
SQL="${SQL} SELECT GROUP_CONCAT(CONCAT('--ignore-table=',table_name) SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${MYSQL_DATA}'"
SQL="${SQL} AND table_name LIKE 'foo_%'"
mysqldump ${MYSQL_CONN} ${MYSQL_DATA} ${EXCLUSION_LIST} > ${MYSQL_DATA}_tables.sql

To exclude all tables from a mysqldump that starts with foo_, adjust this line

SQL="${SQL} AND table_name LIKE 'foo_%'"

to whatever pattern you need. Maybe you can use the REGEXP operator

SQL="${SQL} AND table_name REGEXP '\_BAK\_2[0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

Give it a Try !!!

June 11, 2014 13:46 PM

Related Questions

Updated July 07, 2015 16:02 PM

Updated January 11, 2017 08:02 AM

Updated June 19, 2015 23:02 PM

Updated September 14, 2017 22:06 PM