MySQL高可用之MGR安装测试
程序员文章站
2022-05-20 11:43:03
Preface We've learned the machenism of MGR yesterday,Let's configurate an environment and have some test today.MGR can be installed as plugin like sem ......
Preface
We've learned the machenism of MGR yesterday,Let's configurate an environment and have some test today.MGR can be installed as plugin like semisynchronous replication.
Node information
| ID | IP | Hostname | Database | Port | Port of Seed | Server ID |
| 1 | 192.168.1.101 | zlm2 | MySQL 5.7.21 | 3306 | 33061 | 1013306 |
| 2 | 192.168.1.102 | zlm3 | MySQL 5.7.21 | 3306 | 33062 | 1023306 |
| 3 | 192.168.1.103 | zlm4 | MySQL 5.7.21 | 3306 | 33063 | 1033306 |
Configuration
1 ##Check "/etc/hosts" file on all servers and make sure the right mapping relationship of ip & hostname. 2 [root@zlm2 08:50:56 ~] 3 #cat /etc/hosts 4 127.0.0.1 zlm2 zlm2 5 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 6 #::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 7 192.168.1.100 zlm1 zlm1 8 192.168.1.101 zlm2 zlm2 9 192.168.1.102 zlm3 zlm3 10 192.168.1.103 zlm4 zlm4 11 12 ##Check the parameter in my.cnf which Group Replication needs on server zlm2. 13 [root@zlm2 08:51:06 ~] 14 #vim /data/mysql/mysql3306/my.cnf 15 ... -- Omitted the other parameter 16 #group replication -- These parameters beneath are reauired by Group Replication. 17 server_id=1013306 18 gtid_mode=ON -- Group Replication lies on GTID,so that it should be set "on". 19 enforce_gtid_consistency=ON 20 master_info_repository=TABLE 21 relay_log_info_repository=TABLE 22 binlog_checksum=NONE 23 log_slave_updates=ON -- Make sure the GTID information can be write into binary logs instead of mysql.gtid_executed table. 24 log_bin=binlog 25 binlog_format=ROW 26 transaction_write_set_extraction=XXHASH64 27 loose-group_replication_group_name="ed142e35-6ed1-11e8-86c6-080027de0e0e" -- This is UUID which can be generate by SELECT UUID(); 28 loose-group_replication_start_on_boot=off -- Only if you've finished configuration of Group Replication,then you can set it to "on". 29 loose-group_replication_local_address= "zlm2:33061" 30 loose-group_replication_group_seeds= "zlm2:33061,zlm3:33062,zlm4:33063" -- Candidate members of group,the port can be different from mysqld. 31 loose-group_replication_bootstrap_group=off -- Notice,it merely can be set to "on" in the member who has created the group and started first. 32 33 ##Restart mysqld and add user of Group Replication. 34 (root@localhost mysql3306.sock)[(none)]09:02:18>SET SQL_LOG_BIN=0; 35 Query OK, 0 rows affected (0.00 sec) 36 37 (root@localhost mysql3306.sock)[(none)]09:02:21>CREATE USER rpl_mgr@'%' IDENTIFIED BY 'rpl4mgr'; 38 Query OK, 0 rows affected (0.00 sec) 39 40 (root@localhost mysql3306.sock)[(none)]09:03:26>GRANT REPLICATION SLAVE ON *.* TO rpl_mgr@'%'; 41 Query OK, 0 rows affected (0.00 sec) 42 43 (root@localhost mysql3306.sock)[(none)]09:03:41>FLUSH PRIVILEGES; 44 Query OK, 0 rows affected (0.00 sec) 45 46 (root@localhost mysql3306.sock)[(none)]09:03:47>SET SQL_LOG_BIN=1; 47 Query OK, 0 rows affected (0.00 sec) 48 49 (root@localhost mysql3306.sock)[(none)]09:03:54>CHANGE MASTER TO MASTER_USER='rpl_mgr', MASTER_PASSWORD='rpl4mgr' FOR CHANNEL 'group_replication_recovery'; -- The name of channel is fixed and cannot be changed. 50 Query OK, 0 rows affected, 2 warnings (0.03 sec) 51 52 ##Install the Group Replication plugin. 53 (root@localhost mysql3306.sock)[(none)]09:13:19>INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 54 Query OK, 0 rows affected (0.03 sec) 55 56 (root@localhost mysql3306.sock)[(none)]09:13:25>show plugins; 57 +----------------------------+----------+--------------------+----------------------+---------+ 58 | Name | Status | Type | Library | License | 59 +----------------------------+----------+--------------------+----------------------+---------+ 60 | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | 61 | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | 62 | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | 63 | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | 64 | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | 65 | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | 66 | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | 67 | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 68 | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 69 | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 70 | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 71 | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 72 | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 73 | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 74 | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 75 | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 76 | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 77 | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 78 | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 79 | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 80 | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 81 | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 82 | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 83 | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 84 | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 85 | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 86 | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 87 | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 88 | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 89 | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 90 | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 91 | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 92 | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 93 | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 94 | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 95 | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 96 | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | 97 | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | 98 | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | 99 | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | 100 | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | 101 | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | 102 | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | 103 | ngram | ACTIVE | FTPARSER | NULL | GPL | 104 | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | 105 +----------------------------+----------+--------------------+----------------------+---------+ 106 45 rows in set (0.00 sec) 107 108 (root@localhost mysql3306.sock)[(none)]09:13:47>select * from performance_schema.replication_group_members; 109 +---------------------------+-----------+-------------+-------------+--------------+ 110 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | 111 +---------------------------+-----------+-------------+-------------+--------------+ 112 | group_replication_applier | | | NULL | OFFLINE | -- there's a record here after install the plugin. 113 +---------------------------+-----------+-------------+-------------+--------------+ 114 1 row in set (0.00 sec) 115 116 ##Set server zlm2 the seed member of group,then start up the Group Replicaiton. 117 (root@localhost mysql3306.sock)[(none)]09:20:24>SET GLOBAL group_replication_bootstrap_group=ON; -- This "on" value merely can be set once. 118 Query OK, 0 rows affected (0.00 sec) 119 120 (root@localhost mysql3306.sock)[(none)]09:20:39>START GROUP_REPLICATION; 121 Query OK, 0 rows affected (2.05 sec) 122 123 (root@localhost mysql3306.sock)[(none)]09:20:53>SET GLOBAL group_replication_bootstrap_group=OFF; -- Disable it after starting. 124 Query OK, 0 rows affected (0.00 sec) 125 126 (root@localhost mysql3306.sock)[(none)]09:21:11>select * from performance_schema.replication_group_members; 127 +---------------------------+--------------------------------------+-------------+-------------+--------------+ 128 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | 129 +---------------------------+--------------------------------------+-------------+-------------+--------------+ 130 | group_replication_applier | 1b7181ee-6eaf-11e8-998e-080027de0e0e | zlm2 | 3306 | ONLINE | -- There's a memeber in it. 131 +---------------------------+--------------------------------------+-------------+-------------+--------------+ 132 1 row in set (0.00 sec) 133 134 ##Let's do some operation on the server zlm2. 135 (root@localhost mysql3306.sock)[(none)]10:09:24>show databases; 136 +--------------------+ 137 | Database | 138 +--------------------+ 139 | information_schema | 140 | mysql | 141 | performance_schema | 142 | sys | 143 +--------------------+ 144 4 rows in set (0.00 sec) 145 146 (root@localhost mysql3306.sock)[(none)]10:09:43>create database zlm; 147 Query OK, 1 row affected (0.00 sec) 148 149 (root@localhost mysql3306.sock)[(none)]10:10:06>use zlm; 150 Database changed 151 (root@localhost mysql3306.sock)[zlm]10:10:10>create table test_mgr (id int primary key, name char(10) not null); 152 Query OK, 0 rows affected (0.02 sec) 153 154 (root@localhost mysql3306.sock)[zlm]10:10:18>insert into test_mgr VALUES (1, 'aaron8219'); 155 Query OK, 1 row affected (0.01 sec) 156 157 (root@localhost mysql3306.sock)[zlm]10:10:23>select * from test_mgr; 158 +----+-----------+ 159 | id | name | 160 +----+-----------+ 161 | 1 | aaron8219 | 162 +----+-----------+ 163 1 row in set (0.00 sec) 164 165 (root@localhost mysql3306.sock)[zlm]10:10:30>show binlog events; 166 +---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+ 167 | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 168 +---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+ 169 | binlog.000001 | 4 | Format_desc | 1013306 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 | 170 | binlog.000001 | 123 | Previous_gtids | 1013306 | 150 | | 171 | binlog.000001 | 150 | Gtid | 1013306 | 211 | SET @@SESSION.GTID_NEXT= 'ed142e35-6ed1-11e8-86c6-080027de0e0e:1' | 172 | binlog.000001 | 211 | Query | 1013306 | 270 | BEGIN | 173 | binlog.000001 | 270 | View_change | 1013306 | 409 | view_id=15288745659008026:1 | 174 | binlog.000001 | 409 | Query | 1013306 | 474 | COMMIT | 175 | binlog.000001 | 474 | Gtid | 1013306 | 535 | SET @@SESSION.GTID_NEXT= 'ed142e35-6ed1-11e8-86c6-080027de0e0e:2' | 176 | binlog.000001 | 535 | Query | 1013306 | 627 | create database zlm | 177 | binlog.000001 | 627 | Gtid | 1013306 | 688 | SET @@SESSION.GTID_NEXT= 'ed142e35-6ed1-11e8-86c6-080027de0e0e:3' | 178 | binlog.000001 | 688 | Query | 1013306 | 827 | use `zlm`; create table test_mgr (id int primary key, name char(10) not null) | 179 | binlog.000001 | 827 | Gtid | 1013306 | 888 | SET @@SESSION.GTID_NEXT= 'ed142e35-6ed1-11e8-86c6-080027de0e0e:4' | 180 | binlog.000001 | 888 | Query | 1013306 | 960 | BEGIN | 181 | binlog.000001 | 960 | Table_map | 1013306 | 1009 | table_id: 108 (zlm.test_mgr) | 182 | binlog.000001 | 1009 | Write_rows | 1013306 | 1055 | table_id: 108 flags: STMT_END_F | 183 | binlog.000001 | 1055 | Xid | 1013306 | 1082 | COMMIT /* xid=59 */ | 184 +---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+ 185 15 rows in set (0.00 sec) 186 187 (root@localhost mysql3306.sock)[zlm]10:10:43> 188 189 ##Configure the other two servers like what i've done on server zlm2: 190 -- Omitted. 191 192 ##START Group Replication 193 (root@localhost mysql3306.sock)[(none)]10:06:22>START GROUP_REPLICATION; 194 ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log. 195 (root@localhost mysql3306.sock)[(none)]10:17:02>(root@localhost mysql3306.sock)[(none)]10:17:02>select * from performance_schema.replication_group_members; 196 +---------------------------+--------------------------------------+-------------+-------------+--------------+ 197 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | 198 +---------------------------+--------------------------------------+-------------+-------------+--------------+ 199 | group_replication_applier | 5c77c31b-4add-11e8-81e2-080027de0e0e | zlm3 | 3306 | OFFLINE | 200 +---------------------------+--------------------------------------+-------------+-------------+--------------+ 201 1 row in set (0.00 sec) 202 203 ##There's something wrong when I execute "START GROUP_REPLICATION;".the server zlm3 doesn't join the right group create by server zlm2. 204 the error.log shows below: 205 2018-06-13T07:50:01.249829Z 0 [Note] mysqld (mysqld 5.7.21-log) starting as process 4474 ... 206 2018-06-13T07:50:01.256669Z 0 [Note] InnoDB: PUNCH HOLE support available 207 2018-06-13T07:50:01.256701Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 208 2018-06-13T07:50:01.256705Z 0 [Note] InnoDB: Uses event mutexes 209 2018-06-13T07:50:01.256708Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier 210 2018-06-13T07:50:01.256708Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 211 2018-06-13T07:50:01.256708Z 0 [Note] InnoDB: Using Linux native AIO 212 2018-06-13T07:50:01.256708Z 0 [Note] InnoDB: Number of pools: 1 213 2018-06-13T07:50:01.256718Z 0 [Note] InnoDB: Using CPU crc32 instructions 214 2018-06-13T07:50:01.258124Z 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M 215 2018-06-13T07:50:01.263012Z 0 [Note] InnoDB: Completed initialization of buffer pool 216 2018-06-13T07:50:01.264222Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 217 2018-06-13T07:50:01.289331Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 218 2018-06-13T07:50:01.475746Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 219 2018-06-13T07:50:01.475831Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 220 2018-06-13T07:50:01.781737Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 221 2018-06-13T07:50:01.782469Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 222 2018-06-13T07:50:01.782482Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 223 2018-06-13T07:50:01.783403Z 0 [Note] InnoDB: Waiting for purge to start 224 2018-06-13T07:50:01.960368Z 0 [Note] InnoDB: 5.7.21 started; log sequence number 4470756061 225 2018-06-13T07:50:01.960713Z 0 [Note] Plugin 'FEDERATED' is disabled. 226 2018-06-13T07:50:01.964346Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3306/data/ib_buffer_pool 227 2018-06-13T07:50:01.968486Z 0 [Warning] unknown variable 'loose_tokudb_cache_size=100M' 228 2018-06-13T07:50:01.968509Z 0 [Warning] unknown variable 'loose_tokudb_directio=ON' 229 2018-06-13T07:50:01.968511Z 0 [Warning] unknown variable 'loose_tokudb_fsync_log_period=1000' 230 2018-06-13T07:50:01.968513Z 0 [Warning] unknown variable 'loose_tokudb_commit_sync=0' 231 2018-06-13T07:50:01.968515Z 0 [Warning] unknown variable 'loose-group_replication_group_name=a5e7836a-6edc-11e8-a20d-080027de0e0e' 232 2018-06-13T07:50:01.968516Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=off' 233 2018-06-13T07:50:01.968518Z 0 [Warning] unknown variable 'loose-group_replication_local_address=zlm3:33062' 234 2018-06-13T07:50:01.968520Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=zlm2:33061,zlm3:33062,zlm4:33063' 235 2018-06-13T07:50:01.968521Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=off' 236 2018-06-13T07:50:01.983518Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key 237 2018-06-13T07:50:01.983631Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 238 2018-06-13T07:50:01.983667Z 0 [Note] IPv6 is available. 239 2018-06-13T07:50:01.983673Z 0 [Note] - '::' resolves to '::'; 240 2018-06-13T07:50:01.983690Z 0 [Note] Server socket created on IP: '::'. 241 2018-06-13T07:50:02.036682Z 0 [Note] Event Scheduler: Loaded 0 events 242 2018-06-13T07:50:02.037391Z 0 [Note] mysqld: ready for connections. 243 Version: '5.7.21-log' socket: '/tmp/mysql3306.sock' port: 3306 MySQL Community Server (GPL) 244 2018-06-13T07:50:02.083468Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180613 9:50:02 245 2018-06-13T08:04:38.631676Z 2 [Note] Aborted connection 2 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets) 246 2018-06-13T08:11:22.693094Z 3 [Note] Aborted connection 3 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets) 247 2018-06-13T08:16:01.529090Z 4 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"' 248 2018-06-13T08:16:01.529197Z 4 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 10.0.2.15/24,127.0.0.1/8,192.168.1.102/24 to the whitelist' 249 2018-06-13T08:16:01.529394Z 4 [Note] Plugin group_replication reported: '[GCS] Translated 'zlm3' to 192.168.1.102' 250 2018-06-13T08:16:01.529486Z 4 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.' 251 2018-06-13T08:16:01.531296Z 4 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled' 252 2018-06-13T08:16:01.531336Z 4 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "a5e7836a-6edc-11e8-a20d-080027de0e0e"; group_replication_local_address: "zlm3:33062"; group_replication_group_seeds: "zlm2:33061,zlm3:33062,zlm4:33063"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"' 253 2018-06-13T08:16:01.531375Z 4 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1023306; member_uuid: "5c77c31b-4add-11e8-81e2-080027de0e0e"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; ' 254 2018-06-13T08:16:01.549240Z 6 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. 255 2018-06-13T08:16:01.568485Z 9 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-bin-group_replication_applier.000001' position: 4 256 2018-06-13T08:16:01.569516Z 4 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!' 257 2018-06-13T08:16:01.569528Z 4 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7' 258 2018-06-13T08:16:01.569531Z 4 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1023306' 259 2018-06-13T08:16:01.569631Z 0 [Note] Plugin group_replication reported: 'state 0 action xa_init' 260 2018-06-13T08:16:01.589865Z 0 [Note] Plugin group_replication reported: 'Successfully bound to 0.0.0.0:33062 (socket=62).' 261 2018-06-13T08:16:01.589970Z 0 [Note] Plugin group_replication reported: 'Successfully set listen backlog to 32 (socket=62)!' 262 2018-06-13T08:16:01.590011Z 0 [Note] Plugin group_replication reported: 'Successfully unblocked socket (socket=62)!' 263 2018-06-13T08:16:01.590098Z 0 [Note] Plugin group_replication reported: 'Ready to accept incoming connections on 0.0.0.0:33062 (socket=62)!' 264 2018-06-13T08:16:01.590549Z 0 [Note] Plugin group_replication reported: 'connecting to zlm3 33062' 265 2018-06-13T08:16:01.590788Z 0 [Note] Plugin group_replication reported: 'client connected to zlm3 33062 fd 63' 266 2018-06-13T08:16:01.593734Z 0 [Note] Plugin group_replication reported: 'connecting to zlm3 33062' 267 2018-06-13T08:16:01.593853Z 0 [Note] Plugin group_replication reported: 'client connected to zlm3 33062 fd 65' 268 2018-06-13T08:16:01.593966Z 0 [Note] Plugin group_replication reported: 'connecting to zlm3 33062' 269 2018-06-13T08:16:01.594016Z 0 [Note] Plugin group_replication reported: 'client connected to zlm3 33062 fd 67' 270 2018-06-13T08:16:01.595449Z 0 [Note] Plugin group_replication reported: 'connecting to zlm3 33062' 271 2018-06-13T08:16:01.595554Z 0 [Note] Plugin group_replication reported: 'client connected to zlm3 33062 fd 60' 272 2018-06-13T08:16:01.595792Z 0 [Note] Plugin group_replication reported: 'connecting to zlm3 33062' 273 2018-06-13T08:16:01.595887Z 0 [Note] Plugin group_replication reported: 'client connected to zlm3 33062 fd 70' 274 2018-06-13T08:16:01.596009Z 0 [Note] Plugin group_replication reported: 'connecting to zlm3 33062' 275 2018-06-13T08:16:01.596069Z 0 [Note] Plugin group_replication reported: 'client connected to zlm3 33062 fd 72' 276 2018-06-13T08:16:01.596168Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 277 2018-06-13T08:16:01.596594Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 278 2018-06-13T08:16:01.596622Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 279 2018-06-13T08:16:01.596629Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 280 2018-06-13T08:16:01.596947Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 281 2018-06-13T08:16:01.596965Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 282 2018-06-13T08:16:01.596971Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 283 2018-06-13T08:16:01.597300Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 284 2018-06-13T08:16:01.597314Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 285 2018-06-13T08:16:01.597320Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 286 2018-06-13T08:16:01.597547Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 287 2018-06-13T08:16:01.597568Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 288 2018-06-13T08:16:01.597582Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 289 2018-06-13T08:16:01.597931Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 290 2018-06-13T08:16:01.597960Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 291 2018-06-13T08:16:01.597966Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 292 2018-06-13T08:16:01.598270Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 293 2018-06-13T08:16:01.598297Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 294 2018-06-13T08:16:01.598303Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 295 2018-06-13T08:16:01.598561Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 296 2018-06-13T08:16:01.598583Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 297 2018-06-13T08:16:01.598590Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 298 2018-06-13T08:16:01.598849Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 299 2018-06-13T08:16:01.598876Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 300 2018-06-13T08:16:01.598882Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 301 2018-06-13T08:16:01.599181Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 302 2018-06-13T08:16:01.599199Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 303 2018-06-13T08:16:01.599205Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 304 2018-06-13T08:16:01.599519Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 305 2018-06-13T08:16:01.599549Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 306 2018-06-13T08:16:01.599572Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 307 2018-06-13T08:16:01.599884Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 308 2018-06-13T08:16:01.599896Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 309 2018-06-13T08:16:01.599901Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 310 2018-06-13T08:16:01.600125Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 311 2018-06-13T08:16:01.600139Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 312 2018-06-13T08:16:01.600145Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 313 2018-06-13T08:16:02.600879Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 314 2018-06-13T08:16:02.600930Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 315 2018-06-13T08:16:02.600938Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 316 2018-06-13T08:16:02.601423Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 317 2018-06-13T08:16:02.601449Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 318 2018-06-13T08:16:02.601464Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 319 2018-06-13T08:16:03.604719Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 320 2018-06-13T08:16:03.604760Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 321 2018-06-13T08:16:03.604768Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 322 2018-06-13T08:16:03.605086Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 323 2018-06-13T08:16:03.605103Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 324 2018-06-13T08:16:03.605110Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 325 2018-06-13T08:16:04.606780Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 326 2018-06-13T08:16:04.606820Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 327 2018-06-13T08:16:04.606828Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 328 2018-06-13T08:16:04.607219Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 329 2018-06-13T08:16:04.607232Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 330 2018-06-13T08:16:04.607237Z 0 [Note] Plugin group_replication reported: 'connecting to zlm2 33061' 331 2018-06-13T08:16:05.608667Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm2 with error 113 -No route to host.' 332 2018-06-13T08:16:05.608702Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm2:33061 on local port: 33062.' 333 2018-06-13T08:16:05.608710Z 0 [Note] Plugin group_replication reported: 'connecting to zlm4 33063' 334 2018-06-13T08:16:05.609062Z 0 [Note] Plugin group_replication reported: 'Getting the peer name failed while connecting to server zlm4 with error 111 -Connection refused.' 335 2018-06-13T08:16:05.609080Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to zlm4:33063 on local port: 33062.' 336 2018-06-13T08:16:05.609086Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33062' 337 2018-06-13T08:16:05.609134Z 0 [Note] Plugin group_replication reported: 'state 4338 action xa_terminate' 338 2018-06-13T08:16:05.609141Z 0 [Note] Plugin group_replication reported: 'new state x_start' 339 2018-06-13T08:16:05.609143Z 0 [Note] Plugin group_replication reported: 'state 4338 action xa_exit' 340 2018-06-13T08:16:05.609182Z 0 [Note] Plugin group_replication reported: 'Exiting xcom thread' 341 2018-06-13T08:16:05.609186Z 0 [Note] Plugin group_replication reported: 'new state x_start' 342 2018-06-13T08:16:05.618446Z 0 [Warning] Plugin group_replication reported: 'read failed' 343 2018-06-13T08:16:05.618546Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33062' 344 2018-06-13T08:17:01.570227Z 4 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group' 345 2018-06-13T08:17:01.570326Z 4 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member' 346 2018-06-13T08:17:01.570364Z 4 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.' 347 2018-06-13T08:17:01.570551Z 4 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1' 348 2018-06-13T08:17:01.570559Z 4 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1' 349 2018-06-13T08:17:01.570655Z 9 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed 350 2018-06-13T08:17:01.570836Z 6 [Note] Plugin group_replication reported: 'The group replication applier thread was killed' 351 352 ##Finally,i find out the firewall is not disabled on server zlm2.
353 [root@zlm2 10:29:08 ~] 354 #systemctl status firewalld 355 firewalld.service - firewalld - dynamic firewall daemon 356 Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled) 357 Active: active (running) since Wed 2018-06-13 02:53:04 CEST; 7h ago 358 Main PID: 557 (firewalld) 359 CGroup: /system.slice/firewalld.service 360 └─557 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid 361 362 Jun 13 02:53:04 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon. 363 364 [root@zlm2 10:29:21 ~] 365 #systemctl stop firewalld 366 367 [root@zlm2 10:29:45 ~] 368 #systemctl disable firewalld 369 rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service' 370 rm '/etc/systemd/system/basic.target.wants/firewalld.service' 371 372 [root@zlm2 10:29:57 ~] 373 #systemctl status firewalld 374 firewalld.service - firewalld - dynamic firewall daemon 375 Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled) 376 Active: inactive (dead) 377 378 Jun 13 02:53:03 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon... 379 Jun 13 02:53:04 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon. 380 Jun 13 10:29:44 zlm2 systemd[1]: Stopping firewalld - dynamic firewall daemon... 381 Jun 13 10:29:45 zlm2 systemd[1]: Stopped firewalld - dynamic firewall daemon. 382 383 ##Start Group Replication again. 384 (root@localhost mysql3306.sock)[(none)]10:32:42>START GROUP_REPLICATION; 385 ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log. 386 (root@localhost mysql3306.sock)[(none)]10:34:16> 387 388 2018-06-13T08:33:45.361028Z 0 [ERROR] Plugin group_replication reported: '[GCS] Timeout while waiting for the group communication engine to be ready!' 389 2018-06-13T08:33:45.361070Z 0 [ERROR] Plugin group_replication reported: '[GCS] The group communication engine is not ready for the member to join. Local port: 33062' 390 2018-06-13T08:33:45.361171Z 0 [Note] Plugin group_replication reported: 'state 4338 action xa_terminate' 391 2018-06-13T08:33:45.361185Z 0 [Note] Plugin group_replication reported: 'new state x_start' 392 2018-06-13T08:33:45.361188Z 0 [Note] Plugin group_replication reported: 'state 4338 action xa_exit' 393 2018-06-13T08:33:45.361254Z 0 [Note] Plugin group_replication reported: 'Exiting xcom thread' 394 2018-06-13T08:33:45.361258Z 0 [Note] Plugin group_replication reported: 'new state x_start' 395 2018-06-13T08:33:45.371810Z 0 [Warning] Plugin group_replication reported: 'read failed' 396 2018-06-13T08:33:45.387635Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33062' 397 2018-06-13T08:34:15.349695Z 11 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group' 398 2018-06-13T08:34:15.349732Z 11 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member' 399 2018-06-13T08:34:15.349745Z 11 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.' 400 2018-06-13T08:34:15.349969Z 11 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1' 401 2018-06-13T08:34:15.349975Z 11 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1' 402 2018-06-13T08:34:15.350079Z 16 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed 403 2018-06-13T08:34:15.350240Z 13 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'
The other two servers(zlm3,zlm4) cannot join the group created by zlm2,I've not figured out what's wrong with it yet.I'll test it again in sometime later.
上一篇： 一份完整的微信流量“薅羊毛”指南（裂变）
下一篇： 113. 路径总和 II