卖WIFI的男孩儿
  • 首页
jvmlz
成长是要付出代价的
  1. 首页
  2. mysql
  3. 正文

Maxscale安装-读写分离(1)

2020年10月30日 448点热度 1人点赞 21条评论
%

前言

关于MySQL中间件的产品也很多,之前用过了360的Atlas、玩过MyCat。这边我选择 Maxscale的原因就是功能能满足需求,也看好他的未来发展。

其实有关于如何安装 Maxscale的文章百度一下一大把,写这篇文章主要为了说明配置的某些现象,同时也为之后使用Maxscale的其他配置做下基础。

我的环境

这边我的'一主二从'已经是搭建好的了,如何搭建就不再描述了。

注意:这边我的三个节点都没有开启 GTID,具体是为什么在之后会讲到,这边大家留意一下就行。

192.168.137.11 (Maxscale)
192.168.137.21:3306 (Master)
192.168.137.22:3306 (Slave)
192.168.137.23:3306 (Slave)

这边我使用的用于复制的用户是maxscale,具体权限如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE USER maxscale@'%' IDENTIFIED BY "123456";
GRANT replication slave, replication client ON *.* TO maxscale@'%';
GRANT SELECT ON mysql.* TO maxscale@'%';
GRANT ALL ON maxscale_schema.* TO maxscale@'%';
GRANT SHOW DATABASES ON *.* TO maxscale@'%';
root@(none) 22:34:15>SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 10.1.8-MariaDB-log |
+--------------------+
1 row in set (0.00 sec)
root@(none) 22:34:25>SHOW SLAVE HOSTS;
+------------+----------------+------+------------+
| Server_id  | Host           | Port | Master_id  |
+------------+----------------+------+------------+
| 3306137022 | 192.168.137.21 | 3306 | 3306137021 |
| 3306137023 | 192.168.137.21 | 3306 | 3306137021 |
+------------+----------------+------+------------+
2 rows in set (0.00 sec)

下载Maxscale

在 192.168.137.11 机器上

这边提供下载地址: https://downloads.mariadb.com

我的版本

1
2
3
4
5
6
[root@normal_11 opt]# pwd
/opt
[root@normal_11 opt]# ll
total 149624
-rw-r--r--   1 root root   3587510 Nov  2 21:07 maxscale-2.0.1.centos.7.tar.gz

 

开始安装

 

1
2
3
4
5
6
7
8
9
10
11
12
[root@normal_11 opt]# groupadd maxscale
[root@normal_11 opt]# useradd -g maxscale maxscale
[root@normal_11 opt]# cd /opt
[root@normal_11 opt]# tar -zxf maxscale-2.0.1.centos.7.tar.gz
[root@normal_11 opt]# ln -s maxscale-2.0.1.centos.7 /usr/local/maxscale
[root@normal_11 opt]# chown -R maxscale:maxscale /usr/local/maxscale
[root@normal_11 opt]# mkdir -p /u01/maxscale/{data,cache,logs,tmp}
[root@normal_11 opt]# mkdir -p /u01/maxscale/logs/{binlog,trace}
[root@normal_11 opt]# chown -R maxscale:maxscale /u01/maxscale
[root@normal_11 opt]# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/
[root@normal_11 opt]# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456
1D30C1E689410756D7B82C233FCBF8D9

Maxscale 配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
[root@normal_11 opt]# cat /etc/maxscale.cnf
###################################################
# CREATE USER maxscale@'%' IDENTIFIED BY "123456";
# GRANT replication slave, replication client ON *.* TO maxscale@'%';
# GRANT SELECT ON mysql.* TO maxscale@'%';
# GRANT ALL ON maxscale_schema.* TO maxscale@'%';
# GRANT SHOW DATABASES ON *.* TO maxscale@'%';
# groupadd maxscale
# useradd -g maxscale maxscale
# cd /opt
# tar -zxf maxscale-2.0.1.rhel.7.tar.gz
# ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale
# chown -R maxscale:maxscale /usr/local/maxscale
# mkdir -p /u01/maxscale/{data,cache,logs,tmp}
# mkdir -p /u01/maxscale/logs/{binlog,trace}
# chown -R maxscale:maxscale /u01/maxscale
# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/
# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456
###################################################
[maxscale]
# 开启线程个数,默认为1.设置为auto会同cpu核数相同
threads=auto
# timestamp精度
ms_timestamp=1
# 将日志写入到syslog中
syslog=1
# 将日志写入到maxscale的日志文件中
maxlog=1
# 不将日志写入到共享缓存中,开启debug模式时可打开加快速度
log_to_shm=0
# 记录告警信息
log_warning=1
# 记录notice
log_notice=1
# 记录info
log_info=1
# 不打开debug模式
log_debug=0
# 日志递增
log_augmentation=1
# 相关目录设置
basedir=/usr/local/maxscale/
logdir=/u01/maxscale/logs/trace/
datadir=/u01/maxscale/data/
cachedir=/u01/maxscale/cache/
piddir=/u01/maxscale/tmp/
[server1]
type=server
address=192.168.137.21
port=3306
protocol=MySQLBackend
serv_weight=1
[server2]
type=server
address=192.168.137.22
port=3306
protocol=MySQLBackend
serv_weight=3
[server3]
type=server
address=192.168.137.23
port=3306
protocol=MySQLBackend
serv_weight=3
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
# 监控心态为 10s
monitor_interval=10000
# 当复制slave全部断掉时,maxscale仍然可用,将所有的访问指向master节点
detect_stale_master=true
# 监控主从复制延迟,可用后续指定router service的(配置此参数请求会永远落在 master)
# detect_replication_lag=true
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
router_options=slave
# 允许root用户登录执行
enable_root_user=1
# 查询权重
weightby=serv_weight
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
max_slave_connections=100%
# sql语句中的存在变量只指向master中执行
use_sql_variables_in=master
# 允许root用户登录执行
enable_root_user=1
# 允许主从最大间隔(s)
max_slave_replication_lag=3600
[MaxAdmin Service]
type=service
router=cli
[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=/u01/maxscale/tmp/maxadmin.sock
port=6603

细心的朋友会注意到, 的我配置文件最上面就是安装 Maxscale 的基本步骤,这是本人的一个习惯.

这边我稍微说明一下配置文件的意思:

  1. [server1], [server2], [server3] 我配置了三个Maxscale需要连接的MySQL服务
  2. [MySQL Monitor] 配置一个监听服务, 同时监听着 [server1], [server2], [server3] 的状态
  3. [Read-Only Service] 配置了只读服务, 只在[server2], [server3]中执行

注意: 虽然是只读服务但是同样可以执行 DML DDL, 说以要限制好用户的权限.

  1. [Read-Write Listener] 配置了读写分离的服务
  2. [MaxAdmin Listener] 配置了用户管理Maxscale的服务

演示

这边我们以 [Read-Write Listener] 配置的服务来演示读写分离情况

  1. 启动 Maxscale

如果启动有报错那就查看一下日志 /var/log/message 或 /u01/maxscale/logs/trace/maxscale1.log(自定义)

1
2
3
4
5
6
7
8
[root@normal_11 opt]# /usr/local/maxscale/bin/maxscale -f /etc/maxscale.cnf
[root@normal_11 opt]# netstat -natpl | grep max
tcp        0      0 0.0.0.0:4008            0.0.0.0:*               LISTEN      5507/maxscale      
tcp        0      0 0.0.0.0:6603            0.0.0.0:*               LISTEN      5507/maxscale      
tcp        0      0 0.0.0.0:4006            0.0.0.0:*               LISTEN      5507/maxscale      
tcp        0      0 192.168.137.11:43102    192.168.137.22:3306     ESTABLISHED 5507/maxscale      
tcp        0      0 192.168.137.11:54624    192.168.137.21:3306     ESTABLISHED 5507/maxscale        
tcp        0      0 192.168.137.11:52989    192.168.137.23:3306     ESTABLISHED 5507/maxscale

 

  1. 使用 maxadmin 查看服务

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@normal_11 opt]# /usr/local/maxscale/bin/maxadmin -S /u01/maxscale/tmp/maxadmin.sock      
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.137.21  |  3306 |           0 | Master, Running
server2            | 192.168.137.22  |  3306 |           0 | Slave, Running
server3            | 192.168.137.23  |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> list services
Services.
--------------------------+----------------------+--------+---------------
Service Name              | Router Module        | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read-Only Service         | readconnroute        |      1 |     1
Read-Write Service        | readwritesplit       |      1 |     1
MaxAdmin Service          | cli                  |      3 |     3
--------------------------+----------------------+--------+---------------

通过登录Maxscale的读写分离服务, 来执行sql并且查看日志,查看日志路由情况。

注意: 这边登录的用户就是普通的MySQL用户, 不是maxscale用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
[root@normal_11 opt]# mysql -uHH -p -h192.168.137.11 -P4006
Logging to file '/u01/mysql_history/query.log'
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5524
Server version: 5.5.5-10.0.0 2.0.1-maxscale MariaDB Server
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
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.
HH@192.168.137.11 11:13:46 [(none)]>SELECT * FROM test.t1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | HH1  |   1 |
|  2 | HH2  |   2 |
|  3 | HH3  |   3 |
+----+------+-----+
3 rows in set (0.01 sec)
HH@192.168.137.11 11:15:03 [(none)]>INSERT INTO test.t1 VALUES(NULL, 'HH4', 4);
Query OK, 1 row affected (0.01 sec)
# 使用 HH 登录成的日志
2016-11-03 23:13:46.907   info   : (log_server_connections): Servers and router connection counts:
2016-11-03 23:13:46.907   info   : (log_server_connections): current operations : 0 in  192.168.137.21:3306 RUNNING MASTER
2016-11-03 23:13:46.907   info   : (log_server_connections): current operations : 0 in  192.168.137.22:3306 RUNNING SLAVE
2016-11-03 23:13:46.907   info   : (log_server_connections): current operations : 0 in  192.168.137.23:3306 RUNNING SLAVE
2016-11-03 23:13:46.908   info   : (select_connect_backend_servers): Selected RUNNING MASTER in         192.168.137.21:3306
2016-11-03 23:13:46.908   info   : (select_connect_backend_servers): Selected RUNNING SLAVE in  192.168.137.22:3306
2016-11-03 23:13:46.908   info   : (select_connect_backend_servers): Selected RUNNING SLAVE in  192.168.137.23:3306
2016-11-03 23:13:46.908   info   : (session_alloc): Started Read-Write Service client session [0] for 'HH' from 192.168.137.11
2016-11-03 23:13:46.909   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@version_comment limit 1
2016-11-03 23:13:46.909   [9]  info   : (route_single_stmt): Route query to master      192.168.137.21:3306 <
2016-11-03 23:13:46.922   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select USER()
2016-11-03 23:13:46.922   [9]  info   : (route_single_stmt): Route query to slave       192.168.137.22:3306 <
# 执行 SELECT * FROM test.t1 语句被路由到 192.168.137.22:3306[server2]中的日志
2016-11-03 23:15:02.618   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: SELECT * FROM test.t1
2016-11-03 23:15:02.618   [9]  info   : (route_single_stmt): Route query to slave       192.168.137.22:3306 <
# 执行 INSERT INTO test.t1 VALUES(NULL, 'HH4', 4) 语句被路由到 192.168.137.21:3306[server1]中的日志
2016-11-03 23:17:02.716   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.t1 VALUES(NULL, 'HH4', 4)
2016-11-03 23:17:02.716   [9]  info   : (route_single_stmt): Route query to master      192.168.137.21:3306 <

上面是最基本的读写分离操作

重点参数说明与演示

有许多刚刚搭建Maxscale的朋友会问到为什么我的select总是落在Master上,影响比较大的参数有两个,如下:

1
2
3
4
5
# 监控主从复制延迟,可用后续指定router service的(配置此参数请求会永远落在 master)
detect_replication_lag=true
# 允许主从最大间隔(s).有些朋友在做压力测试的是会说SELECT 会打在Master,多半是这个参数
max_slave_replication_lag=3600

 

  1. detect_replication_lag=true 时的现象

执行 SELECT 语句

1
2
3
4
5
6
7
8
9
10
HH@192.168.137.11 11:24:59 [(none)]>SELECT * FROM test.t1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | HH1  |   1 |
|  2 | HH2  |   2 |
|  3 | HH3  |   3 |
|  4 | HH4  |   4 |
+----+------+-----+
4 rows in set (0.00 sec)

查看路由的日志

1
2
3
# 该查询落在了Master(192.168.137.21:3306)[server1]上了
2016-11-03 23:25:04.364   [7]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: SELECT * FROM test.t1
2016-11-03 23:25:04.364   [7]  info   : (route_single_stmt): Route query to master      192.168.137.21:3306 <

所以我的配置是将 detect_replication_lag=true 给注释了也就是用默认值false.

关于 max_slave_replication_lag 这个参数我就不演示了, 因为涉及到了使用 sysbench 等压力工具不在本文范畴, 有兴趣的自己玩玩, 这边就说说该参数的意义。

标签: maxscale MySQL
最后更新:2020年10月30日

jvmlz

一个不努力的人,别人想拉你一把都不知道你的手在哪里!

点赞

文章评论

  • asmr https://0mniartist.tumblr.com

    Very good blog! Do you have any recommendations for aspiring
    writers? I'm hoping to start my own site soon but I'm a little lost on everything.
    Would you suggest starting with a free platform like Wordpress or go for
    a paid option? There are so many options out there that I'm completely overwhelmed ..
    Any recommendations? Many thanks! 0mniartist asmr

    2021年04月11日
    回复
  • asmr https://0mniartist.tumblr.com

    I know this web page presents quality depending content and additional data, is there any other website which
    offers these data in quality? 0mniartist asmr

    2021年04月11日
    回复
  • 0mniartist

    I am genuinely grateful to the owner of this website who
    has shared this impressive paragraph at at this place.
    asmr 0mniartist

    2021年04月12日
    回复
  • http://bit.ly

    Its such as you learn my mind! You seem to grasp
    so much about this, like you wrote the e book in it or something.
    I feel that you could do with some percent to drive the message home a bit, however other than that,
    this is fantastic blog. A great read. I'll definitely be back.
    0mniartist asmr

    2021年04月15日
    回复
  • Lenora

    Having read this I thought it was extremely enlightening.

    I appreciate you finding the time and effort
    to put this informative article together. I once again find myself personally spending a significant amount of time both reading and posting comments.

    But so what, it was still worth it!

    2021年04月15日
    回复
  • pussy888app

    you're in point of fact a good webmaster. The website loading
    pace is incredible. It kind of feels that you're doing any distinctive
    trick. In addition, The contents are masterpiece.
    you have performed a magnificent task on this topic!

    My web site ... <a href="https://918kiss-m.com/pussy888/">pussy888app</a&gt;

    2021年04月15日
    回复
  • gema playboy2

    This blog was... how do I say it? Relevant!! Finally I have found something that
    helped me. Cheers!

    Also visit my web blog ... <a href="https://918kiss-m.com/playboy2/">gema playboy2</a>

    2021年04月15日
    回复
  • love138 online

    Good information. Lucky me I found your blog by accident (stumbleupon).

    I've book marked it for later!

    My site: <a href="https://918kiss-m.com/love138/">love138 online</a>

    2021年04月15日
    回复
  • aaa1188 slot game list

    I am extremely inspired with your writing talents and
    also with the structure in your blog. Is this a paid subject or did you
    customize it your self? Anyway stay up the nice high quality writing, it is rare to look a great blog like this one these days..

    Also visit my web-site ... <a href="https://918kiss-m.com/aaa1188/">aaa1188 slot game list</a>

    2021年04月15日
    回复
  • lpe88

    I do not know if it's just me or if perhaps everybody else
    encountering issues with your website. It seems like
    some of the written text within your posts are running off the screen. Can somebody else please comment
    and let me know if this is happening to them as well?
    This might be a problem with my browser because I've had this happen before.
    Cheers

    Here is my web-site: <a href="https://918kiss-m.com/lpe88/">lpe88</a&gt;

    2021年04月15日
    回复
  • 918kaya download pc

    It's amazing to visit this site and reading the views of all mates about
    this paragraph, while I am also zealous of getting familiarity.

    my page - <a href="https://918kiss-m.com/918kaya/">918kaya download pc</a>

    2021年04月15日
    回复
  • download slot game greatwall99

    If you would like to improve your know-how just keep visiting this site
    and be updated with the most up-to-date news update posted here.

    Here is my homepage <a href="https://918kiss-m.com/greatwall99/">download slot game greatwall99</a>

    2021年04月15日
    回复
  • http://www.cowerdesign.com/

    Why people still use to read news papers when in this technological world everything is accessible on web?

    Check out my page: <a href="http://www.cowerdesign.com/wikI/ManiehwRoddkd">http://www.cowerdesign.com/</a&gt;

    2021年04月16日
    回复
  • oathbreakermtg.org

    Pretty great post. I simply stumbled upon your blog and wanted to say that I
    have truly loved surfing around your weblog posts. In any
    case I'll be subscribing to your rss feed and I hope you write once more
    soon!

    my web blog :: <a href="https://oathbreakermtg.org/forums/users/ellisbarrington/">oathbreakermtg.org</a&gt;

    2021年04月16日
    回复
  • http://lechon.ca/

    Highly energetic post, I liked that bit. Will there be a
    part 2?

    My page :: <a href="http://lechon.ca/index.php/blog/664617/do-male-enhancement-pills-actually-work-for-troops/">http://lechon.ca/</a&gt;

    2021年04月16日
    回复
  • Andres

    You are my inhalation, I own few blogs and often run out from post :
    ).

    my website: <a href="https://alichehade.net/community/profile/simoigeorgetta/">Andres</a&gt;

    2021年04月16日
    回复
  • http://www.craksracing.com/modules.php?name=Your_Account&op=userinfo&username=MacCormickVern

    This is really interesting, You are a very skilled blogger.
    I've joined your rss feed and look forward to seeking more
    of your magnificent post. Also, I have shared your web
    site in my social networks!

    Also visit my web-site :: <a href="http://www.craksracing.com/modules.php?name=Your_Account&op=userinfo&username=MacCormickVern">http://www.craksracing.com/modules.php?name=Your_Account&op=userinfo&username=MacCormickVern</a&gt;

    2021年04月16日
    回复
  • http://bit.ly/3to3ZZU

    Your way of explaining everything in this piece of writing is genuinely
    nice, every one be able to easily be aware of it, Thanks a lot.
    asmr 0mniartist

    2021年04月16日
    回复
  • http://bookmarkstumble.com/story3079208/profile-of-leblancrosella

    thank you for all your efforts that you have put in this.
    Very interesting info.

    Feel free to surf to my web blog <a href="http://bookmarkstumble.com/story3079208/profile-of-leblancrosella">http://bookmarkstumble.com/story3079208/profile-of-leblancrosella</a&gt;

    2021年04月16日
    回复
  • Situs Judi Slot

    With havin so much content and articles do you ever run into any issues
    of plagorism or copyright infringement? My blog has a lot of exclusive content
    I've either written myself or outsourced but it appears a lot of it
    is popping it up all over the internet without my
    permission. Do you know any methods to help prevent content from being
    stolen? I'd certainly appreciate it.

    2021年04月17日
    回复
  • judi depo pulsa

    Hey There. I discovered your blog the usage of msn. This is a very neatly written article.
    I will make sure to bookmark it and return to
    read extra of your useful information. Thanks for the post.

    I'll certainly return.

    2021年04月17日
    回复
  • 取消回复
    文章归档
    • 2020年十二月
    • 2020年十月
    2021年四月
    一 二 三 四 五 六 日
     1234
    567891011
    12131415161718
    19202122232425
    2627282930  
    « 12月    
    最新 热点 随机
    最新 热点 随机
    Stringboot Netty-SocketIO 集群 Maxscale安装-读写分离(1) IT项目开发管理与绩效考核 Ubuntu 16.04 设置 程序开机启动 用/etc/ rc.local开机启动命令/脚本的方法,网上找的都不好使 Docker搭建WordPress
    Maxscale安装-读写分离(1) IT项目开发管理与绩效考核 Docker搭建WordPress Ubuntu 16.04 设置 程序开机启动 用/etc/ rc.local开机启动命令/脚本的方法,网上找的都不好使 Stringboot Netty-SocketIO 集群
    分类目录
    • IT管理
    • Linux
    • mysql
    • Uncategorized
    • 及时通讯

    COPYRIGHT © 2020 卖WIFI的男孩儿. ALL RIGHTS RESERVED.

    THEME KRATOS MADE BY VTROIS

    滇ICP备17010909号-2