已有的MGR集群上配置InnoDB Cluster

1. 环境信息

 

ip	主机名	mysqld端口	replication通信端口	安装的软件	备注
10.45.53.31	rac2	3306	33061	mysql，mysql-shell	mgr多主集群
172.16.23.170	shard12	3306	33061	mysql，mysql-shell	mgr多主集群
172.16.23.171	catdb	3306	33061	mysql，mysql-shell	mgr多主集群
10.45.53.30	rac1	null	null	mysql客户端，mysql-route	测试mysql-route和读写分离

 

2. mysql-shell三节点安装配置

（1）安装rpm包

rpm -ivh mysql-shell-commercial-8.0.19-1.1.el7.x86_64

rpm -ivh mysql-shell-commercial-debuginfo-8.0.19-1.1.el7.x86_64

（2）建myshell用户，供连接mysql-shell,或者直接用root

GRANT SELECT ON mysql_innodb_cluster_metadata.* TO myshell@'%' identified by '123';

GRANT SELECT ON performance_schema.global_status TO myshell@'%';

GRANT SELECT ON performance_schema.replication_applier_configuration TO myshell@'%';

GRANT SELECT ON performance_schema.replication_applier_status TO myshell@'%';

GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO myshell@'%';

GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO myshell@'%';

GRANT SELECT ON performance_schema.replication_connection_configuration TO myshell@'%';

GRANT SELECT ON performance_schema.replication_connection_status TO myshell@'%';

GRANT SELECT ON performance_schema.replication_group_member_stats TO myshell@'%';

GRANT SELECT ON performance_schema.replication_group_members TO myshell@'%';

GRANT SELECT ON performance_schema.threads TO myshell@'%' WITH GRANT OPTION;

GRANT CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHUTDOWN, SUPER ON *.* TO 'myshell'@'%' WITH GRANT OPTION;

GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'myshell'@'%' WITH GRANT OPTION;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'myshell'@'%' WITH GRANT OPTION;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'myshell'@'%' WITH GRANT OPTION;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'myshell'@'%' WITH GRANT OPTION;

（3）检查三节点是否符合创建cluster

先停三节点组复制

STOP GROUP_REPLICATION;

节点一进入mysql-shell检查参数

mysqlsh myshell@rac2:3306

dba.checkInstanceConfiguration('myshell@rac2:3306')

若参数符合条件，检查结果如下

如果参数不通过，会有推荐值，建议修改my.cnf配置文件重启mysql或直接在线修改

节点二和节点三也可以在rac2主机上检查配置是否通过

dba.checkInstanceConfiguration('myshell@shard12:3306')

dba.checkInstanceConfiguration('myshell@catdb:3306')

（4）在一节点创建cluster--prodCluster

mysqlsh myshell@rac2:3306

var cluster = dba.createCluster('prodCluster')

创建的过程中，会有提示warning，说是需要执行dba.configureLocalInstance()来完善，这个命令三节点只能在各自的主机上进mysql-shell执行，执行过之后，会更新一些参数和修改完善/etc/my.cnf参数

dba.configureLocalInstance(myshell@rac2:3306)

（5）添加其他节点的实例信息

cluster.addInstance('myshell@shard12:3306');

添加的过程中，会有提示warning，说是需要执行dba.configureLocalInstance()来完善，这个命令三节点只能在各自的主机上进mysql-shell执行，执行过之后，会更新一些参数和修改完善/etc/my.cnf参数dba.configureLocalInstance('myshell@shard12:3306')

cluster.addInstance('myshell@catdb:3306')

由于catdb的组复制停的最晚，且其他节点的组复制停了之后，我又在catdb上做了dml操作，添加节点到集群时报错如下，意思是添加节点的gtid不能比cluster的其他两节点大

修复过程：mysqldump对一节点备份， mysqldump -uroot -p123 --single-transaction --master-data=2 --all-databases > all.sql，导入到catdb时报ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty，通过如下命令将GLOBAL.GTID_EXECUTED置空，重新导入成功

reset master;

source all.sql

然后重新将catdb通过rac2主机加入cluster，cluster.addInstance('myshell@catdb:3306')

catdb上执行：dba.configureLocalInstance('myshell@catdb:3306')

（6）查看cluster状态和配置

mysqlsh myshell@catdb:3306

var cluster = dba.getCluster()

cluster.status();

cluster.describe();

如果三节点均down，起了两个节点，另一节点没起，需要cluster.rescan()来重新定义cluster，第三个节点起了后，再重新加入集群

cluster.rescan()

cluster.addInstance()

 

mgr组复制已经自动被拉起来了

至此mysql innodb cluster部署完成，下面安装mysql-route实现集群的读写分离（负载均衡）

 

3.mysql-route安装配置，测试

（1）rpm安装mysql-route

The recommended deployment of MySQL Router is on the same host as the application.

When using a production deployment, we recommend deploying one MySQL Router instance to each machine used to host one of your client applications.

生产环境中建议将mysql-route安装在应用机器上，并且每一种应用安装配置一个mysql-route

rpm -ivh mysql-router-commercial-8.0.19-1.1.el7.x86_64.rpm

（2）配置 /etc/mysqlrouter/mysqlrouter.conf

RPM and Debian : /etc/mysqlrouter/mysqlrouter.conf

安装会自动生成/etc/mysqlrouter/mysqlrouter.conf，建立/run/mysqlrouter，/usr/lib64/mysqlrouter， /var/log/mysqlrouter

mysqlrouter --help 可以看到默认mysqlrouter的配置文件

多主模式下的mgr，可以这样配置 /etc/mysqlrouter/mysqlrouter.conf

# MySQL Router configuration file

#

# Documentation is available at

#    http://dev.mysql.com/doc/mysql-router/en/

[DEFAULT]

logging_folder = /var/log/mysqlrouter

runtime_folder = /var/run/mysqlrouter

config_folder = /etc/mysqlrouter

connect_timeout = 30

read_timeout=30

#Maximum number of connections assigned to a routed destination MySQL server

max_connections = 1024

protocol=classic

[logger]

level = INFO

[routing:rw_mode]

#bind_address默认是127.0.0.1

#多主节点可以配置写负载均衡，将bind_address设为0.0.0.0

bind_address=0.0.0.0

#读写端口6446

bind_port=6446        

mode = read-write

destinations = 10.45.53.31:3306,172.16.23.170:3306,172.16.23.171:3306

#根据group_replication_member_weight设置destinations三节点的顺序，否则读写模式访问从节点会报read-only #错而不会转到下一个节点

routing_strategy = round-robin

## read-write模式下：路由策略多主模式可以将其设为round-robin，单主模式可设first-available

[routing:ro_mode]

bind_address = 0.0.0.0

#读端口6447

bind_port = 6447    

mode = read-only

destinations = 10.45.53.31:3306,172.16.23.170:3306,172.16.23.171:3306

routing_strategy = round-robin

## read-only模式下：单主模式和多主模式可设路由策略为round-robin

 

When using a single-primary cluster, read-write sessions are redirected to the single primary,

with a multi-primary cluster read-write sessions are redirected to one of the primary instances;

For incoming read-only connections MySQL Router redirects connections to one of the secondary instances in a round-robin fashion.

(轮询调度算法) 原理是每一次把来自用户的请求轮流分配给内部中的服务器，从1开始，直到N(内部服务器个数)，然后重新开始循环

（3）启动mysql-router

service mysqlrouter start

6447和6446端口已在rac1（10.45.53.30）上启动

（4）测试

创建测试用户

mysql> create user root@'%' identified by '123';

mysql> grant all privileges on *.* to root@'%';

读测试：

for i in `seq 1 10`; do mysql -h 10.45.53.30 -P6447 -uroot -p123 -e "select @@hostname;"; done

可看出来读是轮询访问10.45.53.31:3306,172.16.23.170:3306,172.16.23.171:3306

写测试：

create table test2 (id int auto_increment primary key);

for i in `seq 1 10`; do mysql -h 10.45.53.30 -P6446 -uroot -p123 -e "insert into test.test2 values(null);"; done

由于三节点的auto_increment_offset值分别为2，3，4，auto_increment_increment为7，可以看出写操作轮询访问了三节点

4667端口不是只能读，多主模式下访问4667端口可读写；单主模式下，取决于mysqlrouter.conf的[routing:ro_mode]是否配置了主节点，若配置了，则可读写，若没配置，只可读，通常情况下是配置的

高可用测试

To test if high availability works, simulate an unexpected halt by killing an instance. The cluster detects the fact that the instance left the cluster and reconfigures itself. Exactly how the cluster reconfigures itself depends on whether you are using a single-primary or multi-primary cluster, and the role the instance serves within the cluster.

In single-primary mode:

If the current primary leaves the cluster, one of the secondary instances is elected as the new primary, with instances prioritized by the lowest server_uuid. MySQL Router redirects read-write connections to the newly elected primary.

If a current secondary leaves the cluster, MySQL Router stops redirecting read-only connections to the instance.

In multi-primary mode:

If a current "R/W" instance leaves the cluster, MySQL Router redirects read-write connections to other primaries.

If a current "R/O" instance leaves the cluster,MySQL Router stops redirecting read-only connections to the instance.

多主模式下停一个节点

停两个节点

其他

新增节点后需要修改conf文件并重新启动mysql-route
When MySQL Router is bootstrapped against a cluster, it records the server instance's addresses in its configuration file. If any additional instances are added to the cluster after bootstrapping the MySQL Router, they are not automatically detected and therefore are not used for connection routing.

To ensure that newly added instances are routed to correctly you must bootstrap MySQL Router against the cluster to read the updated metadata. This means that you must restart MySQL Router, include the --bootstrap option or modify /etc/mysqlrouter/mysqlrouter.conf.

实际上mysql-shell没什么大用处，可以不安装mysql-shell,只安装mysql-router即可实现读写分离

参考文档

https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-conf-options.html#option_mysqlrouter_routing_strategy

https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-working-with-cluster.html