MySQL-Atlas实现读写分离

下载地址： https://github.com/Qihoo360/Atlas/releases

1.介绍

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

2.安装配置

yum install -y Atlas*
cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
vim test.cnf
	[mysql-proxy]
	admin-username = user
	admin-password = pwd
	proxy-backend-addresses = 10.0.0.55:3306
	proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53: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

3.启动atlas

/usr/local/mysql-proxy/bin/mysql-proxyd test start
#查看
ps -ef |grep proxy

4.Atlas功能测试

测试读操作：
	mysql -umha -pmha  -h 10.0.0.53 -P 33060 
	db03 [(none)]>select @@server_id;
测试写操作：
	mysql> begin;select @@server_id;commit;

注意:

	DDL建议不要再Atlas触发，最好是到主库触发（Online DDL或者PT-OSC）。
	DML建议begin; DML;  commit;

4.Atlas 的管理操作

[root@db03 conf]# mysql -uuser -ppwd -h 10.0.0.53 -P2345
db03 [(none)]>select * from help;

4.1 查看所有节点

db03 [(none)]>SELECT * FROM backends;
	+-------------+----------------+-------+------+
	| backend_ndx | address        | state | type |
	+-------------+----------------+-------+------+
	|           1 | 10.0.0.55:3306 | up    | rw   |
	|           2 | 10.0.0.52:3306 | up    | ro   |
	|           3 | 10.0.0.53:3306 | up    | ro   |
	+-------------+----------------+-------+------+
	3 rows in set (0.00 sec)

4.2 节点的上线和下线

db03 [(none)]>SET OFFLINE 1;
	+-------------+----------------+---------+------+
	| backend_ndx | address        | state   | type |
	+-------------+----------------+---------+------+
	|           1 | 10.0.0.55:3306 | offline | rw   |
	+-------------+----------------+---------+------+
	1 row in set (0.01 sec)
db03 [(none)]>SELECT * FROM backends;
	+-------------+----------------+---------+------+
	| backend_ndx | address        | state   | type |
	+-------------+----------------+---------+------+
	|           1 | 10.0.0.55:3306 | offline | rw   |
	|           2 | 10.0.0.52:3306 | up      | ro   |
	|           3 | 10.0.0.53:3306 | up      | ro   |
	+-------------+----------------+---------+------+
db03 [(none)]>SET ONLINE 1;
	+-------------+----------------+---------+------+
	| backend_ndx | address        | state   | type |
	+-------------+----------------+---------+------+
	|           1 | 10.0.0.55:3306 | unknown | rw   |
	+-------------+----------------+---------+------+

4.3 删除和添加节点

db03 [(none)]>REMOVE BACKEND  3;
db03 [(none)]>ADD SLAVE  10.0.0.53:3306;

4.4 用户管理

db01 [(none)]>grant all on *.* to yang@'10.0.0.%' identified by '123';
db03 [(none)]>SELECT * FROM pwds;
db03 [(none)]>add pwd yang:123;

4.5 持久化配置文件

db03 [(none)]>save config;