35. 读写分离-Atlas搭建¶
1. 前提¶
#高可用MHA环境（略）
192.168.178.151
192.168.178.161
192.168.178.162
2. 环境准备¶
IP：192.168.178.152
#关闭防火墙
iptables -F
getenforce
3. 软件下载¶
浏览器访问 https://github.com/Qihoo360/Atlas/releases
只有el6的，没有el7的，下载测试一下。已测试，好用，如下
4. 上传软件包¶
#152
mkdir -p /server/tools
cd /server/tools && ls
[root@152 ~]# mkdir -p /server/tools
[root@152 ~]# cd /server/tools && ls
Atlas-2.2.1.el6.x86_64.rpm
5. 安装atlas¶
cd /server/tools
yum install -y Atlas* #yum localinstall -y Atlas*
[root@152 tools]# yum install -y Atlas-2.2.1.el6.x86_64.rpm
Loaded plugins: fastestmirror
Examining Atlas-2.2.1.el6.x86_64.rpm: Atlas-2.2.1-1.x86_64
Marking Atlas-2.2.1.el6.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package Atlas.x86_64 0:2.2.1-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===========================================================================================================
Package Arch Version Repository Size
===========================================================================================================
Installing:
Atlas x86_64 2.2.1-1 /Atlas-2.2.1.el6.x86_64 15 M
Transaction Summary
===========================================================================================================
Install 1 Package
Total size: 15 M
Installed size: 15 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : Atlas-2.2.1-1.x86_64 1/1
Verifying : Atlas-2.2.1-1.x86_64 1/1
Installed:
Atlas.x86_64 0:2.2.1-1
Complete!
[root@152 tools]#
6. 查看配置文件位置¶
[root@152 tools]# rpm -ql Atlas-2.2.1.el6.x86_64
package Atlas-2.2.1.el6.x86_64 is not installed
[root@152 tools]#
#安装未成功！
7. 上一步查看命令错误，见下文¶
#搜索已安装的包
[root@152 conf]# rpm -qa |grep -i atlas #-i忽略大小写
Atlas-2.2.1-1.x86_64
[root@152 conf]#
#查看安装的包
[root@152 conf]# rpm -qa Atlas
Atlas-2.2.1-1.x86_64
#不知道为啥这样找不到。
[root@152 conf]# rpm -qa Atlas-2.2.1-1.x86_64
[root@152 conf]#
#查看配置文件位置：/usr/local/mysql-proxy/conf/test.cnf
[root@152 conf]# rpm -ql Atlas
/usr/local/mysql-proxy/bin/VERSION
/usr/local/mysql-proxy/bin/encrypt
/usr/local/mysql-proxy/bin/mysql-proxy
/usr/local/mysql-proxy/bin/mysql-proxyd
/usr/local/mysql-proxy/conf/test.cnf
/usr/local/mysql-proxy/lib/libevent-2.0.so.5
/usr/local/mysql-proxy/lib/libglib-2.0.so.0
/usr/local/mysql-proxy/lib/libgmodule-2.0.so.0
/usr/local/mysql-proxy/lib/libgthread-2.0.so.0
/usr/local/mysql-proxy/lib/libjemalloc.so.1
/usr/local/mysql-proxy/lib/liblua-5.1.so
/usr/local/mysql-proxy/lib/libmysql-chassis-glibext.so.0
/usr/local/mysql-proxy/lib/libmysql-chassis-timing.so.0
/usr/local/mysql-proxy/lib/libmysql-chassis.so.0
/usr/local/mysql-proxy/lib/libmysql-proxy.so.0
/usr/local/mysql-proxy/lib/libsql-tokenizer.so.0
/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/chassis.so
/usr/local/mysql-proxy/lib/mysql-proxy/lua/crc32.so
/usr/local/mysql-proxy/lib/mysql-proxy/lua/glib2.so
/usr/local/mysql-proxy/lib/mysql-proxy/lua/lfs.so
/usr/local/mysql-proxy/lib/mysql-proxy/lua/lpeg.so
/usr/local/mysql-proxy/lib/mysql-proxy/lua/mysql.so
/usr/local/mysql-proxy/lib/mysql-proxy/lua/posix.so
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/auth.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/auto-config.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/balance.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/charset.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/commands.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/crc32.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/filter.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/log.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/parser.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/split.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/test.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/ticker.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/tokenizer.lua
/usr/local/mysql-proxy/lib/mysql-proxy/lua/time.so
/usr/local/mysql-proxy/lib/mysql-proxy/plugins/libadmin.so
/usr/local/mysql-proxy/lib/mysql-proxy/plugins/libproxy.so
/usr/local/mysql-proxy/log
[root@152 conf]#
#查看包的信息
[root@152 conf]# rpm -qi Atlas
Name : Atlas
Version : 2.2.1
Release : 1
Architecture: x86_64
Install Date: Thu 29 Apr 2021 02:34:57 PM CST
Group : default
Size : 15376901
License : GPL
Signature : (none)
Source RPM : Atlas-2.2.1-1.src.rpm
Build Date : Wed 17 Dec 2014 02:27:00 PM CST
Build Host : abc
Relocations : /
Packager : chenfei
Vendor : chenfei-xy@360.cn
URL : https://github.com/qihoo360/atlas
Summary : no description given
Description :
no description given
[root@152 conf]#
8. 查看默认配置文件内容¶
[root@152 conf]# pwd
/usr/local/mysql-proxy/conf
[root@152 conf]# cd /usr/local/mysql-proxy/conf
[root@152 conf]# cat test.cnf
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username = user
#管理接口的密码
admin-password = pwd
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 127.0.0.1:3306
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
#proxy-read-only-backend-addresses = 127.0.0.1:3305@1
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = user1:+jKsgB3YAG8=, user2:GS+tr4TPgqc=
#设置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-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
[root@152 conf]#
9. 生成加密密码¶
#通过rpm -ql Atlas查看自带的加密命令 ：/usr/local/mysql-proxy/bin/encrypt
#加密密码
/usr/local/mysql-proxy/bin/encrypt 123
#152
[root@152 conf]# /usr/local/mysql-proxy/bin/encrypt 123
3yb5jEku5h4=
[root@152 conf]#
10. 备份原有配置文件，创建新配置文件¶
#备份
[root@152 conf]# mv test.cnf test.cnf.bak
[root@152 conf]# ls
test.cnf.bak
#创建新的配置文件
vim test.cnf
[mysql-proxy]
admin-username=user
admin-password=pwd
proxy-backend-addresses=192.168.178.150:3306 #VIP地址
proxy-read-only-backend-addresses=192.168.178.161:3306,192.168.178.162:3306 #从库地址
pwds=repl:123,mha:123 #自己创建的管理用户，使用逗号隔开，密码必须加密
daemon=true
keepalive=true
event-threads=8
log-level=message
log-path=/usr/local/mysql-proxy/log #日志目录，通过rpm -ql Atlas查看最后一行
sql-log=ON #记录执行过的sql语句
proxy-address=0.0.0.0:33060 #客户端口，可自定义
admin-address=0.0.0.0:2345 #管理端口，可自定义
charset=utf8
#配置文件中不能带中文注释，否则报错
[mysql-proxy]
admin-username=user
admin-password=pwd
proxy-backend-addresses=192.168.178.150:3306
proxy-read-only-backend-addresses=192.168.178.161:3306,192.168.178.162:3306
pwds=repl:3yb5jEku5h4=,mha:3yb5jEku5h4=
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
11. 启动Atlas¶
#1.通过rpm -ql Atlas查看启动命令位置：/usr/local/mysql-proxy/bin/mysql-proxyd
#152
[root@152 conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
2021-04-29 15:45:37: (critical) can't open log-file '/usr/local/mysql-proxy/log #日志目录，通过rpm -ql Atlas查看最后一行/test.log': No such file or directory
2021-04-29 15:45:37: (message) Initiating shutdown, requested from mysql-proxy-cli.c:415
2021-04-29 15:45:37: (message) shutting down normally, exit code is: 1
error: failed to start MySQL-Proxy of test
[root@152 conf]#
#看样子配置文件不能带中文注释，重新修改配置文件，然后启动测试，成功！
[root@152 conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
[root@152 conf]#
12. 查看进程¶
ps -ef|grep proxy
[root@152 conf]# ps -ef|grep proxy
root 49556 1 0 15:51 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 49557 49556 0 15:51 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 50014 7068 0 15:52 pts/0 00:00:00 grep --color=auto proxy
[root@152 conf]#
13. 主库创建生产用户¶
#161
grant all on *.* to root@'192.168.178.%' identified by '123';
grant select,update,insert on oldboy.* to oldboy@'192.168.178.%' identified by '123';
flush privileges;
commit;#这一步要不要加？（验证一下从库是否出现）已验证，从库出现用户，可以不加
[root@161 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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 user,host from mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| mha | 192.168.178.% |
| repl | 192.168.178.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
5 rows in set (0.00 sec)
mysql>
mysql> grant all on *.* to root@'192.168.178.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant select,update,insert on oldboy.* to oldboy@'192.168.178.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select user,host from mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| mha | 192.168.178.% |
| oldboy | 192.168.178.% |
| repl | 192.168.178.% |
| root | 192.168.178.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
7 rows in set (0.00 sec)
mysql>
#162,查看从库
[root@162 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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 user,host from mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| mha | 192.168.178.% |
| oldboy | 192.168.178.% |
| repl | 192.168.178.% |
| root | 192.168.178.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
7 rows in set (0.00 sec)
mysql>
14. 修改Atlas配置文件，加入生成用户¶
#152
vim /usr/local/mysql-proxy/conf/test.conf
[mysql-proxy]
admin-username=user
admin-password=pwd
proxy-backend-addresses=192.168.178.150:3306
proxy-read-only-backend-addresses=192.168.178.161:3306,192.168.178.162:3306
pwds=root:3yb5jEku5h4=,oldboy:3yb5jEku5h4=
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
15. 重启Atlas¶
#152
[root@152 conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
OK: MySQL-Proxy of test is stopped
OK: MySQL-Proxy of test is started
[root@152 conf]#
[root@152 conf]# ps -ef|grep proxy
root 52534 1 0 16:11 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 52535 52534 0 16:11 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 52588 7068 0 16:11 pts/0 00:00:00 grep --color=auto proxy
[root@152 conf]#
16. 连接Atlas客户端¶
#151/161/162
mysql -uroot -p123 -h192.168.178.152 -P33060
#151
[root@151 ~]# mysql -uroot -p123 -h192.168.178.152 -P33060
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.81-log Source distribution
Copyright (c) 2000, 2017, 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>
mysql>
#161
[root@161 ~]# mysql -uroot -p123 -h192.168.178.152 -P33060
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 2
Server version: 5.0.81-log Source distribution
Copyright (c) 2000, 2017, 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>
#162
[root@162 ~]# mysql -uroot -p123 -h192.168.178.152 -P33060
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 3
Server version: 5.0.81-log Source distribution
Copyright (c) 2000, 2017, 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>
17. 读测试¶
#151
mysql -uroot -p123 -h192.168.178.152 -P33060
show variables like 'server_id';
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 62 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 62 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.00 sec)
18. 写测试¶
#查看俩从库是否为只读，不是只读则设置为只读：set global read_only=1;
#151
[root@151 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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 variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
#162
[root@162 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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>
mysql>
mysql>
mysql>
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.01 sec)
#测试写状态（通过开启事务来测试）
#151
[root@151 ~]# mysql -uroot -p123 -h192.168.178.152 -P33060
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 4
Server version: 5.0.81-log Source distribution
Copyright (c) 2000, 2017, 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> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 62 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
19. 总结¶
#外部应用可以访问如下IP及端口来访问MHA高可用架构数据库，并且实现了读写分离！
mysql -uroot -p123 -h192.168.178.152 -P33060
最后更新:
2022-02-20 08:44:07