定时备份MySQL脚本

useage

mysqldump.sh DB_NAME

mysqldump.sh

#!/bin/sh

DUMP=/usr/bin/mysqldump 
BAK_DIR=/production/backup

DB_HOST=localhost
DB_PORT=3306
if [ ! -n "$1" ]; then
    DB_NAME=dbname
else
    DB_NAME="$1"
fi
DB_USER=root
DB_PASS=root
DB_CHARSET=utf8
DAYS=7

DATE=`date +%Y%m%d_%H`
PREFIX="bak_${DB_NAME}"
BAK_SQL="${PREFIX}_${DATE}.sql"
BAK_TAR="${PREFIX}_${DATE}.tar.gz"

cd $BAK_DIR

# back db
$DUMP --host=$DB_HOST --port=$DB_PORT --user=$DB_USER --password=$DB_PASS $DB_NAME --default-character-set=$DB_CHARSET --opt -Q -R --skip-lock-tables > $BAK_SQL 
# tar .sql to .tar.gz
tar -czf $BAK_TAR $BAK_SQL

# remove .sql file
rm $BAK_SQL

# remove backup files $DAYS ago
find $BAK_DIR -name "${PREFIX}*" -type f -mtime +$DAYS -exec rm {} \;