主从切换实例(传统复制)

最新推荐文章于 2025-06-04 19:08:21 发布

原创最新推荐文章于 2025-06-04 19:08:21 发布 · 540 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

MySQL 专栏收录该内容

109 篇文章

订阅专栏

本文介绍了一种通过制造异常来实现数据库主从切换的方法，并提供了一个具体的脚本实例。该方法适用于需要快速切换主从库场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主从切换案例

M为主库,read write
S* 为从库,read only
现在要对M进行硬件维护,提升S1为主库,接管业务读写. M维护完成后作为从库,如下图
这里写图片描述

首先可以进行如下切换 (A)
这里写图片描述
再进行如下切换(B)

A切换步骤

首先在S1制造”错误”

set session sql_log_bin=0;
create table t_error_maker(id int);
set session sql_log_bin=1;
drop table t_error_maker;

通过在session级别关闭写入binlog,建表,开启写入binlog,删表制造异常, 当S11 S12 S13都执行到drop语句时,会报错停止sql_thread.
通过这种方式,可以让它们停止在同一个位置.
S12

show master status 获取File Position

S11 S13

stop slave;
change master到S12上
start slave;

S12

set global sql_slave_skip_counter=1;
start slave sql_thread;

B切换步骤

M 停止业务写操作

set global read_only=on; 此时只有super权限用户能写入
set global super_read_only=on;禁止super权限用户写
#这里没有通过修改用户密码的方式是因为修改用户密码对已经连接上来的用户无效

等M S1 S12 跑一致后(File Position相同) 停S12 sql_thread, 将业务写入操作接入S1
最后M

set global read_only=off;
set global super_read_only=off;
change master 到S12
start slave

S12

start slave sql_thread;

以上步骤通过脚本完成的话,可以做到对业务造成很小的影响
脚本实例如下,注释掉了B步骤,因为需要配合切换业务写入操作

# -*- coding: utf-8 -*-  
#coding=utf-8
import pymysql
import time
from warnings import filterwarnings
from progressive.bar import Bar
error_message=u"Error 'Unknown table 'fandb.t_error_maker'' on query. Default database: 'fandb'. Query: 'DROP TABLE `t_error_maker` /* generated by server */'"

def dec_progressive(func):
    def progess(*args, **kwargs):
        global i
        i += 100/9
        bar.cursor.restore()
        bar.draw(value=i)
        return func(*args, **kwargs)
    return progess

#创建数据库连接函数
@dec_progressive
def get_conn(host,port,user,password,db='performance_schema',charset='utf8'):
    return pymysql.connect(host=host, port=int(port), user=user,password=password,db=db,charset=charset)


#制造复制异常函数
@dec_progressive
def error_maker(host,port,user,password,db,charset):
    conn=get_conn(host=host, port=int(port), user=user,password=password,db=db,charset=charset)
    cursor = conn.cursor()
    cursor.execute("set session sql_log_bin=0;")
    cursor.execute("create table t_error_maker(id int)")
    cursor.execute("set session sql_log_bin=1")
    cursor.execute("drop table t_error_maker")
    cursor.close()
    conn.close()



#获取slave status
def get_slave_statue(conn,sql):
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("show slave status;")
    result = cursor.fetchone()
    return result
    cursor.close()



#获取master status
@dec_progressive
def get_master_status(conn):
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("show master status;")
    result = cursor.fetchone()
    return result
    cursor.close()


#执行change master语句
@dec_progressive
def change_master(conn,change_string):
    cursor = conn.cursor()
    cursor.execute("stop slave;")
    cursor.execute(change_string)
    cursor.execute("start slave;")
    cursor.close()


#修复slave错误
@dec_progressive
def repair_slave(conn):
    cursor = conn.cursor()
    cursor.execute("set global sql_slave_skip_counter=1;")
    cursor.execute("start slave sql_thread;")
    cursor.close()  

#设置read only
@dec_progressive
def set_read_only(conn,switch):
    cursor = conn.cursor()
    if switch == 'on':
        cursor.execute("set global read_only=on;")
    elif switch == 'off':
        cursor.execute("set global read_only=off;")
    cursor.close()

#启停 sql_thread
@dec_progressive
def set_sql_thread(conn,switch):
    cursor = conn.cursor()
    if switch == 'off':
        cursor.execute("stop slave sql_thread;")
    elif switch == 'on':
        cursor.execute("start slave sql_thread;")
    cursor.close()

#判断 39 40 41是否都因为drop t_error_maker停止
@dec_progressive
def get_error_status():
    while True:
        Last_SQL_Error_39 = get_slave_statue(conn39,'show slave status;')['Last_SQL_Error']
        Last_SQL_Error_40 = get_slave_statue(conn40,'show slave status;')['Last_SQL_Error']
        Last_SQL_Error_41 = get_slave_statue(conn41,'show slave status;')['Last_SQL_Error']
        if Last_SQL_Error_39 == Last_SQL_Error_40 == Last_SQL_Error_41 == error_message:
            break
        else:
            time.sleep(1)

if __name__ == '__main__':
    MAX_VALUE = 100
    bar = Bar(max_value=MAX_VALUE, fallback=True)
    bar.cursor.clear_lines(2)
    bar.cursor.save()
    i=0

    #不显示MySQL的warning
    filterwarnings('ignore',category=pymysql.Warning)
    #连接3306 制造复制异常函数
    print(u"连接3306 制造复制异常函数")
    error_maker(host='172.16.65.36', port=3306, user='root',password='mysql',db='fandb',charset='utf8')
    conn39 = get_conn('10.0.1.39',3306,'root','mysql')
    conn40 = get_conn('10.0.1.40',3306,'root','mysql')
    conn41 = get_conn('10.0.1.41',3306,'root','mysql')

    #判断 39 40 41是否都因为drop t_error_maker停止
    print(u"判断 39 40 41是否都因为drop t_error_maker停止")
    get_error_status()

    #获取40 master status 以供39 41切换
    print(u"获取40 master status 以供39 41切换")
    master_status_40 = get_master_status(conn40)
    File_40,Position_40 = master_status_40['File'],master_status_40['Position']

    change_string = """
    change master to 
    master_host='10.0.1.40',
    master_port=3306,
    master_user='repl',
    master_password='repl',
    master_log_file='%s',
    master_log_pos=%d;
    """ % (File_40,Position_40)

    #39 41切换到40
    print(u"39,41切换到40")
    change_master(conn39,change_string)
    print(u"39切换到40成功")
    change_master(conn41,change_string)
    print(u"41切换到40成功")
    #修复40 slave
    print(u"修复40 slave")
    repair_slave(conn40)

    # conn35 = get_conn('172.16.65.35',3306,'root','mysql')
    # conn36 = get_conn('172.16.65.36',3306,'root','mysql')

    # #35 设置read only
    # set_read_only(conn35,switch='on')

    # #判断35 36 40 是否同步
    # while True:
    #   res35 = get_slave_statue(conn35,'show master status;')
    #   res36 = get_slave_statue(conn36,'show slave status;')
    #   res40 = get_slave_statue(conn40,'show slave status;')
    #   File_35,Position_35 = res35['File'],res35['Position']
    #   File_36,Position_36 = res36['Relay_Master_Log_File'],res36['Exec_Master_Log_Pos']
    #   File_40,Position_40 = res40['Relay_Master_Log_File'],res40['Exec_Master_Log_Pos']
    #   if File_35 == File_36 == File_40 and Position_35 == Position_36 == Position_40:
    #       break
    #   else:
    #       time.sleep(1)

    # #停40 sql_thread
    # set_sql_thread(conn40,switch='off')

    # #写接入36

    # #35 read_only=off
    # set_read_only(conn35,switch='off')

    # master_status_40 = get_master_status(conn40)
    # File_40,Position_40 = master_status_40['File'],master_status_40['Position']

    # change_string = """
    # change master to 
    # master_host='10.0.1.40',
    # master_port=3306,
    # master_user='repl',
    # master_password='repl',
    # master_log_file='%s',
    # master_log_pos=%d;
    # """ % (File_40,Position_40)

    # change_master(conn35,change_string)

    # #起40 sql_thread
    # set_sql_thread(conn40,switch='on')

    bar.cursor.restore()
    bar.draw(value=100)