MySQL之读写分离Atlas
程序员文章站
2022-07-10 16:10:19
1 介绍Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。Atlas基于MySQL-Proxy,主要功能有:读写分离从库负载均衡IP过滤自动分表DBA平滑上下线DB node自动摘除宕机的DB2 环境搭建2.1 架构规划主机MySQL角色MHA角色server_idIPdb01Masternode51192.168.159.51db02Slavenode52192.168.1...
1 介绍
Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。Atlas基于MySQL-Proxy,主要功能有:
- 读写分离
- 从库负载均衡
- IP过滤
- 自动分表
- DBA平滑上下线DB node
- 自动摘除宕机的DB
2 环境搭建
2.1 架构规划
主机 | MySQL角色 | MHA角色 | server_id | IP |
---|---|---|---|---|
db01 | Master | node | 51 | 192.168.159.51 |
db02 | Slave | node | 52 | 192.168.159.52 |
db03 | Slave | node manager atlas |
53 | 192.168.159.53 |
2.2 安装软件
官网:https://github.com/Qihoo360/Atlas
[root@db03 data]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
2.3 配置文件准备
cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
vim test.cnf
[mysql-proxy]
# 管理员用户密码
admin-username = user
admin-password = pwd
# 写节点,可以设置为vip以防止主从更替服务不可用
proxy-backend-addresses = 192.168.159.55:3306
# 读节点
proxy-read-only-backend-addresses = 192.168.159.52:3306,192.168.159.53:3306
# 数据库用户密码
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
# 守护进程模式
daemon = true
# 心跳检测模式
keepalive = true
# 并发连接线程数
event-threads = 8
# Atlas审计日志
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
# Atlas对外提供服务的端口
proxy-address = 0.0.0.0:33060
# Atlas对管理员服务的端口
admin-address = 0.0.0.0:2345
# 字符集
charset=utf8
2.4 开启Atlas
[root@db03 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
[root@db03 ~]# ps -ef | grep proxy
root 16801 1 0 22:46 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 16802 16801 1 22:46 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --default
3 功能测试
# 连接Atlas数据服务
mysql -umha -pmha -h 192.168.159.53 -P33060
3.1 读测试
db03 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 52 |
+-------------+
1 row in set (0.00 sec)
db03 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 53 |
+-------------+
1 row in set (0.00 sec)
3.2 写操作
db03 [(none)]>begin;
Query OK, 0 rows affected (0.00 sec)
db03 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 51 |
+-------------+
1 row in set (0.00 sec)
db03 [(none)]>commit;
Query OK, 0 rows affected (0.00 sec)
4 Atlas管理
# 登录Atlas管理服务
mysql -uuser -ppwd -h 192.168.159.53 -P33060
4.1 查看管理帮助
db03 [(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)
- 查看数据库节点状态
db03 [(none)]>select * from backends;
+-------------+---------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+-------+------+
| 1 | 192.168.159.55:3306 | up | rw |
| 2 | 192.168.159.52:3306 | up | ro |
| 3 | 192.168.159.53:3306 | up | ro |
+-------------+---------------------+-------+------+
3 rows in set (0.00 sec)
- 上线、下线节点
-- 下线节点
db03 [(none)]>set offline 3;
+-------------+---------------------+---------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+---------+------+
| 3 | 192.168.159.53:3306 | offline | ro |
+-------------+---------------------+---------+------+
1 row in set (0.00 sec)
db03 [(none)]>select * from backends;
+-------------+---------------------+---------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+---------+------+
| 1 | 192.168.159.55:3306 | up | rw |
| 2 | 192.168.159.52:3306 | up | ro |
| 3 | 192.168.159.53:3306 | offline | ro |
+-------------+---------------------+---------+------+
3 rows in set (0.00 sec)
-- 上线节点
db03 [(none)]>set online 3;
+-------------+---------------------+---------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+---------+------+
| 3 | 192.168.159.53:3306 | unknown | ro |
+-------------+---------------------+---------+------+
1 row in set (0.00 sec)
db03 [(none)]>select * from backends;
+-------------+---------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+-------+------+
| 1 | 192.168.159.55:3306 | up | rw |
| 2 | 192.168.159.52:3306 | up | ro |
| 3 | 192.168.159.53:3306 | up | ro |
+-------------+---------------------+-------+------+
3 rows in set (0.00 sec)
- 添加、删除节点(不影响配置文件)
-- 添加主库节点,一般不用
db03 [(none)]>add master $backend;
-- 删除节点
db03 [(none)]>remove backend 3;
Empty set (0.00 sec)
db03 [(none)]>select * from backends;
+-------------+---------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+-------+------+
| 1 | 192.168.159.55:3306 | up | rw |
| 2 | 192.168.159.52:3306 | up | ro |
+-------------+---------------------+-------+------+
2 rows in set (0.00 sec)
-- 添加从库节点
db03 [(none)]>add slave 192.168.159.53:3306;
Empty set (0.00 sec)
db03 [(none)]>select * from backends;
+-------------+---------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+-------+------+
| 1 | 192.168.159.55:3306 | up | rw |
| 2 | 192.168.159.52:3306 | up | ro |
| 3 | 192.168.159.53:3306 | up | ro |
+-------------+---------------------+-------+------+
3 rows in set (0.00 sec)
- 用户管理
-- 查看用户
db03 [(none)]>select * from pwds;
+----------+--------------+
| username | password |
+----------+--------------+
| repl | 3yb5jEku5h4= |
| mha | O2jBXONX098= |
+----------+--------------+
2 rows in set (0.00 sec)
-- 添加用户
1. 主库添加用户
db01 [(none)]>grant all on *.* to awei@'192.168.159%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
2. Atlas添加用户
-- 明文方式
db03 [(none)]>add pwd awei:123;
Empty set (0.00 sec)
-- 密文方式
db03 [(none)]>add enpwd awei:3yb5jEku5h4=;
Empty set (0.00 sec)
db03 [(none)]>select * from pwds;
+----------+--------------+
| username | password |
+----------+--------------+
| repl | 3yb5jEku5h4= |
| mha | O2jBXONX098= |
| awei | 3yb5jEku5h4= |
+----------+--------------+
3 rows in set (0.00 sec)
-- 删除用户
db03 [(none)]>remove pwd awei;
Empty set (0.00 sec)
db03 [(none)]>select * from pwds;
+----------+--------------+
| username | password |
+----------+--------------+
| repl | 3yb5jEku5h4= |
| mha | O2jBXONX098= |
+----------+--------------+
2 rows in set (0.00 sec)
- 将当前环境保存到配置文件中
db03 [(none)]>save config;
Empty set (0.00 sec)
本文地址:https://blog.csdn.net/weixin_42511320/article/details/107419319
上一篇: 我给狗试毒呢