Loading

MySQL - Atlas

楔子

接上篇,MHA高可用

就目前而言,我们的MHA高可用架构是这样的:

image

优点就不再过多阐述了,来说说缺点:

  • 硬件利用率不高,给人的感觉就像主库是个老爷们,整天在外头努力拼搏,开门做生意(处理业务请求),还要应对家里的三个小老婆随时取日志。
  • 这样浪费硬件资源,着实不太好,简单来计算,四个服务器,只有一个开门做生意,硬件资源利用率是25%,太低了!而通常怎么也要达到75-80%这样子。
  • 怎么办呢?小老婆不能白养活,也要出去开门接活......

所以我们的架构可以改成这样的:

image

通过中间件,将我们的读和写进行分离。这样,减轻主库的压力,甚至可以更进一步对读做进一步优化,比如如上架构的两个从库做一个轮询。

而且这样的架构特别适合读多写少的业务场景........

扯淡到此为止,我们本篇就来介绍一个第三方的代理Proxy软件——Atlas。

当然,除了Atlas,还有这么多的读写分离中间件,大家可以关注:

  • MySQL Router:MySQL官方提供。
  • Proxy SQL:Percona提供。
  • MaxScale:MariaDB提供。

About Atlas

https://github.com/Qihoo360/Atlas

摘自Atlas的架构

Atlas是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上,对其进行了优化,增加了一些新的功能特性。360内部使用Atlas运行的mysql业务,每天承载的读写请求数达几十亿条。

image
image
图片摘自Atlas的架构

闲话少叙,看怎么搞吧!

Atlas install

前置条件

  • Atlas只能安装运行在64位的系统上。
  • 后端Mysql 版本应大于5.1,建议使用Mysql 5.6以上。

MHA环境是好的,搭建参考:https://www.cnblogs.com/Neeo/articles/7809225.html

这里把它跟MHA Manager安装到同一个节点,在我的环境里,也就是db01节点,也就是当前的主库。

我这里各个环境是好:

[root@db01 ~]# masterha_check_status --conf=/etc/mha/node1.cnf
node1 (pid:43647) is running(0:PING_OK), master:10.0.0.204
[root@db01 ~]# mysql -uroot -p123

db01 [(none)]>show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       207 |      | 3306 |       204 | 6dbfdccb-b609-11eb-9158-000c291b696b |
|       205 |      | 3306 |       204 | ef2baccd-b1ad-11eb-a0fb-000c294bb55c |
|       206 |      | 3306 |       204 | 16d77e8b-b257-11eb-95b5-000c29b09d3c |
+-----------+------+------+-----------+--------------------------------------+
3 rows in set (0.00 sec)

download

install

db01,直接rpm命令安装即可:

yum install -y atlas*
rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

安装完毕后,会有这样的一个目录:

[root@db01 ~]# ll /usr/local/mysql-proxy/
total 4
drwxr-xr-x. 2 root root   75 May 16 22:00 bin
drwxr-xr-x. 2 root root   22 May 16 22:00 conf
drwxr-xr-x. 3 root root 4096 May 16 22:00 lib
drwxr-xr-x. 2 root root    6 Dec 17  2014 log

现在,安装就完事了。

配置文件和相关命令

配置文件介绍

这里列出Atlas默认提供的配置文件模板,下面列出了常用的参数:

[root@db01 ~]# cat  /usr/local/mysql-proxy/conf/test.cnf
# 管理Atlas的用户名和密码,后续可以修改
admin-username = user			
admin-password = pwd

# 指定写节点,这里我们直接让其连接VIP,后续可以跟随VIP自由的漂移
proxy-backend-addresses = 10.0.0.100:3306

# 指定读节点,也就是当前MHA环境的两个从库节点,多个读节点以逗号分隔
proxy-read-only-backend-addresses = 10.0.0.205:3306,10.0.0.206:3306

# 将需要操作MySQL的用户(管理类用户和普通用户),只要是通过Atlas访问数据的,都写在这
# 注意密码是加密的,形式是 用户名:密码,多组用户之间以逗号分隔
# 另外,写在这里的用户,都需要提前在MySQL中创建好
pwds = rs:3yb5jEku5h4=,mha:O2jBXONX098=,root:3yb5jEku5h4=

daemon = true
keepalive = true
event-threads = 8

# Atlas日志相关
log-level = message
log-path = /usr/local/mysql-proxy/log

# 是否记录经过atlas的操作日志
sql-log=ON
# atlas对外提供服务的端口
proxy-address = 0.0.0.0:33060
# 管理atlas用的端口
admin-address = 0.0.0.0:2345
# 字符集和MySQL保持一致
charset=utf8

相关命令

通过上面的配置文件介绍,你会发现密码需要加密才行,当然,Atlas也帮我们提供好了相关的命令:

# 加密密码,这里正好我们将后续用到的两个密码"123"和"mah"加密
[root@db01 ~]# /usr/local/mysql-proxy/bin/encrypt 123
3yb5jEku5h4=
[root@db01 ~]# /usr/local/mysql-proxy/bin/encrypt mha
O2jBXONX098=

# 根据test配置文件管理Atlas
[root@db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxy test start
[root@db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
[root@db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test stop

我自己的配置文件

先将默认的配置模板,做个备份,后续我们根据需要手动编写:

# 注意,下面配置文件中的用户需要提前手动的在主库中创建
[root@db01 tmp]# mysql -uroot -p123
db01 [(none)]>grant all on *.* to root@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

db01 [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| rs            | %         |
| mha           | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

[root@db01 ~]# mv /usr/local/mysql-proxy/conf/test.cnf /usr/local/mysql-proxy/conf/test.cnf.bak
[root@db01 ~]# cat  > /usr/local/mysql-proxy/conf/test.cnf <<EOF
[mysql-proxy]
admin-username = user			
admin-password = pwd
proxy-backend-addresses = 10.0.0.100:3306
proxy-read-only-backend-addresses = 10.0.0.205:3306,10.0.0.206:3306
pwds = rs:3yb5jEku5h4=,mha:O2jBXONX098=,root:3yb5jEku5h4=
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
EOF

读写分离测试

说完了配置,我们就可以尝试着启动,然后操作一把了。

根据配置文件启动Atlas

[root@db01 ~]# cat /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.100:3306
proxy-read-only-backend-addresses = 10.0.0.205:3306,10.0.0.206:3306
pwds = rs:3yb5jEku5h4=,mha:O2jBXONX098=,root:3yb5jEku5h4=
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
[root@db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started

搞点事情吧!

测试读写分离

# 通过Atlas访问数据库
[root@db01 ~]# mysql -uroot -p123 -h 10.0.0.204 -P 33060

db01 [(none)]>测试读操作,你可以反复执行下面的测试命令,观察两个从库是否轮询处理读请求^C
db01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         206 |
+-------------+
1 row in set (0.00 sec)

db01 [(none)]>select @@server_id;   # 可以看到,是轮询的处理读请求
+-------------+
| @@server_id |
+-------------+
|         205 |
+-------------+
1 row in set (0.00 sec)

db01 [(none)]>测试写操作,这里我们也可以"骗"一下Atlas,它遇到事务就认为是写操作^C
db01 [(none)]>begin;select @@server_id;commit;
Query OK, 0 rows affected (0.01 sec)

+-------------+
| @@server_id |
+-------------+
|         204 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Atlas基本管理

当你添加/删除一个用户;调整节点时,都需要手动的修改配置文件,然后重启Atlas,但这样太麻烦了。

Atlas提供了动态管理命令,可以让我们在线的进行修改配置,并且实时生效。

首先想要使用找个动态管理命令,就先需要连接到Atlas管理接口。

# 通过Atlas默认的账号和密码访问它的管理端口
[root@db01 ~]# mysql -uuser -ppwd -h 10.0.0.204 -P 2345
db01 [(none)]>查看Atlas提供的所有管理命令,当然,我们常用的就是用户和节点管理这几个命令^C
db01 [(none)]>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)

db01 [(none)]>查看所有节点信息,可以看到有两个读节点,一个写节点信息^C
db01 [(none)]>select * from backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 10.0.0.100:3306 | up    | rw   |
|           2 | 10.0.0.205:3306 | up    | ro   |
|           3 | 10.0.0.206:3306 | up    | ro   |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)

节点管理

节点管理这里,最常用的就是添加删除从节点:

[root@db01 ~]# mysql -uuser -ppwd -h 10.0.0.204 -P 2345

db01 [(none)]>首先我们演示一个删除从节点,再添加从节点^C
db01 [(none)]>remove backend 3;
Empty set (0.00 sec)

db01 [(none)]>select * from backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 10.0.0.100:3306 | up    | rw   |
|           2 | 10.0.0.205:3306 | up    | ro   |
+-------------+-----------------+-------+------+
2 rows in set (0.00 sec)

db01 [(none)]>再将刚才删除的从节点添加回来^C
db01 [(none)]>add slave 10.0.0.206:3306;
Empty set (0.00 sec)

db01 [(none)]>select * from backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 10.0.0.100:3306 | up    | rw   |
|           2 | 10.0.0.205:3306 | up    | ro   |
|           3 | 10.0.0.206:3306 | up    | ro   |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)

db01 [(none)]>无论如何,将我们动态修改后的配置,同步到配置文件中,直接使用下面命令即可,非常方便^C
db01 [(none)]>save config;
Empty set (0.00 sec)

用户管理

[root@db01 ~]# mysql -uuser -ppwd -h 10.0.0.204 -P 2345

db01 [(none)]>查看所有配置在配置文件中的用户信息^C
db01 [(none)]>select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| rs       | 3yb5jEku5h4= |
| mha      | O2jBXONX098= |
| root     | 3yb5jEku5h4= |
+----------+--------------+
3 rows in set (0.00 sec)

db01 [(none)]>删除指定用户,直接跟用户名即可^C
db01 [(none)]>remove pwd root;
Empty set (0.00 sec)

db01 [(none)]>select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| rs       | 3yb5jEku5h4= |
| mha      | O2jBXONX098= |
+----------+--------------+
2 rows in set (0.00 sec)

db01 [(none)]>添加用户这里,有两种方式,添加密文密码用户和明文密码用户,我们演示一个添加密文密码的用户^C
db01 [(none)]>add enpwd root:3yb5jEku5h4=;
Empty set (0.00 sec)

db01 [(none)]>select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| rs       | 3yb5jEku5h4= |
| mha      | O2jBXONX098= |
| root     | 3yb5jEku5h4= |
+----------+--------------+
3 rows in set (0.00 sec)

db01 [(none)]>无论如何,将我们动态修改后的配置,同步到配置文件中,直接使用下面命令即可,非常方便^C
db01 [(none)]>save config;
Empty set (0.00 sec)

是不是很简单呀。


that's all,see also:

Oldguo-标杆班级-MySQL-lesson11--读写分离架构-Atlas | Atlas的安装 | MySQL中间件Atlas安装及使用 | Mysql代理中间件Atlas安装和配置

posted @ 2017-11-10 21:23  听雨危楼  阅读(366)  评论(0编辑  收藏  举报