手把手教学-MySQL主从复制架构转换MGR架构手动版

1、手把手教学-MySQL主从复制架构转换MGR架构（手动版）环境信息IPportroleinfo3316nodelmaster3316node2slavel3316node3Slave2CentOS Linux release 7.6.1810 (Core)MySQL Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)MySQL RouterVer8.0.20 for Linux on x86_64 (MySQL Community- GPL)MySQL Shell Ver 8.0.20 for Linu

2、x on x86_64-for MySQL 8.0.20 (MySQLCommunity Server(GPL)夕软件位置在三个节点上部署好MySQL、MySQL Router、MySQL Shell 夕搭建复制环境，并开启增强半同步所有节点配置rootlocalhost (none)set global super_read_only=0; Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)create user 1 rep*192,168.188.%* identified by 1 rep3 rows i n set (0.0

3、0 sec)rootlocalhost (none)夕一些tips夕参数文件！手动转换为MGR与通过MySQL Shell转换的最大区别是，后者会自动通过set persist方式将变 更写到mysqld-f文件中，而手动操作需要注意这一点。上述实验完全没编辑f，如果使用set global 在MGR三节点再次冷启动的时候，MGR的配置参数就没了，无法启动 MGR。解决方法是：.在配置过程中用set persist来代替set global，持久化保存配置.如果已经手快重启了全部节点清空了临时配置那么可以使用set persist再次设定一遍设定好后理论上可以直接生效，启动GR。夕 sha2_

4、password 魔咒我通过set global配置后，重启了一下节点，再进行set persist持久化配置后,启动MGR后，maste0顺利online，但是在做node2加入GR时，一直处于RECOVERING检查errlog后发现：2020-05-15T14:35:46.869802+08:00 212020-05-15T14:35:46.906422+08:00 28System MY-010597 Repl fCHANGE MASTWarni ng my-010897 Repl Stori ng MySERROR MY-010584 ReplERROR MY-011582 Repl

5、ERROR MY-011583 ReplSiave I/O forPl ugi n group_Plugi n group_2020-05-15T14:35:46,907876+08:00 282020-05-15T14:35:46.923832+08:00 212020-05-15T14:35:46,923887+08:00 21检查 performance_schema.replication_connection_statusrootlocalhost (none)select * from performance_schema.replication_connec八 八八八八 八八八八

6、八 C 八八八 八八八八 八 八 八八八 A ACHANNEL_NAME: group_replication_recoveGROUP_NAME：SOURCE_UUID：THREAD ID：NULLSERVICE_STATE：OFFCOUNT RECEIVED HEARTBEATS：LAST_HEARTBEAT_TIMESTAMP：0000-00-00 00:00:00.0000RECEIVED_TRANSACTION_SET：LAST_ERROR_NUMBER： 2061LAST_ERROR_MESSAGE: error connect!ng to mast3 rows i n set (0

7、.01 sec)退化至recovering斗犬态，遇至U连接问题，尝试在change master上增力口：rootlocalhost (none)change masterERROR 3139 (HY000)： CHANGE MASTER withto master_user= * rep*,master_password=1the given parameters cannot be perfor这就尴尬了。临时解决方法rootms82 # mysql -h 1 -P 3316 -urep -prep .188 . 81 (none)exi trootms82 #

8、 mysql -S /data/mysql/mysql3316/tmp/mysql.sock rootlocalhost (none)stop group_replication;Query OK, 0 rows affected (4.75 sec)rootlocalhost (none)start group_repli cati on; Query OK, 0 rows affected (5.75 sec)rootlocalhost (none)select * from performance_schema.replication_connec* i. row *CHANNEL_NA

9、ME：GROUP.NAME：SOURCE_UUID： 29ea8b7f-966d-llea-937c-THREAD_ID： NULLSERVICE_STATE： OFFCOUNT_RECEIVED_HEARTBEATS： 0LAST_HEARTBEAT_TIMESTAMP： 0000-00-00 00:00:00.0000RECEIVED_TRANSACTION_SET: 29ea8b7f-966d-llea-937c-3260d70c-966e-llea-ba8b-0242c0a8bc51:1-343LAST_ERROR_NUMBER： 0LAST_ERROR_MESSAGE：LAST_ER

10、ROR_TIMESTAMP： 0000-00-0000:00:00.0000LAST.QU EU ED_TRAN SACTION：00:00:00,000000:00:00.000000:00:00.000000:00:00,000000:00:00.0000LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP： 0000-00-00LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP： 0000-00-00LAST_QUEUED_TRANSACT工ON_START_QUEUE_T工MESTAMP：

11、0000-00-00LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP： 0000-00-00QUEUEING_TRANSACTION：QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP： 0000-00-00QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP：0000-00-0000：00：00.0000QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP：0000-00-00 00:00:00.0000* 2 r o w *CHANNEL

12、_NAME: group_repli cation_appli eGROUP_NAME：SOURCE_UUID：3260d70c-966e-llea-ba8b-3260d70c-966e-llea-ba8b-THREAD_ID： NULLSERVICE_STATE： ONCOUNT_RECEIVED_HEARTBEATS：LAST_HEARTBEAT_TIMESTAMP：RECEIVED_TRANSACTION_SET：3260d70c-966e-llea-ba8b-0242c0a8bc51:1-781:78700000-00-00 00:00:00.000029ea8b7f-966d-lle

13、a-937c-LAST_ERROR_NUMBER：LAST_ERROR_MESSAGE：LAST_ERROR_TIMESTAMP：LAST_QU E U ED_TRAN SACTION：0000-00-00 00:00:00,00003260d70c-966e-llea-ba8b-LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP： LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP： LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP： LAST_QUEU

14、ED_TRANSACTION_END_QUEUE_TIMESTAMP： QUEUEING_TRANSACTION： QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP： QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP： QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP：LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP： LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP：

15、 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP： LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP： QUEUEING_TRANSACTION： QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP： QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP： QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP：0000-00-000000-00-002020-05-152020-05-150000-

16、00-000000-00-000000-00-00* * * 3 r o w *CHANNEL_NAME：GROUP_NAME：SOURCE_UUID：THREAD_ID：SERVICE_STATE：COUNT_RECEIVED_HEARTBEATS：LAST_HEARTBEAT_TIMESTAMP：RECEIVED_TRANSACTION_SET：LAST_ERROR_NUMBER：LAST_ERROR_MESSAGE：LAST_ERROR_TIMESTAMP：LAST_QUEUED_TRANSACTION：LAST_QUEUED_TRANSACT工ON_OR工G工NAL_COMM工T_T工

17、MESTAMP： LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP： LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP： LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP：QUEUEING.TRANSACTION：QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP： QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP： QUEUEING_TRANSACTION_START_QUE

18、UE_TIMESTAMP：3 rows i n set (0.00 sec)0000141400000000:00.000000:00.000038:54,721838:54.721800:00.000000:00,000000:00.0000group_repli cati on_recoveNULLOFF 00000-00-000000-00-000000-00-000000-00-000000-00-000000-00-000000-00-000000-00-000000-00-0000:00:00,000000:00:00.0000000000000000000000000000.00

19、0000.000000,000000.000000:00.000000:00,000000:00.0000正规军解决方法SET GLOBAL group_repli cati on_recovery_use_ssl = ON ;SET GLOBAL group_repl i cati on_recovery_get_publ i c_key = 1;#已合并至!J操作中SET GLOBAL group_repli cation_recovery_publi c_key_path = * path to RSA publi c夕MGr冷启动将三节点全部关掉 mysql shutdown ;启动n

20、odelrootms81 # mysqld -defaults-fi1e=/data/mysql/mysql3316/f &rootms81 # mysql -S /data/mysql/mysql3316/tmp/mysql.sockrootlocalhost (none)set global group_repli cati on_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)start group_repli cation;Query OK, 0 rows affected (3.16

21、 sec)rootlocalhost (none)set global group_repli cati on_bootstrap_group=OFF;Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)seiect * from performance_schema.repli cati on_group_m+I CHANNEL_NAME| MEMBER_ID| MEMBER.+I group_repli cati on_appli er | f78a6902-9679-llea-bl36-0242c0a8bc51 | ms81+r

22、ows i n set (0.01 sec)启动node2rootms82 # mysqld -defaults-fi 1 e=/data/mysql/mysql3316/f & rootms82 # mysql -S /data/mysql/mysql3316/tmp/mysql.sock rootlocalhost (none)start group_repli cation;Query OK, 0 rows affected (3.45 sec)rootlocalhost (none)select * from performance_schema.repli cati on_group

23、_m+I CHANNEL.NAME| MEMBER.HD| MEMBER.+I group_repli cati on_appli er | f78a6902-9679-llea-bl36-0242c0a8bc51 | ms81I group_repli cati on_appli er | faaab4c3-9679-llea-896f-0242c0a8bc52 | ms82+rows i n set (0.00 sec)-同理，启动node3rootms83 # mysql d -defaul ts-fi 1 e=/data/mysql/mysql3316/f & rootms83 # m

24、ysql -S /data/mysql/mysql3316/tmp/mysql.sock rootlocalhost (none)start group_replication;Query OK, 0 rows affected (3.45 sec)rootlocalhost (none)select * from performance_schema.repli cati on_group_m+I CHANNEL_NAME| MEMBER_ID| MEMBER.+ TOC o 1-5 h z Igroup_repli cati on_appli er|f78a6902-9679-llea-b

25、l36-0242c0a8bc51|ms81Igroup_repli cati on_appli er|faaab4c3-9679-llea-896f-0242c0a8bc52|ms82Igroup_repli cati on_appli er|fb3 58b40-9679-llea-94cb-0242c0a8bc53|ms833 rows i n set (0.01 sec)全文完Query OK, 0 rows affected (0.02 sec) rootlocalhost (none)grant replication slave on to 1 rep192.168.188 Quer

26、y OK, 0 rows affected (0.02 sec)rootlocalhost (none)i nstal1Query OK, 0 rows affected (0.01rootlocalhost (none)i nstal1Query OK, 0 rows affected (0.01rootlocalhost (none)i nstal1 Query OK, 0 rows affected (0.02plugi n rpl_semi_sync_slave soname 1 semi sync_ sec) plugi n rpl_semi_sync_master soname 1

27、 semi sync sec) master节点配置rootlocalhost (none)set global rpl_semi_sync_master_enabled=ON; Query OK, 0 rows affected (0.01 sec) TOC o 1-5 h z root!ocalhost (none)show global variables like;+I Variable_name| Value+|rpl_semi_sync_master_enabled|ONIrpl_semi_sync_master_ti meout|10000Irpl_semi_sync_maste

28、r_trace_level|32I rpl_semi_sync_master_wait_for_slave_count | 1Irpl_semi_sync_master_wai t_no_slave|ONIrpl_semi_sync_master_wai t_poi nt|AFTER_SYNCIrpl_semi_sync_slave_enabled|OFFIrpl_semi_sync_slave_trace_level|32 8 rows i n set (0.00 sec) rootlocalhost (none)reset master; Query OK, 0 rows affected

29、 (0.04 sec)slave节点配置rootlocalhost (none)set global rpl_semi_sync_slave.enabled=ON;Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)change master to master_host=*1,master QueryOK, 0 rows affected, 2 warnings (0.04 sec) rootlocalhost (none),reset master; Query OK, 0 rows affected (

30、0.04 sec)slave启动复制rootlocalhost (none)start siave; Query OK, 0 rows affected (0.03 sec) rootlocalhost (none)show siave status G* 1.Siave_IO_State: Master_Host: Master.liser: 3316Connect_Retry: Master_Log_Fi1e: Read_Master_Log_Pos: Relay_Log_Fi1e:Relay_Log_Pos:row Waiting for master to send eventrep

31、Master_Port:60mysql-bi n.000001155ms82-relay-bi n.000002369Relay_Master_Log_Fi1e: mysql-bi n.000001 Siave_lO_Runni ng: YesSi ave_SQL_Runni ng: YesSiave_SQL_Running_State: Slave has read all relay log; waiting for mor Master_Retry_Count: 864001 row i n set (0.00 sec) master查看半同步状态root!ocalhost (none)

32、show global status like %semi%； TOC o 1-5 h z 4-4-+Variable_name| ValueRpl_semi_sync_master_clients|2I Rpl_semi_sync_master_net_avg_wai| 0IRpl_semi_sync_master_net_wait_time|0IRpl_semi_sync_master_net_waits|0IRpl_semi_sync_master_no_times|0IRp1_semi_sync_master_no_tx|0IRpl_semi_sync_master_status|ON

33、IRpl_semi_sync_master_timefunc_failures|0IRpl_semi_sync_master_tx_avg_wai t_ti me|0I Rpl_semi_sync_master_tx_wai| 0IRpl_semi_sync_master_tx_wai ts|0IRpl_semi_sync_master_wait_pos_backtraverse|0IRpl_semi_sync_master_wait_sessions|0IRpl_semi_sync_master_yes_tx|0IRp1_semi_synjsl ave_status|OFF15 rows i

34、 n set (0.00 sec)夕模拟业务，使用脚本产生事务建表rootlocalhost (none)create database kk;Query OK, 1 row affected (0.03 sec) rootlocalhost (none)use kk Database changed rootlocalhost kkcreate tabi e kl ( i d i nt auto_i ncrement pri mary key , dt Query OK, 0 rows affected (0.05 sec)开启一个session，运行脚本产生事务rootms81 # whi

35、1e : ; do echo ni nsert i nto kk.kl(dtl) values(*duangduangd夕手动配置MGr夕配置Master，将Master转为MGr配置参数root!ocalhost kki nstal1 plugi n group_repli cati on soname 1group_repli cati Query OK, 0 rows affected (0.03 sec)bi nlog_checksum=NONE; sec)rootlocalhost kkset persi st Query OK, 0 rows affected (0.02rootl

36、ocalhost kkset persi st Query OK, 0 rows affected (0.00rootlocalhost kkset persi st Query OK, 0 rows affected (0.00transact!on_wri te_set_extracti on=XXHASH64; sec)rootlocalhost kkselect uuid(); TOC o 1-5 h z +I uuid()I+4-|3260d70c-966e-llea-ba8b-0242c0a8bc51 |+1 row i n set (0.00 sec)rootlocalhost

37、kkset persi st group_repli cation_group_name=13260d70c-966e Query OK, 0 rows affected (0.00 sec)rootlocalhost kkset persi st group_repli cati on_local_address=n192.168 ,188Query OK, 0 rows affected (0.00 sec)rootlocalhost kkset persi st Query OK, 0 rows affected (0.00rootlocalhost kkset persi st Que

38、ry OK, 0 rows affected (0.00group_repli cati on_group_seeds=H sec)#也要加上这个，具体文末SET persi st group_repli cati on_recovery_get_publi c_key = 1;rootlocalhost kkset persist group replication bootstrap group=off;Query OK, 0 rows affected (0.00 sec)rootlocalhost kkset persi st group_repli cati

39、 on_start_onboot=off; Query OK, 0 rows affected (0.00 sec)rootlocalhost kkset global group_repli cati on_bootstrap_group=on;Query OK, 0 rows affected (0.00 sec)rootlocalhost kkstart group_repli cati on;Query OK, 0 rows affected (3.36 sec)rootlocalhost kkset global group_repli cati on_bootstrap_group

40、=off;Query OK, 0 rows affected (0.00 sec)rootlocalhost kkselect * from performance_schema.repli cati on_group_membe+| CHANNEL_NAME| MEMBER_ID| MEMBER.+I group.repli cati on_appli er | 29ea8b7f-966d-llea-937c-0242c0a8bc51 | ms81+1 row i n set (0.01 sec)此时发现发生事务的session出现了提醒rootms81ERROR 1290ERROR 129

41、0ERROR 1290rootms81ERROR 1290ERROR 1290ERROR 1290# whi1e : ; do echo ni nsert i nto kk.kl(dtl) values(fduangduangd (HY000) at line 1: The MySQL server i s runni ng with the -super- r (HY000) at line 1: The MySQL server i s runni ng wi th the -super-r (HY000) at line 1: The MySQL server i s runni ng

42、wi th the -super-r夕去配置slavel，转换为MGrrootlocalhost (none)i nstal1 plugi n group_repli cation soname 1group_repli Query OK, 0 rows affected (0.01 sec)rootlocalhost (none)set persi st bi nlog_checksum=NONE;Query OK, 0 rows affected (0.03 sec)rootlocalhost (none)set persi st transaction_wri te_set_extrac

43、tion=xxHASH6 Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)set persi st group_repli cation_group_name=13260d70c-9Query OK, 0 rows affected (0.01 sec)root!ocalhost (none)set persi st group_repli cation_local_address=M192 ,168 Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)set persi

44、 st group_repli cation_group_seeds=M192.168.1 Query OK, 0 rows affected (0.00 sec)也要加上这个，具体文末SET persi st group_repli cati on_recovery_get_publi c_key = 1;rootlocalhost (none)set persi st group_repli cation_bootstrap_group=off; Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)set persi st gro

45、up_repli cation_start_on_boot=off; Query OK, 0 rows affected (0.00 sec)rootlocalhost (none)start group_replication;ERROR 3092 (HY000): The server is not configured properly to be an active me rootlocalhost (none)select * from performance_schema.repli cati on_group_m +I CHANNEL_NAME| MEMBER_ID| MEMBE

46、R.+I group_repli cati on_appli er | 2cbcfaa5-966d-llea-8707-0242c0a8bc52 | ms82 II+1 row i n set (0.01 sec)rootlocalhost (none)stop group_repli cati on; Query OK, 0 rows affected (4.78 sec)rootlocalhost (none)change master to master_user=* rep1,master_password=* Query OK, 0 rows affected, 2 warnings

47、 (0.03 sec)rootlocalhost (none)start group_repli cation; Query OK, 0 rows affected (3.88 sec)rootlocalhost (none)select * from performance_schema.repli cati on_group_m+I CHANNEL.NAME| MEMBER_ID| MEMBER.+I group_repli cati on_appli er | 29ea8b7f-966d-llea-937c-0242c0a8bc51 | ms81I group_repli cati on

48、_appli er | 2cbcfaa5-966d-llea-8707-0242c0a8bc52 | ms82+2 rows in set (0.00 sec)夕如法炮制，改造slave2在改造之前，我突然想到，现有的架构成为了 ： nodel(master)node2(slavel)为MGR， node3(slave2)是nodel(master)的从库，那么检查一下当前三个节点的情况：nodel:rootlocalhost kkselect count(-) from kk.kl;+4-count (*)4-456 |1 row i n set (0.00 sec) rootlocalho

49、st kkshow master status ;I Fi 1 e| Posi ti on | Bi nl og_Do_DB | Bi nl og_lgnore_DB | Executed_G+I mysql-bin.000002 |154142 | 3260d70c-9f78a6902-9679-llea-bl36-0242c0a8bc51:1-111 | +1 row i n set (0.00 sec) TOC o 1-5 h z node2: rootlocalhost (none)select count(*) from kk.kl; +I count(*) |+I 456 | +1

50、 row i n set (0.00 sec)rootlocalhost (none)show master status ; +I File| Posi tion | Bi nlog_Do_DB | Bi nlog_lgnore_DB | Executed_G+I mysql-bin.000002 |109956 | 3260d70c-9f78a6902-9679-llea-bl36-0242c0a8bc51:1-111 | +row i n set (0.00 sec)* L#注意，node2的IO 一 SQL THREAD没有运行，但是Executed_Gti d_Set是跟进的噢 ro

51、otlocalhost (none)show siave statusGrOW X X XX X X X X X X K X X X X X X X XX XXslave_io_state:Master_Host:Master_user:Master_Port:Connect_Retry:Master_Log_Fi1e:Read_Master_Log_Pos:Reiay_Log_Fi1e:Re I ay_Log_Pos:Reiay_Master_Log_Fi 1 e:Siave_lO_Runni ng:Siave_SQL_Runni ng:rep331660mysql-bi n.0000027

52、4606ms82-relay-bi n.000004 74820mysql-bi n.000002NoNoMaster_Server_ld: 813316Master_UUID： f78a6902-9679-llea-bl36-0242c0a8bc51Master_lnfo_Fi1e: mysql.siave_master_infoRetri eved_Gti d_Set: 3260d70c-966e-llea-ba8b-0242c0a8bc51:1-121, f78a6902-9679-llea-bl36-0242c0a8bc51:l-lllExecuted_Gtid_Set: 3260d7

53、0c-966e-llea-ba8b-0242c0a8bc51:1-350, f78a6902-9679-llea-bl36-0242c0a8bc51:l-lllAuto.Position: 11 row i n set (0.00 sec) node3:rootlocalhost (none)select count(*) from kk.kl; TOC o 1-5 h z +I count(*) |+I 456 |+1 row i n set (0.00 sec) rootlocalhost (none)show master status ;+I Fi 1 e| Posi ti on |

54、Bi nlog.Do.DB | Bi nlog_lgnore_DB | Executed.G+I mysql-bin.000001 |169340 | 3260d70c-9f78a6902-9679-llea-bl36-0242c0a8bc51:1-111 | +1 row i n set (0.00 sec)rootlocalhost (none)show siave status G1.Siave_IO_State: Master_Host: Master_user: 3316Connect_Retry: Master_Log_Fi1e: Read_Master_Log_Pos:.1.u.

55、 .1. .1. .1. J.J. u.row 、八八八、八、八、八、八、八八、八八八八八、waiting for master to send eventrep Master_Port:60mysql-bi n.000002154142 Reiay_Log_Fi1e:ms83-relay-bi n.000004154356mysql-bi n . 000002YesYes154356mysql-bi n . 000002YesYesRei ay_Log_Pos:Reiay_Master_Log_Fi1e:Siave_lO_Runni ng:Siave_SQL_Runni ng:Master_UUID： f78a6902-9679-llea-bl36-0242c0a8bc51 Master_lnfo_Fi1e: mysql.siave_master_i nfo Siave_SQL_Runni ng.State: Si ave has read all relay 1og; w