[MYSQL] mysql主从切换步骤

# 声明变量, 可以是传参, 也可以脚本里面写死, 根据自己环境来. 我这里就不考虑mysql的端口了
MASTER_HOST='192.168.101.21'
SLAVE_HOST='192.168.101.22'

# IP判断
local_ip=`ip -4 addr | grep inet | awk '{print $2}' | awk -v ipaddr=${MASTER_HOST} -F '/' '{if ($1==ipaddr) print $1}'`
if [ "${local_ip}" == "" ];then
	echo "当前IP不是主库IP, 请检查"
	exit 1
fi

# 进程判断, 我这里就只管1个Mysqld进程的情况
mysqld_pid=`pidof mysqld`
if [ "${mysqld_pid}" == "" ];then
	echo "mysql进程不存在"
	exit 1
elif [ ${mysqld_pid} -eq ${mysqld_pid} ] 2>/dev/null;then
	echo "mysql进程: ${mysqld_pid}"
else
	echo "存在多个mysql进程: ${mysqld_pid}"
	exit 1
fi

# 配置文件获取. 这里要求均在启动进程里面存在, 不能使用默认的配置文件. (根据自己实际情况修改)
my_cnf=`cat /proc/${mysqld_pid}/cmdline | sed 's/--/\n/g' | awk -F '=' '{if ($1=="defaults-file") print $2}`
if [ "${my_cnf}" == "" ];then
	echo "mysqld未指定配置文件, 请检查"
	exit 1
fi

# 从库查询出复制账号密码,并在主和从做验证
repl_password=`mysql -NB -e "select User_password from mysql.slave_master_info where Host='${MASTER_HOST}';"`

# 用户连接判断, 是否只读之类的我这里就不提供了.
# 从库还要检查主从状态和延迟

# 获取校验值, 做过排序方便比较
mysql -h127.0.0.1 -P3314 -p123456 -NB -e 'select concat("CHECKSUM TABLE `",table_schema,"`.`",table_name,"`;") from information_schema.tables where table_schema not in ("sys","information_schema","mysql","performance");' | mysql -h127.0.0.1 -P3314 -p123456 -NB  | sort > /tmp/.checksumalltable.txt

# 然后比较主和从的md5值即可
md5sum /tmp/.checksumalltable.txt