一.测试环境清单
| 服务器 | 内存 | 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.tar
tar -xzvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
ln -s ./mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
2.3 安装(参数尽可能的少)
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2016-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=45790
2016-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_safe
2016-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
-
master
1.1 配置my.cnf(注意server_id)
log_bin = /mysql/data/master binlog-format = ROW log_slave_updates = ON gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE transaction_write_set_extraction = XXHASH64 slave_parallel_type = LOGICAL_CLOCK slave_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; -
配置子节点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; - 第三个节点按照第二个节点配置
-
查看状态
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: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_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:2000003 mysql> 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 | +--------------------------------------+-------------+--------------+ -
测试(要求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 | +----+