环境声明:
主从服务器位于 192.168.0.0/24 网段
master-->IP:192.168.0.88
master-->IP:192.168.0.89
在Master---主数据库的脚本 master-IP: 192.168.0.88#!/bin/sh
# Function: This is used for mysql-master role
# made by zhigang.wang
# contact: 44850823@qq.com
MYUSER=root
MYPASS="rootpwd"
PORT=3306
SOCKET=/data/$PORT/mysql.sock
DATA_PATH=/data/backup
LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
MYSQL_DIR=/application/mysql/bin
MYSQL_CMD="$MYSQL_DIR/mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYSQL_DUMP="$MYSQL_DIR/mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -A -B -F --single-transaction --events "
[ ! -d $DATA_PATH ] && mkdir -p $DATA_PATH
[ `$MYSQL_CMD -e "select user,host from mysql.user"|grep rep|wc -l` -ne 1 ] &&\
$MYSQL_CMD -e "grant replication slave on *.* to 'rep'@'192.168.0.%' identified by 'rep123';"
$MYSQL_CMD -e "flush tables with read lock;"
echo "-----show master status result-----" >$LOG_FILE
$MYSQL_CMD -e "show master status;" >>$LOG_FILE
${MYSQL_DUMP} | gzip > $DATA_FILE
$MYSQL_CMD -e "unlock tables;"
cat $LOG_FILE
在Slave---从数据库的脚本#!/bin/sh
# Function: This is used for mysql-master role
# made by zhigang.wang
# contact: 44850823@qq.com
MYUSER=root
MYPASS="rootpwd"
PORT=3306
SOCKET=/data/$PORT/mysql.sock
DATA_PATH=/data/backup
LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
MYSQL_DIR=/application/mysql/bin
MYSQL_CMD="$MYSQL_DIR/mysql -u$MYUSER -p$MYPASS -S $SOCKET"
#recover
cd ${DATA_PATH} && rm -f mysql_backup_`date +%F`.sql
gzip -d mysql_backup_`date +%F`.sql.gz
$MYSQL_CMD
#config slave
cat |$MYSQL_CMD<
CHANGE MASTER TO
MASTER_HOST='192.168.0.88',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='rep123',
MASTER_LOG_FILE="`tail -1 $LOG_FILE|cut -f1`",
MASTER_LOG_POS=`tail -1 $LOG_FILE|cut -f2`;
EOF
$MYSQL_CMD -e "start slave;"
$MYSQL_CMD -e "show slave status\G"|egrep "IO_Running|SQL_Running" >>$LOG_FILE
mail -s "MySQL-Slave build status" 44850823@qq.com