MySQL 8.0.22 单主从 MGR docker
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
