Mysql传统主从+Atlas读写分离实验（vsphere环境）
IP架构
主库：Master01 172.17.100.101
从库：Slave01 172.17.100.103
Atlas：172.17.100.109
Mysql采用二进制安装
参考：Mysql二进制安装脚本
配置主库防火墙（从库需要读取主库的binlog）
#这里的103和104（预留）都是从库，109是Atlas中间件
[root@Master01 ~]# iptables -I INPUT -s 172.17.100.103 -j ACCEPT
[root@Master01 ~]# iptables -I INPUT -s 172.17.100.104 -j ACCEPT
[root@Master01 ~]# iptables -I INPUT -s 172.17.100.109 -j ACCEPT
默认开启binlog，从库不做级联的情况下最好先关闭binlog
service mysqld stop
vim /etc/my.cnf
注释掉log_bin和binlog_format（如下）
#log_bin=/usr/local/mysql/log/binlog
#binlog_format=row
分别配置主从的server_id
主库server_id=101
从库server_id=103
在主从库均给用户rep授予replication slave和replication client的权限
mysql> grant replication slave,replication client on *.* to rep@'172.17.100.%' identified by 'beacon';
主库Master01
执行mysqldump将全库导出
flush tables with read lock;
show master status \G
---------------------------------------
mysql> show master status \G
*************************** 1. row ***************************
File: binlog.000004
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
从库Slave01执行
mysql> change master to
-> master_host='172.17.100.101',
-> master_user='rep',
-> master_password='beacon',
-> master_log_file='binlog.000004',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.18 sec)
从库Slave01执行
start slave；
主库Master01执行
unlock tables;
从库Slave01通过show slave status \G查看是否成功
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
双yes代表IO线程和SQL线程均已启动成功，主从配置成功
配置Atlas
[root@Atlas01 conf]# pwd
/usr/local/mysql-proxy/conf #配置的路径
[root@Atlas01 conf]# ll
total 8
-rw-r--r--. 1 root root 2856 Apr 25 10:33 atlas01.cnf
需要修改的参数
#通过2345端口连接到管理接口的时候采用这里的账号和密码
admin-username = root #管理接口的用户名
admin-password = beacon #管理接口的密码
#主从库的IP以及端口
proxy-backend-addresses = 172.17.100.101:3306 #主库
proxy-read-only-backend-addresses = 172.17.100.103:3306 #从库
#在/usr/local/mysql-proxy/bin路径下执行./encrypt beacon得到的结果就是Oi4W0/3yjGQ=
pwds = rep:Oi4W0/3yjGQ=,
#下面3个参数没有进行更改
daemon = true
keepalive = true
event-threads = 8
#日志的路径和级别，日志级别有5个选项，分为message、warning、critical、error、debug
#日志路径需要进行配置，否则在启动mysql-proxyd的时候会因为找不到路径而报错
log-level = debug
log-path =/usr/local/mysql-proxy/log
#实例名，这里的instance名字需要与配置的cnf文件名（atlas01.cnf）以及启动时的instance名（mysql-proxyd atlas01 start）一致
instance = atlas01
#1234是监听端口，2345是管理端口，需要在防火墙里开放这2个端口
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
#字符默认是latin，我这里并没有打开utf8，剩下的3个#开头的配置亦没有进行更改
#charset = utf8
#tables = person.mt.id.3
#client-ips = 127.0.0.1, 192.168.1
#lvs-ips = 192.168.1.1
启动Atlas
[root@Atlas01 bin]# ./mysql-proxy atlas01 start
2018-04-25 10:06:25: (critical) chassis-frontend.c:122: Failed to get log directory, please set by --log-path
2018-04-25 10:06:25: (message) Initiating shutdown, requested from mysql-proxy-cli.c:381
2018-04-25 10:06:25: (message) shutting down normally, exit code is: 1
从报错来看，需要配置日志路径
检查配置，发现已经配置了日志路径，错误原因是启动时选错了，应该使用mysql-proxyd启动（而不是mysql-proxy）
重新执行启动
[root@Atlas01 mysql-proxy]# bin/mysql-proxyd atlas01 start
OK: MySQL-Proxy of atlas01 is started
[root@Atlas01 mysql-proxy]# bin/mysql-proxyd atlas01 status
MySQL-Proxy of atlas01 is running (2950)
MySQL-Proxy of atlas01 is running (2951)
通过ps -ef可以看到对应进程 #这2个进程应该有一个是监控，另一个是管理
[root@Atlas01 mysql-proxy]# ps -ef|grep mysql-proxy
root 2950 1 0 10:33 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/atlas01.cnf
root 2951 2950 0 10:33 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/atlas01.cnf
查看日志，已经生成
[root@Atlas01 log]# pwd
/usr/local/mysql-proxy/log
[root@Atlas01 log]# ll
total 8
-rw-r-----. 1 root root 686 Apr 25 10:33 atlas01.log
-rw-------. 1 root root 4 Apr 25 10:33 atlas01.pid
-rw-rw-rw-. 1 root root 0 Apr 25 10:33 sql_atlas01.log
验证读写分离
连接监控端
[root@Atlas01 ~]# mysql -h 172.17.100.109 -P 1234 -urep -pbeacon
登陆后可以看到2个内建的库，test和information
连接管理端
[root@Atlas01 ~]# mysql -h 172.17.100.109 -P 2345 -uroot -pbeacon
登陆后不能采用数据库的常规操作
管理端采用语句select * from help;可以看到常用的管理类语句，管理端无法通过navicat等客户端进行连接，直接在ssh框里对库进行管理操作
mysql> 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)
通过log路径下的atlas01.log可以看到atlas参与的所有操作的记录（之前配置为debug，信息实在太少，后来我又把他改成message了）
[root@Atlas01 log]# tail -200f atlas01.log
2018-04-25 10:33:45: (message) chassis-unix-daemon.c:138: [angel] we try to keep PID=2951 alive
2018-04-25 10:33:45: (debug) chassis-unix-daemon.c:159: waiting for 2951
2018-04-25 10:33:45: (debug) chassis-unix-daemon.c:123: we are the child: 2951
2018-04-25 10:33:45: (message) mysql-proxy 0.8.2 started - instance: atlas01
2018-04-25 10:33:45: (debug) max open file-descriptors = 1024
2018-04-25 10:33:45: (message) proxy listening on port 0.0.0.0:1234
2018-04-25 10:33:45: (message) added read/write backend: 172.17.100.101:3306 #主库信息，读/写
2018-04-25 10:33:45: (message) added read-only backend: 172.17.100.103:3306 #从库信息，只读
2018-04-25 10:33:45: (message) chassis-event-thread.c:235: starting 8 threads
2018-04-25 14:13:21: (message) chassis-unix-daemon.c:138: [angel] we try to keep PID=3164 alive
2018-04-25 14:13:21: (message) mysql-proxy 0.8.2 started - instance: atlas01
2018-04-25 14:13:21: (message) proxy listening on port 0.0.0.0:1234
2018-04-25 14:13:21: (message) added read/write backend: 172.17.100.101:3306
2018-04-25 14:13:21: (message) added read-only backend: 172.17.100.103:3306
2018-04-25 14:13:21: (message) chassis-event-thread.c:235: starting 8 threads
