快速搭建高可用MySQL环境，实用指南而非理论探讨，数据库使用MariaDb，整体使用docker实现，记录一下方便以后查看。
整体结构拓扑MaxScale作为代理中间件，对应用程序无感知实现读写分离、故障转移
+-------------+
| application |
+------+------+
|
+-----v-----+
| MaxScale |
+-----+-----+
|
+---------------+---------------+
| | |
+----v----- + +--- --v-----+ +-------v-----+
| Master | | Slave1 | | Slave2 |
| (Write) | | (Read) | | (Read) |
+-----------+ +------------+ +-------------+节点信息roleIPportnameMaxScale127.0.0.133060maxscaleMaster127.0.0.13306mariadb1Slave127.0.0.13307mariadb2Slave127.0.0.13308mariadb3搭建节点和盖房子一样，按照拓补图从下往上，先搭建一个主从。和上一篇主从复制步骤差不多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 < 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 < [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 < [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 EOFcat > /opt/mariadb3/conf.d/my.cnf < [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.logserver1上线之后就变成从库，从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_dbMARIADB_USER=my_db_userMARIADB_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执行 读写分离完成