JAVA日知录

MySQL 8.0.22 单主从 MGR Docker

官方文档

https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

目录创建 && docker MySQL 网络

mkdir -p /app/cloud/mysql/{3306,3307,3308}/{data,log,conf.d}
docker network create --subnet=172.18.0.0/24 mgr

容器创建

for N in 0 1 2
do 
  docker run -d --name mgr$[3306 + $N] --ip 172.18.0.$[2 + $N] \
   -h mgr$N -p $[3306 + $N]:3306 --net=mgr \
   -v /app/cloud/mysql/$[3306 + $N]/data:/var/lib/mysql \
   -v /app/cloud/mysql/$[3306 + $N]/log:/var/log/mysql \
   -v /app/cloud/mysql/$[3306 + $N]/conf.d:/etc/mysql/conf.d \
   -e MYSQL_ROOT_PASSWORD=Aa123456 \
   -e TZ=Asia/Shanghai \
   mysql:8.0.22
done

一主二从 my.cnf

for N in 0 1 2
do
cat > /app/cloud/mysql/$[3306 + $N]/conf.d/my.cnf <<EOF
[mysqld]
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

server_id = $[N + 1]
user = mysql
port = 3306

character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
default_storage_engine = INNODB

default_time_zone = '+8:00'
log_timestamps = SYSTEM

log_error = /var/log/mysql/error.log

#general_log = on
#general_log_file = /var/log/mysql/mysql.log

slow_query_log = on
log_queries_not_using_indexes = on
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log

#log_output = 'FILE,TABLE'

gtid_mode = ON
enforce_gtid_consistency = ON

log_bin = binlog
log_slave_updates = ON
binlog_format = ROW
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64

plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot = off
group_replication_local_address = "172.18.0.$[2 + $N]:33061"
group_replication_group_seeds = "172.18.0.2:33061,172.18.0.3:33061,172.18.0.4:33061"
group_replication_bootstrap_group = off
group_replication_recovery_get_public_key = on

report_host = 172.18.0.$[2 + $N]
report_port=3306
EOF
done

修改容器log目录权限

for N in 0 1 2
do
   docker exec -it mgr$[3306 + N] chown -R mysql:mysql /var/log/mysql
done

查看容器ip

docker inspect --format '{{ .NetworkSettings.Networks.mgr.IPAddress}}' mgr3306

更改my.cnf内容后，重启容器

docker restart mgr3306 mgr3307 mgr3308

查看MySQL相关信息

select @@hostname,@@server_id,@@server_uuid,@@group_replication_member_weight

MySQL 设置复制账号，所有节点执行

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa123456';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Aa123456' FOR CHANNEL 'group_replication_recovery';

启动MGR单主模式 mgr3306 上执行

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa123456';
SET GLOBAL group_replication_bootstrap_group=OFF;

SELECT * FROM performance_schema.replication_group_members;

其他节点加入MGR

START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa123456';
SELECT * FROM performance_schema.replication_group_members;

集群状态

自动选举

select @@server_uuid,@@group_replication_member_weight;

MySQL 主挂掉后，会根据这2个参数选举，应用程序需要更改配置中心主从IP配置，设置自动刷新配置

查看谁是主节点

SHOW STATUS;
SHOW STATUS LIKE 'group_replication_primary_member';
show variables like '%read_only';

ShardingSphere-Proxy 4.1.1 配置主从

server.yaml

authentication:
  users:
    root:
      password: Aa123456
    sharding:
      password: Aa123456
      authorizedSchemas: sharding_db

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
  proxy.frontend.flush.threshold: 128  # The default value is 128.
    # LOCAL: Proxy will run with LOCAL transaction.
    # XA: Proxy will run with XA transaction.
    # BASE: Proxy will run with B.A.S.E transaction.
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  proxy.hint.enabled: false
  query.with.cipher.column: true
  sql.show: true
  allow.range.query.with.inline.sharding: true

config-master_slave.yaml

schemaName: master_slave_db

dataSources:
  master_ds:
    url: jdbc:mysql://192.168.1.18:3306/test123?serverTimezone=UTC&useSSL=false
    username: root
    password: Aa123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_0:
    url: jdbc:mysql://192.168.1.18:3307/test123?serverTimezone=UTC&useSSL=false
    username: root
    password: Aa123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_1:
    url: jdbc:mysql://192.168.1.18:3308/test123?serverTimezone=UTC&useSSL=false
    username: root
    password: Aa123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50  

masterSlaveRule:
  name: ms_ds
  masterDataSourceName: master_ds
  slaveDataSourceNames:
    - slave_ds_0
    - slave_ds_1

Sharding-Jdbc 4.1.1 配置主从

pom.xml

<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>

application.yml

spring.shardingsphere.datasource.names: master,slave0

spring.shardingsphere.datasource.master.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name: com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url: jdbc:mysql://192.168.1.18:3306/test123
spring.shardingsphere.datasource.master.username: root
spring.shardingsphere.datasource.master.password: Aa123456

spring.shardingsphere.datasource.slave0.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name: com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url: jdbc:mysql://192.168.1.18:3307/test123
spring.shardingsphere.datasource.slave0.username: root
spring.shardingsphere.datasource.slave0.password: Aa123456

spring.shardingsphere.masterslave.load-balance-algorithm-type: round_robin
spring.shardingsphere.masterslave.name: ms
spring.shardingsphere.masterslave.master-data-source-name: master
spring.shardingsphere.masterslave.slave-data-source-names: slave0

spring.shardingsphere.props.sql.show: true

测试主从

MySQL Driver

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connection.html

Jdbc 必须 8.0 以上驱动

Connection conn = DriverManager.getConnection("jdbc:mysql:replication://address=(type=master)(host=192.168.1.18)(port=3306),address=(type=slave)(host=192.168.1.18)(port=3307),address=(type=slave)(host=192.168.1.18)(port=3308)/test123?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&roundRobinLoadBalance=true", "root", "Aa123456");
conn.setReadOnly(true);

DataGrip/Navicat 连接 ShardingSphere

---

标题：MySQL 8.0.22 单主从 MGR docker
作者：uid1024
地址：http://javadaily.cn/articles/2021/01/05/1609821885001.html