MariaDB主从配置与MaxScale实现MySQL读写分离

253次阅读

共计 9211 个字符，预计需要花费 24 分钟才能阅读完成。

maxscale 是 mariadb 公司开发的一套数据库中间件，可以很方便的实现读写分离方案；并且提供了读写分离的负载均衡和高可用性保障。另外 maxscale 对于前端应用而言是透明的，我们可以很方便的将应用迁移到 maxscale 中实现读写分离方案，来分担主库的压力。maxscale 也提供了 sql 语句的解析过滤功能。

 主机 IP    角色    Server-id    操作系统版本
192.168.1.67    Master    77    RedHat7.3
192.168.1.68    Slave    78    Redhat7.3
192.168..1.69    Slave    79    Redhat7.3

https://downloads.mariadb.com/MaxScale/
https://downloads.mariadb.com/MaxScale/2.2.0/rhel/7server/x86_64/maxscale-2.2.0-1.rhel.7.x86_64.rpm

 将 maxscale 安装在主节点
rpm -ivh maxscale-2.2.0-1.rhel.7.x86_64.rpm

mkdir -p /maxscale/cache    
mkdir -p /maxscale/data    
mkdir -p /maxscale/log    
mkdir -p /maxscale/pid    
mkdir -p /maxscale/tmp

create user 'jiankongdb'@'%' identified by 'jiankong123';
grant SELECT on MySQL.user to 'jiankongdb'@'%';
GRANT SELECT ON mysql.db TO 'jiankongdb'@'%';
GRANT all ON *.* TO 'jiankongdb'@'%';
GRANT SHOW DATABASES ON *.* TO 'jiankongdb'@'%';
grant REPLICATION CLIENT on *.* to 'jiankongdb'@'%';
GRANT replication slave, replication client,SELECT ON *.* TO jiankongdb@'%';

详细参数说明参考官方文档：
https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-21-mariadb-maxscale-configuration-usage-scenarios/
编辑配置文件 /etc/maxscale.cnf

[root@www.linuxmi.com data]# cat /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md

# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md

[maxscale]
threads=auto
ms_timestamp=1             #timestamp 精度  
syslog=1                   #将日志写入到 syslog 中    
maxlog=1                   #将日志写入到 maxscale 的日志文件中  
log_to_shm=0               #不将日志写入到共享缓存中，开启 debug 模式时可打开加快速度  
log_warning=1              #记录告警信息  
log_notice=1               #记录 notice  
log_info=1                 #记录 info  
log_debug=0                #不打开 debug 模式  
log_augmentation=1         #日志递增  
#相关目录设置  
logdir=/maxscale/log/  
datadir=/maxscale/data/  
libdir=/usr/lib64/maxscale/  
cachedir=/maxscale/cache/  
piddir=/maxscale/pid/  
execdir=/usr/bin/  

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

[server1]
type=server
address=192.168.1.67
port=3306
protocol=MySQLBackend
#serv_weight=1

[server2]
type=server
address=192.168.1.68
port=3306
protocol=MySQLBackend
#serv_weight=3

[server3]
type=server
address=192.168.1.69
port=3306
protocol=MySQLBackend
#serv_weight=4

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=jiankongdb
passwd=jiankong123
monitor_interval=10000
detect_stale_master=true

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

# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md

[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=jiankongdb
passwd=jiankong123
router_options=slave
enable_root_user=1
weightby=serv_weight


# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md

[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=jiankongdb
passwd=jiankong123
max_slave_connections=100%
use_sql_variables_in=master
enable_root_user=1
max_slave_replication_lag=3600


# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md

[MaxAdmin Service]
type=service
router=cli

# 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=MySQLClient
port=4008

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

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default

 配置文件中的密码都是经过 maxscale 进行加密后的，可以防止密码泄露，具体的操作步骤为
在刚才配置文件中的 datadir 目录下创建加密文件
[root@www.linuxmi.com data]#maxkeys /maxscale/data  

生成加密后的密码
[root@www.linuxmi.com data]# maxpasswd /maxscale/data/ jiankong123
D88ED80AE158722A2A3FD725E906C601

1787C217C118C9B0F9A44DCB8B178414 就是 123 加密后的密码。我们可以添加到配置文件中。

maxscale -f /etc/maxscale.cnf

[root@www.linuxmi.com software]# maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.1.67    |  3306 |           0 | Master, Running
server2            | 192.168.1.68    |  3306 |           0 | Slave, Running
server3            | 192.168.1.69    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
或者
[root@www.linuxmi.com log]# maxadmin -uadmin -pmariadb
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.1.67    |  3306 |           0 | Master, Running
server2            | 192.168.1.68    |  3306 |           0 | Slave, Running
server3            | 192.168.1.69    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

 在主节点 server1 192.168.1.67 上建立测试表
MariaDB [cndba2]> create table test_maxscale(id int);  
Query OK, 0 rows affected (0.02 sec)  

MariaDB [cndba2]> insert into test_maxscale values(87);  
Query OK, 1 row affected (0.02 sec)  

MariaDB [cndba2]> select * from test_maxscale;  
+------+  
| id   |  
+------+  
|   87 |  
+------+  
1 row in set (0.00 sec)  
在节点 server2 192.168.1.68 上额外加入测试信息
MariaDB [cndba2]> insert into test_maxscale values(86);  
MariaDB [cndba2]> select * from test_maxscale;  
+------+  
| id   |  
+------+  s
|   87 |  
|   86 |  
+------+  
2 rows in set (0.00 sec)  
在节点 server3 192.168.1.69 上额外加入测试信息
MariaDB [cndba2]> insert into test_maxscale values(88);  
Query OK, 1 row affected (0.00 sec)  

MariaDB [cndba2]> select * from test_maxscale;  
+------+  
| id   |  
+------+  
|   87 |  
|   88 |  
+------+  
2 rows in set (0.00 sec)

 通过 mysql 命令行访问 maxscale 所在节点 192.168.1.78 的读写分离 listener 4006 端口
[root@www.linuxmi.com data]# mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select * from cndba2.test_maxscale;"
+------+
| id   |
+------+
|   87 |
|   86 |
+------+ 发现分到了 server2 上面

 加入包含 insert 的 sql 语句
[root@www.linuxmi.com data]# mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "insert into cndba2.test_maxscale values(90);select * from cndba2.test_maxscale;"
+------+
| id   |
+------+
|   87 |
|   86 |
|   90 |
+------+
发现转发到 server2 中，但是也包含 90 的值，我们需要到主节点 server3 和另外一个 slave 进行验证
在 server1 主节点中
MariaDB [(none)]> select * from cndba2.test_maxscale;  
+------+
| id   |
+------+
|   87 |
|   90 |
+------+
2 rows in set (0.00 sec)
在 server3 另一个 slave 节点中
MariaDB [cndba2]> select * from test_maxscale;
+------+
| id   |
+------+
|   87 |
|   88 |
|   90 |
+------+
3 rows in set (0.00 sec)
maxscale 实现了读写分离。

[root@www.linuxmi.com mysql]#  maxadmin -uadmin -pmariadb
MaxScale> show service "Read-Write-Service"
    Service:                             Read-Write-Service
    Router:                              readwritesplit
    State:                               Started

    use_sql_variables_in:      master
    slave_selection_criteria:  LEAST_CURRENT_OPERATIONS
    master_failure_mode:       fail_instantly
    max_slave_replication_lag: 3600
    retry_failed_reads:        true
    strict_multi_stmt:         true
    strict_sp_calls:           false
    disable_sescmd_history:    true
    max_sescmd_history:        0
    master_accept_reads:       false

    Number of router sessions:               121
    Current no. of router sessions:          1
    Number of queries forwarded:              272
    Number of queries forwarded to master:    0 (0.00%)
    Number of queries forwarded to slave:     272 (100.00%)
    Number of queries forwarded to all:       121 (44.49%)
    Started:                             Mon Nov 27 23:53:13 2017
    Root user access:                    Enabled
    Backend databases:
        [192.168.1.67]:3306    Protocol: MySQLBackend    Name: server1
        [192.168.1.68]:3306    Protocol: MySQLBackend    Name: server2
        [192.168.1.69]:3306    Protocol: MySQLBackend    Name: server3
    Total connections:                   122
    Currently connected:                 1

[root@www.linuxmi.com mysql]# for i in `seq 1 10`; do mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2>/dev/null & done
[1] 3963
[2] 3964
[3] 3965
[4] 3966
[5] 3967
[6] 3968
[7] 3969
[8] 3970
[9] 3971
[10] 3972
[root@www.linuxmi.com mysql]# +------------+
| @@hostname |
+------------+
| mariadb2   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb3   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb3   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb2   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb2   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb3   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb2   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb3   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb2   |
+------------+
+------------+
| @@hostname |
+------------+
| mariadb3   |
+------------+

[1]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[2]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[3]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[4]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[5]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[6]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[7]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[8]   Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[9]-  Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
[10]+  Done                    mysql -P4006 -ujiankongdb -pjiankong123 -h192.168.1.67 -e "select @@hostname;" 2> /dev/null
可以看到会话被平均分配到两个节点上

：

正文完

星哥玩云-微信公众号