Linux12 -MYSQL数据库 --＞16数据库的读写分离

MySQL读写分离

一、读写分离简介

1.1常见高并发场景

随着一个网站的业务不断扩展，数据不断增加，数据库的压力也会越来越大，对数据库或者SQL的基本优化可能达不到最终的效果，此时可以考虑通过添加数据库节点来使其达到提升性能的目的通常有以下常见几种方案！

# 读写分离
打开一个帖子内容页，需要select帖子表，和帖子评论表，每个耗时10ms的话。每秒1000次查询就是这个数据库的极限了。也就是说，这个论坛只能承载每秒500次访问。那么我们就可以对这个数据库做读写分离，来成倍提高数据库的读性能。

# 水平分表
交易历史，这种表。因为很少查询1年前的信息。所以，我们可以按年份来进行水平分表。将今年的表优先对待。就减少了要查询的表的大小。

# 分库
如果我们的系统非常大了，功能非常多，就会有很多类型的数据库表，例如营销活动的表，和用户账号的表是没有关联的，那么我们就可以将他们分到两个数据库中，然后放到不同的独立的数据库服务器，就能使数据库吞吐量成倍增加。

# 垂直分表

典型的应用场景是在文章列表这样的场景，一般来讲，我们的文章表会有title、userId、Content等字段，其中的Content字段一般是Text或者LongText类型，而其它的字段都是固定长度的数据类型。我们知道一个数据库优化规则是：
如果一个表的所有字段都是固定长度类型的，那么它就是定长表，定长表比动态长度表查询性能要高
那么，我们就可以使用垂直分表来将文章表分成文章表和文章内容表。于是文章列表页面所需的查询，就只需要查询一张定长表了。
# 引入Cache
通常来说即使前者的数据库架构做得再好，对于定时抢购/抽奖/等，这种类似高密度密集访问的场景也是力不从心，最好的方法还是在服务层和数据库层添加一个缓存层用，使其让请求命中至缓存层，数据层只持久化变更的数据即可。

1.2 读写分离原理

• 只在主服务器上写
• 只在从服务器上读

MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。简单来说，读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询。当业务量非常大时，一台服务器的性能无法满足需求，就可以通过配置主从复制实现写分离来分摊负载，避免因负载太高而造成无法及时响应请求。

1.3 读写分离类型

# 基于程序代码内部实现
在代码中根据select,insert进行路由分类，这类方法也是目前生产环境应用最广泛的，优点是性能好，因为在程序代码中已经将读写的数据源拆分至两个，所以不需要额外的MySQL proxy解析SQL报文，在进行路由至不同数据库节点。缺点是通常该架构较复杂，运维成本相对较高。

# 基于中间代理层实现
代理层一般位于客户端和服务器之间，代理服务器接到客户端请求后通过解析SQL文本再将SQL路由至可用的数据库节点中。优点是程序不需要改造可以实现无缝迁移，可移植性较好。缺点是性能相对前者略微逊色一些，并且并不是所有的读操作都能够被路由至从节点中。

二、Atlas读写分离

环境说明：

主机	IP	角色
db00	192.168.15.50	manager
db01	192.168.15.51	mysql主库，atlas
db02	192.168.15.52	mysql从库
db03	192.168.15.53	mysql从库

2.1 Atlas简介

  Mysql 的 proxy 中间件有比较多的工具，例如，mysql-proxy(官方提供), atlas , cobar, mycat, tddl, tinnydbrouter等等。
	而Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。

　　同时，有超过50家公司在生产环境中部署了Atlas，超过800人已加入了我们的开发者交流群，并且这些数字还在不断增加。而且安装方便。配置的注释写的蛮详细的，都是中文。

　　Atlas官方链接： https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md

　　Atlas下载链接： https://github.com/Qihoo360/Atlas/releases

2.2 Atlas主要功能

# 主要功能：
	1.读写分离
	2.从库负载均衡
	3.IP过滤
	4.自动分表
	5.DBA可平滑上下线DB(不影响用户的体验,把你的数据库下线)
	6.自动摘除宕机的DB

# Atlas相对于官方MySQL-Proxy的优势

1.将主流程中所有Lua代码用C重写，Lua仅用于管理接口
2.重写网络模型、线程模型
3.实现了真正意义上的连接池
4.优化了锁机制，性能提高数十倍

2.3 Atlas主要架构

Atlas是一个位于应用程序与MySQL之间中间件。在后端DB看来，Atlas相当于连接它的客户端，在前端应用看来，Atlas相当于一个DB。Atlas作为服务端与应用程序通讯，它实现了MySQL的客户端和服务端协议，同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节，同时为了降低MySQL负担，它还维护了连接池。
　Atlas使得应用程序员无需再关心读写分离、分表等与MySQL相关的细节，可以专注于编写业务逻辑，同时使得DBA的运维工作对前端应用透明，上下线DB前端应用无感知。

企业读写分离及分库分表其他方案了解

Mysql-proxy（oracle）

Mysql-router（oracle）

Atlas (Qihoo 360)

Atlas-sharding (Qihoo 360)

Cobar（是阿里巴巴（B2B）部门开发）

Mycat（基于阿里开源的Cobar产品而研发）

TDDL Smart Client的方式（淘宝）

Oceanus(58同城数据库中间件)

OneProxy（原支付宝首席架构师楼方鑫开发 ）

vitess（谷歌开发的数据库中间件）

Heisenberg(百度)

TSharding(蘑菇街白辉)

Xx-dbproxy（金山的Kingshard、当当网的sharding-jdbc ）

amoeba

2.4 准备主从环境

2.5 Atlas安装

# 虽然包时el6的，但是centos7也能用
1.下载软件包
[root@db00 ~]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
2.将软件包上传至服务器
[root@db00 ~]# rz
3.使用YUM方式进行安装
[root@db00 ~]# rpm -ivh  Atlas-sharding_1.0.1-el6.x86_64.rpm
[root@db00 ~]# yum -y localinstall Atlas-2.2.1.el6.x86_64.rpm 

#安装完成后atlas的安装目录为/usr/local/mysql-proxy

2.6 授权Atlas管理账号

#授权atlas管理账号，主从数据库服务器都需要授权，需授权账号地址如下：
grant all on *.* to 'bigegon'@'%' identified by 'Mm2021@mm';
grant all on *.* to 'user1'@'%' identified by 'Mm2021@mm';
flush privileges;
#制作密码，将生成的加密密码写入以下配置文件内
[root@db00 conf]# /usr/local/mysql-proxy/bin/encrypt atlas
KsWNCR6qyNk=

2.7 修改test配置文件

对应修改配置参数：

[root@db00 mysql-proxy]# vim /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username = user
#管理接口的密码
admin-password = 'Mm2021@mm'
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.15.250:3306  # 写节点（主库）
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
#proxy-read-only-backend-addresses = 127.0.0.1:3305@1
proxy-read-only-backend-addresses = 192.168.15.52:3306,192.168.15.53:3306 # 写节点（从库）
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = root:+jKsgB3YAG8=, user2:5W0q2hE95hjOd2d7iUtBfw==
#设置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  # sql记录（可做审计）

#实例名称，用于同一台机器上多个Atlas实例间的区分
#instance = test

#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:3306  # 业务连接端口

#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

2.8 服务管理命令

1.服务启动命令 
[root@db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start

2.服务停止命令
[root@db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test stop

3.加密密码生成方式
[root@db01 ~]# /usr/local/mysql-proxy/bin/encrypt atlas
KsWNCR6qyNk=
# atlas为数据库主从授权的密码

2.9检查启动

1.用ps命令检查启动
[root@db00 ~]#  ps -ef|grep [m]ysql-proxy
root       1970      1  0 23:42 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root       1971   1970  0 23:42 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root       1988   1885  0 23:42 pts/0    00:00:00 tail -f /usr/local/mysql-proxy/log/test.log

2.用netstat命令检查启动
[root@db00 ~]# netstat -lntup|grep [m]ysql-proxy
tcp        0      0 0.0.0.0:1234            0.0.0.0:*               LISTEN      1971/mysql-proxy    
tcp        0      0 0.0.0.0:2345            0.0.0.0:*               LISTEN      1971/mysql-proxy    

2.10 Atlas管理界面

进入管理接口

1.用altas管理的用户登入	# 配置文件内指定的用户与密码
# 进入管理接口,使用配置文件中指定的管理账号与密码
mysql -ubigegon -p'Mm2021@mm' -h127.0.0.1 -P2345
	- admin为test配置文件管理账号
	- 2345为Atlas监听的管理接口IP和端口
#查看帮助,依据帮助查找可执行的管理操作
mysql> SELECT * FROM help;
#查看后端的数据库
SELECT * FROM backends;

2.查看可用命令帮助
[root@db01 ~]# mysql> select * from help;

3.查询后端代理的库
[root@db01 ~]# mysql> select * from backends;
4.测试平滑摘除某一个库
mysql> remove backend 2;
Empty set (0.00 sec)
# 查看
mysql> select * from backends;

2 rows in set (0.00 sec)
# 提交保存
mysql> save config;
Empty set (0.01 sec)

5.测试平滑添加某一个库
mysql> add slave 192.168.15.52:3306;
# 查看
mysql> select * from backends;
3 rows in set (0.00 sec)
# 提交保存
mysql> save config;
Empty set (0.01 sec)

在任意一台机器上，登录atlas主机

#1、为了很好地观察读写分离的效果，可以先把从库的sql延迟设置大
stop slave;
CHANGE MASTER TO MASTER_DELAY = 60;
start slave;
	
#2、登录atlas来进行测试
# -u、-p为atlas配置文件中pwds指定的账号和密码
# -h后的地址为atlas主机的地址
# -P自定atlas监听的ip
mysql -uroot -p -h 192.168.15.200 -P 3306

mysql> create database db1;
此时去主库查看，发现有db1库，但是去所有从库查看发现都没有
过了一分钟后从库上都有db1了，然后我们通过atlas也可以查看到了
mysql> show databases;

2.11 Atlas工作界面

1、连接管理服务，使用配置文件中管理接口账户

mysql -ubigegon -p-h192.168.15.50 -P2345

2、 持久化配置文件

mysql> save config;
Empty set (0.00 sec)

2.12节点管理

1、查看所有节点

mysql> SELECT * FROM backends;
+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 192.168.15.100:3306 | up    | rw   |
|           2 | 192.168.15.101:3306 | up    | ro   |
|           3 | 192.168.15.102:3306 | up    | ro   |
+-------------+---------------------+-------+------+
3 rows in set (0.00 sec)

2、 节点的上线和下线

下线

mysql> set offline 1;
+-------------+---------------------+---------+------+
| backend_ndx | address             | state   | type |
+-------------+---------------------+---------+------+
|           1 | 192.168.15.51:3306 | offline | rw   |
+-------------+---------------------+---------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM backends;
+-------------+---------------------+---------+------+
| backend_ndx | address             | state   | type |
+-------------+---------------------+---------+------+
|           1 | 192.168.15.51:3306 | offline | rw   |
|           2 | 192.168.15.52:3306 | up      | ro   |
|           3 | 192.168.15.53:3306 | up      | ro   |
+-------------+---------------------+---------+------+
3 rows in set (0.00 sec)

上线

mysql> set online 1;
+-------------+---------------------+---------+------+
| backend_ndx | address             | state   | type |
+-------------+---------------------+---------+------+
|           1 | 192.168.15.100:3306 | unknown | rw   |
+-------------+---------------------+---------+------+
1 row in set (0.00 sec)

3、 添加删除节点

删除

mysql> remove backend 3;
mysql> SELECT * FROM backends;

添加

mysql> add slave 192.168.15.102:3306;
mysql> SELECT * FROM backends;

2.13 用户管理

1、在主库增加数据库用户

mysql> grant all on *.* to user1@'192.168.10.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2、查看当前用

mysql> select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| test     | 3yb5jEku5h4= |
| repl     | 3yb5jEku5h4= |
+----------+--------------+
2 rows in set (0.00 sec)

3、 增加Atlas用户

mysql> add pwd user1:123;
Empty set (0.00 sec)

mysql> select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| root     | 3yb5jEku5h4= |
| egon     | 3yb5jEku5h4= |
| user1    | 3yb5jEku5h4= |
+----------+--------------+
3 rows in set (0.00 sec)

[root@db01 mysql-proxy]# mysql -h 192.168.100.112 -P1234 -uatlas -patlas
mysql> show databases;
mysql> select user,host from mysql.user;

2.12 读写分离测试

#读请求测试

[root@db01 mysql-proxy]# mysql -h 192.168.100.112 -P1234 -uatlas -patlas
mysql> select @@server_id;

#也可在数据库主库或从库查看select查询情况
mysql -uroot -p123
mysql> show global status like '%select%';

#写请求测试
#在数据库主库和从库查看insert情况，将从库同步延时设置为180秒
mysql> stop slave;
mysql> change master to master_delay=180;
mysql> start slave;

mysql> show global status like '%insert%';