MySQL：主从切换
免费下载
https://postgres.fun/20150302160150.html
一环境信息
主库192.168.2.37/3306主机名db1
备库192.168.2.38/3306主机名db2
版本5.6.20
备注：主从节点mysql安装略，Replication安装略。
二主从切换
主，备节点都要创建Replication用户
1
2
createuser'rep1'@'192.168.2.%'identifiedby'rep1abcd1243d';
GRANTREPLICATIONSLAVEON*.*TO'rep1'@'192.168.2.%';
查询从库状态
1 root@localhost:francs>showslavestatusG
查询主库状态
1
2
3
4
5
6
7
root@localhost:francs>showslavehosts;
+-----------+-------------------+------+-----------+------------------
--------------------+
|Server_id|Host|Port|Master_id|Slave_UUID|
+-----------+-------------------+------+-----------+------------------
--------------------+
|2|192.168.2.38(db2)|3306|1|ad397a06-7c56-11e4-b2fb-
000c29dcb3b5|
+-----------+-------------------+------+-----------+------------------
--------------------+
1rowinset(0.00sec)
从库:停止IO_THREAD线程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
root@localhost:francs>stopslaveIO_THREAD;
root@localhost:francs>showslavestatusG
*1.row*
Slave_IO_State:
Master_Host:192.168.2.37
Master_User:rep1
Master_Port:3306
Connect_Retry:60
Master_Log_File:bin-log.000001
Read_Master_Log_Pos:362
Relay_Log_File:db2-relay-bin.000002
Relay_Log_Pos:523
Relay_Master_Log_File:bin-log.000001
Slave_IO_Running:No
Slave_SQL_Running:Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
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
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:362
Relay_Log_Space:694
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:NULL
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:1
Master_UUID:0c130d47-22bb-11e4-aaaa-000c2986ac80
Master_Info_File:mysql.slave_master_info
SQL_Delay:0
SQL_Remaining_Delay:NULL
Slave_SQL_Running_State:Slavehasreadallrelaylog;waiting
fortheslaveI/Othreadtoupdateit
Master_Retry_Count:86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:0
1rowinset(0.00sec)
激活从库(从库上操作)
1
2
3
4
5
6
7
8
9
10
11
root@localhost:francs>stopslave;
root@localhost:francs>resetmaster;
root@localhost:francs>resetslaveall;
root@localhost:francs>showbinarylogs;
+----------------+-----------+
|Log_name|File_size|
+----------------+-----------+
|bin-log.000001|120|
+----------------+-----------+
1rowinset(0.00sec)
备注：resetslaveall命令会删除从库的replication参数，之后showslavestatusG的信
息返回为空。
将原来主库变为从库
of 4
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论