server1配置
首先停止数据库
[root@server1 mysql]# systemctl stop mysqld
清楚数据
[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# vim /etc/my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server1:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.52.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
#根据实际情况修改主机名和网段
启动服务
[root@server1 mysql]# systemctl restart mysqld
[root@server1 mysql]# mysql -p
#首先修改临时密码
alter user root@localhost identified by 'Westos+1';
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Westos+1';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Westos+1' FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group=ON; #只在server1上执行
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF; #只在server1上执行
mysql> SELECT * FROM performance_schema.replication_group_members;
server2配置
首先停止数据库
[root@server2 mysql]# systemctl stop mysqld
清楚数据
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# vim /etc/my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server2:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.52.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
#根据实际情况修改主机名和网段
启动服务
[root@server2 mysql]# systemctl restart mysqld
[root@server2 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'Westos+1';
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Westos+1' FOR CHANNEL 'group_replication_recovery';
mysql> SELECT * FROM performance_schema.replication_group_members;
server3配置
首先停止数据库
[root@server3 ~]# systemctl stop mysqld
清楚数据
[root@server3 ~]# cd /var/lib/mysql
[root@server3 mysql]# rm -fr *
[root@server3 mysql]# vim /etc/my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server3:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.52.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
#根据实际情况修改主机名和网段
启动服务
[root@server3 mysql]# systemctl restart mysqld
[root@server3 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'Westos+1';
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Westos+1' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
测试
所有节点都可以读写数据
server1:
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');
mysql> SELECT * FROM t1;
server2:
mysql> select * from test.t1;
mysql> INSERT INTO test.t1 VALUES (2,'westos');
server3:
mysql> select * from test.t1;
mysql> INSERT INTO test.t1 VALUES (3, 'westos');
慢查询
mysql> set global slow_query_log=ON;
mysql> show variables like "long%";
mysql> set long_query_time=5;
mysql> select sleep(5); #模拟慢查询语句
mysql> show variables like "slow%";
mysql路由器
安装软件
[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
配置服务
[root@server4 ~]# cd /etc/mysqlrouter/
[root@server4 mysqlrouter]# vim mysqlrouter.conf
...
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.168.36.132:3306,192.168.36.133:3306,192.168.56.134:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 192.168.36.134:3306,192.168.36.133:3306,192.168.56.132:3306
routing_strategy = first-available
启动服务
[root@server4 mysqlrouter]# systemctl enable --now mysqlrouter.service
安装mysql客户端工具
[root@server4 ~]# yum install -y mariadb
在mysql集群中创建远程测试用户
mysql> grant all on test.* to 'wxh'@'%' identified by 'westos';
连接mysql路由器
[root@server4 ~]# mysql -h 192.168.36.135 -P 7001 -u wxh -pwestos
MySQL [(none)]> select * from test.t1;
+----+--------+
| c1 | c2 |
+----+--------+
| 1 | Luis |
| 2 | wxh |
| 3 | westos |
+----+--------+
3 rows in set (0.00 sec)
在server1上查看网络连接
[root@server1 mysql]# yum install -y lsof
[root@server1 mysql]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 15184 mysql 22u IPv6 87530 0t0 TCP *:mysql (LISTEN)
mysqld 15184 mysql 83u IPv6 93365 0t0 TCP server1:mysql->server4:55504 (ESTABLISHED)
[root@server4 ~]# mysql -h 192.168.36.135 -P 7002 -u wxh -pwestos
[root@server3 mysql]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 30811 mysql 22u IPv6 73360 0t0 TCP *:mysql (LISTEN)
mysqld 30811 mysql 81u IPv6 78061 0t0 TCP server3:mysql->server4:38594 (ESTABLISHED)