在MHA基础上使用atlas实现读写分离

1.下载安装atlas

https://github.com/Qihoo360/Atlas/releases
yum install -y Atlas*

2.安装配置

cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
 vi test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306     #漂移IP   写节点
proxy-read-only-backend-addresses = 10.0.0.53:3306,10.0.0.54:3306   #从节点 读节点
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=       #repl密码：123 mha密码：mha  后端数据库用户和密码
daemon = true         #后台运行
keepalive = true        #心跳检测
event-threads = 8     #并发数量16核设置为8，8核设置为4，一般是半数
log-level = message    
log-path = /usr/local/mysql-proxy/log   #日志目录
sql-log=ON     #记录sql日志，所有经过atlas的sql语句都会记录在/usr/local/mysql-proxy/log目录下
proxy-address = 0.0.0.0:33060    #对外提供服务的端口
admin-address = 0.0.0.0:2345     #管理员操作atlas连的端口
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.54 -P 33060 
db03 [(none)]>select @@server_id;

测试写操作：

mysql> begin;select @@server_id;commit;

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

5. Atlas 的管理操作

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

5.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)

5.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   |
+-------------+----------------+---------+------+

5.3 删除和添加节点

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

5.4 用户管理

db01 [(none)]>grant all on *.* to oldguo@'10.0.0.%' identified by '123';（主从复制主库）
db03 [(none)]>SELECT * FROM pwds; （atlas管理用户登录）
db03 [(none)]>add pwd oldguo:123;（atlas管理用户登录）

5.5 持久化配置文件

db03 [(none)]>save config;

主库宕机修复

1添加配置文件缺失的节点信息
[root@db003 ~]# cat /etc/mha/app1.cnf

2 启动宕机的原主库

CHANGE MASTER TO 
MASTER_HOST='10.0.0.53',
 MASTER_PORT=3306,
 MASTER_AUTO_POSITION=1,
 MASTER_USER='repl', 
 MASTER_PASSWORD='123';

start slave；

3 检查vip有没有在现主库上
4状态检查

masterha_check_ssh   --conf=/etc/mha/app1.cnf 
masterha_check_repl  --conf=/etc/mha/app1.cnf 

5启动高可用

开启MHA(db03)：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

6 查看atlas节点状态

[root@db003 /usr/local/bin]# mysql -uuser -ppwd -h 10.0.0.54 -P2345
3306 [(none)]>SELECT * FROM backends;

7读写节点测试

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

测试写操作：
mysql> begin;select @@server_id;commit;