Linux
Nginx,MySQL,PHP/Python

MySQL-读写分离Atlas

Atlas是由360公司web平台基础架构团队开发维护的一个基于MySQL协议的数据中间层项目,它是mysql-proxy0.8.2版本的基础上,对其进行了优化,增加了一些新的功能特性
1.只能安装在64位系统上
2.后端mysql版本应大于5.1,建议使用mysql5.6以上

配置

检查MHA状态
[root@m01 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:5494) is running(0:PING_OK), master:10.0.0.52
我的主是db02,server_id=7
从:db01,server_id=6,db03,server_id=8

上传rpm包并解压
[root@m01 ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

书写配置文件
[root@m01 ~]# vim /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306         写节点,MHA的vip
proxy-read-only-backend-addresses = 10.0.0.51:3306,10.0.0.53:3306   读节点
pwds = aaaa: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=utf8force-gtid-consistency=true

启动
[root@m01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start

测试
[root@m01 ~]# mysql -umha -pmha -h 10.0.0.61 -P 33060

读操作
m01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.01 sec)
m01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
1 row in set (0.00 sec)
m01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)
m01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
1 row in set (0.01 sec)
读操作在6和8之间切换

写操作
m01 [(none)]>begin;select @@server_id;commit;
Query OK, 0 rows affected (0.01 sec)
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
m01 [(none)]>begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
写操作只在7上,主库

添加用户

在主库添加用户
grant all on *.* to root@'10.0.0.%' identified by '123';

创建密码加入Atlas配置文件
[root@m01 ~]# /usr/local/mysql-proxy/bin/encrypt 123
3yb5jEku5h4=                              给密码123加密,Atlas自带工具
[root@m01 ~]# vim /usr/local/mysql-proxy/conf/test.cnf
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,root:3yb5jEku5h4=

重启
[root@m01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart

管理

登录
[root@m01 ~]# mysql -uuser -ppwd -h127.0.0.1 -P2345

查看后端所有节点
m01 [(none)]>SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.51:3306 | up    | ro   |
|           3 | 10.0.0.53:3306 | up    | ro   |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)

删除从节点
m01 [(none)]>REMOVE BACKEND  3;
Empty set (0.00 sec)
m01 [(none)]>SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.51:3306 | up    | ro   |
+-------------+----------------+-------+------+
2 rows in set (0.00 sec)

添加节点
m01 [(none)]>ADD SLAVE 10.0.0.53:3306;
Empty set (0.00 sec)
m01 [(none)]>SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.51:3306 | up    | ro   |
|           3 | 10.0.0.53:3306 | up    | ro   |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)

查看密码
m01 [(none)]>SELECT * FROM pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| aaaa     | 3yb5jEku5h4= |
| mha      | O2jBXONX098= |
+----------+--------------+
2 rows in set (0.00 sec)

删除密码
m01 [(none)]>REMOVE PWD aaaa;
Empty set (0.00 sec)
m01 [(none)]>SELECT * FROM pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| mha      | O2jBXONX098= |
+----------+--------------+
1 row in set (0.00 sec)

添加密码
m01 [(none)]>ADD ENPWD aaaa:3yb5jEku5h4=;
Empty set (0.00 sec)
m01 [(none)]>SELECT * FROM pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| mha      | O2jBXONX098= |
| aaaa     | 3yb5jEku5h4= |
+----------+--------------+
2 rows in set (0.00 sec)
赞(3)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

LNMP社群 不仅仅是技术

关于我们联系我们