由于mysql主主会出现些问题,比如幻读等,于是主从同步出现主故障时,需主从互切,刚好正在学python就用python写了个主从互切脚本,开始也有用shell写。
主要步骤为:
从上:stop slave
flush logs
再利用 show master status\G取二进制日志和位置为变量
主上:stop slave
然后用change master来切到从(利用上面的变量)
start slave
然后show slave status\G判断
下面为shell写的主从互切脚本 (在从mysql上操作,可进一步研究)#!/bin/bash
#coding=utf-8
#author=min
#date=2015.10.16
#use:change slave to master when master was bad
master_ip=192.168.1.136 # source master ip
slave_ip=192.168.1.134 # source slave ip
root_passwd=****** # root"password for logining myself mysql
user=paixian # user to login master"s mysql
passwd=******** # password for user to login master"s mysql
master_user=myslave # user to change master for master"s mysql
master_password=******** # user"s password to change master for master"s mysql
master_host=$(mysql -uroot -p$root_passwd -e "show slave status\G"|grep Master_Host|awk -F": " "{print $2}") 2>/dev/null #find the master host now
#echo $master_host #to test the master host
[ $master_host = $master_ip ] && slave_host=$slave_ip || slave_host=$master_ip 2>/dev/null #definition the slave host
#echo $slave_host #to test the slave host
mysql -uroot -p$root_passwd -e "stop slave" 2>/dev/null #stop the mysql slave thread
#echo $?1 #test the stop slave is yes or ok
mysql -uroot -p$root_passwd -e "flush logs" 2>/dev/null #flush the binlogs
#echo $?2 #test flush logs is ok or no
bin_log=$(mysql -uroot -p$root_passwd -e "show master status\G"|grep File|awk -F": " "{print $2}") 2>/dev/null #find the master_log_file
#echo $bin_log #test the master_log_file
pos=$(mysql -uroot -p$root_passwd -e "show master status\G"|grep Position|awk -F": " "{print $2}") 2>/dev/null #find the master_log_pos
#echo $pos #test the master_log_pos
mysql -u$user -p$passwd -h$master_host -e "stop slave" 2>/dev/null #stop the mysql slave thread
#echo $?3 #03 ok 13 no to test the stop slave threa is ok?
mysql -u$user -p$passwd -h$master_host -e "change master to master_host="$slave_host",master_user="$master_user",master_password="$master_password",master_log_file="$bin_log",master_log_pos=$pos" 2>/dev/null #change slave to master
#echo $?4 #04 ok 14 no to test the change slave to master is ok?
mysql -u$user -p$passwd -h$master_host -e "start slave" 2>/dev/null #slave start slave thread
#echo $?5 #05 ok 15 no to test the start slave thread is ok?
mysql -u$user -p$passwd -h$master_host -e "show slave status\G" 2>/dev/null #slave show slave status
#echo $?6 #06 ok 16 no to test slave status
下面为python写的主从互切脚本(能在管理mysql的服务器上操作)#!/usr/sbin/python
# ^-^ coding: utf-8 ^-^
# ^-^ author: chen min ^-^
# ^-^ time: 2015-10-22 ^-^
import MySQLdb
slave=raw_input("please input slave_ip:")
if slave=="192.168.1.134":
master="192.168.1.136"
else:
master="192.168.1.134"
global slave,master
def getpos():
conn = MySQLdb.Connect(
host = "%s"%slave,
port = 3306,
db = "test",
user = "paixian",
passwd = "***********",
charset = "utf8"
)
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
try:
# print slave,master,type(slave)
cursor.execute("stop slave")
cursor.execute("flush logs")
sql = "show master status"
n = cursor.execute(sql)
a = cursor.fetchall()
bin_log = a[0].get("File")
pos = a[0].get("Position")
global bin_log,pos
conn.commit()
except Exception as e:
print e
finally:
cursor.close()
conn.close()
def changeslave():
conn1 = MySQLdb.Connect(
host = "%s"%master,
port = 3306,
db = "test",
user = "paixian",
passwd = "***********",
charset = "utf8"
)
cursor1=conn1.cursor(cursorclass=MySQLdb.cursors.DictCursor)
try:
# print bin_log,pos,slave,master
cursor1.execute("stop slave")
cursor1.execute("change master to master_host="%s", master_user="myslave", master_password="123123", master_log_file="%s", master_log_pos=%d"%(slave,bin_log,pos))
cursor1.execute("start slave")
m = cursor1.execute("show slave status")
m1 = cursor1.fetchall()
y = m1[0].get("Slave_IO_Running")
x = m1[0].get("Slave_SQL_Running")
# print x,y
if x=="Yes" and y=="Yes":
print "slave to master is ok"
conn1.commit()
except Exception as e:
print e
finally:
cursor1.close()
conn1.close
try:
getpos()
changeslave()
except Exception as e:
print e