MySQL备份脚本与rsync创建

脚本功能：

1·指定不同的参数，实现分库、分表、rsync创建

2·打印执行结果，同时记录到日志

脚本内容如下：

#!/bin/bash

STARTTIME=`/bin/date +%Y%m%d%H%M%S`
STARTTIME7=`date -d '7 days ago' +%Y%m%d%H%M%S`
YEARMONTH=`date +%Y%m`
MKDIR='/bin/mkdir'
MYSQLDUMP='/usr/bin/mysqldump'
GZIP='/bin/gzip'
RM='/bin/rm'
MV='/bin/mv'
CHMOD='/bin/chmod'
TOUCH='/bin/touch'
BACKUP_DIR='/server/backup_mysql'
BINLOG_DIR='/var/lib/mysql'
BINLOG_NAME='mysql-bin'

#define function
function log_success_msg()
{
echo " SUCCESS! $@"
}
function log_failure_msg()
{
echo " ERROR! $@"
}

function ResultStat() {
if [ $RESULT -eq 0 ]; then
log_success_msg " [`/bin/date +%Y%m%d%H%M%S`] $STATS_NAME
-------------------------------------------------------------------------------"|tee -a backup_mysql.$YEARMONTH.log
else
log_failure_msg " [`/bin/date +%Y%m%d%H%M%S`] $STATS_NAME
-------------------------------------------------------------------------------"|tee -a backup_mysql.$YEARMONTH.log
fi
}

function UsageError()
{
echo
echo "$0 : Run Error"
echo "Usage : Try '$0 -d  or -a'
-d : designation database or table
-a : detach of all databases
-rsyncd : configuration rsync server"
}

function CreateDir()
{
if [ ! -d $BACKUP_DIR ]; then
$MKDIR -p $BACKUP_DIR
RESULT=$?
STATS_NAME="Check OR Create dir...$BACKUP_DIR"
ResultStat
sleep 2
fi
cd $BACKUP_DIR
}

# create .my.cnf file
if [ ! -e '/root/.my.cnf' ]; then
$TOUCH /root/.my.cnf
$MV /root/.my.cnf /root/.my.cnf.$STARTTIME.bak
cat >/root/.my.cnf <<+
[client]
user = root
password = 123456
+
RESULT=$?
STATS_NAME="The /root/.my.cnf is created."
ResultStat
fi

# chose backup type
if [ $# -eq 0 ]; then
UsageError
exit 1
elif [ $1 = '-d' ]; then
CreateDir
if [[ -n $2 && -z $3 ]]; then
$MYSQLDUMP -x -R --triggers --master-data=2 $2  |$GZIP >$2.$STARTTIME.sql.gz
RESULT=$?
STATS_NAME="# $MYSQLDUMP -x -R --triggers --master-data=2 $2  |$GZIP >$2.$STARTTIME.sql.gz"
ResultStat
elif [[ -n $2 && -n $3 ]]; then
$MYSQLDUMP -x -R --triggers --master-data=2 $2 $3 |$GZIP >$2.$3.$STARTTIME.sql.gz
RESULT=$?
STATS_NAME="# $MYSQLDUMP -x -R --triggers --master-data=2 $2 $3 |$GZIP >$2.$3.$STARTTIME.sql.gz"
ResultStat
else
UsageError
exit 1
fi
elif [ $1 = '-a' ]; then
CreateDir
for db in $(mysql -e "show databases" | egrep -v "(Database|test|information_schema|performance_schema)")
do
$MYSQLDUMP -x -R --triggers --master-data=2 $db |$GZIP >$db.$STARTTIME.sql.gz
RESULT=$?
STATS_NAME="# $MYSQLDUMP -x -R --triggers --master-data=2 $db |$GZIP >$db.$STARTTIME.sql.gz"
ResultStat
done
elif [ $1 = '-rsyncd' ]; then
PASSWORD_FILE='/usr/local/rsync/rsyncd.password'
RSYNCD_DIR='/usr/local/rsync'
if [ ! -d $RSYNCD_DIR ]; then
$MKDIR -p $RSYNCD_DIR
RESULT=$?
STATS_NAME="# $MKDIR -p $RSYNCD_DIR"
ResultStat
fi
if [ ! -e '/etc/xinetd.d/rsync' ]; then
$TOUCH /etc/xinetd/d/rsync
fi
$MV /etc/xinetd.d/rsync /etc/xinetd.d/rsync.$STARTTIME.bak
cat >/etc/xinetd.d/rsync <<+
service rsync
{
disable = no
socket_type = stream
wait = no
user = root
server = /usr/bin/rsync
server_args = --daemon --config=/etc/rsyncd.conf
log_on_failure += USERID
}
+
RESULT=$?
STATS_NAME="The /etc/xinetd.d/rsync is created."
ResultStat

if [ ! -e '/etc/rsyncd.conf' ]; then
$TOUCH /etc/rsyncd.conf
fi
$MV /etc/rsyncd.conf /etc/rsyncd.conf.$STARTTIME.bak
cat >/etc/rsyncd.conf <<+
uid = root
gid = root
use chroot = no
max connections = 5
lock file = /usr/local/rsync/rsyncd.lock
log file = /usr/local/rsync/rsyncd.log
pid file = /usr/local/rsync/rsyncd.pid
hosts allow = 192.168.250.250
hosts deny = *
ignore errors
read only = yes
list = no
auth users = backupdbuser
secrets file = /usr/local/rsync/rsyncd.password

[BINLOG]
path = $BINLOG_DIR
include = $BINLOG_NAME.*
exclude = *

[DUMPDB]
path = $BACKUP_DIR
+
RESULT=$?
STATS_NAME="The /etc/rsyncd.conf is created."
ResultStat

cat >$PASSWORD_FILE <<+
backupdbuser:vn55JU;bp
+
$CHMOD 600 $PASSWORD_FILE
RESULT=$?
STATS_NAME="The $PASSWORD_FILE is created.
# $CHMOD 600 $PASSWORD_FILE
please start RsyncServer. Refer: /etc/init.d/xinetd reload | restart"
ResultStat
exit 0
else
UsageError
exit 1
fi

#rm 7 days ago
for db in $(mysql -e "show databases" | egrep -v "(Database|test|information_schema|performance_schema)")
do
if [ ! -f $db.$STARTTIME7.sql.gz ]; then
STATS_NAME="$db.$STARTTIME7.sql.gz is not exist..."
ResultStat
else
$RM $db.$STARTTIME7.sql.gz -rf
RESULT=$?
STATS_NAME="# $RM $db.$STARTTIME7.sql.gz -rf..."
ResultStat
fi
done