mysql mgr+atlas 实现读写分离

最新推荐文章于 2025-06-18 17:13:31 发布

原创最新推荐文章于 2025-06-18 17:13:31 发布 · 360 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库架构 #dba

mysql 专栏收录该内容

20 篇文章

订阅专栏

本文介绍了如何通过360团队的Atlas中间件在MySQL集群上部署读写分离架构，详细步骤包括安装Atlas、配置参数、启动服务以及测试读写分离效果。Atlas将查询请求分发到从节点，写请求则路由到主节点，但当主节点切换时，需手动更新配置，不适合实时性要求高的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

上一篇我们将mgr多主集群切换到单主模式，实现了一主(master)两备(secondary)的架构。在高并发模式下，paxos协议的乐观锁可能会导致事务的冲突、回滚和性能的下降，这次我们尝试通过中间件atlas的方式，实现读写分离的架构。

atlas是360团队在MySQL-Proxy 0.8.2版本的基础上，进一步优化的项目，该中间件在当前很多场景中得到广泛应用。这里将atlas部署在Node3服务器上，通过atlas实现数据库的读写分离。

1、部署atlas软件在node3节点，并进行参数的配置

#####安装atlas软包
rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
#####配置atlas配置文件
cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnfbak


####编辑配置文件，填写下列配置
vi test.cnf

[mysql-proxy]
proxy-backend-addresses = 192.168.170.17:3306
proxy-read-only-backend-addresses = 192.168.170.18:3306,192.168.170.19:3306 
pwds = xhh:/iZxz+0GRoA=,mha:/iZxz+0GRoA= 
daemon = true
keepalive = true
event-threads = 8 
log-level = message 
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-username = user
admin-password = pass
admin-address = 0.0.0.0:12345
charset=utf8 


#####关键参数说明
#####proxy-backend-addresses   主库的写节点
#####proxy-read-only-backend-addresses  读写点
#####pwds 应用连接的用户和加密密码
#####daemon 以后台的方式运行
#####keepalive 心跳检测
#####event-threads 开启的线程
#####proxy-address  对外提供服务的代理地址
#####admin-address  内部管理地址
#####admin-username、admin-password  内部管理的用户名和密码

2、启动atlas服务

[root@rac3 ~]# cd /usr/local/mysql-proxy/bin
[root@rac3 bin]# ls
encrypt  mysql-proxy  mysql-proxyd  VERSION
[root@rac3 bin]# 

#####encrypt   制作加密的
[root@rac3 bin]# ./encrypt 123456
/iZxz+0GRoA=

#####mysql-proxyd   atlas的启动进程

[root@rac3 bin]# ./mysql-proxyd test start
OK: MySQL-Proxy of test is started
[root@rac3 bin]#

3、测试atlas读写分离

#####通过atlas代理地址连接mgr集群
[root@rac3 bin]# /usr/local/mysql/bin/mysql -uxhh -p123456 -P13306 -h192.168.170.19
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 1
Server version: 5.0.81-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)
mysql> 
#####atlas分别将查询的请求分别发到节点2和节点3进行处理


#####atlas将写的请求发送到节点1进行处理
mysql>  begin;select @@server_id;commit;
Query OK, 0 rows affected (0.01 sec)
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

4、atlas端口管理

#####通过配置文件定义用户名和密码连接到atlas内置库，可进行一些节点管理等操作
[root@rac3 bin]# /usr/local/mysql/bin/mysql -uuser -ppass -P12345 -h192.168.170.19
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 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2020, 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.

mysql> select * from help;
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | shows this help                                         |
| SELECT * FROM backends     | lists the backends and their state                      |
| SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id     | online backend server, ...                              |
| ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
| ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
| SELECT * FROM clients      | lists the clients                                       |
| ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
| REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
| SELECT * FROM pwds         | lists the pwds                                          |
| ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
| ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
| REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
| SAVE CONFIG                | save the backends to config file                        |
| SELECT VERSION             | display the version of Atlas                            |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)

mysql> 
mysql> select * from backends;
+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 192.168.170.17:3306 | up    | rw   |
|           2 | 192.168.170.18:3306 | up    | ro   |
|           3 | 192.168.170.19:3306 | up    | ro   |
+-------------+---------------------+-------+------+
3 rows in set (0.00 sec)
mysql> 
####通过backends可以查询到后端的节点，type状态为读写、读等

通过atlas+mgr实现了读写分离，但如果主节点发生切换，需要手动修改atlas的配置来实现业务的耦合，在一些7*24小时的应用场景不太符合业务实时性的要求，下篇我们将通过虚拟地址的方式来解决这一问题。