Listing Tables in MySQL database & generating mysql dump

gistfile1.txt

/*
* List all the tables in a particular database, excluding some
*/

SELECT table_name 
FROM information_schema.tables
WHERE table_schema = '[db name]'
AND table_name NOT IN ('table1', 'table2', 'table3') 
AND table_name NOT LIKE 'another_table_%' 
AND table_name NOT LIKE '%_some_other_pattern%';



/*
* Same query as above but putting the tables in a comma-separated list. Note that
* we need to extend the max length of group_concat to ensure we get all the tables
* in the case of a large db.
*/
SET group_concat_max_len = 10240;
SELECT GROUP_CONCAT(table_name separator ',') AS 'table name' 
FROM information_schema.tables
WHERE table_schema = '[db name]'
AND table_name NOT IN ('table1', 'table2', 'table3') 
AND table_name NOT LIKE 'another_table_%' 
AND table_name NOT LIKE '%_some_other_pattern%';



/*
* To do a database dump from the prompt using the ideas above to exclude tables 
* from the resulting mysql dump file.
* IMPORTANT NOTE: the below is for the command prompt.
*/
SQL = "SET group_concat_max_len = 10240;"
SQL = "${SQL} SELECT GROUP_CONCAT(table_name separator ',') AS 'table name' FROM information_schema.tables"
SQL = "${SQL} WHERE table_schema = '[db name]' AND table_name NOT IN ('table1', 'table2', 'table3')"
SQL = "${SQL} AND table_name NOT LIKE 'another_table_%' AND table_name NOT LIKE '%_some_other_pattern%';"
TABLELIST = `mysql -u [username] -p -h [host] -AN -e"${SQL}"`
mysqldump -u [username] -p -h [host] [db name] ${TABLELIST} > mysql_dump_file.sql