快速搭建高可用MySQL环境，实用指南而非理论探讨，数据库使用MariaDb，整体使用docker实现，记录一下方便以后查看。

整体结构

拓扑

MaxScale作为代理中间件，对应用程序无感知实现读写分离、故障转移

              +-------------+
              | application |
              +------+------+
                     |
               +-----v-----+
               |  MaxScale |
               +-----+-----+
                     |
     +---------------+---------------+
     |               |               |
+----v----- + +--- --v-----+ +-------v-----+
|  Master   | |   Slave1   | |    Slave2   | 
| (Write)   | |   (Read)   | |    (Read)   |
+-----------+ +------------+ +-------------+

节点信息

role	IP	port	name
MaxScale	127.0.0.1	33060	maxscale
Master	127.0.0.1	3306	mariadb1
Slave	127.0.0.1	3307	mariadb2
Slave	127.0.0.1	3308	mariadb3

搭建节点

和盖房子一样，按照拓补图从下往上，先搭建一个主从。和上一篇主从复制步骤差不多https://evlan.cc/archives/mariadb-replication.html，所以细节不再赘述

持久化目录

创建节点持久化目录保存数据、日志、配置

mkdir -p /opt/mariadb1/{data,logs,conf.d,docker-entrypoint-initdb.d}
mkdir -p /opt/mariadb2/{data,logs,conf.d}
mkdir -p /opt/mariadb3/{data,logs,conf.d}

创建数据库初始化的env，设置root密码、创建一个数据库和对他有权限的用户

cat > /opt/mariadb1/mariadb.env <<EOF
MARIADB_ROOT_PASSWORD=some-password-string
MARIADB_DATABASE=my_db
MARIADB_USER=my_db_user
MARIADB_PASSWORD=my_db_user_password
EOF

配置文件

cat > /opt/mariadb1/conf.d/my.cnf <<EOF
[mariadb]
log_bin
log_basename=mariadb
binlog_format=row
server_id=1
gtid_strict_mode=1
port=3306

log_slave_updates=1

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_timeout=3000

rpl_semi_sync_slave_enabled=1

character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

[client]
default_character_set=utf8mb4
EOF

创建从库配置文件，两个从库只有ID和端口不一样。

cat > /opt/mariadb2/conf.d/my.cnf <<EOF
[mariadb]
log_bin
log_basename=mariadb
binlog_format=row
server_id=2
gtid_strict_mode=1
port=3307

log_slave_updates=1

read_only=ON
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_timeout=3000

rpl_semi_sync_slave_enabled=1

character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

[client]
default_character_set=utf8mb4
EOF

cat > /opt/mariadb3/conf.d/my.cnf <<EOF
[mariadb]
log_bin
log_basename=mariadb
binlog_format=row
server_id=3
gtid_strict_mode=1
port=3308

log_slave_updates=1

read_only=ON
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_timeout=3000

rpl_semi_sync_slave_enabled=1

character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

[client]
default_character_set=utf8mb4
EOF

启动数据库

直接docker run启动主库

docker run -d \
  --name mariadb1 \
  -v /opt/mariadb1/data:/var/lib/mysql \
  -v /opt/mariadb1/conf.d:/etc/mysql/conf.d \
  -v /opt/mariadb1/logs:/var/log/mysql \
  -v /opt/mariadb1/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d \
  --env-file /opt/mariadb1/mariadb.env  \
  --network host \
  --restart=unless-stopped \
  mariadb:11.7.2

配置主从

创建账号

执行docker exec -it mariadb1 mariadb -uroot -p输入env里面设置的密码进入容器创建用来主从复制的账号

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'replication_user_password';
GRANT REPLICATION REPLICA ON *.* TO 'replication_user'@'%';

理论上由于开启了半同步复制，执行sql会卡。之前文章有说明。

部署从库

同步主库数据文件

备份主库到full_backup目录
docker exec -it mariadb1 mariadb-backup --backup --target-dir=full_backup --user=root --password

从容器里面复制出来并删除容器里面的full_backup

docker cp mariadb1:full_backup .
docker exec -it mariadb1 rm -rf full_backup

复制到从库各自的目录内，并处理、还原到数据目录

mv full_backup /opt/mariadb2/
cp -r /opt/mariadb2/full_backup /opt/mariadb3/
docker run --rm -v /opt/mariadb2/full_backup:/mariadb_backup_files mariadb:11.7.2 mariadb-backup --prepare
docker run --rm -v /opt/mariadb3/full_backup:/mariadb_backup_files mariadb:11.7.2 mariadb-backup --prepare
docker run --rm -v /opt/mariadb2/full_backup:/mariadb_backup_files -v /opt/mariadb2/data:/var/lib/mysql mariadb:11.7.2 mariadb-backup --copy-back --target-dir=/mariadb_backup_files
docker run --rm -v /opt/mariadb3/full_backup:/mariadb_backup_files -v /opt/mariadb3/data:/var/lib/mysql mariadb:11.7.2 mariadb-backup --copy-back --target-dir=/mariadb_backup_files
docker run --rm -v /opt/mariadb2/data:/var/lib/mysql mariadb:11.7.2 chown -R mysql:mysql /var/lib/mysql/
docker run --rm -v /opt/mariadb3/data:/var/lib/mysql mariadb:11.7.2 chown -R mysql:mysql /var/lib/mysql/

查看binlog文件和position或gtid，后面主从复制要用
cat /opt/mariadb2/full_backup/mariadb_backup_binlog_info会展示类似于mariadb-bin.000002 704 0-1-5

启动从库

docker run -d \
  --name mariadb2 \
  -v /opt/mariadb2/data:/var/lib/mysql \
  -v /opt/mariadb2/conf.d:/etc/mysql/conf.d \
  -v /opt/mariadb2/logs:/var/log/mysql \
  --network host \
  --restart=unless-stopped \
  mariadb:11.7.2

docker run -d \
  --name mariadb3 \
  -v /opt/mariadb3/data:/var/lib/mysql \
  -v /opt/mariadb3/conf.d:/etc/mysql/conf.d \
  -v /opt/mariadb3/logs:/var/log/mysql \
  --network host \
  --restart=unless-stopped \
  mariadb:11.7.2

设置主从复制

分别进入两个从节点设置主从复制信息
docker exec -it mariadb2 mariadb -uroot -p

docker exec -it mariadb3 mariadb -uroot -p

此处使用gtid

SET GLOBAL gtid_slave_pos="0-1-5";
CHANGE MASTER TO
MASTER_HOST='主节点IP',
MASTER_PORT=3306,
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_user_password',
MASTER_USE_GTID=slave_pos;
START REPLICA;

使用SHOW REPLICA STATUS \G;命令看到这两行就OK了


              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

配置maxscale

部署maxscale

持久化目录

创建持久化目录保存数据、日志并授权

mkdir -p /opt/maxscale/{data,logs}
docker run --rm -v /opt/maxscale/:/opt/maxscale/ mariadb/maxscale:24.02 chown -R maxscale:maxscale /opt/maxscale/

创建数据库账号

MaxScale需要两个数据库用户供自己使用，在主库创建账号
docker exec -it mariadb1 mariadb -uroot -p

maxscale_monitor账号用于监视MariaDB服务器的健康状况并对其执行操作

CREATE USER 'maxscale_monitor'@'%' IDENTIFIED BY 'strong_monitor_password';
GRANT RELOAD, PROCESS, SHOW DATABASES, BINLOG MONITOR, EVENT, SET USER, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, BINLOG ADMIN, SLAVE MONITOR ON *.* TO `maxscale_monitor`@`%`;
GRANT SELECT ON `mysql`.`user` TO `maxscale_monitor`@`%`;
GRANT SELECT ON `mysql`.`global_priv` TO `maxscale_monitor`@`%`;

maxscale_service账号用于获取用户帐户信息

CREATE USER 'maxscale_service'@'%' IDENTIFIED BY 'strong_service_password';
GRANT SHOW DATABASES ON *.* TO `maxscale_service`@`%`;
GRANT SELECT ON `mysql`.* TO `maxscale_service`@`%`;

创建配置文件

创建配置文件/opt/maxscale/maxscale.cnf
配置文件的注释不能跟在配置末尾，要在新行以#开头

根据cpu自动线程数

[maxscale]
threads=auto

三个节点信息

[server1]
type=server
address=节点IP
port=3306

[server2]
type=server
address=节点IP
port=3307

[server3]
type=server
address=节点IP
port=3308

监控信息

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=maxscale_monitor
password=strong_monitor_password
monitor_interval=2s
#开启故障转移
auto_failover=true
#开启恢复自动加入
auto_rejoin=true
#简单拓扑结构 1主多从
enforce_simple_topology=true
replication_user=replication_user
replication_password=replication_user_password

读写分离信息

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale_service
password=strong_service_password
transaction_replay=true
transaction_replay_timeout=30s
#让root用户也能登录
enable_root_user=true

读写分离服务的端口

[Read-Write-Listener]
type=listener
service=Read-Write-Service
port=33060

启动

docker run --name maxscale -d -v /opt/maxscale/data/:/var/lib/maxscale/ -v /opt/maxscale/logs/:/var/log/maxscale/ -v /opt/maxscale/maxscale.cnf:/etc/maxscale.cnf  --network host mariadb/maxscale:24.02

查看日志tail -f -n 40 /opt/maxscale/logs/maxscale.log输出大概是这样

2025-06-25 05:53:28   notice : server3 sent version string '11.7.2-MariaDB-ubu2404-log'. Detected type: MariaDB, version: 11.7.2.
2025-06-25 05:53:28   notice : server1 sent version string '11.7.2-MariaDB-ubu2404-log'. Detected type: MariaDB, version: 11.7.2.
2025-06-25 05:53:28   notice : server2 sent version string '11.7.2-MariaDB-ubu2404-log'. Detected type: MariaDB, version: 11.7.2.
2025-06-25 05:53:28   notice : Server 'server1' charset: utf8mb4_unicode_ci
2025-06-25 05:53:28   notice : Server 'server2' charset: utf8mb4_unicode_ci
2025-06-25 05:53:28   notice : Server 'server3' charset: utf8mb4_unicode_ci
2025-06-25 05:53:28   notice : [mariadbmon] Selecting new primary server.
2025-06-25 05:53:28   notice : [mariadbmon] Setting 'server1' as primary.
2025-06-25 05:53:28   notice : Server changed state: server1[xxx.xxx.xxx.xxx:3306]: master_up. [Down] -> [Master, Running]
2025-06-25 05:53:28   notice : Server changed state: server2[xxx.xxx.xxx.xxx:3307]: slave_up. [Down] -> [Slave, Running]
2025-06-25 05:53:28   notice : Server changed state: server3[xxx.xxx.xxx.xxx:3308]: slave_up. [Down] -> [Slave, Running]
2025-06-25 05:53:28   notice : Starting a total of 1 services...
2025-06-25 05:53:28   notice : (Read-Write-Listener); Listening for connections at [::]:33060
2025-06-25 05:53:28   notice : Service 'Read-Write-Service' started (1/1)
2025-06-25 05:53:28   notice : MaxScale started with 2 worker threads.
2025-06-25 05:53:29   notice : Read 10 user@host entries from 'server1' for service 'Read-Write-Service'.

执行maxctrl 查看服务列表 docker exec -it maxscale maxctrl list servers

┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬────────┬─────────────────┐
│ Server  │ Address        │ Port │ Connections │ State           │ GTID   │ Monitor         │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server1 │ xxx.xx.xxx.xxx │ 3306 │ 0           │ Master, Running │ 0-1-12 │ MariaDB-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server2 │ xxx.xx.xxx.xxx │ 3307 │ 0           │ Slave, Running  │ 0-1-12 │ MariaDB-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server3 │ xxx.xx.xxx.xxx │ 3308 │ 0           │ Slave, Running  │ 0-1-12 │ MariaDB-Monitor │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴────────┴─────────────────┘

此时就能通过33060端口正常操作数据库了。

测试

故障转移

模拟主库意外停止或失联，docker stop mariadb1，然后查看日志和状态

tail -f -n 40 /opt/maxscale/logs/maxscale.log
表示主库连不上，从'server2 server3'选中了server2替代server1，然后设置server3的主库为server2

2025-06-25 06:08:27   notice : Server changed state: server1[xxx.xx.xxx.xxx:3306]: master_down. [Master, Running] -> [Down]
2025-06-25 06:08:27   warning: [mariadbmon] Primary has failed. If primary does not return in 4 monitor tick(s), failover begins.
2025-06-25 06:08:34   notice : [mariadbmon] Selecting a server to promote and replace 'server1'. Candidates are: 'server2', 'server3'.
2025-06-25 06:08:34   notice : [mariadbmon] Selected 'server2'.
2025-06-25 06:08:34   notice : [mariadbmon] Performing automatic failover to replace failed primary 'server1'.
2025-06-25 06:08:35   notice : [mariadbmon] Redirecting 'server3' to replicate from 'server2' instead of 'server1'.
2025-06-25 06:08:35   notice : [mariadbmon] All redirects successful.
2025-06-25 06:08:35   notice : [mariadbmon] All redirected slaves successfully started replication from 'server2'.
2025-06-25 06:08:35   notice : [mariadbmon] Failover 'server1' -> 'server2' performed.
2025-06-25 06:08:35   notice : Server changed state: server2[xxx.xx.xxx.xxx:3307]: new_master. [Slave, Running] -> [Master, Running]

查看服务列表 docker exec -it maxscale maxctrl list servers

┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬────────┬─────────────────┐
│ Server  │ Address        │ Port │ Connections │ State           │ GTID   │ Monitor         │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server1 │ xxx.xx.xxx.xxx │ 3306 │ 0           │ Down            │ 0-1-12 │ MariaDB-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server2 │ xxx.xx.xxx.xxx │ 3307 │ 0           │ Master, Running │ 0-1-12 │ MariaDB-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server3 │ xxx.xx.xxx.xxx │ 3308 │ 0           │ Slave, Running  │ 0-1-12 │ MariaDB-Monitor │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴────────┴─────────────────┘

自动加入

启动mariadb1模拟服务重新上线docker start mariadb1，然后查看日志和状态

tail -f -n 40 /opt/maxscale/logs/maxscale.log
server1上线之后就变成从库，从server2进行复制了

2025-06-25 06:14:25   notice : Server changed state: server1[xxx.xx.xxx.xxx:3306]: server_up. [Down] -> [Running]
2025-06-25 06:14:25   notice : [mariadbmon] Directing standalone server 'server1' to replicate from 'server2'.
2025-06-25 06:14:25   notice : [mariadbmon] Replica connection from server1 to [xxx.xx.xxx.xxx]:3307 created and started.
2025-06-25 06:14:25   notice : [mariadbmon] 1 server(s) redirected or rejoined the cluster.
2025-06-25 06:14:25   notice : Server changed state: server1[xxx.xx.xxx.xxx:3306]: new_slave. [Running] -> [Slave, Running]

查看服务列表 docker exec -it maxscale maxctrl list servers

┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬────────┬─────────────────┐
│ Server  │ Address        │ Port │ Connections │ State           │ GTID   │ Monitor         │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server1 │ xxx.xx.xxx.xxx │ 3306 │ 0           │ Slave, Running  │ 0-1-12 │ MariaDB-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server2 │ xxx.xx.xxx.xxx │ 3307 │ 0           │ Master, Running │ 0-1-12 │ MariaDB-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────┤
│ server3 │ xxx.xx.xxx.xxx │ 3308 │ 0           │ Slave, Running  │ 0-1-12 │ MariaDB-Monitor │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴────────┴─────────────────┘

读写分离

部署的时候创建了一个初始的用户，用这个账号进行测试，按照上一步的测试，此时server1和server3是从库，server2是主库。

MARIADB_DATABASE=my_db
MARIADB_USER=my_db_user
MARIADB_PASSWORD=my_db_user_password

使用Dbeaver填好端口和密码然后一直不停的执行语句select @@server_id;进行读操作，会看到返回结果在1和3之间来回切换，说明请求被均匀的分布在server1和server3这两个从库上面了。

测试写，创建一个表CREATE TABLE my_db.test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));,插入数据INSERT INTO my_db.test_table (`data`) VALUES ( @@server_id);，然后查询表数据select * FROM my_db.test_table;，可以看到data这一列都是2，说明写操作是server2执行

读写分离完成

MaxScale 简单配置读写分离故障转移

整体结构

拓扑

节点信息

搭建节点

持久化目录

配置文件

启动数据库

配置主从

创建账号

部署从库

同步主库数据文件

启动从库

设置主从复制

配置maxscale

部署maxscale

持久化目录

创建数据库账号

创建配置文件

启动

测试

故障转移

自动加入

读写分离

添加新评论

最新文章

最近回复

分类

归档

其它

MaxScale 简单配置读写分离 故障转移

整体结构

拓扑

节点信息

搭建节点

持久化目录

配置文件

启动数据库

配置主从

创建账号

部署从库

同步主库数据文件

启动从库

设置主从复制

配置maxscale

部署maxscale

持久化目录

创建数据库账号

创建配置文件

启动

测试

故障转移

自动加入

读写分离

添加新评论

最新文章

最近回复

分类

归档

其它

MaxScale 简单配置读写分离故障转移