mysql主从 切换_发个mysql主从切换的脚本

#!/bin/bash

LANG=zh_CN.UTF-8;

mysql1="/usr/local/mysql5-1/bin/mysql -uroot -p111111";

mysql2="/usr/local/mysql5-2/bin/mysql -uroot -p111111";

mysql3="/usr/local/mysql5-2/bin/mysql -uroot -p111111";

cat << README

==================================================================================================================

操作步骤：

1、会询问你想要切换哪个slave为下一个master，可以输入1(mysql5-1)  2(mysql5-2)  3(mysql5-2)

2、根据你的输入，会连接到该服务器，然后show processlist;检查是否已经同步日志完毕。如果同步完毕，输入y继续

3、停掉该mysql的slave进程。之后reset master，成为主库。

4、询问你接下来的slave是谁，还是输入1、2、3，会有与之前的master的判断

5、还会连接到接下来成为master的库里，然后给slave授权。都这为止，master迁移完毕。

6、在slave上检查日志是否同步完毕，然后stop slave，之后change master to新的master，最后start slave。

7、在slave上show slave status\G;看看进程跑没跑着

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

注意事项：

1、不涉及迁回原来的主库哦。其实步骤一样

2、都弄完了，最好在主库上建个测试库，测测

==================================================================================================================

README

LOG=/home/log

now_master=`cat ${LOG}`

echo -e "\033[1m\E[31;40m现在的主库是${now_master},挂了，悲剧啊...\033[0m";

###  1

echo -e "\033[1m\E[32;40m开始执行操作步骤1\033[0m";

echo "-----------------------------------------------"

read -p "哥们，你想让谁成为主库 [1|2|3] " MASTER

case ${MASTER} in

1) m_con=${mysql1};;

2) m_con=${mysql2};;

3) m_con=${mysql3};;

*) echo "好好看说明去！！" && exit 1;;

esac

echo ""

### 2

echo -e "\033[1m\E[32;40m开始执行操作步骤2\033[0m";

echo "----------------------------------------------"

${m_con}<< SHOW

show processlist;

SHOW

read -p "哥们，日志都同步好了吧 [yes|no]" LOG_Y

case ${LOG_Y} in

yes|y) echo "好的，我们继续";;

*) echo "没事，等会再看看" && exit 1;;

esac

### 3

echo -e "\033[1m\E[32;40m开始执行操作步骤3\033[0m";

echo -e "\033[1m\E[33;40m从这开始，就不可逆了，注意看提示\033[0m";

echo "---------------------------------------------"

${m_con}<< STOP_SLAVE

stop slave;

reset master;

STOP_SLAVE

### 4

echo -e "\033[1m\E[32;40m开始执行操作步骤4\033[0m";

echo "--------------------------------------------"

echo "接下来，你的主库会是${MASTER},也可以做从哦，只要你愿意"

unset SLAVE_

until [[ ${SLAVE_} == 1 || ${SLAVE_} == 2 || ${SLAVE_} == 3 ]]; do

read -p "现在，你需要告诉我谁会是接下来的从库[1|2|3]" SLAVE_

echo ""

done

case ${SLAVE_} in

1)      s_con=${mysql1};;

2)      s_con=${mysql2};;

3)      s_con=${mysql3};;

esac

### 5

echo -e "\033[1m\E[32;40m开始执行操作步骤5\033[0m";

echo "--------------------------------------------"

case ${SLAVE_} in

1)

${m_con}<< GRANT_SLAVE

grant replication slave,reload,super on *.* to slave@'192.168.1.3' identified by 'slave';

GRANT_SLAVE

;;

2)

${m_con}<< GRANT_SLAVE

grant replication slave,reload,super on *.* to slave@'192.168.1.3' identified by 'slave';

GRANT_SLAVE

;;

3)

${m_con}<< GRANT_SLAVE

grant replication slave,reload,super on *.* to slave@'192.168.1.3' identified by 'slave';

GRANT_SLAVE

;;

esac

### 6

echo -e "\033[1m\E[32;40m开始执行操作步骤6\033[0m";

echo "--------------------------------------------";

${s_con} << SHOW_SLAVE

show processlist;

SHOW_SLAVE

read -p "日志都同步完了吧？[yes|no]" SLAVE_LOG_

case ${SLAVE_LOG_} in

yes|y) echo "好的，我们继续";;

*) echo "悲剧了，接下来的步骤手动执行吧，呵呵" && exit 1;;

esac

case ${MASTER} in

1)

${s_con} << STOP_SLAVE2

stop slave;

change master to master_host='192.168.1.3',master_user='slave',master_password='slave',master_port=3306;

start slave;

STOP_SLAVE2

;;

2)

${s_con} << STOP_SLAVE2

stop slave;

change master to master_host='192.168.1.3',master_user='slave',master_password='slave',master_port=3307;

start slave;

STOP_SLAVE2

;;

3)

${s_con} << STOP_SLAVE2

stop slave;

change master to master_host='192.168.1.3',master_user='slave',master_password='slave',master_port=3307;

start slave;

STOP_SLAVE2

;;

esac

### 7

echo -e "\033[1m\E[32;40m开始执行操作步骤7\033[0m";

echo -e "----------------------------------------";

echo "好的，现在我们来检查一下";

sleep 2;

${s_con} << CHECK_STATUS

show slave status\G;

CHECK_STATUS

echo ""

echo "bye"

echo ${MASTER} > log;

阅读(1496) | 评论(0) | 转发(0) |