Atlas实现MySQL数据库的读写分离

在实现了mysql的主从配置后，博主这里准备实现一下mysql的读写分离
这里我选择的是360团队的Atlas作为代理当然还可以使用mycat，one proxy，proxySQL等等开源的中间件，有兴趣的可以自己尝试一下
一、准备工作：三台服务器
① 主库服务器：192.168.64.136
② 从库服务器：192.168.64.138
③ 中间件Atlas服务器：192.168.64.137 （该服务器必须有可以远程连接 主从库的账户 主从新建账号密码可以保持一致）

MySQL主从已经配置好之后，主从对Atlas进行授权
mysql> grant all on . to atlas@‘192.168.153.130‘ identified by ‘123456‘;
博主所做的所以操作都是在虚拟机上测试的 系统版本：centos 7

二、下载安装包方式：
可以到360 github Atlas主页下载 https://github.com/Qihoo360/Atlas/releases/
wget https://github.com/Qihoo360/Atlas/releases/download/2.0.3/Atlas-2.0.3.el5.x86_64.rpm
三、完成以后安装
以后安装

# rpm -ivh Atlas-2.0.3.el5.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:Atlas-2.0.3-1                    ################################# [100%]

安装完成以后，会默认在/usr/local/mysql-prox,生成4个文件夹：

[root@test ~]# ll /usr/local/mysql-proxy/
total 4
drwxr-xr-x. 2 root root   75 May 28 14:45 bin  #可执行的文件
drwxr-xr-x. 2 root root   22 May 28 14:45 conf #配置文件
drwxr-xr-x. 3 root root 4096 May 28 14:45 lib  #依赖包
drwxr-xr-x. 2 root root    6 Dec 17  2014 log  #存放日志 
[root@test ~]#

四、生成密码并打开配置文件进行修改
密码的生成：

[root@localhost bin]# ./encrypt 12345678
A5/cqkRqleYa8s/oWZlMvQ==

命令行打开：vim /usr/local/mysql-proxy/conf/test.cnf

[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名        不需要修改
admin-username = user
#管理接口的密码
admin-password = pwd
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.64.136:3306  #主库IP以及端口 这里已经换成本机的主库信息
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
#proxy-read-only-backend-addresses = 192.168.64.129:3306@1  #原配置文件自带 未修改
proxy-read-only-backend-addresses = 192.168.64.138:3306@1   #从库的IP和端口
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = altas:A5/cqkRqleYa8s/oWZlMvQ== #此密码已经是我生成好替换了的
#设置Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
daemon = true
#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
keepalive = true
#工作线程数，对Atlas的性能有很大影响，可根据情况适当设置
event-threads = 8
#日志级别，分为message、warning、critical、error、debug五个级别
log-level = message
#日志存放的路径
log-path = /usr/local/mysql-proxy/log
#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF
#sql-log = OFF
sql-log = REALTIME  #此配置修改是为了后面测试读写分离的日志记录   生产环境请关闭
#慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。
#sql-log-slow = 10
#实例名称，用于同一台机器上多个Atlas实例间的区分
#instance = test
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234
#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345
#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项
#tables = person.mt.id.3
#默认字符集，设置该项后客户端不再需要执行SET NAMES语句
#charset = utf8
#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1
#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
#lvs-ips = 192.168.1.1

vim 编辑完成以后需要重新启动： ./mysql-proxyd test restart 使配置生效

注意：
运行文件是：mysql-proxyd(不是mysql-proxy）
test是conf目录下配置文件的名字，也是配置文件里instance项的值，二者要统一

[root@test bin]# ./mysql-proxyd test restart
OK: MySQL-Proxy of test is stopped
OK: MySQL-Proxy of test is started

具体启动地址：cd /usr/local/mysql-proxy/bin

查看Atlas是否启动

[root@test mysql-proxy]# ps -ef | grep mysql-proxy
root       1791      1  0 11:49 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root       1792   1791  0 11:49 ?        00:00:03 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf

五、使用atlas连接接进入数据库
1.进入Atlas的管理模式：‘mysql -h127.0.0.1 -P2345 -uuser -ppwd’
127.0.0.1写成192.168.64.137也行,因为mysql和atlas安装在同一台虚拟机上所以可以使用127.0.0.1
连接成功：

[root@test mysql-proxy]# mysql -h127.0.0.1 -P2345 -uuser -ppwd #终端命令
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看mysql管理员模式容许操作： select * from help;

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", ...                        |
+----------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

查看主从库的读写情况： select * from backends;

mysql>  select * from backends;
+-------------+----------------------+-------+------+
| backend_ndx | address              | state | type |
+-------------+----------------------+-------+------+
|           1 | 192.168.153.128:3306 | up    | rw   |
|           2 | 192.168.153.129:3306 | up    | ro   |
+-------------+----------------------+-------+------+
2 rows in set (0.01 sec)

使用工作模式进入：mysql -h127.0.0.1 -P1234 -uatlas -p12345678

[root@test mysql-proxy]# mysql -h127.0.0.1 -P1234 -uroot1 -proot123 #终端命令
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.81-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

六、读写分离测试

mysql> show databases; #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test; #使用数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小猪   |
|  2 | 小红   |
|  3 | 小夏   |
|  4 | 小猪   |
+----+--------+
4 rows in set (0.00 sec)

mysql> insert into user(name) values('狗蛋');
Query OK, 1 row affected (0.03 sec)

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 夏杰   |
|  2 | 小红   |
|  3 | 小夏   |
|  4 | 小猪   |
|  5 | 狗蛋   |
+----+--------+
5 rows in set (0.00 sec)
mysql> update user set name="问问" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 问问   |
|  2 | 小红   |
|  3 | 小夏   |
|  4 | 小猪   |
|  5 | 狗蛋   |
+----+--------+
5 rows in set (0.00 sec)

查看数据日志，看看到底是否读写分离了，增删改访问主库，查询访问从库：

[root@localhost log]# ls
sql_test.log  test.log  test.pid
[root@localhost log]# cat sql_test.log 
[08/17/2018 17:23:45] C:127.0.0.1 S:192.168.64.138 OK 79.013 "select @@version_comment limit 1"
[08/17/2018 17:23:58] C:127.0.0.1 S:192.168.64.138 OK 0.572 "SELECT DATABASE()"
[08/17/2018 17:23:58] C:127.0.0.1 S:192.168.64.138 OK 37.698 "show databases"
[08/17/2018 17:23:58] C:127.0.0.1 S:192.168.64.138 OK 0.682 "show tables"
[08/17/2018 17:24:06] C:127.0.0.1 S:192.168.64.138 OK 43.254 "select * from user"
[08/17/2018 17:25:31] C:127.0.0.1 S:192.168.64.138 OK 2.561 "select @@version_comment limit 1"
[08/17/2018 17:25:38] C:127.0.0.1 S:192.168.64.138 OK 0.910 "SELECT DATABASE()"
[08/17/2018 17:25:38] C:127.0.0.1 S:192.168.64.138 OK 2.643 "show databases"
[08/17/2018 17:25:38] C:127.0.0.1 S:192.168.64.138 OK 1.784 "show tables"
[08/17/2018 17:25:45] C:127.0.0.1 S:192.168.64.138 OK 1.279 "select * from user"
[08/17/2018 17:26:12] C:127.0.0.1 S:192.168.64.136 ERR 2.724 "update user name='夏杰' where id=1"
[08/17/2018 17:26:20] C:127.0.0.1 S:192.168.64.136 OK 42.266 "update user set name='夏杰' where id=1"
[08/17/2018 18:12:41] C:127.0.0.1 S:192.168.64.138 OK 42.129 "select @@version_comment limit 1"
[08/17/2018 18:12:45] C:127.0.0.1 S:192.168.64.138 OK 0.571 "SELECT DATABASE()"
[08/17/2018 18:12:45] C:127.0.0.1 S:192.168.64.138 OK 3.731 "show databases"
[08/17/2018 18:12:45] C:127.0.0.1 S:192.168.64.138 OK 1.911 "show tables"
[08/17/2018 18:13:21] C:127.0.0.1 S:192.168.64.136 OK 33.236 "insert into user(name) values('狗蛋')"
[08/17/2018 18:13:26] C:127.0.0.1 S:192.168.64.136 OK 1.011 "select * from user"
[08/17/2018 18:14:42] C:127.0.0.1 S:192.168.64.136 OK 49.867 "update user set name="夏杰" where id=1"
[08/17/2018 18:14:45] C:127.0.0.1 S:192.168.64.138 OK 1.129 "select * from user"
[08/17/2018 18:14:53] C:127.0.0.1 S:192.168.64.136 OK 2.959 "update user set name="问问" where id=1"
[08/17/2018 18:14:54] C:127.0.0.1 S:192.168.64.138 OK 0.948 "select * from user"

由上可以看到查询访问的是192.168.64.138从库，插入更新访问的是192.168.64.136主库，读写分离实现成功