![5be749fa12901df9043392e610262bb3.png](https://i-blog.csdnimg.cn/blog_migrate/1ae6145d802e60ddb49f854d6311340d.jpeg)
本文分享如何在docker环境,搭建Mysql Innodb Cluster集群。
MySQL InnoDB Cluster为用户提供了完整的MySQL高可用性解决方案。
本文使用了以下组件:
MySQL Shell :新的mysql 客户端工具,具备JavaScript和python可编程能力,作为搭建InnoDB Cluster管理工具。
MySQL Router :访问路由转发中间件,内置读写分离,负载均衡,并提供应用程序访问的failover能力。
MySQL Group Replication:Mysql 高可用与高扩展的解决方案。
准备MGR节点
上一篇文章中分享了不使用Mysql Shell,如何在docker环境下搭建Mysql MGR集群,请参考 -- docker环境,搭建mysql mgr单主集群
现在搭建Mysql Innodb Cluster,继续使用上一篇文章中的bin/mysql-mgr:8.0.18镜像,不过docker-entrypoint.sh需添加如下内容
if [ "$DEPLOY" = 'cluster' ]; then
cat>>/etc/mysql/mysql.conf.d/mysqld.cnf<<EOF
binlog_checksum=NONE
enforce_gtid_consistency=ON
gtid_mode=ON
server_id=$ID
EOF
exec gosu mysql mysqld
fi
如果运行shell脚步时传入cluster参数,则配置binlog_checksum,enforce_gtid_consistency,gtid_mode,server_id四个选项,这是搭建innodb cluster必须要的配置。
启动mgr容器
for i in `seq 1 3`; do
sudo docker run -d --name=mgr-$i --network mysql-net --network-alias mgr-$i
-e DEPLOY=cluster -e ID=$i bin/mysql-mgr:8.0.18
done
通过mysql-mgr.sh,创建clusteradmin用户
sudo docker exec mgr-1 /usr/local/bin/mysql-mgr.sh
sudo docker exec mgr-2 /usr/local/bin/mysql-mgr.sh
sudo docker exec mgr-3 /usr/local/bin/mysql-mgr.sh
关于mysql-mgr.sh,请回顾docker环境,搭建mysql mgr单主集群
这里运行shell脚步没有传参,所以只会创建用户,不会启动MGR插件。
这里使用的是clusteradmin用户,创建该用户的sql如下
CREATE USER 'clusteradmin'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION ;
使用mysql-shell搭建MGR集群
下载mysql-shell_8.0.18-1ubuntu16.04_amd64.deb,构建bin/mysql-shell:8.0.18镜像,Dockerfile内容如下
FROM bin/ubuntu:16.04
WORKDIR /var/lib
COPY mysql-shell_8.0.18-1ubuntu16.04_amd64.deb .
RUN apt-get update
&& apt-get install -yqq libcurl3 libffi6 libpython3.5 python3
&& dpkg -i mysql-shell_8.0.18-1ubuntu16.04_amd64.deb
&& rm mysql-shell_8.0.18-1ubuntu16.04_amd64.deb
启动mysql-shell容器
sudo docker run -it --rm --network mysql-net bin/mysql-shell:8.0.18 /bin/bash
下面使用mysql-shell来搭建MGR集群
(MGR节点容器的ip为172.19.0.2/3/4)
root$ mysqlsh 'clusteradmin'@'172.19.0.2':3306
Please provide the password for 'clusteradmin@172.19.0.2:3306':*****
JS > dba.checkInstanceConfiguration('clusteradmin@172.19.0.2:3306')
...
{
"status": "ok"
}
JS > dba.checkInstanceConfiguration('clusteradmin@172.19.0.3:3306')
dba.checkInstanceConfiguration可以检查用户权限,mysql配置等是否满足搭建MGR集群的条件。三个MGR节点都要检查一遍。
创建一个mgr集群
JS > dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance '172.19.0.2:3306'.
...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
...
这时会执行如下操作:
在连接的节点上创建mysql.mysql_innodb_cluster_metadata存储元数据信息
验证配置信息
将此节点注册成seed节点
创建必要的管理账号
启动 Group Replication
添加其他节点
mysql-js> var cluster=dba.getCluster('mycluster')
mysql-js> cluster.addInstance('clusteradmin@172.19.0.3:3306')
...
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
...
Waiting for server restart..
这时mysql shell会关闭172.19.0.3这个docker容器
我们需要手动启动该docker 容器
sudo docker start mgr-2
这时shell会继续执行,执行成功则提示
The instance '172.19.0.3:3306' was successfully added to the cluster
另一个节点同样操作。
进入mgr节点,查看集群信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 3bda4d8d-5fb9-11ea-83ff-0242ac130004 | 8b1980ce28ca | 3306 | ONLINE | SECONDARY | 8.0.18 |
| group_replication_applier | d1cf31de-5fb8-11ea-96fb-0242ac130003 | 9285dd1f5f42 | 3306 | ONLINE | SECONDARY | 8.0.18 |
| group_replication_applier | f1a60665-5fb7-11ea-8ac9-0242ac130002 | 1e26ccb22f83 | 3306 | ONLINE | PRIMARY | 8.0.18 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
可以看到mgr集群已经搭建完成,master节点是mgr-1(f1a60665-5fb7-11ea-8ac9-0242ac130002是mgr-1的service id,可以通过/var/lib/mysql/auto.cnf查看)。
使用mysql-router转发请求
下载mysql-router-community_8.0.18-1ubuntu16.04_amd64.deb,构建bin/mysql-router:8.0.18,Dockerfile如下
FROM bin/ubuntu:16.04
WORKDIR /usr/lib
COPY mysql-router-community_8.0.18-1ubuntu16.04_amd64.deb .
RUN dpkg -i mysql-router-community_8.0.18-1ubuntu16.04_amd64.deb
&& rm mysql-router-community_8.0.18-1ubuntu16.04_amd64.deb
&& groupadd -r mysql && useradd -r -g mysql mysql
启动router
sudo docker run -it --name mysql-router --network mysql-net bin/mysql-router:8.0.18 /bin/bash
mysql route可以自动生成配置,但它需要通过
IP登录MGR集群中master节点的root用户,
所以需要在master节点创建一个'root'@'172.19.0.5'用户并授权(172.19.0.5是mysql-router的IP)
CREATE USER 'root'@'172.19.0.5' IDENTIFIED WITH sha256_password BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.19.0.5' WITH GRANT OPTION ;
FLUSH PRIVILEGES;
自动生成配置
# mysqlrouter --bootstrap 172.19.0.2:3306 --directory /opt/mysqlrouter --user mysql
查看生成配置的配置
[routing:mycluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=first-available
protocol=classic
[routing:mycluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://mycluster/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic
[routing:mycluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=first-available
protocol=x
[routing:mycluster_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://mycluster/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x
可以看到,生成的配置分为protocol模式和非protocol模式。
每种模式都创建了ro只读端口和rw读写端口。
启动mysql router
# gosu mysql bash start.sh
这是mysql router就可以转发mysql请求了,这是可以通过Ctrl+P+Q进行退出(而不关闭)容器。
通过mysql-router连接MGR集群
进入mgr-2容器,通过mysql-router连接MGR集群
$ sudo docker exec -it mgr-2 /bin/bash
root # mysql -h172.19.0.5 -uclusteradmin -P6446 -p
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0e19b738-6776-11ea-a9c2-0242ac130002 | 7c1c0ad54481 | 3306 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | 7c249d4d-6776-11ea-bbfe-0242ac130003 | 80a621c44a0f | 3306 | ONLINE | SECONDARY | 8.0.18 |
| group_replication_applier | 959de6ec-6776-11ea-bbf7-0242ac130004 | a00ed2787b02 | 3306 | ONLINE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
172.19.0.5是mysql-router的容器IP,这里通过6446读写端口和clusteradmin用户登录到MGR集群。
执行一些简单的sql操作
mysql> create database cms;
mysql> use cms;
mysql> create table blog (id bigint primary key auto_increment,title varchar(128),content text);
mysql> insert blog values(1, 'java', 'hello,java');
这时,我们把master容器(即mgr-1容器)停止,再来看MGR集群情况
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 7c249d4d-6776-11ea-bbfe-0242ac130003 | 80a621c44a0f | 3306 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | 959de6ec-6776-11ea-bbf7-0242ac130004 | a00ed2787b02 | 3306 | ONLINE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
可以看到master节点已经自动切换到mgr-2了。(7c249d4d-6776-11ea-bbfe-0242ac130003是mgr-2的service id,可以通过/var/lib/mysql/auto.cnf查看)
继续执行sql操作
mysql> insert blog values(2, 'mysql', 'hello,mysql');
MGR集群切换主节点后,mysql-router可以根据Mysql InnoDB Cluster中的metadata进行调整,保证client 的请求被路由到新的主服务器节点,所以继续执行sql是可以成功的(可能有一两次失败,重试即可),client不需要做调整。
如果您觉得本文不错,欢迎关注我的微信公众号,您的关注是我坚持的动力!
![c1f3aa1691cb32540af507e4da33a541.png](https://i-blog.csdnimg.cn/blog_migrate/5aab53641a747bab1a1af957eaaa76b6.png)