dump a database with selective rows and tables

dump_mysql_db.sh

#!/bin/sh

MYSQLDUMP=`which mysqldump`

# mysql settings
DB="mysql"
HOST="127.0.0.1"
USER="root"
PASS="root"

# backup file settings
BACKUPDIR="${HOME}/backup/mysql"
mkdir -p $BACKUPDIR
DATE=`date "+%Y%-m%-d-%H%M"`
FILE="$BACKUPDIR/$DB-$DATE.sql.bz2"

OPTS="--verbose --force --quick --compress --comments --triggers --routines --dump-date --skip-add-drop-table --extended-insert --order-by-primary"

# dump schema
$MYSQLDUMP --host=$HOST --user=$USER --password=$PASS --no-data $OPTS $DB | bzip2 > $FILE

# tables to ignore in data dump
IGNORED_TABLES="--ignore-table=DB.table1 \
--ignore-table=DB.table2 \
"

# dump data
$MYSQLDUMP --host=$HOST --user=$USER --password=$PASS --no-create-db --no-create-info $OPTS $IGNORED_TABLES $DB | bzip2 >> $FILE

# selectively dump database table rows
TS_MONTH='unix_timestamp(date_add(curdate(),INTERVAL -3 MONTH))'
$MYSQLDUMP --host=$HOST --user=$USER --password=$PASS --no-create-db --no-create-info --where="DATE_FIELD > $TS_MONTH" $OPTS $DB THE_TABLE | bzip2 >> $FILE