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.

MySQL useful dates

You get asked weird things in reporting, but the worst are translating the nice, neat, sensible dates in database tables into the strange ways that people think about dates.

Here is some MySQL SQL that returns some useful relative dates:

• start of the week
• start of last week
• start of the month
• end of previous month
• start of the previous month


select
date(@dt) base_date,
date(@dt) - interval weekday(@dt) day week_start,
date(@dt) - interval weekday(@dt) day - interval 1 week last_week_start,
date(@dt) - interval (dayofmonth(@dt)-1) day month_start,
date(@dt) - interval (dayofmonth(@dt)) day end_prev_mth,
(date(@dt) - interval (dayofmonth(@dt)-1) day) - interval 1 month start_prev_mth
from
(select @dt := now()) x