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

阅读量

主从切换案例

M为主库,read write
S* 为从库,read only
现在要对M进行硬件维护,提升S1为主库,接管业务读写. M维护完成后作为从库,如下图
img
首先可以进行如下切换 (A)
img
再进行如下切换(B)
img

A切换步骤

首先在S1制造”错误”

1
2
3
4
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
1
show master status 获取File Position

S11 S13
1
2
3
stop slave;
change master到S12上
start slave;

S12
1
2
set global sql_slave_skip_counter=1;
start slave sql_thread;

B切换步骤

M 停止业务写操作

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

等M S1 S12 跑一致后(File Position相同) 停S12 sql_thread, 将业务写入操作接入S1
最后M
1
2
3
4
set global read_only=off;
set global super_read_only=off;
change master 到S12
start slave

S12
1
start slave sql_thread;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
# -*- 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_status(conn,sql):
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql)
    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_status(conn39,'show slave status;')['Last_SQL_Error']
        Last_SQL_Error_40 = get_slave_status(conn40,'show slave status;')['Last_SQL_Error']
        Last_SQL_Error_41 = get_slave_status(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)
    #连接36 制造复制异常函数
    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_status(conn35,'show master status;')
    #   res36 = get_slave_status(conn36,'show slave status;')
    #   res40 = get_slave_status(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)

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :