1、构建mysql-shell 镜像
由于没有好用的mysql-shell镜像，这里先手工构建一个
1.1 下载 mysql-shell
fedora 32 版本
下载 mysql-shell-8.0.22-1.fc32.x86_64.rpm
下载地址
https://dev.mysql.com/downloads/shell/1.2 编写docker file
FROM fedora
MAINTAINER Wang Changjin <wangcj@cares.sh.cn>
ADD mysql-shell-8.0.22-1.fc32.x86_64.rpm /mysql-shell-8.0.22-1.fc32.x86_64.rpm
COPY cluster.js /
RUN rpm -ivh /mysql-shell-8.0.22-1.fc32.x86_64.rpm
ENTRYPOINT ["/bin/bash", "-c", "while true; do sleep 1; done"]
1.3 集群创建脚本 cluster.js
try {
print('Setting up InnoDB cluster...\n');
shell.connect('root@mysqldb3:3306', '123456');
var cluster = dba.createCluster("test",{autoRejoinTries:1000});
print('Adding instances to the cluster.');
cluster.addInstance({user: "root", host: "mysqldb1", port: 3306, password: '123456'});
print('.');
cluster.addInstance({user: "root", host: "mysqldb2", port: 3306, password: '123456'});
print('.\nInstances successfully added to the cluster.');
print('\nInnoDB cluster deployed successfully.\n');
} catch(e) {
print('\nThe InnoDB cluster could not be created.\n\nError: ' +
+ e.message + '\n');
}
1.4 目录结构
1.5 软件版本
操作系统：Fedora release 31 (Thirty One)
docker : 19.03.8
docker-compose : 1.25.4
2 启动 mysql innodb cluster 集群
2.1 mysqld 配置
总共启动三个实例，保证server_id 不同
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
server_id=1
max_connections=4096
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
default_storage_engine=INNODB
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_log_buffer_size=12M
innodb_flush_log_at_trx_commit=2
lower_case_table_names=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
expire_logs_days=1
skip-host-cache
skip-name-resolve
2.2 compose 文件
version: "3.7"
services:
mysql1:
image: mysql
volumes:
- /root/mysql_cluster/config/mysql1.cnf:/etc/mysql/my.cnf
- /etc/localtime:/etc/localtime
environment:
MYSQL_ROOT_PASSWORD: 123456
hostname: mysqldb1
container_name: mysqldb1
restart: always
networks:
mysql_cluster:
ipv4_address: 172.20.1.4
extra_hosts:
- "mysqldb2:172.20.1.2"
- "mysqldb3:172.20.1.3"
mysql2:
image: mysql
restart: always
volumes:
- /root/mysql_cluster/config/mysql2.cnf:/etc/mysql/my.cnf
- /etc/localtime:/etc/localtime
environment:
MYSQL_ROOT_PASSWORD: 123456
hostname: mysqldb2
container_name: mysqldb2
networks:
mysql_cluster:
ipv4_address: 172.20.1.2
extra_hosts:
- "mysqldb1:172.20.1.4"
- "mysqldb3:172.20.1.3"
mysql3:
image: mysql
restart: always
volumes:
- /root/mysql_cluster/config/mysql3.cnf:/etc/mysql/my.cnf
- /etc/localtime:/etc/localtime
environment:
MYSQL_ROOT_PASSWORD: 123456
hostname: mysqldb3
container_name: mysqldb3
networks:
mysql_cluster:
ipv4_address: 172.20.1.3
extra_hosts:
- "mysqldb1:172.20.1.4"
- "mysqldb2:172.20.1.2"
mysql-shell:
build:
context: .
volumes:
- /etc/localtime:/etc/localtime
container_name: mysql-shell
networks:
mysql_cluster:
ipv4_address: 172.20.1.5
extra_hosts:
- "mysqldb1:172.20.1.4"
- "mysqldb2:172.20.1.2"
- "mysqldb3:172.20.1.3"
mysql-router:
image: mysql/mysql-router
restart: always
environment:
MYSQL_HOST: mysqldb1
MYSQL_PORT: 3306
MYSQL_USER: root
MYSQL_PASSWORD: 123456
ports:
- "6446-6447:6446-6447"
depends_on:
- mysql1
- mysql2
- mysql3
volumes:
- /etc/localtime:/etc/localtime
container_name: mysql-router
networks:
mysql_cluster:
ipv4_address: 172.20.1.6
extra_hosts:
- "mysqldb1:172.20.1.4"
- "mysqldb2:172.20.1.2"
- "mysqldb3:172.20.1.3"
networks:
mysql_cluster:
ipam:
driver: default
config:
- subnet: "172.20.1.0/24"2.3 集群配置
2.3.1 启动脚本 start
docker-compose -f mysql.yaml up -d
check(){
ret=1
while [[ $ret != 0 ]]
do
sleep 1s
echo "wait for $1"
docker logs $1 > aa 2>&1
cat aa|grep 'port: 3306 MySQL Community Server - GPL' > /dev/null
ret=$?
done
}
check 'mysqldb1'
docker exec -i mysql-shell mysqlsh --uri='root:123456@mysqldb1:3306' << EOF
dba.checkInstanceConfiguration()
dba.configureLocalInstance()
EOF
check 'mysqldb2'
docker exec -i mysql-shell mysqlsh --uri='root:123456@mysqldb2:3306' << EOF
dba.checkInstanceConfiguration()
dba.configureLocalInstance()
EOF
check 'mysqldb3'
docker exec -i mysql-shell mysqlsh --uri='root:123456@mysqldb3:3306' << EOF
dba.checkInstanceConfiguration()
dba.configureLocalInstance()
EOF
docker exec -i mysql-shell mysqlsh -f /cluster.js << EOF
C
C
EOF
rm -fr aa > /dev/null 2>&1
2.3.2 执行启动脚本
[root@localhost mysql_cluster]# ./start
Creating network "mysql_cluster_mysql_cluster" with the default driver
Creating mysql_cluster_mysql3_1 ... done
Creating mysql-shell ... done
Creating mysql_cluster_mysql2_1 ... done
Creating mysql_cluster_mysql1_1 ... done
Creating mysql-router ... done
Logger: Tried to log to an uninitialized logger.
WARNING: Using a password on the command line interface can be insecure.
Validating MySQL instance at mysqldb1:3306 for use in an InnoDB cluster...
This instance reports its own address as mysqldb1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'mysqldb1:3306' is valid to be used in an InnoDB cluster.
Configuring MySQL instance at mysqldb1:3306 for use in an InnoDB cluster...
This instance reports its own address as mysqldb1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
The instance 'mysqldb1:3306' is valid to be used in an InnoDB cluster.
The instance 'mysqldb1:3306' is already ready to be used in an InnoDB cluster.
Logger: Tried to log to an uninitialized logger.
WARNING: Using a password on the command line interface can be insecure.
Validating MySQL instance at mysqldb2:3306 for use in an InnoDB cluster...
This instance reports its own address as mysqldb2:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'mysqldb2:3306' is valid to be used in an InnoDB cluster.
Configuring MySQL instance at mysqldb2:3306 for use in an InnoDB cluster...
This instance reports its own address as mysqldb2:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
The instance 'mysqldb2:3306' is valid to be used in an InnoDB cluster.
The instance 'mysqldb2:3306' is already ready to be used in an InnoDB cluster.
Logger: Tried to log to an uninitialized logger.
WARNING: Using a password on the command line interface can be insecure.
Validating MySQL instance at mysqldb3:3306 for use in an InnoDB cluster...
This instance reports its own address as mysqldb3:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'mysqldb3:3306' is valid to be used in an InnoDB cluster.
Configuring MySQL instance at mysqldb3:3306 for use in an InnoDB cluster...
This instance reports its own address as mysqldb3:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
The instance 'mysqldb3:3306' is valid to be used in an InnoDB cluster.
The instance 'mysqldb3:3306' is already ready to be used in an InnoDB cluster.
Logger: Tried to log to an uninitialized logger.
Setting up InnoDB cluster...
A new InnoDB cluster will be created on instance 'mysqldb3:3306'.
Validating instance configuration at mysqldb3:3306...
This instance reports its own address as mysqldb3:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysqldb3:33061'. Use the localAddress option to override.
Creating InnoDB cluster 'test' on 'mysqldb3:3306'...
Adding Seed Instance...
Adding instances to the cluster.Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
WARNING: A GTID set check of the MySQL instance at 'mysqldb1:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
mysqldb1:3306 has the following errant GTIDs that do not exist in the cluster:
c821ff23-89fa-11ea-a385-0242ac140104:1-5
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of mysqldb1:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
NOTE: Group Replication will communicate with other members using 'mysqldb1:33061'. Use the localAddress option to override.
Validating instance configuration at mysqldb1:3306...
This instance reports its own address as mysqldb1:3306
Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: mysqldb1:3306 is being cloned from mysqldb3:3306
** Stage DROP DATA: Completed
** Stage FILE COPY: Completed
** Stage PAGE COPY: Completed
** Stage REDO COPY: Completed
** Stage FILE SYNC: Completed
** Stage RESTART: Not Started
NOTE: mysqldb1:3306 is shutting down...
* mysqldb1:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 69.05 MB transferred in about 1 second (~1.00 B/s)
State recovery already finished for 'mysqldb1:3306'
.The instance 'mysqldb1:3306' was successfully added to the cluster.
WARNING: A GTID set check of the MySQL instance at 'mysqldb2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
mysqldb2:3306 has the following errant GTIDs that do not exist in the cluster:
c756c519-89fa-11ea-b9b6-0242ac140102:1-5
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of mysqldb2:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
NOTE: Group Replication will communicate with other members using 'mysqldb2:33061'. Use the localAddress option to override.
Validating instance configuration at mysqldb2:3306...
This instance reports its own address as mysqldb2:3306
Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: mysqldb2:3306 is being cloned from mysqldb3:3306
** Stage DROP DATA: Completed
** Stage FILE COPY: Completed
** Stage PAGE COPY: Completed
** Stage REDO COPY: Completed
** Stage FILE SYNC: Completed
** Stage RESTART: Not Started
NOTE: mysqldb2:3306 is shutting down...
* Waiting for server restart... ready
* mysqldb2:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 69.05 MB transferred in about 1 second (~1.00 B/s)
State recovery already finished for 'mysqldb2:3306'
The instance 'mysqldb2:3306' was successfully added to the cluster.
.
Instances successfully added to the cluster.
InnoDB cluster deployed successfully.
* Waiting for server restart... ready
2.3.3 查看集群状态
可以看到集群起来了
3 配置mysql router 进行连接
3.1 连接 6446 端口 mysql-router
可以看到路由到 mysqldb3
3.2 关闭mysqldb3，查看连接情况
[root@localhost mysql_cluster]# docker stop mysql_cluster_mysql3_1
mysql_cluster_mysql3_1
[root@localhost mysql_cluster]# docker exec -it mysql-shell mysqlsh --uri 'root@mysqldb1:3306'
Logger: Tried to log to an uninitialized logger.
Please provide the password for 'root@mysqldb1:3306': ******
MySQL Shell 8.0.19
Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@mysqldb1:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 60
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL mysqldb1:3306 ssl JS > var c = dba.getCluster('test')
MySQL mysqldb1:3306 ssl JS > c.status()
{
"clusterName": "test",
"defaultReplicaSet": {
"name": "default",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"mysqldb1:3306": {
"address": "mysqldb1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"mysqldb2:3306": {
"address": "mysqldb2:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"mysqldb3:3306": {
"address": "mysqldb3:3306",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003 (HY000): Can't connect to MySQL server on 'mysqldb3' (110)",
"status": "(MISSING)"
}
},
"topologyMode": "Multi-Primary"
},
"groupInformationSourceMember": "mysqldb1:3306"
}
MySQL mysqldb1:3306 ssl JS >
可以看到mysqldb3 不在线
mysqlrouter 连接到了mysqldb1
4 故障恢复
4.1 集群某个节点实例关闭
1、启动掉线节点实例
2、集群会自动加入掉线的节点
4.2 集群断电全部关闭
1、启动集群所有实例
2、dba.rebootClusterFromCompleteOutage()
4.3 集群某个节点掉线的情况下，然后整个集群断电关闭
1、启动集群所有实例
2、dba.rebootClusterFromCompleteOutage()
3、将断线的节点从集群删除重新加入
4.4 集群某个节点网络断开，实例正常
1、恢复网络连接
2、Cluster.rejoinInstance(如果集群创建指定了自动加入重试次数，会自动加入)
3、设置 自动加入重试 c.setOption(‘autoRejoinTries’,1000)
4.5 强制删除节点
强制删除挂掉的实例
cluster.removeInstance(‘root@mysql4:3310’,{force:1})
增加实例
cluster.addInstance(‘root@mysql4:3310’)
5 组复制参数设置
group_replication_bootstrap_group=ON
作用：集群全部节点挂掉后，重启节点能自动拉起集群，不需要手动去dba.rebootClusterFromCompleteOutage()
设置方式：
按照常规方式安装好集群，选择其中一台机器作为bootstrap节点，将上述配置写入my.cnf
注意：集群安装好之前不能将此项配置写入my.cnf,否则mysql实例无法启动
bootstrap 节点挂掉后，集群会选择其他节点写入，但是尽快恢复bootstrap节点到正常状态
group_replication_transaction_size_limit=150000000
组复制事务大小限制。默认为145M，可调大到1450M
注意：集群安装好之前不能将此项配置写入my.cnf,否则mysql实例无法启动
group_replication_member_expel_timeout=5
成员恢复等待时间默认5秒，最大3600秒
注意：集群安装好之前不能将此项配置写入my.cnf,否则mysql实例无法启动
本博客所有文章除特别声明外，均采用 CC BY-SA 3.0协议 。转载请注明出处！