使用Docker快速搭建InnoDB Cluster集群的过程

感谢

感谢方神的大力帮助,自己对数据库基本一窍不通.只是照葫芦画瓢做出来的.
感谢来自如下两个网站的资料,我进行了一定程度的融合.

https://blog.csdn.net/weixin_43972854/article/details/105792720
https://github.com/wagnerjfr/mysql-innodb-cluster

需要说明的一点是,我这边使用Docker-compose的放肆搭建了三个数据库节点,然后使用github中的内容进行手动创建集群.
再次感谢提供帮助的方神.
本次仅是一个简单测试,无法进入生产, 仅能够进行一些简单功能场景验证.

背景说明

客户想要真正的集群,可以自主切换主从,高可用.
主从复制模式需要手工触发, 不符合客户的高端需求.所以只能选择Mysql Cluster

资料准备

主要是两个镜像:
注意 mysql/mysql-server 其实内部带了 mysqlsh的脚本工具比较方便.

mysql/mysql-router           8.0.28    acccea81feeb   4 weeks ago    241MB
mysql/mysql-server           8.0.28    434c35b82b08   4 weeks ago    417MB

以及一份docker-compose.yaml以及一份 mysql-server.env 作为密码信息

搭建数据库

创建网络信息
创建数据库存储需要的目录

docker network create sg-net
mkdir -p /mysqlcluster/data/mysql-{1,2,3}

搭建数据库

docker-compose.yaml文件
注意我这边将文件放到了 /mysqlcluste目录下
注意我这边创建了3个数据库. 密码使用 mysql-server.env 目录下的内容
注意这里面的密码很重要.参数是我自己选取的不一定完全正确.

version: '3'
services:
  mysql-server-1:
    env_file:
      - mysql-server.env
    image: mysql/mysql-server:8.0.28
    container_name: mysql-server-1
    command: ["mysqld","--server_id=1","--skip_ssl","--innodb_ft_min_token_size=1","--ft_min_word_len=1","--ngram_token_size=1","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve", "--default_authentication_plugin=mysql_native_password","--lower-case-table-names=1","--innodb_strict_mode=0","--character_set_server=utf8"]
    ports:
      - "3301:3306"
    restart: always
    volumes:
      - ./data/mysql-1:/var/lib/mysql
    networks:
      - sg-net
  mysql-server-2:
    env_file:
      - mysql-server.env
    image: mysql/mysql-server:8.0.28
    container_name: mysql-server-2
    command: ["mysqld","--server_id=2","--skip_ssl","--innodb_ft_min_token_size=1","--ft_min_word_len=1","--ngram_token_size=1","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve", "--default_authentication_plugin=mysql_native_password","--lower-case-table-names=1","--innodb_strict_mode=0","--character_set_server=utf8"]
    ports:
      - "3302:3306"
    restart: always
    volumes:
      - ./data/mysql-2:/var/lib/mysql
    depends_on:
      - mysql-server-1
    networks:
      - sg-net
  mysql-server-3:
    env_file:
      - mysql-server.env
    image: mysql/mysql-server:8.0.28
    container_name: mysql-server-3
    command: ["mysqld","--server_id=3","--skip_ssl","--innodb_ft_min_token_size=1","--ft_min_word_len=1","--ngram_token_size=1","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve", "--default_authentication_plugin=mysql_native_password","--lower-case-table-names=1","--innodb_strict_mode=0","--character_set_server=utf8"]
    ports:
      - "3303:3306"
    restart: always
    volumes:
      - ./data/mysql-3:/var/lib/mysql
    depends_on:
      - mysql-server-1
    networks:
      - sg-net
networks:
  sg-net:
    external: true

搭建过程

mysql-server.env的内容为:

MYSQL_ROOT_PASSWORD=Yourpassword
MYSQL_ROOT_HOST=%

搭建过程

启动数据库服务

在 /mysqlcluster 的目录下面
执行 
docker-compose up -d 
就可以启动服务.
注意可以进行相关的查看
docker ps
CONTAINER ID   IMAGE                       COMMAND                  CREATED        STATUS                  PORTS                                                                                    NAMES
5f576a916dff   mysql/mysql-server:8.0.28   "/entrypoint.sh mysq…"   13 hours ago   Up 12 hours (healthy)   33060-33061/tcp, 0.0.0.0:3302->3306/tcp, :::3302->3306/tcp                               mysql-server-2
67f944d464db   mysql/mysql-server:8.0.28   "/entrypoint.sh mysq…"   13 hours ago   Up 12 hours (healthy)   33060-33061/tcp, 0.0.0.0:3303->3306/tcp, :::3303->3306/tcp                               mysql-server-3
2158117042dd   mysql/mysql-server:8.0.28   "/entrypoint.sh mysq…"   13 hours ago   Up 12 hours (healthy)   33060-33061/tcp, 0.0.0.0:3301->3306/tcp, :::3301->3306/tcp                               mysql-server-1

搭建过程

进行用户初始化以及简单设置
需要说明的一点是 mysql-server-1 等的容器名字是通过 docker-compose.yaml 文件进行制定的.

创建用户:
for N in 1 2 3 
do docker exec -it mysql-server-$N mysql -uroot -pYourpassword \
  -e "CREATE USER 'inno'@'%' IDENTIFIED BY 'Yourpassword';" \
  -e "GRANT ALL privileges ON *.* TO 'inno'@'%' with grant option;" \
  -e "reset master;"
done
修改用户密码
for N in 1 2 3 
do docker exec -it mysql-server-$N mysql -uroot -pYourpassword \
  -e "alter USER 'inno'@'%' IDENTIFIED BY 'Yourpassword';" 
done

搭建过程

查看用户和数据库的机器名信息

for N in 1 2 3 
do docker exec -it mysql-server-$N mysql -uinno -pYourpassword \
  -e "SHOW VARIABLES WHERE Variable_name = 'hostname';" \
  -e "SELECT user FROM mysql.user where user = 'inno';"
done

搭建过程

使用 mysqlsh 进行实例的创建工作

docker exec -it mysql-server-1 mysqlsh -uroot -pYourpassword -S/var/run/mysqld/mysqlx.sock
然后执行命令
dba.configureInstance("inno@mysql-server-1:3306") --password=Yourpassword
然后另外两个容器都需要同步进行处理
docker exec -it mysql-server-2 mysqlsh -uroot -pYourpassword -S/var/run/mysqld/mysqlx.sock
dba.configureInstance("inno@mysql-server-2:3306") --password=Yourpassword

docker exec -it mysql-server-3 mysqlsh -uroot -pYourpassword -S/var/run/mysqld/mysqlx.sock
dba.configureInstance("inno@mysql-server-3:3306") --password=Yourpassword
注意 需要使用 \q 的方式退出容器执行窗口

搭建过程

注意此时需要进行重启容器的操作,不然无法进行后续工作

docker restart $(docker ps -qa )

进行下一步的处理
注意需要连接具体的数据库需要输入密码

docker exec -it mysql-server-1 mysqlsh -uroot -pYourpassword -S/var/run/mysqld/mysqlx.sock

\c inno@mysql-server-1:3306

创建集群以及可以查看集群状态

var cluster = dba.createCluster("mycluster")
cluster.status()

这个时候集群是不完整的提示需要至少3个才可以实现
然后执行如下命令进行添加
注意需要根据反馈的数据进行添加, 需要输入 c 作为 Clone的含义

cluster.addInstance("inno@mysql-server-2:3306")
cluster.addInstance("inno@mysql-server-3:3306")

mysql-router 创建

注意这个时候很难使用本地文件方式搭建router,可以使用容器方式搭建router
直接执行docker run 即可
参数很简单不做解释

docker run -d --name mysql-router --net=sg-net \
   -e MYSQL_HOST=mysql-server-1 \
   -e MYSQL_PORT=3306 \
   -e MYSQL_USER=inno \
   -e MYSQL_PASSWORD=Yourpassword \
   -e MYSQL_INNODB_CLUSTER_MEMBERS=3 \
   -p 6446:6446 \
   -p 6447:6447 \
   --restart=always \
   mysql/mysql-router

集群状况查看

可以进入任意一个节点进行查看

docker exec -it mysql-server-1 mysqlsh -uroot -pYourpassword -S/var/run/mysqld/mysqlx.sock
# 注意需要先定义一下 var cluster 才可以进行下一步的操作.
var cluster = dba.getCluster("mycluster");
cluster.status()

也可以使用sql方式进行查看集群状态

docker exec -it mysql-server-1 mysqlsh -uroot -pYourpassword -S/var/run/mysqld/mysqlx.sock
# 输入 \sql  进入sql模式
select * from performance_schema.replication_group_members;
一般效果为: 
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | edc0b4de-9177-11ec-8bd7-0242ac120002 | 8ba858033be2 |        3306 | ONLINE       | PRIMARY     | 8.0.28         | XCom                       |
| group_replication_applier | ee39a004-9177-11ec-8bcf-0242ac120003 | f9a2648c10c0 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
| group_replication_applier | ee3e5516-9177-11ec-8b0d-0242ac120004 | 0a0b93abe0e0 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+

断电之后处理

需要说明一点的是本次测试是在一个机器上面进行,断电重启集群会坏掉.
当然只是docker stop 一个容器, 然后再次进行docker restart 是不会导致集群挂掉的.
每次重启机器需要手动进行维护方法主要如下:

# 因为是异常断电,理论上3个机器是同事掉线的可以选择任何一个进行如下处理
docker exec -it mysql-server-1 mysqlsh -uinno -pYourpassword -S/var/run/mysqld/mysqlx.sock
 
\sql # 进入sql模式 
# 执行如下3个SQL
SET GLOBAL group_replication_bootstrap_group=ON;
start group_replication;
SET GLOBAL group_replication_bootstrap_group=Off;
# 注意其他两个数据库服务只需要执行:
start group_replication;
即可.

重启某节点的演练.

重启主节点会看到集群有3个online,减少到两个:

docker stop mysql-server-1
对比一下节点信息
 MySQL  localhost+  SQL > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | edc0b4de-9177-11ec-8bd7-0242ac120002 | 8ba858033be2 |        3306 | ONLINE       | PRIMARY     | 8.0.28         | XCom                       |
| group_replication_applier | ee39a004-9177-11ec-8bcf-0242ac120003 | f9a2648c10c0 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
| group_replication_applier | ee3e5516-9177-11ec-8b0d-0242ac120004 | 0a0b93abe0e0 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.0027 sec)
 MySQL  localhost+  SQL > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | ee39a004-9177-11ec-8bcf-0242ac120003 | f9a2648c10c0 |        3306 | ONLINE       | PRIMARY     | 8.0.28         | XCom                       |
| group_replication_applier | ee3e5516-9177-11ec-8b0d-0242ac120004 | 0a0b93abe0e0 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+

启动节点会看到集群会自愈

docker start mysql-server-1
# 会发现会自动 recovering 然后进行Secondary的状态
 MySQL  localhost+  SQL > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | edc0b4de-9177-11ec-8bd7-0242ac120002 | 8ba858033be2 |        3306 | RECOVERING   | SECONDARY   | 8.0.28         | XCom                       |
| group_replication_applier | ee39a004-9177-11ec-8bcf-0242ac120003 | f9a2648c10c0 |        3306 | ONLINE       | PRIMARY     | 8.0.28         | XCom                       |
| group_replication_applier | ee3e5516-9177-11ec-8b0d-0242ac120004 | 0a0b93abe0e0 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.0005 sec)
 MySQL  localhost+  SQL > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | edc0b4de-9177-11ec-8bd7-0242ac120002 | 8ba858033be2 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
| group_replication_applier | ee39a004-9177-11ec-8bcf-0242ac120003 | f9a2648c10c0 |        3306 | ONLINE       | PRIMARY     | 8.0.28         | XCom                       |
| group_replication_applier | ee3e5516-9177-11ec-8b0d-0242ac120004 | 0a0b93abe0e0 |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.0006 sec)
 MySQL  localhost+  SQL >

数据库

使用Docker快速搭建InnoDB Cluster集群的过程

感谢

背景说明

资料准备

搭建数据库

搭建数据库

搭建过程

搭建过程

搭建过程

搭建过程

搭建过程

搭建过程

mysql-router 创建

集群状况查看

断电之后处理

重启某节点的演练.

相关

mysql数据库死锁的产生原因及解决办法

【数据库】快速安装 SQL Server Express

关于SQL递归查询在不同数据库中的实现方法

GraphX 在图数据库 Nebula Graph 的图计算实践

主流开源分布式图数据库 Benchmark

图数据库 Nebula Graph 的安装部署

图数据库 Nebula Graph 是什么

Windows安装SQL Server数据库

SQL Server 维护计划实现数据库备份（Step by Step）(转)

mysql查出来的时间跟数据库相差13/14小时

Gin中记录Gorm数据库表生成-查询使用

如何用时序数据库 CTSDB 与 TARS 结合，解决海量监控数据难题

标签