MaxScale 实现 MySQL 读写分离

2023-10-18

字数统计: 3.2k字 | 阅读时长≈ 16分

环境准备

数据库是一主两从的架构（mysql 的主从搭建不在此说明），maxscale 安装在单独的服务器上

主机名	IP 地址	系统版本	软件版本	角色	Server ID
mysql-master	192.168.0.71	CentOS 8.2	MySQL 8.0.23	Master	1
mysql-slave01	192.168.0.72	CentOS 8.2	MySQL 8.0.23	Slave	2
mysql-slave02	192.168.0.73	CentOS 8.2	MySQL 8.0.23	Slave	3
maxscale	192.168.0.73	CentOS 8.2	MaxScale 2.5.26	maxscale	-

部署 MaxScale

下载 MaxScale

Maxscale 是由 MariaDB 官方提供的中间件，并负责升级和维护，可在官网中进行下载，另外可以查看官方提供的详细文档，本例中所有的安装方式都是选用maxscale-2.5.26 版本。

下载地址: MaxScale 下载页面

安装 MaxScale

执行以下命令安装依赖
1
dnf install -y libatomic

执行以下命令安装 maxScale

1	rpm -ivh maxscale-2.5.26-1.rhel.8.x86_64.rpm

启动 MaxScale

# 启动 MaxScale 并配置 MaxScale 开机启动
systemctl enable --now maxscale.service

# 检查 MaxScale 是否开机启动
systemctl is-enabled maxscale.service

配置 MaxScale

以 yum 或 rpm 方式安装的 maxscale，默认的参数文件是 /etc/maxscale.cnf，也可以在启动的时候使用 -f 参数指定参数文件，如 maxscale -f /u01/maxscale.cnf。

每个版本的参数文件会所有不同，官网提供了详细的参数文件说明，本例记录了一些项目所需的参数说明。

修改 MaxScale 的配置文件，改好的配置如下

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-25/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-25-mariadb-maxscale-configuration-guide/

[maxscale]
threads=auto

syslog=0
maxlog=1
log_info=1

# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#

[mysql-master]
type=server
address=192.168.0.71
port=3306
protocol=MariaDBBackend

[mysql-slave01]
type=server
address=192.168.0.72
port=3306
protocol=MariaDBBackend

[mysql-slave02]
type=server
address=192.168.0.73
port=3306
protocol=MariaDBBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/maxscale-25-monitors/

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=mysql-master,mysql-slave01,mysql-slave02
user=scalemon
password=c{fFlbw82dUh
monitor_interval=2000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-25-readconnroute/

#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#password=mypwd
#router_options=slave

# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-25-readwritesplit/

[Read-Write-Service]
type=service
router=readwritesplit
servers=mysql-master,mysql-slave01,mysql-slave02
user=maxscale
password=Aih]rxs67Dgj
max_slave_connections=100%
# connection_timeout=300
# max_connections=100
max_slave_replication_lag=60
# master_reconnection=1
# master_failure_mode=error_on_write


# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=MariaDBClient
#port=4008

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

参数文件中主要是5个模块，Global parameters、Server definitions、Monitor for the servers、Service definitions 和 Listener definitions for the services。详细的配置: [MariaDB MaxScale Configuration Guide

](https://mariadb.com/kb/en/mariadb-maxscale-25-mariadb-maxscale-configuration-guide/)
- Global parameter 是全局参数，上述文件中主要对线程数和日志做了设置
- threads=auto表示跟CPU核数一样也可以自定义其他数值；
- 对于log日志，只需要记录在专门的日志文件即可，无需在syslog再记录一份，日志级别需要查看每条查询的路由情况可以把info级别开启，后续需要注意日志文件逐渐变大，可做转储；
- 其余参数不进行配置使用默认的即可。
- Server definitions配置了后端数据库，参考上述文件即可，其中protocol目前只有MariaDBBackend协议。
- Monitor for the servers 是配置监控后端数据库的相关参数，其中有几种模式，这里采用的是 mariadbmon
- servers 的值对应上一个模块中后端数据库，用逗号隔开；
- monitor_interval 设置的是监控频率，单位是 ms，默认是2000ms；
- user 和 password 是监控数据库时所用到的数据库用户和密码，此用户需要在数据库上创建并赋权，需要 replication 和 client 权限

    1
MySQL [(none)]> grant replication client on *.* to to scalemon@'%';


- Service definitions 是配置我们所用的服务策略，如 readconnroute 和 readwritesplit 等，项目主要是需要读写分离，即readwritesplit。
  - router 设置成 readwritesplit；
  - max_slave_connections 设置成100%，表示读负载到所有的 slave 数据库
  - max_slave_replication_lag 设置的是从库最大延迟，单位是秒，即当从库的延迟大于所设置的值时，读请求不再路由到此从库上
  - user 和 password 是路由账号，maxscale 使用该账号将不同的请求分发到不同的节点上。当客户端连接到 maxscale 这个节点上时，maxscale 节点会使用该账号去查后端数据库，检查客户端登陆的用户是否有权限或密码是否正确等等；

    1
2
3
MySQL [(none)]> grant select on mysql.* to maxscale@'%';

MySQL [(none)]> grant show databases on *.* to maxscale@'%';


  - master_reconnection 和 master_failure_mode 是设置主库宕机下的配置
    master_failure_mode 有三个值，fail_instantly、fail_on_write 和 error_on_write，默认是 fail_instantly 即当主库宕机时，立即关闭连接并不再接受新连接；fail_on_write 和 error_on_write 分别代表是主库宕机的情况下，当有写请求连接来时，分别断开连接和报错，即主库宕机时只接受只读连接；如果主库恢复了，只读连接在不断开的情况下变成读写连接的话，就需要 master_reconnection 设置成1，即enable。
- Listener definitions for the services 用于配置上述服务策略的监听
  - service 设置成上述服务策略中的对应名称
  - protocol 目前只支持 MariaDBClient
  - port 监听的端口，读写分离默认的是 4006，只读默认的是 4008。

在所有节点执行以下命令，创建用户并授权

MySQL [(none)]> create user 'maxscale'@'%' identified by 'Aih]rxs67Dgj';

MySQL [(none)]> create user 'scalemon'@'%' identified by 'c{fFlbw82dUh';

MySQL [(none)]> grant replication client on *.*  to 'scalemon'@'%';

MySQL [(none)]> grant select on mysql.* to 'maxscale'@'%';

MySQL [(none)]> grant show databases on *.* to 'maxscale'@'%';

MySQL [(none)]> flush privileges;

重启 MaxScale 服务
1
systemctl restart maxscale.service

命令说明

maxscale

maxscale 命令用于启动 maxscale 服务，详细参数可以使用 --help 查看

# maxscale --help

Usage : maxscale [OPTION]...

-c, --config-check          validate configuration file and exit
-e, --export-config=FILE    export configuration to a single file
-d, --nodaemon              enable running in terminal process
-f, --config=FILE           relative or absolute pathname of config file
-l, --log=[file|stdout]     log to file or stdout
                            (default: file)
-L, --logdir=PATH           path to log file directory
-A, --cachedir=PATH         path to cache directory
-B, --libdir=PATH           path to module directory
-C, --configdir=PATH        path to configuration file directory
-D, --datadir=PATH          path to data directory,
                            stores internal MaxScale data
-E, --execdir=PATH          path to the maxscale and other executable files
-F, --persistdir=PATH       path to persisted configuration directory
-M, --module_configdir=PATH path to module configuration directory
-H, --connector_plugindir=PATH
                            path to MariaDB Connector-C plugin directory
-J, --sharedir=PATH         path to share directory
-N, --language=PATH         path to errmsg.sys file
-P, --piddir=PATH           path to PID file directory
-R, --basedir=PATH          base path for all other paths
-r  --runtimedir=PATH       base path for all other paths expect binaries
-U, --user=USER             user ID and group ID of specified user are used to
                            run MaxScale
-s, --syslog=[yes|no]       log messages to syslog (default:yes)
-S, --maxlog=[yes|no]       log messages to MaxScale log (default: yes)
-G, --log_augmentation=0|1  augment messages with the name of the function
                            where the message was logged (default: 0)
-p, --passive               start MaxScale as a passive standby
-g, --debug=arg1,arg2,...   enable or disable debug features. Supported arguments:
disable-module-unloading   disable module unloading at exit. Will produce better
                            Valgrind leak reports if leaked memory was allocated in
                            a shared library
enable-module-unloading    cancels disable-module-unloading
redirect-output-to-file    redirect stdout and stderr to the file given as an argument
enable-statement-logging   enable the logging of monitor and authenticator SQL statements sent by MaxScale to the servers
disable-statement-logging  disable the logging of monitor and authenticator SQL statements sent by MaxScale to the servers
enable-cors                enable CORS support in the REST API
allow-duplicate-servers    allow multiple servers to have the same address/port combination
gdb-stacktrace             Use GDB to generate stacktraces
-v, --version               print version info and exit
-V, --version-full          print full version info and exit
-?, --help                  show this help

Defaults paths:
config file       : /etc/maxscale.cnf
configdir         : /etc
logdir            : /var/log/maxscale
cachedir          : /var/cache/maxscale
libdir            : /usr/lib64/maxscale
sharedir          : /usr/share/maxscale
datadir           : /var/lib/maxscale
execdir           : /usr/bin
language          : /var/lib/maxscale
piddir            : /var/run/maxscale
persistdir        : /var/lib/maxscale/maxscale.cnf.d
module configdir  : /etc/maxscale.modules.d
connector plugins : /usr/lib64/mysql/plugin

If '--basedir' is provided then all other paths, including the default
configuration file path, are defined relative to that. As an example,
if '--basedir /path/maxscale' is specified, then, for instance, the log
dir will be '/path/maxscale/var/log/maxscale', the config dir will be
'/path/maxscale/etc' and the default config file will be
'/path/maxscale/etc/maxscale.cnf'.

MaxScale documentation: https://mariadb.com/kb/en/maxscale/

以 yum 和 rpm 方式安装，有些默认的参数如下：

config file       : /etc/maxscale.cnf
configdir         : /etc
logdir            : /var/log/maxscale
cachedir          : /var/cache/maxscale
libdir            : /usr/lib64/maxscale
sharedir          : /usr/share/maxscale
datadir           : /var/lib/maxscale
execdir           : /usr/bin
language          : /var/lib/maxscale
piddir            : /var/run/maxscale
persistdir        : /var/lib/maxscale/maxscale.cnf.d
module configdir  : /etc/maxscale.modules.d
connector plugins : /usr/lib64/mysql/plugin

maxctrl

在老的版本中是 maxadmin，2.4 及之后的版本开始统一使用 maxctrl 命令来操作 maxscale，先看下基本格式 maxctrl --help

# maxctrl --help
maxctrl

Commands:
maxctrl list <command>        List objects
maxctrl show <command>        Show objects
maxctrl set <command>         Set object state
maxctrl clear <command>       Clear object state
maxctrl drain <command>       Drain objects
maxctrl enable <command>      Enable functionality
maxctrl disable <command>     Disable functionality
maxctrl create <command>      Create objects
maxctrl destroy <command>     Destroy objects
maxctrl link <command>        Link objects
maxctrl unlink <command>      Unlink objects
maxctrl start <command>       Start objects
maxctrl stop <command>        Stop objects
maxctrl alter <command>       Alter objects
maxctrl rotate <command>      Rotate log files
maxctrl reload <command>      Reload objects
maxctrl call <command>        Call module commands
maxctrl cluster <command>     Cluster objects
maxctrl api <command>         Raw REST API access
maxctrl classify <statement>  Classify statement

Global Options:
-u, --user      Username to use                    [string] [default: "admin"]
-p, --password  Password for the user. To input the password manually, use -p
                '' or --password=''              [string] [default: "mariadb"]
-h, --hosts     List of MaxScale hosts. The hosts must be in HOST:PORT format
                and each value must be separated by a comma.
                                            [string] [default: "127.0.0.1:8989"]
-t, --timeout   Request timeout in plain milliseconds, e.g '-t 1000', or as
                duration with suffix [h|m|s|ms], e.g. '-t 10s'
                                                    [string] [default: "10000"]
-q, --quiet     Silence all output. Ignored while in interactive mode.
                                                    [boolean] [default: false]
--tsv           Print tab separated output          [boolean] [default: false]

HTTPS/TLS Options:
-s, --secure                  Enable HTTPS requests [boolean] [default: false]
--tls-key                     Path to TLS private key                 [string]
--tls-passphrase              Password for the TLS private key        [string]
--tls-cert                    Path to TLS public certificate          [string]
--tls-ca-cert                 Path to TLS CA certificate              [string]
-n, --tls-verify-server-cert  Whether to verify server TLS certificates
                                                    [boolean] [default: true]

Options:
--version  Show version number                                       [boolean]
--help     Show help                                                 [boolean]

If no commands are given, maxctrl is started in interactive mode. Use `exit` to
exit the interactive mode.

查看具体命令的使用方法，如查看 list 命令的使用方法：maxctrl list help

# maxctrl list help
Usage: list <command>

Commands:
maxctrl list servers              List servers
maxctrl list services             List services
maxctrl list listeners [service]  List listeners
maxctrl list monitors             List monitors
maxctrl list sessions             List sessions
maxctrl list filters              List filters
maxctrl list modules              List loaded modules
maxctrl list threads              List threads
maxctrl list users                List created users
maxctrl list commands             List module commands

Global Options:
-u, --user      Username to use  [string] [default: "admin"]
-p, --password  Password for the user. To input the password manually, use -p '' or --password=''  [string] [default: "mariadb"]
-h, --hosts     List of MaxScale hosts. The hosts must be in HOST:PORT format and each value must be separated by a comma.  [string] [default: "127.0.0.1:8989"]
-t, --timeout   Request timeout in plain milliseconds, e.g '-t 1000', or as duration with suffix [h|m|s|ms], e.g. '-t 10s'  [string] [default: "10000"]
-q, --quiet     Silence all output. Ignored while in interactive mode.  [boolean] [default: false]
--tsv           Print tab separated output  [boolean] [default: false]

HTTPS/TLS Options:
-s, --secure                  Enable HTTPS requests  [boolean] [default: false]
--tls-key                     Path to TLS private key  [string]
--tls-passphrase              Password for the TLS private key  [string]
--tls-cert                    Path to TLS public certificate  [string]
--tls-ca-cert                 Path to TLS CA certificate  [string]
-n, --tls-verify-server-cert  Whether to verify server TLS certificates  [boolean] [default: true]

Options:
--version  Show version number  [boolean]
--help     Show help  [boolean]

使用示例

查看后端数据库 maxctrl list servers

# maxctrl list servers
┌───────────────┬──────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server        │ Address      │ Port │ Connections │ State           │ GTID │
├───────────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ mysql-master  │ 192.168.0.71 │ 3306 │ 0           │ Master, Running │      │
├───────────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ mysql-slave01 │ 192.168.0.72 │ 3306 │ 0           │ Slave, Running  │      │
├───────────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ mysql-slave02 │ 192.168.0.73 │ 3306 │ 0           │ Slave, Running  │      │
└───────────────┴──────────────┴──────┴─────────────┴─────────────────┴──────┘

查看 maxscale 的服务策略 maxctrl list services

# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬────────────────────────────────────────────┐
│ Service            │ Router         │ Connections │ Total Connections │ Targets                                    │
├────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0           │ 0                 │ mysql-master, mysql-slave01, mysql-slave02 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴────────────────────────────────────────────┘

简单的测试

测试读写分离

创建一个用于测试的数据库用户：

MySQL [(none)]> create user 'scott'@'%' identified by 'Tiger123!@#';

MySQL [(none)]> grant all privileges on metaverse.* to 'scott'@'%';

MySQL [(none)]> flush privileges;

通过 maxscale 访问数据库

1	mysql -uscott -pTiger123!@# -h192.168.0.71 -P4006

验证读写分离

# mysql -uscott -p'Tiger123!@#' -h192.168.0.70 -P4006
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use metaverse;
Database changed
MySQL [metaverse]> show tables;
ERROR 1045 (28000): Access denied for user 'scott'@'%' (using password: YES)
MySQL [metaverse]> CREATE TABLE student (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
    -> sNo CHAR(10) NOT NULL COMMENT '',
    -> sName VARCHAR(20) NOT NULL COMMENT '',
    -> sex CHAR(1) NOT NULL DEFAULT '' COMMENT '',
    -> birthday DATE NOT NULL COMMENT '',
    -> deptName VARCHAR(30) NOT NULL COMMENT '',
    -> remark VARCHAR(80) COMMENT '',
    -> PRIMARY KEY (id), /*id*/
    -> UNIQUE (sNo), /*sNo*/
    -> INDEX (sName) /*sName*/
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

MySQL [metaverse]> insert into student(sNo,sName,sex,birthday,deptName)
    -> VALUES('2011230215','','','1995-7-23','2012');
Query OK, 1 row affected (0.00 sec)

MySQL [metaverse]> select * from student;
+----+------------+-------+-----+------------+----------+--------+
| id | sNo        | sName | sex | birthday   | deptName | remark |
+----+------------+-------+-----+------------+----------+--------+
|  1 | 2011230215 |       |     | 1995-07-23 | 2012     | NULL   |
+----+------------+-------+-----+------------+----------+--------+
1 row in set (0.01 sec)

查看 MaxScale 的日志，可以看到读的请求发送到了从库上，写的请求发送到的主库上，如果 log 日志开启了 info 级别，在日志中也可以清楚看到路由情况。