Create User Backup and Create Authorization For User backup
CREATE USER 'backup'@'10.66.66.5' IDENTIFIED BY 'xxxxxxx';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR, CREATE TABLESPACE, SHOW DATABASES, SELECT, CREATE, SHOW VIEW ON *.* TO 'backup'@'10.66.66.5';Create Script Backup
mkdir -p app-backup/{var,etc}
echo -e "database1\n database2" > app-backup/etc/DB.list#! /bin/sh
APP_NAME=app
DIR_ROOT="/usr/local/script/${APP_NAME}-backup"
DIR_BACKUP="/DB_BACKUP/${APP_NAME}_DB"
DB_LIST="$DIR_ROOT/etc/DB.list"
REPORT="$DIR_ROOT/var/report.$$"
TS=`date +%A`
FULL_TS=`date +%d`
DATE_TS=`date +%Y%m%d`
HOST="10.66.66.2"
USER="backup"
PASS="xxxx"
echo "------------------- DB BACKUP ASSET --------------------------" >> $REPORT.$DATE_TS
echo "------------------- PROCESS STARTED `date` --" >> $REPORT.$DATE_TS
for a in $(cat $DB_LIST);
do echo "------------------- $a has been backup ----" >> $REPORT.$DATE_TS;
/usr/bin/mysqldump -h ${HOST} -u ${USER} -p ${PASS} --extended-insert=FALSE --single-transaction -q --no-autocommit -B $a | gzip > $DIR_BACKUP/${APP_NAME}_DB.$a.$TS.gz;
echo "`du -ks $DIR_BACKUP/${APP_NAME}_DB.$a.$TS.gz`" >> $REPORT.$DATE_TS;
if [ $FULL_TS == 01 ]
then
echo "------------------- $a MONTHLY FULL backup proceeding ----" >> $REPORT.$DATE_TS;
cp $DIR_BACKUP/${APP_NAME}_DB.$a.$TS.gz $DIR_BACKUP/full/${APP_NAME}_DB.$a.`date +%y%m%d`.gz
echo "------------------- $a MONTHLY FULL backup DONE ! ----" >> $REPORT.$DATE_TS;
fi
done
mysql -u ${USER} -h ${HOST} -p ${PASS} -e "show master status\G" >> $REPORT.$DATE_TS
echo "------------------- PROCESS FINISH `date` --" >> $REPORT.$DATE_TS
