Building a mysqldump script to back up your database

Sometimes, I want to backup the mySQL database but not everything...using mysqldump we can develop a little script to organise what we need.

select
concat('mysqldump -u<uid> -p<passwd> --max_allowed_packet=256M ',
table_schema,' ', group_concat(table_name ORDER BY table_name DESC SEPARATOR ' ')
,' > /tmp/mysqldump_20120319_',table_schema
) tables_ from information_schema.TABLES where table_schema not in ('information_schema','mysql','db1','db2','db2','db3')
and table_type='BASE TABLE'
group by table_schema


This returns a series of command strings for mysqldump that lists each table in the non-excluded schemas and dumps them into a dump file for each schema. This means a table name can be deleted if you don't want/need to have it backed-up enough to justify storing a (e.g) 20GB insert statement.

These can be pasted into a file and run with sh and cron ....or in a batch file in Windows.

Adjust the UID, pwd, dumpfile name/location and database schemas as needed.