MySQL 8.0.13组复制安装步骤和踩坑经验分享

环境描述: 主机: 192.168.0.51   alpha-mysql-0-51     主节点 192.168.0.52   alpha-mysql-0-52     从节点 192.168.16.15  zhsq-mysql16-15      从节点 操作系统版本:CentOS release 6.8 开始在主节点上安装组复制: 第一个主节点(192.168.0.51)上的配置文件内容: [root@alpha-mysql-0-51 ~]#cat /etc/my.cnf [mysqld] sql_mode = NO_ENGINE_SUBSTITUTION server_id=14051 port=3306 user=mysql character_set_server=utf8mb4 skip_name_resolve max_connections=100 basedir=/usr/local/mysql-8.0.13 datadir=/home/mysql/data socket=/tmp/mysql.sock pid-file=/home/mysql/mysqld.pid #transaction_isolation=read-committed default_storage_engine=innodb max_allowed_packet=128M max_heap_table_size=64M tmp_table_size=64M read_buffer_size=2M sort_buffer_size=2M read_rnd_buffer_size=4M open_files_limit=81920 table_open_cache=10000 table_definition_cache=10000 secure-file-priv = NULL #secure_file_priv='' wait_timeout=86400 default_authentication_plugin=mysql_native_password log_error=/home/mysql/log/mysqld.err log_timestamps=system slow_query_log=1 slow_query_log_file=/home/mysql/slow_query.log long_query_time=3 log_bin=/home/mysql/data/mysql-bin binlog_format=row binlog_row_image=minimal binlog_rows_query_log_events binlog_error_action=ABORT_SERVER #expire_logs_days=1 --disabled from mysql8.0 binlog_expire_logs_seconds=86400 slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=/home/mysql/data/relay-log relay_log_recovery=ON log_slave_updates skip_slave_start         innodb_open_files=8000 innodb_buffer_pool_size=4G innodb_max_dirty_pages_pct=90 innodb_buffer_pool_instances=8 innodb_buffer_pool_dump_at_shutdown=ON innodb_lock_wait_timeout=120 innodb_io_capacity=1000 innodb_io_capacity_max=2000 innodb_flush_method=O_DIRECT innodb_file_per_table=1 innodb_flush_log_at_trx_commit=0 innodb_log_file_size=100M innodb_log_buffer_size=10M innodb_log_files_in_group=3 innodb_purge_threads=4 innodb_thread_concurrency=0 innodb_print_all_deadlocks=ON innodb_deadlock_detect=ON innodb_strict_mode=ON innodb_sort_buffer_size=64M innodb_read_io_threads=6 innodb_write_io_threads=6 gtid_mode=ON enforce_gtid_consistency=ON binlog_checksum=NONE transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off    loose-group_replication_local_address= "192.168.0.51:24901" loose-group_replication_group_seeds= "10.186.0.51:24901,192.168.0.52:24902,192.168.16.15:24903" loose-group_replication_bootstrap_group= off [mysql] socket=/tmp/mysql.sock default-character-set = utf8mb4 prompt="\\u@\\h :\\d\\r:\\m:\\s>" [mysqldump] socket=/tmp/mysql.sock [mysqladmin] socket=/tmp/mysql.sock 备注： loose-group_replication_start_on_boot=off   这句的意思是在mysqld启动时不自动启动组复制，如果想自动启动，可以改成on 初始化mysql服务: # mysqld  --initialize-insecure --user=mysql 启动第一个节点上的mysql8.0: # mysqld_safe --user=mysql &  [2] 27593 [root@alpha-mongo-140-51 /home/mysql]#2018-11-07T06:17:06.255849Z mysqld_safe Logging to '/home/mysql/log/mysqld.err'. 2018-11-07T06:17:06.323053Z mysqld_safe Starting mysqld daemon with databases from /home/mysql/data 创建复制用户并安装组复制插件: mysql>set sql_log_bin=0;       Query OK, 0 rows affected (0.01 sec) mysql>CREATE USER repl@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) 备注：设置sql_log_bin=0这个步骤很重要，创建用户操作不能记录到binlog文件中，否则会报错: [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.' mysql>GRANT REPLICATION SLAVE ON *.* TO repl@'%'; Query OK, 0 rows affected (0.00 sec) mysql>set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql>CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.34 sec) mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.02 sec) 设置白名单(局域网内机器不在一个网段时候需要设置)： mysql>set global group_replication_ip_whitelist="192.168.0.51,192.168.0.52,192.168.16.15"; Query OK, 0 rows affected (0.00 sec) 查看白名单: mysql>show global variables like '%white%'; +--------------------------------+----------------------------------------------+ | Variable_name                  | Value                                        | +--------------------------------+----------------------------------------------+ | group_replication_ip_whitelist | 192.168.0.51,192.168.0.52,192.168.16.15      | +--------------------------------+----------------------------------------------+ 1 row in set (0.01 sec) mysql>SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME LIKE '%group%' \G *************************** 1. row ***************************            PLUGIN_NAME: group_replication         PLUGIN_VERSION: 1.1          PLUGIN_STATUS: ACTIVE            PLUGIN_TYPE: GROUP REPLICATION    PLUGIN_TYPE_VERSION: 1.2         PLUGIN_LIBRARY: group_replication.so PLUGIN_LIBRARY_VERSION: 1.9          PLUGIN_AUTHOR: ORACLE     PLUGIN_DESCRIPTION: Group Replication (1.1.0)         PLUGIN_LICENSE: GPL            LOAD_OPTION: ON 1 row in set (0.10 sec) 查看已安装的插件: mysql>show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name                            | Status   | Type               | Library              | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog                          | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     | | sha256_password                 | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     | | caching_sha2_password           | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     | | sha2_cache_cleaner              | ACTIVE   | AUDIT              | NULL                 | GPL     | | InnoDB                          | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | INNODB_TRX                      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_CMP                      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_CMP_RESET                | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_CMPMEM                   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_CMPMEM_RESET             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_CMP_PER_INDEX            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_CMP_PER_INDEX_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_BUFFER_PAGE              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_BUFFER_PAGE_LRU          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_BUFFER_POOL_STATS        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_TEMP_TABLE_INFO          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_METRICS                  | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_FT_DEFAULT_STOPWORD      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_FT_DELETED               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_FT_BEING_DELETED         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_FT_CONFIG                | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_FT_INDEX_CACHE           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_FT_INDEX_TABLE           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_TABLES                   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_TABLESTATS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_INDEXES                  | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_TABLESPACES              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_COLUMNS                  | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_VIRTUAL                  | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_CACHED_INDEXES           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     | | MyISAM                          | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | MRG_MYISAM                      | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | MEMORY                          | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | CSV                             | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | PERFORMANCE_SCHEMA              | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | TempTable                       | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | BLACKHOLE                       | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | ARCHIVE                         | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     | | FEDERATED                       | DISABLED | STORAGE ENGINE     | NULL                 | GPL     | | mysqlx                          | ACTIVE   | DAEMON             | NULL                 | GPL     | | mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL                 | GPL     | | ngram                           | ACTIVE   | FTPARSER           | NULL                 | GPL     | | group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |       +---------------------------------+----------+--------------------+----------------------+---------+ 45 rows in set (0.01 sec) 备注：查看最后一行，group_replication ACTIVE表明组复制插件已安装。 开启第一个节点组复制: mysql>SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) 启动组复制: mysql> START GROUP_REPLICATION; ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log. 报错了，日志里的告警信息如下：  [Warning] [MY-011682] [Repl] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.' 提示需要设置参数slave-preserve-commit-order,设置此参数是为了控制Slave上的binlog提交顺序和Master上的binlog的提交顺序一样,保证GTID的顺序。 mysql>set global slave_preserve_commit_order=on; Query OK, 0 rows affected (0.00 sec) 再次启动group replication: root@localhost :(none)02:23:14>START GROUP_REPLICATION; Query OK, 0 rows affected (3.47 sec) 启动成功,日志如下: 2018-11-07T14:23:22.130524+08:00 9 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.' 2018-11-07T14:23:22.351635+08:00 259 [System] [MY-010597] [Repl] '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=''. 查看组复制成员: mysql>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | a0d757d3-e254-11e8-a3f7-525400bf555b | alpha-mysql-0-51   |        3306 | ONLINE       | PRIMARY     | 8.0.13         | +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ 1 row in set (0.02 sec) 创建测试数据: mysql>create database test; Query OK, 1 row affected (0.05 sec) mysql>use test Database changed mysql>create table test(id int primary key,name varchar(10)); Query OK, 0 rows affected (0.15 sec) mysql>insert into test values(1,'lovepeihy'); Query OK, 1 row affected (0.07 sec) 第二个节点(从节点)配置文件: [root@alpha-mysql-0-52 /]#cat /etc/my.cnf [mysqld] sql_mode = NO_ENGINE_SUBSTITUTION server_id=14052 port=3306 user=mysql character_set_server=utf8mb4 skip_name_resolve max_connections=100 basedir=/usr/local/mysql-8.0.13 datadir=/home/mysql/data socket=/tmp/mysql.sock pid-file=/home/mysql/mysqld.pid #transaction_isolation=read-committed default_storage_engine=innodb max_allowed_packet=128M max_heap_table_size=64M tmp_table_size=64M read_buffer_size=2M sort_buffer_size=2M read_rnd_buffer_size=4M open_files_limit=81920 table_open_cache=10000 table_definition_cache=10000 secure-file-priv = NULL #secure_file_priv='' wait_timeout=86400 default_authentication_plugin=mysql_native_password log_error=/home/mysql/log/mysqld.err log_timestamps=system slow_query_log=1 slow_query_log_file=/home/mysql/slow_query.log long_query_time=3 log_bin=/home/mysql/data/mysql-bin binlog_format=row #binlog_checksum=NONE binlog_row_image=minimal binlog_rows_query_log_events binlog_error_action=ABORT_SERVER #expire_logs_days=1 --disabled in mysql8.0 binlog_expire_logs_seconds=86400 slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=/home/mysql/data/relay-log relay_log_recovery=ON log_slave_updates skip_slave_start #key_buffer_size=4M #bulk_insert_buffer_size=4M #myisam_sort_buffer_size=6M #myisam_max_sort_file_size=10G #myisam_repair_threads=1 #myisam_recover_options=default         innodb_open_files=8000 #innodb_page_size=8192 innodb_buffer_pool_size=4G innodb_max_dirty_pages_pct=90 #innodb_buffer_pool_dump_pct=40 innodb_buffer_pool_instances=8 #innodb_buffer_pool_load_at_startup=ON innodb_buffer_pool_dump_at_shutdown=ON innodb_lock_wait_timeout=120 innodb_io_capacity=1000 innodb_io_capacity_max=2000 innodb_flush_method=O_DIRECT #innodb_file_format=Barracuda innodb_file_per_table=1 #innodb_undo_directory=/undolog/ #innodb_undo_logs=128 #innodb_undo_tablespaces=3 #innodb_undo_log_truncate=1 #innodb_max_undo_log_size=2G #innodb_purge_rseg_truncate_frequency=128 #innodb_flush_neighbors=2 innodb_flush_log_at_trx_commit=0 innodb_log_file_size=100M innodb_log_buffer_size=10M innodb_log_files_in_group=3 innodb_purge_threads=4 innodb_thread_concurrency=0 innodb_print_all_deadlocks=ON innodb_deadlock_detect=ON innodb_strict_mode=ON innodb_sort_buffer_size=64M innodb_read_io_threads=6 innodb_write_io_threads=6 gtid_mode=ON enforce_gtid_consistency=ON binlog_checksum=NONE slave_preserve_commit_order=ON transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.0.52:24902" loose-group_replication_group_seeds= "192.168.0.51:24901,192.168.0.52:24902,192.168.16.15:24903" loose-group_replication_bootstrap_group= off [mysql] socket=/tmp/mysql.sock default-character-set = utf8mb4 prompt="\\u@\\h :\\d\\r:\\m:\\s>" [mysqldump] socket=/tmp/mysql.sock [mysqladmin] socket=/tmp/mysql.sock 初始化第二个节点服务: # mysqld  --initialize-insecure --user=mysql 启动第二个节点上的mysql8.0: # mysqld_safe --user=mysql &  重复第一个节点创建用户并安装组复制插件的步骤： mysql>set sql_log_bin=0;   Query OK, 0 rows affected (0.00 sec) mysql>CREATE USER repl@'%' identified by '123456'; Query OK, 0 rows affected (0.01 sec) mysql>GRANT REPLICATION SLAVE ON *.* TO repl@'%'; Query OK, 0 rows affected (0.00 sec) mysql>set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql>CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.28 sec) mysql>set global group_replication_ip_whitelist="192.168.0.51,192.168.0.52,192.168.16.15"; Query OK, 0 rows affected (0.00 sec) mysql>show variables like '%white%'; +--------------------------------+----------------------------------------------+ | Variable_name                  | Value                                        | +--------------------------------+----------------------------------------------+ | group_replication_ip_whitelist | 192.168.0.51,192.168.0.52,192.168.16.15 | +--------------------------------+----------------------------------------------+ 1 row in set (0.01 sec) root@localhost :(none)02:37:26>INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.02 sec) root@localhost :(none)02:37:37>START GROUP_REPLICATION; Query OK, 0 rows affected (4.05 sec) root@localhost :(none)02:37:59>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | a0d757d3-e254-11e8-a3f7-525400bf555b | alpha-mysql-0-51   |        3306 | ONLINE       | PRIMARY     | 8.0.13         | | group_replication_applier | ff85c59b-e256-11e8-9c48-52540098ed65 | alpha-mysql-0-52   |        3306 | ONLINE       | SECONDARY   | 8.0.13         | +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.09 sec) 查看主节点创建的数据： root@localhost :(none)02:46:38>show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | | test               | +--------------------+ 5 rows in set (0.04 sec) root@localhost :test02:46:42>show tables; +----------------+ | Tables_in_test | +----------------+ | test           | +----------------+ 1 row in set (0.00 sec) root@localhost :test02:46:45>desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id    | int(11)     | NO   | PRI | NULL    |       | | name  | varchar(10) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) root@localhost :test02:46:47>select * from test; +----+-----------+ | id | name      | +----+-----------+ |  1 | lovepeihy | +----+-----------+ 1 row in set (0.00 sec) 第三个节点按照第二个节点同样的方法做： 最终查出来的结果如下: root@localhost :test03:59:14>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 9f9cf39f-e262-11e8-9394-525400a6c4f1 | zhsq-mysql16-15    |        3306 | ONLINE       | SECONDARY   | 8.0.13         | | group_replication_applier | d6235934-e261-11e8-b243-52540098ed65 | alpha-mysql-0-52   |        3306 | ONLINE       | SECONDARY   | 8.0.13         | | group_replication_applier | da5643f1-e25f-11e8-b0ee-525400bf555b | alpha-mysql-0-51   |        3306 | ONLINE       | PRIMARY     | 8.0.13         | +---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) 注意:如果在从节点上插入数据，会报错： mysql>insert into test select * from test; ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement 至此，MySQL 8.0.13的组复制安装完毕。 补充: mysql的组复制配置有关的参数解释: gtid_mode=ON                     ###是否打开GTID模式 enforce_gtid_consistency=ON      ###是否强制事务一致 binlog_checksum=NONE             ###是否开启binlog校验功能,设置成不开启 slave_preserve_commit_order=ON   ###控制Slave上的binlog提交顺序和Master上的binlog的提交顺序一样,保证GTID的顺序 transaction_write_set_extraction=XXHASH64      ###开启主键信息采集功能，8.0.2开始默认值为XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"     ###设置组名，随便起，但是不能与UUID重复 loose-group_replication_start_on_boot=off       ###MySQL SERVER启动时不自动启动组复制 loose-group_replication_local_address= "192.168.0.51:24901"    ###设置成员的本地地址，后面端口号为组复制的端口号 loose-group_replication_group_seeds= "192.186.140.51:24901,192.168.0.52:24902,192.168.16.15:24903"   ###设置种子成员的地址,有几台机器设置几个 loose-group_replication_bootstrap_group= off     ###配置是否自动引导组