mysql主從自動設定、診斷及測試指令碼

標籤：mysql主從自動設定、診斷及測試指令碼

#!/bin/sh

# ****************************************************************************

# Filename:

#                master_slave_configure.sh

# Function:

#                Automatic configuration of master to slave.

# Author:

#                [email protected]

#

# Usage Example:

#                ./master_slave_configure.sh mip:mport sip:sport

# ****************************************************************************

# Change History:

# ----------------------------------------------------------------------------

# Init Development              Peek.Ren                2014-09-16

# ****************************************************************************

#

function help()

{

cat << EOF

./master_slave_configure.sh IP1:PORT1 IP2:PORT2 [VARS]

        VARS: inputting variables,pay attention to the input correct format.

EOF

}

function input_user()

{

        read username

echo $username

}

function input_passwd()

{

        stty -echo

        read password

        stty echo

        echo $password

}

function get_master_info()

{

echo ""

echo ""

MYCMD="mysql -u$1 -p$2 -h$3 -P$4"

Master_Log_File=`${MYCMD} -e "show master status\G;" | sed ‘1d‘ | sed ‘3,$d‘ | awk -F ‘:‘ ‘{print $2}‘  | sed ‘2d‘ | sed ‘s/\ //g‘` 

Master_Log_Pos=`${MYCMD} -e "show master status\G;" | sed ‘1d‘ | sed ‘3,$d‘ | awk -F ‘:‘ ‘{print $2}‘ | sed ‘1d‘ | sed ‘s/\ //g‘`

echo "[NOTICE]: Capture master data file and pos success!!"

}

function do_slave_config()

{

MYCMD="mysql -u$1 -p$2 -h$3 -P$4"

$MYCMD -e "stop slave;" &> /dev/null

if [ $? = 0 ];then

echo "[NOTICE]: stop slave success"

else

echo "[NOTICE]: stop slave error"

echo "[NOTICE]: Please check relation configure"

exit 1

fi

SLAVECONFIG="master_host=‘$db1_ip‘,master_port=$db1_port,master_user=‘$db1_user‘,master_password=‘$db1_passwd‘,master_log_file=‘$Master_Log_File‘,master_log_pos=$Master_Log_Pos"

CHANGE_MASTER="change master to ${SLAVECONFIG};" 

$MYCMD -e "$CHANGE_MASTER" &> /dev/null

if [ $? = 0 ];then

echo "[NOTICE]: slave configure success"

else

echo "[NOTICE]: slave configure failure"

exit 1

fi

$MYCMD -e "start slave;" &> /dev/null

if [ $? = 0 ];then

echo "[NOTICE]: slave start success"

else

echo "[NOTICE]: slave start failure"

exit 1

fi

}

function get_ip()

{

        echo ${1%%:*} 

}

function get_port()

{

        echo ${1##*:}

}

function parse_param()

{

        if [ $# -ne 2 ] && [ $# -ne 3 ]

        then

                help

                exit 0

        fi

        db1_ip=`get_ip $1`

        db1_port=`get_port $1`

        db2_ip=`get_ip $2`

        db2_port=`get_port $2`

}

function slave_check()

{

MYCMD="mysql -u$1 -p$2 -h$3 -P$4"

Seconds_Behind_Master=`$MYCMD -e "show slave status\G;" | grep "Seconds_Behind_Master:" | awk -F ‘:‘ ‘{print $2}‘`

        Slave_IO_Running=`$MYCMD -e "show slave status\G;" | grep "Slave_IO_Running:" | awk -F ‘:‘ ‘{print $2}‘`

        Slave_SQL_Running=`$MYCMD -e "show slave status\G;" | grep "Slave_SQL_Running:" | awk -F ‘:‘ ‘{print $2}‘`

        if [ $Seconds_Behind_Master = "NULL" ];then

                echo "[NOTICE]: Disruption of replication"

                if [ $Slave_IO_Running = "Yes" -a $Slave_SQL_Running = "No" ];then

                        echo "[NOTICE]: Slave_IO_Running is OK and Slave_SQL_Running is failure"

elif [ $Slave_IO_Running = "Connecting" -a $Slave_SQL_Running = "Yes" ];then

                        echo "[NOTICE]: Slave_IO_Running is Connecting and Slave_SQL_Running is OK"

                elif [ $Slave_IO_Running = "No" -a $Slave_SQL_Running = "Yes" ];then

                        echo "[NOTICE]: Slave_IO_Running is failure and Slave_SQL_Running is OK"

                else

                        echo "[NOTICE]: Slave_IO_Running is failure and Slave_SQL_Running is failure"

                fi

        elif [ $Seconds_Behind_Master -eq 0 ];then

                echo "[NOTICE]: slave status OK!"

        elif [ $Seconds_Behind_Master -gt 0 ];then

                echo "[NOTICE]: slave has beened delayed compared with master"

        else

                echo "[NOTICE]: slave Unknown fault!"

        fi

}

function consistence_master_check()

{

MYCMD="mysql -u$1 -p$2 -h$3 -P$4"

$MYCMD -e "use test;drop table if exists tt;create table tt(id int,name varchar(10));insert into tt select 1,‘rgf‘;" &> /dev/null

if [ $? = 0 ];then

echo "[NOTICE]: Master create table success."

else

echo "[NOTICE]: create table failure,please check create synatx."

exit 1

fi

}

function consistence_slave_check()

{

MYCMD="mysql -u$1 -p$2 -h$3 -P$4"

$MYCMD -e "use test;select * from tt;" && echo "" 

if [ $? = 0 ];then

echo "[NOTICE]: Congratulations!slave configuration OK."

else

echo "[NOTICE]: Please check configure."

exit 1

fi

}

function delete_master_test()

{

MYCMD="mysql -u$1 -p$2 -h$3 -P$4"

$MYCMD -e "use test;drop table if exists tt;" &> /dev/null

echo "[NOTICE]: Master test data have been deleted!"

echo "[NOTICE]: Congratulations!master-slave configuration complete success."

}

function main()

{

        parse_param [email protected]

        echo -n "Please Input the Master Username($db1_ip:$db1_port)[repl_user]:"

        db1_user=`input_user`

        echo -n "Please Input the master Password($db1_ip:$db1_port):"

        db1_passwd=`input_passwd`

echo ""

echo ""

echo -n "Please Input the Slave Username($db2_ip:$db2_port)[repl_user]:"

        db2_user=`input_user`

        echo -n "Please Input the Slave Password($db2_ip:$db2_port):"

        db2_passwd=`input_passwd`

        get_master_info $db1_user $db1_passwd $db1_ip $db1_port

echo "Now,beginning to configure slave database..."

echo "..........................................."

do_slave_config $db2_user $db2_passwd $db2_ip $db2_port

slave_check $db2_user $db2_passwd $db2_ip $db2_port

consistence_master_check $db1_user $db1_passwd $db1_ip $db1_port

consistence_slave_check $db2_user $db2_passwd $db2_ip $db2_port

echo "Now,beginning to delete master test data..."

delete_master_test $db1_user $db1_passwd $db1_ip $db1_port

}

main [email protected]

mysql主從自動設定、診斷及測試指令碼