二十四、Mysql读写分离之Atlas
1.读写分离 2.从库负载均衡 3.IP过滤 4.自动分表 5.DBA可平滑上下线DB 6.自动摘除宕机的DB
Atlas相对于官方MySQL-Proxy的优势
1.将主流程中所有Lua代码用C重写，Lua仅用于管理接口 2.重写网络模型、线程模型 3.实现了真正意义上的连接池 4.优化了锁机制，性能提高数十倍
Atlas官方链接： https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md Atlas下载链接： https://github.com/Qihoo360/Atlas/releases
Atlas在后端DB看来，Atlas相当于连接它的客户端，在前端应用看来，Atlas相当于一个DB。
Atlas作为服务端与应用程序通讯，它实现了MySQL的客户端和服务端协议，同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节，同时为了降低MySQL负担，它还维护了连接池。
Altas 10.0.0.104 altas MHA 10.0.0.105 mha manager node db01 10.0.0.101 mysql-master+mha node db02 10.0.0.102 mysql-slave1+mha node db03 10.0.0.103 mysql-slave2+mha node System OS: CentOS Linux release 7.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=101 db02: server_id=102 db03: server_id=103
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以上
部署节点db01,db02,db03。
部署参考：https://www.cnblogs.com/yaokaka/p/13914362.html 文档中的二进制部署
my.cnf配置文件
db01
[mysqld] basedir=/app/mysql/ datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=101 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/mysql/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db01 [\d]>
db02
[mysqld] basedir=/app/mysql/ datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=102 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/mysql/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db02 [\d]>
db03
[mysqld] basedir=/app/mysql/ datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=103 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/mysql/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db03 [\d]>
部署节点db01 master; db02 slave1;db03 slave3
部署参考文档：https://www.cnblogs.com/yaokaka/p/14087397.html
#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
部署节点：mha,db01,db02,db03
部署参考文档：https://www.cnblogs.com/yaokaka/p/14094537.html
6、部署Atlas
部署节点：altas
1、下载安装altas
mkdir /app cd /app wget 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
[root@atlas app]# cd /usr/local/mysql-proxy/ [root@atlas mysql-proxy]# ll total 0 drwxr-xr-x 2 root root 75 Dec 12 14:54 bin drwxr-xr-x 2 root root 22 Dec 12 14:54 conf drwxr-xr-x 3 root root 331 Dec 12 14:54 lib drwxr-xr-x 2 root root 6 Dec 17 2014 log
altas配置文佳中，需要主从关系的repl的密码和MHA的mha的密码，且密码为加密密钥。 该实验主从的用户和密码：repl:123 mha的用户和密码：mha:mha
主从的密码加密
[root@atlas mysql-proxy]# /usr/local/mysql-proxy/bin/encrypt 123 3yb5jEku5h4= 主从的复制用户：repl 加密密码：3yb5jEku5h4=
mha的密码加密
[root@atlas mysql-proxy]# /usr/local/mysql-proxy/bin/encrypt mha O2jBXONX098= mha的用户：mha 加密密码：O2jBXONX098=
#1、备份源配置文件 [root@atlas mysql-proxy]# cd /usr/local/mysql-proxy/conf/ [root@atlas conf]# cp test.cnf test.cnf.bak #2、修改配置文件 [root@mysql-db01 /]# cat >> /usr/local/mysql-proxy/conf/test.cnf <<EOF [mysql-proxy] admin-username = user admin-password = pwd proxy-backend-addresses = 10.0.0.101:3306 proxy-read-only-backend-addresses = 10.0.0.102:3306,10.0.0.103:3306 pwds = repl:3yb5jEku5h4=, mha:O2jBXONX098= 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:3306 admin-address = 0.0.0.0:2345 charset = utf8 EOF
[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
[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" tcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 8709/mysql-proxy tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 8709/mysql-proxy
[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" tcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 8752/mysql-proxy tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 8752/mysql-proxy
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.00 sec) mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 103 | +-------------+ 1 row in set (0.00 sec) #写测试，只会在master中写入数据 mysql> begin;select @@server_id;commit; Query OK, 0 rows affected (0.01 sec) +-------------+ | @@server_id | +-------------+ | 101 | +-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
开发人员申请一个应用用户 app( select update insert) 密码123456,要通过10网段登录 1. 在主库中,创建用户 grant select ,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 restart 5.登录测试 [root@atlas app]# mysql -uapp -p123456 -h 10.0.0.104 -P 3306 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 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 clear the current input statement. mysql>
以下操作均在altas上执行
3.1连接altas的管理关口2345
[root@atlas app]# mysql -uuser -ppwd -h 10.0.0.104 -P 2345 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, 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> 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)
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)
删除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.00 sec) mysql> remove backend 3; Empty set (0.00 sec) 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.00 sec) #配置文件中该节点信息还存在 [root@atlas ~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103 proxy-read-only-backend-addresses = 10.0.0.102:3306,10.0.0.103:3306
mysql> save config; Empty set (0.31 sec) #配制文件中slave2节点信息被删除 [root@atlas ~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103 [root@atlas ~]#
mysql> add slave 10.0.0.103:3306; Empty set (0.00 sec) 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) #没有动态保存，配置文件中任没有改节点信息 [root@atlas ~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103 [root@atlas ~]#
动态保存
mysql> save config; Empty set (0.30 sec) #配置文佳已自动添加节点slave2信息 [root@atlas ~]# cat /usr/local/mysql-proxy/conf/test.cnf|grep 10.0.0.103 proxy-read-only-backend-addresses=10.0.0.102:3306,10.0.0.103:3306 [root@atlas ~]#
使用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:3306 proxy-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 = pwd proxy-backend-addresses = 10.0.0.101:3306 proxy-read-only-backend-addresses = 10.0.0.102:3306,10.0.0.103:3306 pwds = repl:3yb5jEku5h4=, mha:O2jBXONX098= 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:3306 admin-address = 0.0.0.0:2345 charset = 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:3306 proxy-read-only-backend-addresses=10.0.0.102:3306 [group-1] proxy-backend-addresses=10.0.0.103:3306 proxy-read-only-backend-addresses=10.0.0.104:3306
I have a dream so I study hard!!!
