Mysql
MySQL 5.7 組複製禁用 TCP/IP 訪問
我正在使用 Windows 2012 R2 系統上的多主複製在 MySQL 5.7 上測試新的組複製功能。我用於測試單個伺服器並使用不同的數據目錄和 tcp 埠等安裝到 MySQL 實例 onm TCP-Ports 3306 和 3306。
一切正常，甚至設置組複製。但是當我使用時
start replication，我不能mysql -h 127.0.0.1 -u root -p再通過套接字（ ）連接每個 TCP/IP（mysql -h localhost -u root -p）。在第二個主伺服器上開始組複製之前：
>mysql -h 127.0.0.1 -P 3307 -u root -p -e "status; show processlist; SELECT * FROM performance_schema.replication_group_members" -------------- mysql Ver 14.14 Distrib 5.7.17, for Win64 (x86_64) Connection id: 16 Current database: Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 Using delimiter: ; Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: cp850 Conn. characterset: cp850 TCP port: 3307 Uptime: 2 min 52 sec Threads: 1 Questions: 43 Slow queries: 0 Opens: 127 Flush tables: 1 Open tables: 120 Queries per second avg: 0.250 -------------- +----+------+-----------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+----------+------------------+ | 16 | root | localhost:49333 | NULL | Query | 0 | starting | show processlist | +----+------+-----------------+------+---------+------+----------+------------------+ +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | c6dc60ef-f8db-11e6-b78f-005056aae5b3 | RS-6-48 | 3307 | OFFLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+開始組複製
mysql -P 3307 -u root -p -e "START GROUP_REPLICATION"之後，嘗試通過 tcp/ip 進行連接被卡住了。TCP-Port 已打開，您可以連接，但僅此而已。伺服器只能通過以下方式訪問
localhost：>mysql -h localhost -P 3307 -u root -p -e "status; show processlist; SELECT * FROM performance_schema.replication_group_members" -------------- mysql Ver 14.14 Distrib 5.7.17, for Win64 (x86_64) Connection id: 39 Current database: Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 Using delimiter: ; Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: cp850 Conn. characterset: cp850 TCP port: 3307 Uptime: 6 min 33 sec Threads: 4 Questions: 87 Slow queries: 0 Opens: 137 Flush tables: 1 Open tables: 130 Queries per second avg: 0.221 -------------- +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+ | 28 | system user | | NULL | Connect | 30 | System lock | NULL | | 31 | system user | | NULL | Connect | 30 | Slave has read all relay log; waiting for more updates | NULL | | 39 | root | localhost:49358 | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+ +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 76c585ef-f8df-11e6-80f7-005056aae5b3 | RS-6-48 | 3306 | ONLINE | | group_replication_applier | c6dc60ef-f8db-11e6-b78f-005056aae5b3 | RS-6-48 | 3307 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+如果再次停止複制。
127.0.0.1再次下可以訪問伺服器。解決這個問題的任何提示？謝謝。
附錄 1：開始組複製時 MySQL 錯誤日誌的摘錄：
2017-02-23T06:59:15.291541Z 12 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"' 2017-02-23T06:59:15.291541Z 12 [Note] Plugin group_replication reported: 'The Winsock 2.2 dll was found okay' 2017-02-23T06:59:15.291541Z 12 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 10.32.6.48/8,127.0.0.1/8 to the whitelist' 2017-02-23T06:59:15.291541Z 12 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled' 2017-02-23T06:59:15.291541Z 12 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "ced7cae9-6e09-455e-9fe0-a55627f7291d"; group_replication_local_address: "10.32.6.48:3307"; group_replication_group_seeds: "10.32.6.48:3306,10.32.6.48:3307"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"' 2017-02-23T06:59:15.291541Z 14 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, 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=''. 2017-02-23T06:59:15.307218Z 12 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!' 2017-02-23T06:59:15.307218Z 17 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log '.\railsys-enterprise-relay-bin-group_replication_applier.000008' position: 4 2017-02-23T06:59:15.307218Z 12 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7' 2017-02-23T06:59:15.307218Z 12 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 2' 2017-02-23T06:59:15.307218Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_init' 2017-02-23T06:59:15.307218Z 0 [Note] Plugin group_replication reported: 'Successfully bound to 0.0.0.0:3307 (socket=2688).' 2017-02-23T06:59:15.307218Z 0 [Note] Plugin group_replication reported: 'Successfully set listen backlog to 32 (socket=2688)!' 2017-02-23T06:59:15.307218Z 0 [Note] Plugin group_replication reported: 'Successfully unblocked socket (socket=2688)!' 2017-02-23T06:59:15.307218Z 0 [Note] Plugin group_replication reported: 'connecting to 10.32.6.48 3307' 2017-02-23T06:59:15.307218Z 0 [Note] Plugin group_replication reported: 'Ready to accept incoming connections on 0.0.0.0:3307 (socket=2688)!' 2017-02-23T06:59:15.322881Z 0 [Note] Plugin group_replication reported: 'client connected to 10.32.6.48 3307 fd 2996' 2017-02-23T06:59:15.322881Z 0 [Note] Plugin group_replication reported: 'connecting to 10.32.6.48 3307' 2017-02-23T06:59:15.338478Z 0 [Note] Plugin group_replication reported: 'client connected to 10.32.6.48 3307 fd 2696' 2017-02-23T06:59:15.338478Z 0 [Note] Plugin group_replication reported: 'connecting to 10.32.6.48 3307' 2017-02-23T06:59:15.354138Z 0 [Note] Plugin group_replication reported: 'client connected to 10.32.6.48 3307 fd 2740' 2017-02-23T06:59:15.354138Z 0 [Note] Plugin group_replication reported: 'connecting to 10.32.6.48 3307' 2017-02-23T06:59:15.369687Z 0 [Note] Plugin group_replication reported: 'client connected to 10.32.6.48 3307 fd 3000' 2017-02-23T06:59:15.369687Z 0 [Note] Plugin group_replication reported: 'connecting to 10.32.6.48 3307' 2017-02-23T06:59:15.385311Z 0 [Note] Plugin group_replication reported: 'client connected to 10.32.6.48 3307 fd 2676' 2017-02-23T06:59:15.385311Z 0 [Note] Plugin group_replication reported: 'connecting to 10.32.6.48 3307' 2017-02-23T06:59:15.400933Z 0 [Note] Plugin group_replication reported: 'client connected to 10.32.6.48 3307 fd 3004' 2017-02-23T06:59:15.400933Z 0 [Note] Plugin group_replication reported: 'connecting to 10.32.6.48 3306' 2017-02-23T06:59:15.416653Z 0 [Note] Plugin group_replication reported: 'client connected to 10.32.6.48 3306 fd 2752' 2017-02-23T06:59:16.838441Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_snapshot' 2017-02-23T06:59:16.838441Z 0 [Note] Plugin group_replication reported: 'new state x_recover' 2017-02-23T06:59:16.838441Z 0 [Note] Plugin group_replication reported: 'state 4277 action xa_complete' 2017-02-23T06:59:16.838441Z 0 [Note] Plugin group_replication reported: 'new state x_run' 2017-02-23T06:59:21.213450Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 76c585ef-f8df-11e6-80f7-005056aae5b3:1, c6dc60ef-f8db-11e6-b78f-005056aae5b3:1, ced7cae9-6e09-455e-9fe0-a55627f7291d:1-1501 > Group transactions: 76c585ef-f8df-11e6-80f7-005056aae5b3:1, ced7cae9-6e09-455e-9fe0-a55627f7291d:1-1502' 2017-02-23T06:59:21.213450Z 0 [Warning] Plugin group_replication reported: 'The member contains transactions not present in the group. It is only allowed to join due to group_replication_allow_local_disjoint_gtids_join option' 2017-02-23T06:59:21.213450Z 0 [Note] Plugin group_replication reported: 'Starting group replication recovery with view_id 14877553113022027:16' 2017-02-23T06:59:21.213450Z 20 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10' 2017-02-23T06:59:27.291607Z 20 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='RS-6-48', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. 2017-02-23T06:59:27.322957Z 20 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 76c585ef-f8df-11e6-80f7-005056aae5b3 at RS-6-48 port: 3306.' 2017-02-23T06:59:27.322957Z 21 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2017-02-23T06:59:27.322957Z 22 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log '.\railsys-enterprise-relay-bin-group_replication_recovery.000001' position: 4 2017-02-23T06:59:27.322957Z 21 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'replication@RS-6-48:3306',replication started in log 'FIRST' at position 4 2017-02-23T06:59:27.354089Z 20 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.' 2017-02-23T06:59:27.354089Z 21 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery' 2017-02-23T06:59:27.354089Z 21 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'railsys-enterprise-bin.000002', position 607517 2017-02-23T06:59:27.354089Z 20 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='RS-6-48', 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=''. 2017-02-23T06:59:27.385522Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'附錄2：伺服器A my.ini：
[mysqld] port=3306 loose-group_replication_local_address="10.32.6.48:3306" loose-group_replication_group_seeds="10.32.6.48:3306,10.32.6.48:3307"伺服器 B my.ini
[mysqld] port=3307 loose-group_replication_local_address="10.32.6.48:3307" loose-group_replication_group_seeds="10.32.6.48:3306,10.32.6.48:3307"
MySQL 錯誤日誌應該有一些線索。您可以在發布後顯示日誌片段
START GROUP_REPLICATION嗎？編輯：看到 MySQL 錯誤日誌片段後…
看起來您可能正在為 mysql 協議和 GCS（組通信系統）使用相同的埠？
您必須使用不同的埠。例如，在您的配置中：
[mysqld] port=3307 group_replication_local_address = '10.32.6.48:6606' group_replication_group_seeds = '10.32.6.49:6606,10.32.6.50:6606'