Atlas官方链接: https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
Atlas下载链接: https://github.com/Qihoo360/Atlas/releases
二、Atlas应用环境
Atlas是一个位于前端应用与后端MySQL数据库之间的中间件,它使得应用程序员无需再关心读写分离、分表等与MySQL相关的细节,可以专注于编写业务逻辑,同时使得DBA的运维工作对前端应用透明,上下线DB前端应用无感知。
Atlas在后端DB看来,Atlas相当于连接它的客户端,在前端应用看来,Atlas相当于一个DB。
Atlas作为服务端与应用程序通讯,它实现了MySQL的客户端和服务端协议,同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节,同时为了降低MySQL负担,它还维护了连接池。
三、Atlas的部署
1、实验环境
Altas 10.0.0.104altas
MHA10.0.0.105mha manager node
db0110.0.0.101 mysql-master+mha node
db0210.0.0.102 mysql-slave1+mha node
db0310.0.0.103 mysql-slave2+mha node
System OS: CentOS Linux release7.6.1810(Core)
Mysql version: mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 二进制部署
Mysql安装部署目录:
程序目录:/app/mysql
数据目录:/data/mysql/data
binlog目录:/data/mysql/binlog/mysql-bin
mysql server_id分配
db01: server_id=101db02: server_id=102db03: server_id=103
2、Altas部署注意事宜
1)、Atlas只能安装运行在64位的系统上2)、Centos 5.X安装 Atlas-XX.el5.x86_64.rpm,Centos 6.X安装Atlas-XX.el6.x86_64.rpm。3)、后端mysql版本应大于5.1,建议使用Mysql 5.6以上
3、mysql5.7二进制部署
部署节点db01,db02,db03。
my.cnf配置文件
db01
[mysqld]
basedir=/app/mysql/datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=101port=3306secure-file-priv=/tmp
autocommit=0log_bin=/data/mysql/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1[mysql]
prompt=db01 [\d]>
db02
[mysqld]
basedir=/app/mysql/datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=102port=3306secure-file-priv=/tmp
autocommit=0log_bin=/data/mysql/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1[mysql]
prompt=db02 [\d]>
db03
[mysqld]
basedir=/app/mysql/datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=103port=3306secure-file-priv=/tmp
autocommit=0log_bin=/data/mysql/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=truelog-slave-updates=1[mysql]
prompt=db03 [\d]>
4、部署mysql的GTID主从
部署节点db01 master; db02 slave1;db03 slave3
#db02
[root@db02 app]# mysql-uroot -p -e 'show slave status\G;'|grep -i yes
Enter password:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#db03
[root@db03 app]# mysql-uroot -p -e 'show slave status\G;'|grep -i yes
Enter password:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5、部署MHA
部署节点:mha,db01,db02,db03
6、部署Atlas
部署节点:altas
1、下载安装altas
mkdir /app
cd/appwget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
2、altas目录
[root@atlas app]# cd /usr/local/mysql-proxy/[root@atlas mysql-proxy]# ll
total0drwxr-xr-x 2 root root 75 Dec 12 14:54bin
drwxr-xr-x 2 root root 22 Dec 12 14:54conf
drwxr-xr-x 3 root root 331 Dec 12 14:54lib
drwxr-xr-x 2 root root 6 Dec 17 2014 log
3、生成主从复制用户repl及mha用户的加密密码
altas配置文佳中,需要主从关系的repl的密码和MHA的mha的密码,且密码为加密密钥。
该实验主从的用户和密码:repl:123mha的用户和密码:mha:mha
主从的密码加密
[root@atlas mysql-proxy]# /usr/local/mysql-proxy/bin/encrypt 1233yb5jEku5h4=主从的复制用户:repl
加密密码:3yb5jEku5h4=
mha的密码加密
[root@atlas mysql-proxy]# /usr/local/mysql-proxy/bin/encrypt mha
O2jBXONX098=mha的用户:mha
加密密码:O2jBXONX098=
4、修改altas的配置文件
#1、备份源配置文件
[root@atlas mysql-proxy]# cd /usr/local/mysql-proxy/conf/[root@atlas conf]#cptest.cnf test.cnf.bak
#2、修改配置文件
[root@mysql-db01 /]# cat >> /usr/local/mysql-proxy/conf/test.cnf <
[mysql-proxy]
admin-username =user
admin-password = pwdproxy-backend-addresses = 10.0.0.101:3306proxy-read-only-backend-addresses = 10.0.0.102:3306,10.0.0.103:3306pwds= repl:3yb5jEku5h4=, mha:O2jBXONX098=daemon= truekeepalive= trueevent-threads = 8log-level =message
log-path = /usr/local/mysql-proxy/log
sql-log =ON
proxy-address = 0.0.0.0:3306admin-address = 0.0.0.0:2345charset=utf8
EOF
5、altas配置文件说明
[mysql-proxy]
#(必备,默认值即可)管理接口的用户名
admin-username =user
#(必备,默认值即可)管理接口的密码
admin-password = pwd#(必备,根据实际情况配置)主库的IP和端口(可vip)
proxy-backend-addresses = 192.168.0.12:3306#(非必备,根据实际情况配置)从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔。如果想让主库也能分担读请求的话,只需要将主库信息加入到下面的配置项中
proxy-read-only-backend-addresses = 192.168.0.13:3306,192.168.0.14:3306#(必备,根据实际情况配置)用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,用户名与密码之间用冒号分隔。主从数据库上需要先创建该用户并设置密码(用户名和密码在主从数据库上要一致)。比如用户名为myuser,密码为mypwd,执行./encrypt mypwd结果为HJBoxfRsjeI=。如果有多个用户用逗号分隔即可。则设置如下行所示:
pwds= myuser: HJBoxfRsjeI=,myuser2:HJBoxfRsjeI=#(必备,默认值即可)Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true
daemon= true#(必备,默认值即可)设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true
keepalive= true#(必备,根据实际情况配置)工作线程数,推荐设置成系统的CPU核数
# 对性能和正常运行起到重要作用
event-threads = 4#(必备,默认值即可)日志级别,分为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会主动关闭经过'wait-timeout'时间后一直未活跃的连接。单位:秒wait-timeout = 10#(必备,默认值即可)Atlas监听的工作接口IP和端口;代表客户端应该使用1234这个端口连接Atlas来发送SQL请求。
proxy-address = 0.0.0.0:1234#(必备,默认值即可)Atlas监听的管理接口IP和端口 ;代表DBA应该使用2345这个端口连接Atlas来执行运维管理操作。
admin-address = 0.0.0.0:2345#(可选项,可不设置)分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项,子表需要事先建好,子表名称为表名_数字,数字范围为[0,子表数-1],如本例里,子表名称为mt_0、mt_1、mt_2
tables= person.mt.id.3#(可选项,可不设置)默认字符集,若不设置该项,则默认字符集为latin1
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
6、启动atlas,并查看端口号
[root@atlas conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
[root@atlas conf]# netstat-lntup |egrep "3306|2345"tcp0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 8709/mysql-proxy
tcp0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 8709/mysql-proxy
7、atlas命令添加进环境变量
[root@atlas conf]# echo 'export PATH=/usr/local/mysql-proxy/bin:$PATH' >> /etc/profile
[root@atlas conf]# source/etc/profile
[root@atlas conf]# mysql-proxyd test restart
OK: MySQL-Proxy of test is stopped
OK: MySQL-Proxy of test is started
[root@atlas conf]# netstat-lntup |egrep "3306|2345"tcp0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 8752/mysql-proxy
tcp0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 8752/mysql-proxy
四、Altas读写分离测试
1.Altas的读写测试
#Altas
[root@atlas app]# mysql-umha -pmha -h 10.0.0.104 -P 3306#读测试,会分别从slave1和slave2中读取数据
mysql> select@@server_id;+-------------+
| @@server_id |
+-------------+
| 102 |
+-------------+
1 row in set (0.00sec)
mysql> select@@server_id;+-------------+
| @@server_id |
+-------------+
| 103 |
+-------------+
1 row in set (0.00sec)
#写测试,只会在master中写入数据
mysql> begin;select@@server_id;commit;
Query OK,0 rows affected (0.01sec)+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+
1 row in set (0.00sec)
Query OK,0 rows affected (0.00 sec)
2、生产用户要求,创建app用户
开发人员申请一个应用用户 app( selectupdate insert) 密码123456,要通过10网段登录1. 在主库中,创建用户
grantselect ,update,insert on *.* to app@'10.0.0.%' identified by '123456';2. 在atlas中添加生产用户/usr/local/mysql-proxy/bin/encrypt 123456 ---->制作加密密码3.在altas配置文件中添加app:密码
vim test.cnf
pwds= repl:3yb5jEku5h4=,mha:O2jBXONX098=,app:/iZxz+0GRoA=
4.重启altas/usr/local/mysql-proxy/bin/mysql-proxyd test restart5.登录测试
[root@atlas app]# mysql-uapp -p123456 -h 10.0.0.104 -P 3306mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 3Server version:5.0.81-log MySQL Community Server (GPL)
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 clearthe current input statement.
mysql>
3、Altas的基本管理
以下操作均在altas上执行
3.1连接altas的管理关口2345
[root@atlas app]# mysql -uuser -ppwd -h 10.0.0.104 -P 2345mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 1Server version:5.0.99-agent-admin
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 clearthe current input statement.
mysql>
3.2打印altas管理命令的帮助信息
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)
3.3查询所有后端节点信息
mysql> select *from backends;+-------------+-----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-----------------+-------+------+
| 1 | 10.0.0.101:3306 | up | rw |
| 2 | 10.0.0.102:3306 | up | ro |
| 3 | 10.0.0.103:3306 | up | ro |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)
3.4动态添加删除节点
删除slave2:10.0.0.103节点
mysql> select *from backends;+-------------+-----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-----------------+-------+------+
| 1 | 10.0.0.101:3306 | up | rw |
| 2 | 10.0.0.102:3306 | up | ro |
| 3 | 10.0.0.103:3306 | up | ro |
+-------------+-----------------+-------+------+
3 rows in set (0.00sec)
mysql> remove backend 3;
Empty set (0.00sec)
mysql> select *from backends;+-------------+-----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-----------------+-------+------+
| 1 | 10.0.0.101:3306 | up | rw |
| 2 | 10.0.0.102:3306 | up | ro |
+-------------+-----------------+-------+------+
2 rows in set (0.00sec)
#配置文件中该节点信息还存在
[root@atlas~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103proxy-read-only-backend-addresses = 10.0.0.102:3306,10.0.0.103:3306
动态保存配置
mysql>save config;
Empty set (0.31sec)
#配制文件中slave2节点信息被删除
[root@atlas~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103[root@atlas~]#
3.5动态添加slave2节点
mysql> add slave 10.0.0.103:3306;
Empty set (0.00sec)
mysql> select *from backends;+-------------+-----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-----------------+-------+------+
| 1 | 10.0.0.101:3306 | up | rw |
| 2 | 10.0.0.102:3306 | up | ro |
| 3 | 10.0.0.103:3306 | up | ro |
+-------------+-----------------+-------+------+
3 rows in set (0.00sec)
#没有动态保存,配置文件中任没有改节点信息
[root@atlas~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103[root@atlas~]#
动态保存
mysql>save config;
Empty set (0.30sec)
#配置文佳已自动添加节点slave2信息
[root@atlas~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103proxy-read-only-backend-addresses=10.0.0.102:3306,10.0.0.103:3306[root@atlas~]#
五、Altas自动分表介绍
使用Atlas的分表功能时,首先需要在配置文件test.cnf设置tables参数。
tables参数设置格式:数据库名.表名.分表字段.子表数量,
比如:
你的数据库名叫test,表名叫sharding_test,分表字段叫id,那么就写为test.sharding_test如果还有其他的分表,以逗号分隔即可。
添加如下配置
[shardrule-0]
table= test.sharding_test #分表名,有数据库+表名组成
type=range #sharding类型:range 或 hash
shard-key = id#sharding 字段,以id列来分表groups = 0:0-999,1:1000-1999 #分片的group,如果是range类型的sharding,则groups的格式是:group_id:id范围。如果是hash类型的sharding,则groups的格式是:group_id。例如groups = 0, 1。id=0-999在group0组,id=1000-1999在group1组
[group-0]
# master
proxy-backend-addresses=10.0.0.101:3306# slave
proxy-read-only-backend-addresses=10.0.0.102:3306[group-1]
proxy-backend-addresses=10.0.0.103:3306proxy-read-only-backend-addresses=10.0.0.104:3306#定义两个dbgroup(数据库组), 每个dbgroup有一个master, 一个slave, sharding_test使用range的方式, 以id作为shard key, 属于test数据库, dbgroup0属于范围0- 999, dbgroup1 属于范围 1000 - 1999。
完成altas分表配置
[root@atlas ~]# cat /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username =user
admin-password = pwdproxy-backend-addresses = 10.0.0.101:3306proxy-read-only-backend-addresses = 10.0.0.102:3306,10.0.0.103:3306pwds= repl:3yb5jEku5h4=, mha:O2jBXONX098=daemon= truekeepalive= trueevent-threads = 8log-level =message
log-path = /usr/local/mysql-proxy/log
sql-log =ON
proxy-address = 0.0.0.0:3306admin-address = 0.0.0.0:2345charset=utf8
#定义分表的信息
[shardrule-0]
table=test.sharding_test
type=range
shard-key = id
groups = 0:0-999,1:1000-1999#定义数据库组
[group-0]
proxy-backend-addresses=10.0.0.101:3306proxy-read-only-backend-addresses=10.0.0.102:3306[group-1]
proxy-backend-addresses=10.0.0.103:3306proxy-read-only-backend-addresses=10.0.0.104:3306