Simple database backup script. Splits structure and data dumps.

db-sync.sh

#!/bin/bash

# Database User
USER="my-db-user"

# Database Password
PASSWORD="my-db-passwd"

# Database Name
DB="my-db-name"

# Date format used for the file name.
DATE=`date +%Y-%m-%d-%H.%M.%S`

# Dump the database structure.
TABLES=`mysql -u ${USER} -p${PASSWORD} --skip-column-names -e 'show tables' ${DB}`

mysqldump -u ${USER} -p${PASSWORD} --opt --add-drop-table --no-data ${DB} ${TABLES} > ${DB}.${DATE}.sql


# Dump the database data and append it to the structure dump.
# Excludes search, cache, sessions, and watchdog data. Edit as needed for custom exclusions.
TABLES=`mysql -u ${USER} -p${PASSWORD} --skip-column-names -e 'show tables' ${DB} | egrep -v '(^search.*)|(^cache.*)|^(accesslog|cache|sessions|watchdog)$'`

mysqldump -u ${USER} -p${PASSWORD} --opt --no-create-info ${DB} ${TABLES} >> ${DB}.${DATE}.sql

# Gunzip the sql dump file
gzip ${DB}.${DATE}.sql