分享一下基于Mysql主从复制+读写分离的方案。
主从复制：Mysql Master-Slave bin-log。
读写分离：Mariadb MaxScale Proxy。
搭建环境是基于Docker，Docker Compose。

1. 创建目录，1台主master，2台从slave。

mkdir -p mysql/master/conf
mkdir -p mysql/master/data
mkdir -p mysql/master/logs
mkdir -p mysql/slave1/conf
mkdir -p mysql/slave1/data
mkdir -p mysql/slave1/logs
mkdir -p mysql/slave2/conf
mkdir -p mysql/slave2/data
mkdir -p mysql/slave2/logs
mkdir -p mysql/maxscale

mysql目录结构如下：

.
├── docker-compose.yml
├── master
│   ├── conf
│   │   └── config-file.cnf
│   ├── data
│   └── logs
├── maxscale
│   └── my-maxscale.cnf
├── slave1
│   ├── conf
│   │   └── config-file.cnf
│   ├── data
│   └── logs
└── slave2
    ├── conf
    │   └── config-file.cnf
    ├── data
    └── logs

2. 创建Mysql的配置文件 config-file.cnf 并放入conf文件夹。

2.1 Master的 config-file.cnf 参考配置

[client]
port = 3306
default-character-set = utf8mb4
[mysqld]
port = 3306
default-authentication-plugin = mysql_native_password
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
explicit_defaults_for_timestamp = true
lower_case_table_names = 1
max_allowed_packet = 128M
# Replication Master Server (default)
# binary logging is required for replication
log-bin = bin-log
log-bin-index = bin-log.index
sync_binlog = 1
# binary logging format - mixed recommended
binlog_format = mixed
expire_logs_days = 3
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 100
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=utf8
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

2.2 Slave的 config-file.cnf 参考配置

[client]
port = 3307
default-character-set = utf8mb4
[mysqld]
port = 3307
default-authentication-plugin = mysql_native_password
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
explicit_defaults_for_timestamp = true
lower_case_table_names = 1
max_allowed_packet = 128M
# Replication Master Server (default)
# binary logging is required for replication
log-bin = bin-log 
log-bin-index = bin-log.index 
# 可以不配置中继日志，当服务器以从模式运行时，将会自动开启
relay-log = relay-log
# 指定 relay 日志的索引文件。
relay-log-index = relay-log.index
read_only = 1
# binary logging format - mixed recommended
binlog_format = mixed
#
expire_logs_days = 3
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 101
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=utf8
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

3. 创建MaxScale的配置文件 my-maxscale.cnf 并放入maxscale文件夹。

官方配置参考点这里。推荐一篇讲解MaxScale的文章。

[server1]
type=server
# Replace address and port of yours, and the same as below.
address=192.168.3.77
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=192.168.3.77
port=3307
protocol=MariaDBBackend
[server3]
type=server
address=192.168.3.77
port=3308
protocol=MariaDBBackend
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
# user and password should be created in all above mysql servers, and be authorized.
user=maxscalemon
password=111111
failcount=3
backend_connect_timeout=3
backend_write_timeout=3
backend_read_timeout=3
auto_failover=true
auto_rejoin=true
enforce_read_only_slaves=1
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
# user and password should be created in all above mysql servers, and be authorized.
user=maxscalerouter
password=111111
master_failure_mode=fail_on_write
# Solve the problem of invalid recursive query
use_sql_variables_in=master
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MySQLClient
# Solve ipv6 problem
address=0.0.0.0
port=4006

4. 创建docker-compose.yml文件并放在根目录。

MaxScale的官方配置和官方文档参考。

version: '3.8'
services:
  mysql-master:
    container_name: mysql-master
    image: mysql:5.7
    # restart: always
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 111111
    ports:
      - 3306:3306
    volumes:
            - ./master/data:/var/lib/mysql
      - ./master/logs:/var/log/mysql
      - ./master/conf:/etc/mysql/conf.d
  mysql-slave1:
    container_name: mysql-slave1
    image: mysql:5.7
    # restart: always
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 111111
    ports:
      - 3307:3307
    volumes:
      - ./slave1/data:/var/lib/mysql
      - ./slave1/logs:/var/log/mysql
      - ./slave1/conf:/etc/mysql/conf.d
    depends_on:
      - mysql-master
  mysql-slave2:
    container_name: mysql-slave2
    image: mysql:5.7
    # restart: always
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 111111
    ports:
      - 3308:3308
    volumes:
      - ./slave2/data:/var/lib/mysql
      - ./slave2/logs:/var/log/mysql
      - ./slave2/conf:/etc/mysql/conf.d
    depends_on:
      - mysql-master
  maxscale:
    container_name: mysql-maxscale
    image: mariadb/maxscale:2.5
    depends_on:
      - mysql-master
      - mysql-slave1
      - mysql-slave2
    volumes:
      - ./maxscale:/etc/maxscale.cnf.d
    ports:
      - 4006:4006 # readwrite port
      # - '4008:4008' # readonly port
      - 8989:8989 # REST API port

5. 以上配置都创建好后，开始实操。

5.1 注释掉docker-compose.yml的maxscale节点。下面这段。

因为MaxScale的读写分离依赖主从复制的Mysql集群，并且依赖Mysql的授权用户。

  maxscale:
    container_name: mysql-maxscale
    image: mariadb/maxscale:2.5
    depends_on:
      - mysql-master
      - mysql-slave1
      - mysql-slave2
    volumes:
      - ./maxscale:/etc/maxscale.cnf.d
    ports:
      - 4006:4006 # readwrite port
      # - '4008:4008' # readonly port
      - 8989:8989 # REST API port

5.2 单独启动Mysql。

docker-compose up

启动好后如下图。

5.3 配置Mysql主从复制。

5.3.1 连接Master主库。

5.3.1.1创建从库用户。

CREATE USER 'slave-user'@'%' IDENTIFIED BY 'slave-password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave-user'@'%';

5.3.1.2 查看主库状态。

SHOW MASTER STATUS;

File和Position要记下来，后面配置从库使用。

5.3.2 连接Slave从库。

5.3.2.1 配置从库连接主库。

CHANGE MASTER TO 
master_host = '192.168.3.77', -- Master主库的ip
master_port = 3306, -- Master主库的port
master_user = 'slave', -- 在Master主库创建的用于同步数据的账号
master_password = '111111', -- 在Master主库创建的用于同步数据的密码
master_log_file = 'mysql-master-bin.000003', -- Master主库的File
master_log_pos = 831, -- Master主库的Position
master_connect_retry = 30;

5.3.2.2 开启/停止/查看从库。

START SLAVE; -- 开启复制
STOP SLAVE; -- 停止复制
RESET SLAVE; -- 重置复制
SHOW SLAVE STATUS; -- 查看从库状态

查看从库状态并确认Slave_IO_Running和Slave_SQL_Running是Yes。

至此，Mysql主从复制完成。

5.3.3 继续创建Maxscale使用监控账号和路由账号，顺便测试一下主从复制是否已成功。

5.3.3.1 创建监控用户并授权。

确认创建的用户名和密码对应 my-maxscale.cnf 中 [MariaDB-Monitor] 节点下的user和password。

CREATE USER 'maxscalemon'@'%' IDENTIFIED BY '111111';
GRANT REPLICATION CLIENT on *.* to 'maxscalemon'@'%';
FLUSH PRIVILEGES;

5.3.3.2 创建路由用户并授权。

确认创建的用户名和密码对应 my-maxscale.cnf 中 [Read-Write-Service] 节点下的user和password。

CREATE USER 'maxscalerouter'@'%' IDENTIFIED BY '111111';
GRANT SELECT ON mysql.* TO maxscalerouter@'%';
GRANT SHOW DATABASES ON *.* TO maxscalerouter@'%';
FLUSH PRIVILEGES;

5.3.4 分别主库和从库，查看 监控用户 和 路由用户 是否已创建完成并已从主库同步到从库中。

SELECT `User`, `Host` FROM `user` WHERE `User` LIKE 'maxscale%';

5.4 配置读写分离。

停止Container，打开先前注释掉docker-compose.yml的maxscale节点，重新启动，启动好后如下图。

5.5 测试读写分离。

5.5.1 新建1个Mysql账号并授权。

CREATE USER 'rwtest' @'%' IDENTIFIED BY '111111';
GRANT ALL PRIVILEGES ON *.* TO 'rwtest' @'%';

5.5.2 连接MaxScale读写分离端口4006。连接成功后建表并执行增删改查语句。

5.5.3 MaxScale2.5提供了可视化页面来查看Mysql服务端的运行状态。

打开浏览器访问：http://192.168.3.77:8989/
默认账号密码：admin / mariadb

6. Spring + Druid 集成。

数据源配置与单机版mysql相同，无缝对接。

6.1 pom.xml

<properties>
    <mysql.version>8.0.27</mysql.version>
    <druid.version>1.1.24</druid.version>
</properties>
<dependencies>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.version}</version>
    <scope>runtime</scope>
  </dependency>
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>${druid.version}</version>
  </dependency>
</dependencies>

6.2 application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://192.168.3.77:4006/your-database?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
spring.datasource.druid.username=beauty
spring.datasource.druid.password=111111