MySQL 5.7.17 Group Replication Installation

一.测试环境清单

服务器 内存 IP MySQL Red Hat Enterprise Linux Server release 6.4 (Santiago) 512M 192.168.2.2 5.7.17-log Red Hat Enterprise Linux Server release 6.4 (Santiago) 512M 192.168.2.3 5.7.17-log Red Hat Enterprise Linux Server release 6.4 (Santiago) 512M 192.168.2.4 5.7.17-log

二.数据库安装

2.1 下载

http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar

2.2 定制安装目录

tar -xvf mysql-5.7.17-linux-glibc2.5-x86_64.tartar -xzvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gzln -s ./mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysqlchown -R mysql:mysql /usr/local/mysql

2.3 安装(参数尽可能的少)

./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data2016-06-22 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2016-06-22 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.2016-06-22 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.2016-06-22 0 [Warning] InnoDB: New log files created, LSN=457902016-06-22 0 [Warning] InnoDB: Creating foreign key constraint system tables.2016-06-22 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a64236af-3834-11e6-bd78-005056329da5.2016-06-22 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2016-06-22 1 [Note] A temporary password is generated for root@localhost: NJ2MN9j/9rxf

2.4 启动

设置配置文件my.cnf设置启动文件mysql.server./bin/mysqld_safe2016-06-22 mysqld_safe Logging to '/usr/local/mysql/data/node01.err'.2016-06-22 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

2.5 重置root密码

mysql -uroot -p'NJ2MN9j/9rxf'> SET PASSWORD = PASSWORD('123456');> show warnings;+---------+------+-----------------------------------------------------------------+| Level   | Code | Message                                                                                                                                                          +---------+------+-----------------------------------------------------------------+| Warning | 1287 | 'SET PASSWORD = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release. Please use SET PASSWORD = '<plaintext_password>' instead |+---------+------+-----------------------------------------------------------------+1 row in set (0.00 sec)> SET PASSWORD = '123456';> select version();+-----------+| version() |+-----------+| 5.7.17    |+-----------+1 row in set (0.00 sec)

三.配置Group Replication

1. master

   1.1 配置my.cnf(注意server_id)

   log_bin                 =       /mysql/data/masterbinlog-format           =       ROWlog_slave_updates       =       ONgtid_mode                               = ONenforce_gtid_consistency                = ONmaster_info_repository                  = TABLErelay_log_info_repository               = TABLEbinlog_checksum                         = NONEtransaction_write_set_extraction        = XXHASH64slave_parallel_type                     = LOGICAL_CLOCKslave_preserve_commit_order             = ON*以上为必须开启按钮

   1.2 安装

   mysql> INSTALL PLUGIN group_replication SONAME "group_replication.so"; mysql> select uuid();mysql> SET GLOBAL group_replication_group_name = "d7097896-a444-43e9-ba00-2cb3a12bcd39";mysql> SET GLOBAL group_replication_local_address = "192.168.2.2:3307";mysql> SET GLOBAL group_replication_bootstrap_group = ON;mysql> START GROUP_REPLICATION;mysql> GRANT replication_slave on *.* to repl@'%' identified by 'repl'; mysql> FLUSH PRIVILEGES;

2. 配置子节点node

   mysql> INSTALL PLUGIN group_replication SONAME "group_replication.so";mysql> SET GLOBAL group_replication_group_name = "d7097896-a444-43e9-ba00-2cb3a12bcd39”;mysql> SET GLOBAL group_replication_local_address = "192.168.2.3:3307";mysql> SET GLOBAL group_replication_group_seeds = "192.168.2.2:3307";mysql> CHANGE MASTER TO MASTER_USER="repl",MASTER_PASSWORD="repl" FOR CHANNEL "group_replication_recovery";mysql> START GROUP_REPLICATION;

3. 第三个节点按照第二个节点配置

4. 查看状态

   mysql> select * from performance_schema.replication_group_member_stats\G*************************** 1. row ***************************                      CHANNEL_NAME: group_replication_applier                           VIEW_ID: 14665874156441477:3                         MEMBER_ID: f032af87-3826-11e6-9ba3-0050562a2d7e       COUNT_TRANSACTIONS_IN_QUEUE: 0        COUNT_TRANSACTIONS_CHECKED: 6          COUNT_CONFLICTS_DETECTED: 0COUNT_TRANSACTIONS_ROWS_VALIDATING: 0TRANSACTIONS_COMMITTED_ALL_MEMBERS: d7097896-a444-43e9-ba00-2cb3a12bcd39:1-7:1000003:2000003,f032af87-3826-11e6-9ba3-0050562a2d7e:1-3    LAST_CONFLICT_FREE_TRANSACTION: d7097896-a444-43e9-ba00-2cb3a12bcd39:2000003mysql> SELECT MEMBER_ID,MEMBER_HOST,MEMBER_STATE FROM performance_schema.replication_group_members; +--------------------------------------+-------------+--------------+| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |+--------------------------------------+-------------+--------------+| 1b6b53e7-37ef-11e6-bbb6-00505623092e | node02      | ONLINE       || a64236af-3834-11e6-bd78-005056329da5 | node01      | ONLINE       || f032af87-3826-11e6-9ba3-0050562a2d7e | master      | ONLINE       |+--------------------------------------+-------------+--------------+

5. 测试(要求innodb_read_only & tx_read_only 开启)

   mysql -uroot -p -h192.168.2.2 -e "insert into db_test.tb_test values(1);"mysql -uroot -p -h192.168.2.3 -e "insert into db_test.tb_test values(2);"mysql -uroot -p -h192.168.2.4 -e "insert into db_test.tb_test values(3);"mysql -uroot -p -h192.168.2.2 -e "select * from db_test.tb_test;"+----+| id |+----+|  1 ||  2 ||  3 |+----+mysql -uroot -p -h192.168.2.3 -e "select * from db_test.tb_test;"+----+| id |+----+|  1 ||  2 ||  3 |+----+      mysql -uroot -p -h192.168.2.4 -e "select * from db_test.tb_test;"+----+| id |+----+|  1 ||  2 ||  3 |+----+