Atlas简介
Atlas 是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。
源码 Github： https://github.com/Qihoo360/Atlas
功能
- 读写分离
1.监控、2.对应用透明、3.语义的分析，分析语句的类型、4.进行语句的路由、5.对于读的操作应该有负载均衡的功能、6.平滑上下线节点能力、7.黑名单 - 从库负载均衡
- 自动分表
- IP过滤
- SQL语句黑白名单
- DBA可平滑上下线DB
- 自动摘除宕机的DB
Atlas读写分离原理图（Altas负责分析SQL，将W和R分别路由到主库与从库上）
应用场景
Atlas是一个位于前端应用与后端MySQL数据库之间的中间件，它使得应用程序员无需再关心读写分离、分表等与MySQL相关的细节，可以专注于编写业务逻辑，同时使得DBA的运维工作对前端应用透明，上下线DB前端应用无感知。
安装、配置
下载地址：https://github.com/Qihoo360/Atlas/releases
注意事项：
1、Atlas只能安装运行在64位的系统上
2、Centos 5.X安装 Atlas-XX.el5.x86_64.rpm，Centos 6.X安装Atlas-XX.el6.x86_64.rpm。
3、后端mysql版本应大于5.1，建议使用Mysql 5.6以上
## 我使用的是CentOS6.9 用的是Atlas-2.2.1.el6.x86_64.rpm （点击下载）
安装软件（Atlas也安装在db03上）
- 下载并安装Atlas
[root@db03 ~]# cat /etc/redhat-release CentOS release 6.9 (Final) [root@db03 ~]# cd /server/ scripts/ tools/ [root@db03 ~]# cd /server/tools/ [root@db03 tools]# wget https://file.sudo.ren/Altas/Atlas-2.2.1.el6.x86_64.rpm --2019-09-05 10:22:00-- https://file.sudo.ren/Altas/Atlas-2.2.1.el6.x86_64.rpm Resolving file.sudo.ren... 218.78.4.211 Connecting to file.sudo.ren|218.78.4.211|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 4963681 (4.7M) [application/x-redhat-package-manager] Saving to: “Atlas-2.2.1.el6.x86_64.rpm.1” 100%[============================================================================================>] 4,963,681 114K/s in 43s 2019-09-05 10:22:43 (113 KB/s) - “Atlas-2.2.1.el6.x86_64.rpm.1” saved [4963681/4963681] [root@db03 tools]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm Preparing... ########################################### [100%] 1:Atlas ########################################### [100%] [root@db03 tools]# cd /usr/local/mysql-proxy/ [root@db03 mysql-proxy]# ll total 16 drwxr-xr-x 2 root root 4096 Sep 5 10:23 bin drwxr-xr-x 2 root root 4096 Sep 5 10:23 conf drwxr-xr-x 3 root root 4096 Sep 5 10:23 lib drwxr-xr-x 2 root root 4096 Dec 17 2014 log [root@db03 mysql-proxy]### Atlas 默认会安装在/usr/local/mysql-proxy/目录下。
- 修改配置文件conf/test.cnf
vim /usr/local/mysql-proxy/conf/test.cnf [mysql-proxy] admin-username = user admin-password = pwd proxy-backend-addresses = 10.0.0.25:3306 proxy-read-only-backend-addresses = 10.0.0.22:3306,10.0.0.23:3306 pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098= 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-address = 0.0.0.0:2345 charset=utf8## proxy-backend-addresses = 10.0.0.25:3306 表示主库ip端口，这里连接的是MHA的VIP
## proxy-read-only-backend-addresses 表示读从库，可添加多个，用“,”分隔
## pwds 用户名和密码（格式--账户:密码），密码为加密状态，加密方法：/usr/local/mysql-proxy/bin/encrypt yourpassword
## proxy-address = 0.0.0.0:33060 应用连接地址
## admin-address = 0.0.0.0:2345 管理地址
## Atlas是qihoo 360（国内）开发软件，所以配置文件是中文注释，大家可以自己查看，我就不详细说明了 -
测试读写分离
测试读取操作：（用33060端口连接，mysql -umha -pmha -h10.0.0.23 -p33060）[root@db03 mysql-proxy]# mysql -umha -pmha -h10.0.0.23 -p33060 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'mha'@'10.0.0.23' (using password: YES) [root@db03 mysql-proxy]# mysql -umha -pmha -h10.0.0.23 -P 33060 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 Source distribution Copyright (c) 2000, 2017, 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> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 23 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 22 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 23 | +---------------+-------+ 1 row in set (0.01 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 22 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 23 | +---------------+-------+ 1 row in set (0.00 sec)# 其中Server version: 5.0.81-log Source distribution 与 正常3306端口连接的版本也不一样
# 通过多次查看server_id，每次只能查看22和23，说明主库并没有执行 读的 操作
测试写入操作：（设置两个从库为只读 set global read_only=1）
db02/db03：mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec)用33060端口连接，mysql -umha -pmha -h10.0.0.23 -p33060，并写入数据。
mysql> create database db001; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db001 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.02 sec)## 写入成功，说明是向db01中写入的。
## 注意：set global read_only=1; 只向普通用户生效，对管理用户无效 -
管理Atlas（mysql -uuser -ppwd -h127.0.0.1 -P2345）
[root@db03 log]# mysql -uuser -ppwd -h127.0.0.1 -P2345 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, 2017, 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> SELECT * FROM backends; +-------------+----------------+-------+------+ | backend_ndx | address | state | type | +-------------+----------------+-------+------+ | 1 | 10.0.0.25:3306 | up | rw | | 2 | 10.0.0.22:3306 | up | ro | | 3 | 10.0.0.23:3306 | up | ro | +-------------+----------------+-------+------+ 3 rows in set (0.00 sec)## SELECT * FROM help 通过此命令，可以查看所有命令。
##比如 SELECT * FROM backends 查看所有节点
## REMOVE BACKEND $backend_id 动态删除节点
## ADD SLAVE 10.0.0.23:3306; 动态添加节点mysql> REMOVE BACKEND 3; Empty set (0.00 sec) mysql> select * from backends; +-------------+----------------+-------+------+ | backend_ndx | address | state | type | +-------------+----------------+-------+------+ | 1 | 10.0.0.25:3306 | up | rw | | 2 | 10.0.0.22:3306 | up | ro | +-------------+----------------+-------+------+ 2 rows in set (0.00 sec) mysql> add slave 10.0.0.23:3306; Empty set (0.00 sec) mysql> select * from backends; +-------------+----------------+---------+------+ | backend_ndx | address | state | type | +-------------+----------------+---------+------+ | 1 | 10.0.0.25:3306 | up | rw | | 2 | 10.0.0.22:3306 | up | ro | | 3 | 10.0.0.23:3306 | unknown | ro | +-------------+----------------+---------+------+ 3 rows in set (0.00 sec) mysql> select * from backends; +-------------+----------------+-------+------+ | backend_ndx | address | state | type | +-------------+----------------+-------+------+ | 1 | 10.0.0.25:3306 | up | rw | | 2 | 10.0.0.22:3306 | up | ro | | 3 | 10.0.0.23:3306 | up | ro | +-------------+----------------+-------+------+ 3 rows in set (0.00 sec)
## SAVE CONFIG可在线修改配置。
save前：vim /usr/local/mysql-proxy/conf/test.cnf [mysql-proxy] proxy-read-only-backend-addresses = 10.0.0.22:3306,10.0.0.23:3306删除节点db03，save后：
mysql> remove backend 3; Empty set (0.00 sec) mysql> save config; Empty set (0.02 sec)[root@db03 ~]# vim /usr/local/mysql-proxy/conf/test.cnf [mysql-proxy] proxy-read-only-backend-addresses=10.0.0.22:3306重新添加节点db03，save：
mysql> add slave 10.0.0.23:3306; Empty set (0.00 sec) mysql> save config; Empty set (0.01 sec)vim /usr/local/mysql-proxy/conf/test.cnf [mysql-proxy] proxy-read-only-backend-addresses = 10.0.0.22:3306,10.0.0.23:3306## 也就是说，如果只删除不save的话，只是临时操作，配置文件没有被改动，如果重启Atlas，则恢复原状。
最近在做项目，所以几天没有更新博客了，如果项目比较成功的话，我也想把主从复制给用上。
最后修改于 2019-09-05 09:36:20
如果觉得我的文章对你有用，请随意赞赏
扫一扫支付
