当前位置：移动技术网 > IT编程>数据库>Mysql > MySQL之读写分离Atlas

MySQL之读写分离Atlas

2020年07月19日 | 移动技术网IT编程 | 我要评论

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

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

阿里云数据库迁移遇到的问题总结

注：本文不深究任一知识点，仅限于记录阿里云迁移数据库遇到的问题及解决办法。原博客服务器：阿里云ECS+Ubunt... [阅读全文]
mysql练习题 -- 第一天

表信息dept 部门表字段DEPTNO 部门编号DNAME 部门名称LOC 位置emp 员工表字段EMPN... [阅读全文]
mysql 存储过程

DELIMITER $$CREATE PROCEDURE build_school_list ()BEGIN D... [阅读全文]
数据库优化-索引的创建-MySQL-index-SQL优化-避免全表扫描

定义索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和... [阅读全文]
Mysql 8.0新特性（1）

Mysql 8.0新特性MySQL 8.0中添加的功能数据字典原子数据定义语句（Atomic DDL）升级程序安... [阅读全文]
[数据库]--DML、DDL、TCL语言

DML语言（数据操作语言）1.插入语句insert语法1：insert into 表名(列名...) value... [阅读全文]
MySql支持Emoji表情(原创非转载)

节省大家时间先上解决方案！解决方案：将Mysql的编码从utf8转换成utf8mb41.修改mysql配置文件（... [阅读全文]
MySQL索引使用及优化

#3. 索引使用策略及优化##3.1 索引的好处##3.2 什么情况下可以用到B树索引(1) 定义有主键的列一定... [阅读全文]
mysql 实现n小时/n分钟为分组

SELECTme.value_,me.times,DATE_FORMAT(concat(date(me.time... [阅读全文]
mysql 获取数据库表所有字段，GROUP_CONCAT()拼接字段缺失问题解决

mysql 获取数据库表所有字段，并用GROUP_CONCAT()拼接一、查询数据库表所有表字段(字段过多展示拼... [阅读全文]

网友评论


验证码：

MySQL之读写分离Atlas

2020年07月19日 | 移动技术网IT编程 | 我要评论

1 介绍

2 环境搭建

2.1 架构规划

2.2 安装软件

2.3 配置文件准备

2.4 开启Atlas

3 功能测试

3.1 读测试

3.2 写操作

4 Atlas管理

4.1 查看管理帮助

您可能感兴趣的文章:

相关文章:

网友评论