首页 > 代码库 > mysql主从自动配置、诊断及测试脚本
mysql主从自动配置、诊断及测试脚本
#!/bin/sh
# ****************************************************************************
# Filename:
# master_slave_configure.sh
# Function:
# Automatic configuration of master to slave.
# Author:
# gfsunny@163.com
#
# 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 $@
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 $@
mysql主从自动配置、诊断及测试脚本