mysql数据库主从切换IP的python脚本

发布时间:2019-09-19编辑:脚本学堂
分享一例python脚本,实现mysql数据库的主从正常切换IP操作,有需要的朋友参考下。

脚本功能:
mysql/ target=_blank class=infotextkey>mysql数据库主从在正常情况下,因某种原因需要切换IP的情况下使用,主要通过内网切换公网IP。

代码:
 

复制代码 代码示例:
#!/usr/bin/env python
#site: www.jb200.com
# -*- coding: utf-8 -*- 
#script used in Slave Server for change server ip 
import os,sys,time,socket,paramiko,MySQLdb 
 
def mysql_connect(ip,sql):#数据库连接 
    try: 
        conn = MySQLdb.connect(host = ip,user = 'repl',passwd = 'VQrtetr8',connect_timeout=5) 
        cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) 
        cursor.execute(sql) 
        alldata = cursor.fetchall() 
        return alldata 
    except MySQLdb.Error,e: 
        return e.args[0] 
 
def ssh_conm(conm):#连接主库服务器并执行命令 
    host = mysql_connect('127.0.0.1',"show slave status")[0]["Master_Host"] 
    #stat = mysql_chcek(host) 
    username='root'
    password = '3141315'
    port = 63008
    s=paramiko.SSHClient() 
    s.set_missing_host_key_policy(paramiko.AutoAddPolicy()) 
    s.connect(host,port,username,password) 
    stdin,stdout,stderr=s.exec_command(conm) 
    result = stdout.read() 
    s.close() 
    return result 
 
def log_pos():#获取当前主从的同步位置 
    result = mysql_connect('127.0.0.1',"show slave status") 
    host = result[0]["Master_Host"] 
    s_log = result[0]["Relay_Master_Log_File"].split('.')[1] 
    s_pos = result[0]["Exec_master_log_pos"] 
    result = mysql_connect(host,"show master status") 
    if result == 2003:#这里是应对mysql数据库无法连接,可以选择据继续和终止脚本 
        choose = raw_input('Can not connect Master,Do you go on ?  (y or n) :') 
        if choose == 'y': 
            return 0,0,0,0
        else: 
            sys.exit(1) 
    m_log = result[0]["File"].split('.')[1] 
    m_pos = result[0]["Position"] 
    return s_log,m_log,s_pos,m_pos 
 
def show():#循环5次判断主从同步是否一致 
    print "Check Master-Slave now..."
    for i in range(5): 
        s_log,m_log,s_pos,m_pos = log_pos() 
        if s_log == m_log and s_pos == m_pos: 
            print "Slave and Master is OK."
            break
        else: 
            print "Slave and Master not OK,please wait moment."
            if i == 4: 
                #print "Must be show Replcation Error,Quit this script now !" 
                choose = raw_input('Master-slave not synchronous,Do you go on ?  (y or n) :') 
                if choose == 'y': 
                    break
                else: 
                    sys.exit(1) 
            i = i + 1
            time.sleep(5) 
 
def replace(): 
    #显示当前主从的IP,并检测主从3306是否存在 
    print "Before switching IP:"
    s_ip = os.popen("cat /etc/sysconfig/network-scripts/ifcfg-eth0|grep IPADDR|linuxjishu/13830.html target=_blank class=infotextkey>awk -F '=' '{print $2}'").read().split()[:1][0] 
    print "s_ip: " + s_ip 
    conm = "cat /etc/sysconfig/network-scripts/ifcfg-eth0|grep IPADDR|awk -F '=' '{print $2}'"
    m_ip = ssh_conm(conm).split()[:1][0] 
    print "m_ip: " + m_ip 
    print
    #1:停用主的网卡 
    conm = "ifdown eth0"
    ssh_conm(conm) 
    #2: 判断主从同步是否一致,如果一致则切换,不一致则手动查看问题所在(不一致的情况重复检测5次,间隔5s,检测五次依然不同步可以选择停止脚本的执行或继续执行) 
    show() 
    #3:修改从IP为主的IP 
    os.popen("sed -i 's/%s/%s/' /etc/sysconfig/network-scripts/ifcfg-eth0" % (s_ip,m_ip)) 
    print "Slave change IP is OK"
    #4:修改主IP为从的IP 
    conm = "sed -i 's/%s/%s/' /etc/sysconfig/network-scripts/ifcfg-eth0" % (m_ip,s_ip) 
    ssh_conm(conm) 
    print "Master change IP is OK"
    #5: 显示替换之后主从的IP 
    print
    print "After switching IP:"
    s_ip = os.popen("cat /etc/sysconfig/network-scripts/ifcfg-eth0|grep IPADDR|awk -F '=' '{print $2}'").read().split()[:1][0] 
    print "s_ip: " + s_ip 
    conm = "cat /etc/sysconfig/network-scripts/ifcfg-eth0|grep IPADDR|awk -F '=' '{print $2}'"
    m_ip = ssh_conm(conm).split()[:1][0] 
    print "m_ip: " + m_ip 
    #6: 停止主从同步 
    mysql_connect("127.0.0.1","slave stop") 
    print "Slave stop OK"
    #7: 重启用主从的网卡 
    print
    print "Network service restart now,please wait ......"
    conm = "ifup eth0"
    ssh_conm(conm) 
    os.system("ifdown eth0") 
    os.system("ifup eth0") 
    print "Network reatrt OK !"
 
if __name__ == "__main__": 
    replace()