感谢
- 感谢方神的大力帮助,自己对数据库基本一窍不通.只是照葫芦画瓢做出来的.
- 感谢来自如下两个网站的资料,我进行了一定程度的融合.
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_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
搭建过程
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()
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 >