Ignore multiple tables with prefix from mysql backup

Mysql provides a functionality to create a backup of your database using mysqldump. It also enable the user to provide an option --ignore-table which skips the table from the backup process. The doc from the MySQL says -


Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option also can be used to ignore views.

If you want to skip a table from a mysql backup of the database, you can specify the table using this options. For eg.

mysqldump -u<username> -p --ignore-tables=<db_name>.<table_name> db_name > backup.sql

If there are more than one table, you need to specify each table_name against this option. For eg. 

mysqldump -u<username> -p --ignore-tables=<db_name>.<table_name1> --ignore-tables=<db_name>.<table_name2> db_name > backup.sql

This becomes a problem if you have a database with multiple prefixed tables and you don't want to consider them in the backup as you will need to specify all of them using --ignore-table option which is certainly a not-so-good way if there are more than 20 to 30 tables. To make this easier, simple below code(php) can be used to generate a command which ignores all the tables starting from a substring and we can use this directly on the terminal to skip a set of tables from the mysql backup. Modify the code according to your requirements.

//Get all the tables starting from the text that you don't need to include in the dump.
$ignoreTableQuery = "SHOW TABLES LIKE 'prefix_%'";
$ignoreTables = mysql_query($ignoreTableQuery);
//Initialize the basic dump command.
$command = 'mysqldump -uroot -p ';
while($val = mysql_fetch_array($ignoreTables)) {
   //This is the field returned from "Show tables command". Catch this in a variable.
   $fieldName = 'Tables_in_<dbname>_(prefix_%)';
   //Make a query to ignore all the tables starting from the prefix.
   $command .= "--ignore-table=<db_name>.{$val->$fieldName} ";
//End the command.
$command .= "<db_name> > backup.sql"
echo $command;

Use the above returned $command string and execute it on the terminal to skip all the tables starting from "prefix_".