高性能高可用MySQL（主从同步，读写分离，分库分表，去中心化，虚拟IP,心跳机制）高性能高可用MySQL（主从同步，读写分离，分库分表，去中心化，虚拟IP,心跳机制）1、面试题2、mysql分区的数据类型一、主从同步(详细看上一篇博客) 二.mycat:

87 阅读 0 评论 58 点赞

我是靠谱客的博主直率铃铛，最近开发中收集的这篇文章主要介绍高性能高可用MySQL（主从同步，读写分离，分库分表，去中心化，虚拟IP,心跳机制）高性能高可用MySQL（主从同步，读写分离，分库分表，去中心化，虚拟IP,心跳机制）1、面试题2、mysql分区的数据类型一、主从同步(详细看上一篇博客) 二.mycat:，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

高性能高可用MySQL（主从同步，读写分离，分库分表，去中心化，虚拟IP,心跳机制）

视频地址：https://www.bilibili.com/video/BV1ry4y1v7Tr?p=8&spm_id_from=pageDriver

1、面试题

为什么要分库分表（设计高并发系统的时候，数据库层面该如何设计）？用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？你们具体是如何对数据库如何进行垂直拆分或水平拆分的？

其实这块肯定是扯到高并发了，因为分库分表一定是为了支撑高并发、数据量大两个问题的。而且现在说实话，尤其是互联网类的公司面试，基本上都会来这么一下，分库分表如此普遍的技术问题，不问实在是不行，而如果你不知道那也实在是说不过去！

（1）为什么要分库分表？（设计高并发系统的时候，数据库层面该如何设计？）

说白了，分库分表是两回事儿，大家可别搞混了，可能是光分库不分表，也可能是光分表不分库，都有可能。我先给大家抛出来一个场景。

假如我们现在是一个小创业公司（或者是一个BAT公司刚兴起的一个新部门），现在注册用户就20万，每天活跃用户就1万，每天单表数据量就1000，然后高峰期每秒钟并发请求最多就10。。。天，就这种系统，随便找一个有几年工作经验的，然后带几个刚培训出来的，随便干干都可以。

结果没想到我们运气居然这么好，碰上个CEO带着我们走上了康庄大道，业务发展迅猛，过了几个月，注册用户数达到了2000万！每天活跃用户数100万！每天单表数据量10万条！高峰期每秒最大请求达到1000！同时公司还顺带着融资了两轮，紧张了几个亿人民币啊！公司估值达到了惊人的几亿美金！这是小独角兽的节奏！

好吧，没事，现在大家感觉压力已经有点大了，为啥呢？因为每天多10万条数据，一个月就多300万条数据，现在咱们单表已经几百万数据了，马上就破千万了。但是勉强还能撑着。高峰期请求现在是1000，咱们线上部署了几台机器，负载均衡搞了一下，数据库撑1000 QPS也还凑合。但是大家现在开始感觉有点担心了，接下来咋整呢。。。。。。

再接下来几个月，我的天，CEO太牛逼了，公司用户数已经达到1亿，公司继续融资几十亿人民币啊！公司估值达到了惊人的几十亿美金，成为了国内今年最牛逼的明星创业公司！天，我们太幸运了。

但是我们同时也是不幸的，因为此时每天活跃用户数上千万，每天单表新增数据多达50万，目前一个表总数据量都已经达到了两三千万了！扛不住啊！数据库磁盘容量不断消耗掉！高峰期并发达到惊人的5000~8000！别开玩笑了，哥。我跟你保证，你的系统支撑不到现在，已经挂掉了！

好吧，所以看到你这里你差不多就理解分库分表是怎么回事儿了，实际上这是跟着你的公司业务发展走的，你公司业务发展越好，用户就越多，数据量越大，请求量越大，那你单个数据库一定扛不住。

比如你单表都几千万数据了，你确定你能抗住么？绝对不行，单表数据量太大，会极大影响你的sql执行的性能，到了后面你的sql可能就跑的很慢了。一般来说，就以我的经验来看，单表到几百万的时候，性能就会相对差一些了，你就得分表了。

分表是啥意思？就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户id来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在200万以内。

分库是啥意思？就是你一个库一般我们经验而言，最多支撑到并发2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒1000左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

这就是所谓的分库分表，为啥要分库分表？你明白了吧

（2）用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？

比较常见的包括：cobar、TDDL、atlas、sharding-jdbc、mycat

cobar：阿里b2b团队开发和开源的，属于proxy层方案。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库join和分页等操作。

TDDL：淘宝团队开发的，属于client层方案。不支持join、多表查询等语法，就是基本的crud语法是ok，但是支持读写分离。目前使用的也不多，因为还依赖淘宝的diamond配置管理系统。

atlas：360开源的，属于proxy层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在5年前了。所以，现在用的公司基本也很少了。

sharding-jdbc：当当开源的，属于client层方案。确实之前用的还比较多一些，因为SQL语法支持也比较多，没有太多限制，而且目前推出到了2.0版本，支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从2017年一直到现在，是不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。

mycat：基于cobar改造的，属于proxy层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于sharding jdbc来说，年轻一些，经历的锤炼少一些。

所以综上所述，现在其实建议考量的，就是sharding-jdbc和mycat，这两个都可以去考虑使用。

sharding-jdbc这种client层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合sharding-jdbc的依赖；

mycat这种proxy层方案的缺点在于需要部署，自己及运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

通常来说，这两个方案其实都可以选用，但是我个人建议中小型公司选用sharding-jdbc，client层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多；

但是中大型公司最好还是选用mycat这类proxy层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护mycat，然后大量项目直接透明使用即可。

我们，数据库中间件都是自研的，也用过proxy层，后来也用过client层

（3）你们具体是如何对数据库如何进行垂直拆分或水平拆分的？

水平拆分的意思，就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来抗更高的并发，还有就是用多个库的存储容量来进行扩容。

垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

这个其实挺常见的，不一定我说，大家很多同学可能自己都做过，把一个大表拆开，订单表、订单支付表、订单商品表。

还有表层面的拆分，就是分表，将一个表变成N个表，就是让每个表的数据量控制在一定范围内，保证SQL的性能。否则单表数据量越大，SQL性能就越差。一般是200万行左右，不要太多，但是也得看具体你怎么操作，也可能是500万，或者是100万。你的SQL越复杂，就最好让单表行数越少。

好了，无论是分库了还是分表了，上面说的那些数据库中间件都是可以支持的。就是基本上那些中间件可以做到你分库分表之后，中间件可以根据你指定的某个字段值，比如说userid，自动路由到对应的库上去，然后再自动路由到对应的表里去。

你就得考虑一下，你的项目里该如何分库分表？一般来说，垂直拆分，你可以在表层面来做，对一些字段特别多的表做一下拆分；水平拆分，你可以说是并发承载不了，或者是数据量太大，容量承载不了，你给拆了，按什么字段来拆，你自己想好；分表，你考虑一下，你如果哪怕是拆到每个库里去，并发和容量都ok了，但是每个库的表还是太大了，那么你就分表，将这个表分开，保证每个表的数据量并不是很大。

2、mysql分区的数据类型

而且这儿还有两种分库分表的方式，一种是按照range来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了；或者是按照某个字段hash一下均匀分散，这个较为常用。

range来分，好处在于说，后面扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据

hash分法，好处在于说，可以平均分配没给库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的这么一个过程

mysql数据库数据分区的类型：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
(mysql5.5之前的版本只接收int类型的参数)

举例如下：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
)；

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
(mysql5.5之前的版本只接收int类型的参数)

举例如下：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
)；

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
(mysql5.5之前的版本只接收int类型的参数)

KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

举例如下：
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

mysql处理海量数据

主从同步：会带来数据冗余
1.“从”节点可以作为备份节点
2. 主从同步可以实现读写分离（mycat中间件）

mysql中解决并发问题：读写分离
1.一般项目中，读>>写
100002=10000读+2次

实现读写分离？中间件 mycat
mycat
   1: 读写分离
   2：分表分库（大量数据分成小的）

haproxy:搭建多个mycat集群

集群：防止单点故障

去中心化：
1.多个节点之间彼此发送心跳（感知对方是否还存活）
2.维护一个 VIP（虚拟ip）

6台=1windows +5台centos7

准备5centos：
（为了学习方便，先全部使用超级管理员root）
   配置一个，克隆出其他的
   克隆完毕后需要处理：
   1.内存
   2.ip
   vi /etc/sysconfig/network-scripts/ifcfg-ens33
   删除网卡中的唯一标志符
   3.hostname
   hostnamectl set-hostname bigdata05
   4.bigdata01/02/03/04修改映射

   centos:    vi /etc/hosts
   windows:   C:WindowsSystem32driversetc
   增加6个计算机全部的映射
   5.刷新网络
   systemctl restart network
   6.ssh
   生成秘钥   ssh-keygen -t rsa
   私钥发给自己 ssh-copy-id localhost
   公钥发给别人 ssh-copy-id 别人的ip

一、主从同步(详细看上一篇博客)

windows - bigdata01

windows主
bigdata01：从

centos7关闭防火墙:

systemctl stop firewalld
systemctl disable firewalld

为了学习方便，在学习时，临时把防火墙关闭（开放端口）
处理mysql权限：开放(windows/centos)远程访问MySql的权限：

登录Mysql管理员账户：

grant all privileges on *.* to 'root'@'192.168.2.%' identified by 'root' with grant option ;
flush privileges;

本地的mysql可以被： 192.168.2.% ，root/root 访问

1.配置master(windows)

my.ini
[mysqld]
#master的id
server-id=1
#binary log
log-bin="C:/Program Files/MySQL/data/mysql-bin"
#异常信息的日志
log-error="C:/Program Files/MySQL/data/mysql-error"
# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
#MySQL根路径
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
#指定需要同步哪些数据库（默认全部数据库）
#binlog-do-bo=mydb01

#指定需要不同步哪些数据库（排除）
binlog-ignore-db=mysql

#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

2.配置slave(centos)

/etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=mydb01
replicate-do-db=mydb02

windwos/centos需要在配置完毕后重启服务

3.设置主从关系：

   slave ->master
   显示master的位置
   通过以下命令查看master的信息：
   （windows）show master status;
File: mysql-bin.000001
Position:107

salve：指定master (slave指定master作为自己的老大)
（bigdata01,mysql命令）

在mysql中写：
change master to
master_host='192.168.2.2',
master_user='root',
master_password='root',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=107;

验证主从同步：

准备数据：
create database mydb02;
create database mydb01;

开启slave （bigdata01,mysql命令）:
start slave ;

验证主从是否成功执行：
show slave status G，必须保证以下两个为yes：
Slave_IO_Running:
Slave_SQL_Running:

如果有问题，查看日志：Last_IO_Errno/Last_SQL_Errno
本次提示：master和slave有相同的server-id

当前主 windows: 5.5.62
centos7: 5.5.58

通过命令行指定：
server-id=2 （永久设置）
临时设置： bigdata01(slave) : set global server_id=2;
再次尝试：
stop slave ;
start slave;

尝试在master中mydb01/mydb02中创建表、插入数据，然后测试slave中是否有相应的表和数据。

如果遇到以下错误：出错的原因：之前已经搭建/启动主从
   1.ERROR 1201 :could not initlze master info structure ...
       数据库之前已经设置过了主从同步
   解决：关闭并重置slave（在slave中sql命令中）
       slave stop
       reset slave ;
       change master to ....（检查最新的 File、Position）

   2.ERROR 1198 :..... RUN STOP SLAVE ..
       数据库之前已经设置过了主从同步，并且也已经开启了主从同步
       stop slave ;
       change master to ....（检查最新的 File、Position）

启动Mysql:
/etc/init.d/mysql start
chkconfig mysql on

二.mycat:

1.分库分表：
mycat研究对象：水平拆分:订单数据( 订单数据1 订单数据2 订单数据3)
了解：（微服务）垂直拆分：系统（订单数据库、用户数据库）

2.读写分离：读-》服务器节点（数据库）
写-》服务器节点（数据库）

安装mycat：中国人开发的

上传、解压缩
配置（/usr/local/mycat/conf）

1.server.xml（配置访问Mycat的用户，schemas代表逻辑库）
   <user name="root" defaultAccount="true">
       <property name="password">root</property>
       
       <property name="schemas">mydb</property>
   </user>

2.schema.xml（读写分离、分库分表）

读写分离的写：主从同步的Master
读写分离的读：主从同步的Slave

配置文件中的dataHost ：


balance:读请求的负载均衡
       0 ：不开启读写分离，所有的读操作都发送到writehost中
       1：全部的readhost和stand by writehost（多个写节点待命的那些）都参与读操作的负责均衡
       2：读请求随机发送给readhost、writehost
       3：读请求随机发送给writehost中的readhost （writehost不参与读请求） --推荐（一般用的是1和3）

   writeType:写请求的负载均衡
       0：写请求先发送给schema.xml中的第一个writehost。当第一个writehost挂掉，再自动切换到下一个writehost中。切换的记录会被记录在 conf/dnindex.properties --推荐
       1：写请求随机发送到所有的wirtehost中

   switchType: 是否允许 “读操作”在readhost和writehost上自动切换（解决延迟问题：当从readhost中读取数据中出现网络延迟等问题时，自动从writehost中读数据）
-1：不许
1：默认，允许
2：根据“主从同步的状态” 自动选择是否切换。
主从之间会持续发送心跳。当心跳检测机制发现了IO延迟，则readhost自动切换到writehost；否则不切换。必须将心跳设置 show slave status --推荐

3.rule.xml：分库分表的拆分规则(平均拆分)
mydb->mydb01+mydb02

把第2步中的rule改成mod-long

准备数据：

确保bigdata、bigdata01中都存在mydb01和mydb02
表：student

create table student(id int(4) ,name varchar(10) ) ;

验证读写分离：
写：bigdata(master)
验证逻辑：
向mycat中插入数据 -> 是否会自动写入 writehost（bigdata）

具体步骤：
   开启mycat
   bin/mycat start
   查看状态
   bin/mycat status

   登录mycat
       提示：
       不能直接在mycat所在节点登录：mysql -uroot -proot -P8066 (这里虽然是mysql命令，但是还是mycat)
       需要借助一个已经安装了mysql软件的计算机上远程操作mysql:
       在windows上远程连接 bigdata02上的 Mycat :mysql -uroot -proot -h192.168.2.129 -P8066

show database;查看一下mydb在不在来验证是否登陆成功

向mycat中插入数据

insert into student(id,name) values(1,'zs');//sql92语法，严格

验证：从bigdata(master)中查看数据是否写入

读：bigdata01(slave)

上图左边是mycat逻辑库逻辑表，右图是主节点（master）,可以看到，已经分库分表成功。

验证：bigdata01(slave)中查看是否读：

在bigdata01中插入数据

use mydb01;
insert into student values(3,'ww');

在mycat中读：

常见bug：
   如果无法正常实现mycat功能，调试：
   日志：
       mycat/logs ：
           mycat.log：执行出错
           wrapper.log：启动错误

   Invalid DataSource:0 ，常见解决方案：防火墙、IP、端口，权限问题：临时开放全部的权限

操作Mycat: 和sql 92基本一致 ; 操作端口8066 ；使用mycat的方法和mysql基本一致
管理端口9066

  SQL92：语法严格的SQL
       insert into student(id,name) values(1,zs) ;
   SQL99：语法较为宽松
       insert into student values(1,zs) ;

开放mysql的一切权限(root)

mycat-> a ,b ,在a,b计算机中的Mysql中通过以下执行开放权限(root)

mycat-> bigdata/bigdata01
use mysql ;
update user set
`Select_priv` = 'Y', `Insert_priv` = 'Y',`Update_priv` = 'Y',`Delete_priv` = 'Y',
`Create_priv` = 'Y',`Drop_priv` = 'Y',`Reload_priv` = 'Y',`Shutdown_priv` = 'Y',
`Process_priv` = 'Y',`File_priv` = 'Y',`Grant_priv` = 'Y',`References_priv` = 'Y',
`Index_priv` = 'Y',`Alter_priv` = 'Y',`Show_db_priv` = 'Y',`Super_priv` = 'Y',
`Create_tmp_table_priv` = 'Y',`Lock_tables_priv` = 'Y',`Execute_priv` = 'Y',
`Repl_slave_priv` = 'Y',`Repl_client_priv` = 'Y',`Create_view_priv` = 'Y',
`Show_view_priv` = 'Y',`Create_routine_priv` = 'Y',`Alter_routine_priv` = 'Y',
`Create_user_priv` = 'Y',`Event_priv` = 'Y',`Trigger_priv` = 'Y',`Create_tablespace_priv` = 'Y'
where user="root" ;

flush privileges;

三、搭mycat集群,保证高可用

---
在bigta03上再次搭建mycat，和bigdat02组成mycat集群
启动bigdata02/03上的mycat
--

通过haproxy 整合多个mycat:

在bigdata04搭建haproxy，用于整合2个mycat
在线安装haproxy:
查看可用的haproxy版本：yum list | grep haproxy

   haproxy.x86_64
   在线安装：
       yum -y install haproxy.x86_64
   设置：用户名是haproxy
   chown -R haproxy:haproxy /etc/haproxy/

配置文件（1.日志）
   vi /etc/rsyslog.conf
   将以下2个指令的注释释放：
   $ModLoad imudp
   $UDPServerRun 514
   设置日志文件的路径：
   local2.* 日志的保存文件
重启日志服务:
   systemctl restart rsyslog.service

配置文件（2.haproxy）
yum安装后的配置文件默认etc
vi /etc/haproxy/haproxy.cfg (里面的日志文件名和刚才配置的保持一致)