首页 > 代码库 > 主从脚本(简写)

主从脚本(简写)

#!/bin/bash

################################################

#this scripts is created by zhangyj

#zhangyj QQ:791880666

#mobile phone:13716430471

#thanks for oldboy teacher

#Function: The Scripts for mysql-slave

################################################


####reload functions

[ ! -x /etc/init.d/functions ] && chmod +x /etc/init.d/functions

. /etc/init.d/functions


###The definition of the variable and dir

MYUSER=xxx

MYPASS="xxx"

MYSQL_PATH=/opt/mysql

DATA_PATH=/backup/mysql/data

LOG_PATH=/backup/mysql/log

Slave-Location=/home/zhangyj/mysql-slave

Slave-Db=/backup/mysql/slave


#####the definition of the file

LOG_FILE=${LOG_PATH}/mysql_logs_`date +%F-%H`.log

DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz

MYSQL_SLAVE_LOG=${Slave-Location}/mysql-slave.log

MYSQL_CMD="${MYSQL_PATH}/bin/mysql -u$MYUSER -p‘$MYPASS‘"

MYSQL_DUMP="${MYSQL_PATH}/bin/mysqldump -u$MYUSER -p$MYPASS -A -B --default-character-set=utf8 --single-transaction --events -e"

Lan_Ip="`ifconfig eth0|grep ‘inet addr‘|cut -d ":" -f2|cut -d " " -f1`"

Wlan_Ip="`ifconfig eth1|grep ‘inet addr‘|cut -d ":" -f2|cut -d " " -f1`"

Master_Ip="10.172.233.102"

Slave_Ip="10.172.229.182"


#mkdir dir

[ ! -d ${MYSQL_PATH} ] && mkdir -p ${MYSQL_PATH}

[ ! -d ${DATA_PATH} ] && mkdir -p ${MYSQL_PATH}

[ ! -d ${LOG_PATH} ] && mkdir -p ${MYSQL_PATH}

[ ! -d ${Slave-Location} ] && mkdir -p ${MYSQL_SLAVE_LOG}

[ ! -d ${Slave-Db} ] && mkdir -p ${Slave-Db}


###master_mysql-backup

master-mysql-backup(){

   ${MYSQL_CMD} -e "show master status;" | sed -n "2p" | awk ‘{print $1}‘ > ${Slave-Location}/test

   ${MYSQL_CMD} -e "show master status;" | sed -n "2p" | awk ‘{print $2}‘ >> ${Slave-Location}/test

   echo "------------Begin-master-`date +%F-%H-%M`-----------" >> ${MYSQL_SLAVE_LOG} 2> /dev/null

   $MYSQL_CMD -e "flush tables with read lock;" 

   echo "show master status result" >> ${MYSQL_SLAVE_LOG} 2> /dev/null

   $MYSQL_CMD -e "show master status;" >> ${MYSQL_SLAVE_LOG} 2> /dev/null

   ${MYSQL_DUMP} | gzip > ${DATA_FILE} 2> /dev/null

   $MYSQL_CMD -e "unlock tables;"

   echo "------------End-master-`date +%F-%H-%M`-----------" >> ${MYSQL_SLAVE_LOG} 2> /dev/null

   [ ! -d /home/zhangyj ] && mkdir -p /home/zhangyj

   echo "fullbackup mysql-master-backup is ok " >> /home/zhangyj/master-backup-mysql 2> /dev/null

}


###slave-mysql-backup

slave-mysql-backup(){

   ####lock_table

   echo "------------Begin-slave-`date +%F-%H-%M`-----------" >> ${MYSQL_SLAVE_LOG} 2> /dev/null

   $MYSQL_CMD -e "flush tables with read lock;" 

   echo "show master status result" >> ${MYSQL_SLAVE_LOG} 2> /dev/null

   $MYSQL_CMD -e "show master status;" >> ${MYSQL_SLAVE_LOG}

   ${MYSQL_DUMP} | gzip > ${DATA_FILE}

   $MYSQL_CMD -e "unlock tables;"

   echo "------------End-slave-`date +%F-%H-%M`-----------" >> ${MYSQL_SLAVE_LOG}

   [ ! -d /home/zhangyj ] && mkdir -p /home/zhangyj

   echo "fullbackup mysql-slave-backup is ok " >> /home/zhangyj/master-backup-mysql 2> /dev/null

}



################################

####restore fullbackup

restore-mysql(){

   [ -d ${DATA_PATH} ] && cd ${DATA_PATH}

   [ $? -eq 0 ] && gzip -d mysql_backup_`date +%F-%H`.sql.gz

   [ $? -eq 0 ] && $MYSQL_CMD -default-character-set=utf8 < mysql_backup_`date +%F-%H-%M`.sql

   [ $? -eq 0 ] && action "restore mysql is ok " /bin/true

}



#######start slave mysql

slave(){

   echo "scp master db and db-log"

   scp -rf root@${Master_Ip}:${DATA_PATH}/mysql_backup_`date +%F`.sql.gz ${Slave-Db}

   [ $? -ne 0 ] && tail -30 $Log_File |tee -a ${Slave-Db}/err.log && exit 1;

   

   gzip -d ${Slave-Db}/mysql_backup_`date +%F`.sql.gz

   [ $? -ne 0 ] && tail -30 $Log_File |tee -a ${Slave-Db}/err.log && exit 1;

   

   $MYSQL_CMD -default-character-set=utf8 < ${Slave-Db}/mysql_backup_`date +%F`.sql

   [ $? -ne 0 ] && tail -30 $Log_File |tee -a ${Slave-Db}/err.log && exit 1;   


   scp -rf root@${Master_Ip}:${Slave-Location}/test ${Slave-Location}/

   [ $? -ne 0 ] && tail -30 $Log_File |tee -a ${Slave-Db}/err.log && exit 1;

   

   [ -f ${Slave-Location}/test ] && echo "slave test is ok" || exit 1

   aa="`sed -n ‘1p‘ ${Slave-Location}/test`"

   bb="`sed -n ‘2p‘ ${Slave-Location}/test`"

   

   cat |$MYSQL_CMD<< EOF

   CHANGE MASTER TO  

      MASTER_HOST=${Master_Ip}, 

      MASTER_PORT=3306,

      MASTER_USER=${MYUSER},

      MASTER_PASSWORD=${MYPASS}, 

      MASTER_LOG_FILE="${aa}",

      MASTER_LOG_POS="${bb}";

   EOF

   $MYSQL_CMD -e "start slave;" 

   $MYSQL_CMD -e "show slave status\G"|egrep "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master" > ${Slave-Location}/IO-SQL

   [ -f ${Slave-Location}/IO-SQL ] && cat ${Slave-Location}/IO-SQL || exit 1

   read -p "1: yes 2: no" num

   case "$num" in

      1)

         action "Mysql slave is sucess..." /bin/true

      ;;

      2)

         action "Mysql slave is false..." /bin/false 

      ;;

      *)

         exit 1

      ;;

   esac  

}


######install menu

cat << EOF

----------------------------------------

|****Please Enter Your Choice:[1-3]****|

----------------------------------------

(1)  Backup Master 

(2)  Backup Slave

(3)  Restore mysql

(4)  Mysql Slave

(5)  EXIT

EOF

read -p "Please Enter Your Choice: " num

case "$num" in

   1)

       master-mysql-backup

   ;;

   2)

      slave-mysql-backup

   ;;

   3)

      restore-mysql

   ;;

   4)

      slave

   ;;

   5)

      exit 1

   ;;

esac


本文出自 “三人行,必有我师焉” 博客,请务必保留此出处http://lovers.blog.51cto.com/5850489/1576280

主从脚本(简写)