MySQL8.0.12_InnoDB_Cluster
#修改其他节点的主机名
hostnamectl set-hostname node2
hostnamectl set-hostname node3
su -
vim /etc/hosts
192.168.92.101 node1
192.168.92.102 node2
192.168.92.103 node3
#三台服务器设置免密
ssh-keygen -t rsa
ssh-copy-id node1
ssh-copy-id node2
ssh-copy-id node3
#查看ssh-keygen是否成功
ssh node1 date
ssh node2 date
ssh node3 date
1、在三个节点上源码安装MySQL 8.0.12，省略
2、修改my.cnf配置文件
vi /data/mysqldata/3306/my.cnf [mysqld]下添加
#节点1
server_id=1
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.92.101:24901"
loose-group_replication_group_seeds= "192.168.92.101:24901,192.168.92.102:24901,192.168.92.103:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.92.0/24"
---------------------
# 每个节点server_id、loose-group_replication_local_address值不一样，根据IP做相应的更改
# 多主模式添加一下两行
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
#节点2
server_id=2
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.92.102:24901"
loose-group_replication_group_seeds= "192.168.92.101:24901,192.168.92.102:24901,192.168.92.103:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.92.0/24"
loose-group_replication_allow_local_disjoint_gtids_join=ON
#节点3
server_id=3
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.92.103:24901"
loose-group_replication_group_seeds= "192.168.92.101:24901,192.168.92.102:24901,192.168.92.103:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.92.0/24"
loose-group_replication_allow_local_disjoint_gtids_join=ON
3、在三个节点创建复制账户
mysql -uroot -poracle
SET SQL_LOG_BIN=0;
#创建用户并identified密码
CREATE USER root@'%' IDENTIFIED BY 'oracle';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT BACKUP_ADMIN, BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_SLAVE_ADMIN, RESOURCE_GROUP_ADMIN, RESOURCE_GROUP_USER, ROLE_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN, XA_RECOVER_ADMIN ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
4、在第一个节点创建集群并添加节点二、节点三
下载mysql shell和router：https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el7
rpm -ivh mysql-shell-8.0.12-1.el7.x86_64.rpm
rpm -ivh mysql-router-8.0.12-1.el7.x86_64.rpm
#查看组复制插件
ls /usr/local/src/mysql/plugin/group_replication/
#备库是虚拟机复制，需要更改server_uuid
cd /data/mysqldata/3306/data
mv auto.cnf auto.cnf.bak
/usr/local/src/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf --user=mysql &
#进入mysql shell 退出mysqlshell：ctrl+z或者\q
mysqlsh
#或者\connect root@node1:3306连接到mysql
shell.connect('root@node1:3306')
dba.configureLocalInstance()
shell.connect('root@node2:3306')
dba.configureLocalInstance()
shell.connect('root@node3:3306')
dba.configureLocalInstance()
#检测是否就绪：
dba.checkInstanceConfiguration('root@node1:3306')
shell.connect('root@node1:3306')
var cluster=dba.createCluster('myCluster')
cluster.addInstance('root@node2:3306');
cluster.addInstance('root@node3:3306');
cluster.status();
#mysql8.0rpm包安装方式报错
Dba.createCluster: error installing plugin 'group_replication': node1:3306: Can't open shared library '/usr/lib64/mysql/plugin/group_replication.so' (errno: 2 /usr/lib64/mysql/plugin/group_replication.so: symbol X509_check_ip_asc, version libcrypto.so.10 not defined in file libcrypto.so) (RuntimeError)
5、所有节点停止后重启集群：
var cluster = dba.rebootClusterFromCompleteOutage('myCluster');
6、配置 MySQL Router
#报错：Configuration syntax error: Option line before start of section
mysqlrouter --bootstrap root@node1:3306 --user=mysqlrouter
mysqlrouter &
----------------------
#mysql router配置
#安装mysqlrouter：
vi /etc/mysqlrouter/mysqlrouter.conf 设置连接数:
max_connections=1024
[DEFAULT]
logging_folder = /var/log/mysqlrouter/
plugin_folder = /usr/lib64/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter
[logger]
level = INFO
[routing:read_write]
bind_address = 192.168.92.101
[routing:read_only]
bind_address = 192.168.92.101
bind_port = 7002
mode = read-only
destinations = node2:3306,node3:3306
protocol=classic
max_connections=1024
[keepalive]
interval = 60
----------------------
max_connections=1024
[DEFAULT] logging_folder = /var/log/mysqlrouter/ plugin_folder = /usr/lib64/mysqlrouter runtime_folder = /var/run/mysqlrouter config_folder = /etc/mysqlrouter
[logger] level = INFO
[routing:read_write] bind_address = 192.168.9.102
[routing:read_only] bind_address = 192.168.9.102 bind_port = 7002 mode = read-only destinations = vm002:3306,vm003:3306 protocol=classic max_connections=1024
[keepalive] interval = 60
#重启mysqlrouter：
#systemctl restart mysqlrouter
-----------------------------------
create database mytest default character set utf8;
create table test(id int,name varchar(200);
#GR环境下要求每个表都需要有主键，没有主键就会报错
alter table test add primary key pk_id(id);
insert into test values (1,'n1');
----------------------------------------------------------------------------------------------------
#系统重启后需要重启集群
mysqlsh
shell.connect('root@localhost:3306');
dba.rebootClusterFromCompleteOutage('myCluster')
#ReferenceError: cluster is not defined，需要先执行cluster=dba.getCluster();
cluster=dba.getCluster();
cluster.status();
#从库查看读写模式
mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
+-----------------------+-------+
----------------------------------------------------------------------------------------------------
注意：集群启动失败执行以下操作
# 在失败节点执行
SELECT @@global.gtid_executed;
# 在主节点执行
SET GTID_NEXT='4e2cb39d-6af1-11e8-a65c-000c29cdfd39:1';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';
