vim /etc/hosts
追加内容
192.168.1.11 node1
192.168.1.12 node2
192.168.1.13 node3
修改hostname
vim /etc/hostname
重启网络使配置文件生效
systemctl restart network
docker pull mysql:8.0.23
创建配置文件夹
mkdir -p /docker_data/mysql/conf/
vim /docker_data/mysql/conf/my.cnf
内容如下
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=33061/tcp --permanent
firewall-cmd --reload
必须使用连通宿主机ip的网络
docker run -d --network=host --name mysql --privileged=true --restart=always -v /docker_data/mysql/data:/var/lib/mysql -v /docker_data/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 --name mysql-8.0.23 mysql:8.0.23
启动以后进入mysql容器,安装插件
docker exec -it mysql-8.0.23 bash
#容器执行:mysql -uroot -p
#输入mysql密码
#安装插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
#退出mysql容器,回到宿主机,修改配置文件
修改配置文件
vim /docker_data/mysql/conf/my.cnf
追加内容
三台服务器除了server_id和group_replication_local_address不同其它全部相同
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
port=3306
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
#MGR
#其他存储引擎,包括临时MEMORY存储引擎,可能会导致组复制中出现错误。
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#服务唯一ID
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
#添加MGR插件(如果是docker则在容器里安装后再写入以下配置,否则启动会报错,因为你没有安装插件)
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=OFF
#根据当前服务器的别名配置,如:你当前服务器是node2那就改成node2,然后server_id改成2或者其它不重复的,node1~3都是自定义的
group_replication_local_address= "node1:33061"
group_replication_group_seeds= "node1:33061,node2:33061,node3:33061"
group_replication_bootstrap_group=OFF
保存配置文件,重启mysql容器
docker restart mysql-8.0.23
#先禁用二进制日志
SET SQL_LOG_BIN=0;
CREATE USER copy@'%' IDENTIFIED BY 'copy';
GRANT REPLICATION SLAVE ON *.* TO copy@'%';
GRANT CONNECTION_ADMIN ON *.* TO copy@'%';
GRANT BACKUP_ADMIN ON *.* TO copy@'%';
#GRANT GROUP_REPLICATION_STREAM ON *.* TO copy@'%';
FLUSH PRIVILEGES;
#重新启用二进制日志
SET SQL_LOG_BIN=1;
#启动单主模式主从复制
CHANGE MASTER TO MASTER_USER='copy', MASTER_PASSWORD='copy' FOR CHANNEL 'group_replication_recovery';
我的是node1
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
#查看组复制情况
SELECT * FROM performance_schema.replication_group_members;
成功则查询出如下所示内容:
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | ec4f4975-7480-11ed-a4eb-000c29993f4c | node1 | 3306 | ONLINE | PRIMARY | 8.0.23 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
即node1和2
START GROUP_REPLICATION;
# 从库查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;
手动停止主节点node1,模拟宕机
docker stop mysql-8.0.23
任一从节点执行SQL,发现从节点node2变成了主节点(PRIMARY)
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 | c40efe0e-7480-11ed-aeda-00505629a57a | node2 | 3306 | ONLINE | PRIMARY | 8.0.23 |
| group_replication_applier | ce0f8f45-7480-11ed-b616-0050562dbd10 | node3 | 3306 | ONLINE | SECONDARY | 8.0.23 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
重启node1节点,加入组
docker start mysql-8.0.23
node1执行SQL
#重启后必须执行START GROUP_REPLICATION;重新加入组,否则节点不会自动加入组
START GROUP_REPLICATION;
#任一节点执行查询节点信息SQL
SELECT * FROM performance_schema.replication_group_members;
node1变成从节点
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c40efe0e-7480-11ed-aeda-00505629a57a | node2 | 3306 | ONLINE | PRIMARY | 8.0.23 |
| group_replication_applier | ce0f8f45-7480-11ed-b616-0050562dbd10 | node3 | 3306 | ONLINE | SECONDARY | 8.0.23 |
| group_replication_applier | ec4f4975-7480-11ed-a4eb-000c29993f4c | node1 | 3306 | ONLINE | SECONDARY | 8.0.23 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
一个组最多可以包含 9 台服务器。正在尝试添加 具有 9 个成员的组的另一个服务器导致请求 加入被拒绝。此限制已从测试中确定 并将基准测试作为小组执行的安全边界 在稳定的局域网上可靠运行。
可以通过Apache ShardingSphere-JDBC实现高可用。
《Shardingsphere官网》