Mycat2

1. 数据库瓶颈问题：

　　- 数据库数据量大，查询效率低；

　　- 分布式数据库架构复杂对接困难；

　　- 高访问高并发对数据库压力大；

解决方案：Mycat

2. Mycat是什么？

2.1 数据库中间件　　

　　Mycat是数据库中间件(连接软件组件和应用的计算机软件，便于软件的各部件之间相互沟通)；

　　数据库中间件：连接Java应用程序和数据库；

2.2 为什么要用Mycat？

　　1). 避免Java与数据库紧耦合；

　　2). 高访问高并发对数据库的压力；

　　3). 读写请求数据不一致；

2.3 Mycat官网：http://www.mycat.org.cn/

3. Mycat能干什么、原理

3.1 读写分离

 

 

 3.2 数据分片

垂直拆分(分库)、水平拆分（分表）、垂直+水平拆分（分库分表）

 

 

 3.3 多数据源整合

 

 

 3.4 Mycat原理

Mycat通过拦截用户发送的SQL语句，并对其进行特定的分析，如分片分析、路由分析、读写分离分析、缓存分析等；然后将此SQL发往后端真实数据库，并将返回的结果进行适当的处理，最终在返回给用户；

4. 安装Mycat

4.1 安装

http://dl.mycat.org.cn/2.0/install-template/

http://dl.mycat.org.cn/2.0/1.21-release/

修改文件权限：mycat、wrapper-linux-ppc-64、wrapper-linux-x86-64、wrapper-linux-x86-32

4.2 启动

4.2.1 在MySQL数据库中添加用户；

　　create user 'mycat'@'%' identified by 'password';

　　grant XA_RECOVER_ADMIN ON *.* TO ‘root’@'%';(必须要赋的权限)

　　grant ALL PRIVILEGES ON *.* TO 'mycat'@'%';(视情况赋权限)

　　flush privileges;

4.2.2 修改mycat的prototype配置

　　启动mycat之前需要确认prototype数据源所对应的mysql数据库配置，修改对应的user、password、url中的ip；

　　vim conf/datasources/prototypeDs.datasource.json;

4.2.3 验证数据库访问情况

　　Mycat作为数据库中间件要和数据库部署在不同的机器上，所以要验证远程访问情况；

　　mysql -uroot -pxxxxxx -h xx.xx.xx.xx -P 3306

　　如果远程访问报错，请建立对应用户：

　　grant all privileges on *.* to root@'缺少的host' identified by 'xxxxxx';

　　set global validate_password_policy = 0;

　　flush privileges;

4.2.4 启动mycat

　　./mycat start、./mycat status、./mycat stop、./mycat console

4.2.5 登录

　　登录后台管理窗口：mysql -uroot -pxxxxxx -P 9066（运维）

　　登录数据窗口：mysql -uroot -pxxxxxx -P 8066

5. Mycat2相关概念

　　分库分表：按照一定规则将数据库中的表拆分为多个带有数据库实例、物理库、物理表访问路径的分表；

　　　　分库：电商项目，分为用户库和订单库等等；

　　　　分表：一张订单表数据数百万，达到Mysql单表瓶颈，可以分到多个数据库中的多张表；

　　逻辑库：数据库代理中的数据库，可以包含多个逻辑表；

　　　　Mycat中定义的库，在逻辑上存在，物理上在MySQL里并不存在。有可能是多个MySQL数据库共同组成一个逻辑库；

　　逻辑表：数据库代理中的表，映射代理连接的数据库中的表(物理表)；

　　　　Mycat里定义的表，在逻辑上存在，可以映射真实的MySQL数据库中的表；可以一对一、也可以一对多；

　　物理库/物理表；

　　拆分键：描述拆分逻辑表的数据规则的字段

　　物理分表：逻辑表的一个组成部分；

　　物理分库：包含多个物理分表的库；

　　分库：指通过多个数据库拆分分片表，每个数据库都是一个物理分表，物理分库名字相同；分库是个动作，需要多个数据库的参与；

　　分片表/水平分片表：按照一定的规则将数据拆分成多个分区的表，属于逻辑表中的一种；

　　单表：没有分片、没有数据冗余的表；

　　全局表/广播表：每个数据库实例都冗余全量数据的逻辑表；(字典表，每个分片表都需要完整的字典数据翻译字段)

　　ER表：狭义指父子表中的子表，分片键指向父表中的分片键，两表的分片算法相同；

　　　　　　广义是指具有相同数据分布的一组表；(订单表-订单详情表)

　　集群：多个数据节点组成逻辑节点，可以将多个数据源地址视为一个数据源地址，并提供自动故障恢复、转移，即实现高可用，负载均衡的组件；

　　数据源：连接后端数据库的组件；Mycat通过数据源连接MySQL数据库；

　　原型库：Mycat2后面真实的数据库，本次为MySQL;

6. 配置文件

6.1 服务(server)

　　 服务相关配置：server.json 默认配置即可；

6.2 用户

　　用户相关配置：users/root.user.json

 　　ip：客户端访问ip，填写后会对客户端的IP进行限制；

　　isolation：事务隔离级别；

　　transactionType：事务类型；

　　　　Proxy: 本地事务，多数据库连接，commit失败会导致数据不一致；

　　　　xa事务：需要确认存储节点类型是否支持XA；

　　　　set transaction_policy = 'xa/proxy'；

6.3 数据源

　　配置Mycat连接的数据源信息；mycat/conf/databases/prototypeDs.datasource.json

 6.4 集群

　　配置集群信息；mycat/conf/clusters/{集群名称}.cluster.json

6.5 逻辑库表

　　配置逻辑库，实现分库分表； mycat/conf/schemas/mysql.schema.json

7. 搭建一主一从　　　　　

 7.1 搭建MySQL主从复制

　　binlog日志三种格式：

　　　　STATEMENT：记录SQL语句，但是如果出现类似now()函数，则会产生数据不一致的问题；

　　　　ROW：记录数据行；

　　　　MIXED：解决了一部分数据不一致的问题，但是不能处理@@host name 系统变量等问题；

　　Step1：修改主机和从机的my.cnf配置文件；

# 主服务器配置
server-id = 1
# 启用二进制日志
log-bin = mysql-bin
# 设置不需要复制的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
# 设置需要复制的数据库
binlog-do-db=mydb 
# 设置logbin格式
binlog_format = STATEMENT

# 从服务器配置
server-id = 2
relay-log = mysql-relay

　　Step2：重新启动主机和从机上的MySQL服务；

systemctl restart mysqld.service
systemctl status mysqld.service

　　Step3：主机和从机均关闭防火墙；

systemctl stop firewalld.service

　　Step4：在主机上建立账户并授权slave；　

create user 'slave'@'%' identified by 'xxxxxx';
alter user 'slave'@'%' identified with mysql_native_password by 'xxxxxx';
grant replication slave on *.* to 'slave'@'%';
flush privileges;
show master status;

　　Step5：在从机上配置需要复制的主机；

change master to master_host='192.168.75.128',master_user='slave',master_password='xxxxxx',master_log_file='mysql-bin.000001',master_log_pos=597;

　　Step6：启动从服务器复制功能；

start slave;

　　Step7：查看从服务器状态；

show slave status\G;

　　Step8：重新配置主从关系；

stop slave;
reset master;

7.2 Mycat读写分离配置

　　Step1：登录Mycat，创建逻辑库，创建数据源；

create database mydb;

# 修改mydb.schema.json指定数据源：
"targetName":"prototype",

　　Step2：使用注解方式添加数据源；

/*+mycat:createDataSource{"name":"rwSepw","url":"jdbc:mysql://192.168.75.128:3306/mydb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","user":"root","password":"lcl19960822"} */;

/*+mycat:createDataSource{"name":"rwSepr","url":"jdbc:mysql://192.168.75.100:3306/mydb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","user":"root","password":"lcl19960822"} */;

/*+mycat:showDataSources{}*/;

　　Step3：更新集群信息，添加dr0从节点；

/*! mycat:createCluster{"name":"prototype","masters":["rwSepw"],"replica":["rwSepr"]} */

/*+ mycat:showClusters{} */;

　　Step4: 验证读写分离是否配置成功；

insert into mytbl values(1,@@hostname);

8. 搭建双主双从

8.1 实现主从复制

　　需求：一个主机用于处理所有写请求，另一个主机和所有从机负责处理读请求；当m1宕机后，m2主机负责处理写请求，m1和m2互为备机；

　　Step1：修改两个主机和两个从机的my.cnf配置文件；

# Master 1 
# 主服务器ID
server-id = 1
# 启用二进制日志
log-bin = mysql-bin
# 设置不要复制的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
# 设置要复制的数据库
binlog-do-db=mydb
# 设置logbin格式
binlog_format = STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制文件；
log-slave-updates
# 表示自增长字段每次递增的量，指自增字段的起始值，默认值为1，取值范围为1~65535;
auto-increment-increment = 2
# 表示自增字段从哪个数开始，一次递增多少，取值范围为1~65535;
auto-increment-offset = 1


# Master 2
server-id = 3
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=mydb
binlog_format=STATEMENT
log-slave-updates
auto-increment-increment=2
auto-increment-offset=2


# Slave 1
# 指定从服务器唯一ID
 server-id = 2
# 启用中继日志
  relay-log = mysql-relay


# Slave 2
server-id=4
relay-log=mysql-relay

　　Step2：重新启动mysql服务；

　　Step3：关闭四台主机的防火墙；

　　Step4：在两台主机上建立账户并授权slave；

# Master 1
create user 'slave'@'%' identified by 'lcl19960822';
alter user 'slave'@'%' identified with mysql_native_password by 'lcl19960822';
grant replication slave on *.* to 'slave'@'%';


# Master 2
create user 'slave2'@'%' identified by 'lcl19960822';
alter user 'slave2'@'%' identified with mysql_native_password by 'lcl19960822';
grant replication slave on *.* to 'slave2'@'%';　　

　　Step5：在从机上配置需要复制的主机；

# Slave 1
change master to master_host='192.168.75.128',master_user='slave',master_password='lcl19960822',master_log_file='mysql-bin.000001',master_log_pos=154;


# Slave 2
change master to master_host='192.168.75.101',master_user='slave2',master_password='lcl19960822',master_log_file='mysql-bin.000001',master_log_pos=154;

　　Step6：两个主机互相复制，并启动两台主服务器的复制功能；

# Master1
change master to master_host='192.168.75.101',master_user='slave2',master_password='lcl19960822',master_log_file='mysql-bin.000001',master_log_pos=154;

# Master2
change master to master_host='192.168.75.128',master_user='slave',master_password='lcl19960822',master_log_file='mysql-bin.000001',master_log_pos=154;

8.2 修改Mycat的集群配置实现多种主从

{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "rwSepw","rwSepw2"
        ],
        "maxCon":2000,
        "name":"prototype",
        "readBalanceType":"BALANCE_ALL",
        "replicas":[
                "rwSepr","rwSepr2","rwSepw2"
        ],
        "switchType":"SWITCH"
}

9. 分库分表

9.1 分库

　　由于两台主机上的两个数据库中的表，不能进行关联查询；因此分库的原则为：有紧密关联的表应该分在一个库内，而相互之间没有关联关系的表应该分到不同的库里；

9.2 分表

　　MySQL单表存储的数据条目是存在瓶颈的，单表达到1000万条就达到了瓶颈，会影响查询的效率，需要通过水平拆分进行优化；

　　Mycat2可以在终端直接创建数据源、集群、库表，并在创建时指定分库分表。

　　Step1：添加数据源信息；

/*+mycat:createDataSource{"name":"dw0","url":"jdbc:mysql://192.168.75.128:3306？useSSL=false","user":"root","password":"lcl19960822"}*/

/*+mycat:createDataSource{"name":"dr0","url":"jdbc:mysql://192.168.75.128:3306?useSSL=false","user":"root","password":"lcl19960822"}*/

/*+mycat:createDataSource{"name":"dw1","url":"jdbc:mysql://192.168.75.100:3306?useSSL=false","user":"root","password":"lcl19960822"}*/

/*+mycat:createDataSource{"name":"dr1","url":"jdbc:mysql://192.168.75.100:3306?useSSL=false","user":"root","password":"lcl19960822"}*/

　　Step2：添加集群配置；

/*! mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]} */;

/*! mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]} */;

9.3 广播表

create table db1.travelrecord( 
    `id` int not null auto_increment primary key, 
    `name` varchar(20))
    engine = innodb charset=utf8 broadcast;

9.4 分片表

create table test( 
    id int, name varchar(20)) 
    engine=innodb default charset=utf8 
    dbpartition by mod_hash(id)  
    tbpartition by mod_hash(id) 
    tbpartitions 1 
    dbpartitions 2;

9.5 ER表

create table test( 
    id int, name varchar(20),test_id int) 
    engine=innodb default charset=utf8 
    dbpartition by mod_hash(test_id)  
    tbpartition by mod_hash(test_id) 
    tbpartitions 1 
    dbpartitions 2;

9.6 常用分片规则

　　1). MOD_HASH

　　2). RIGHT_SHIFT　　

　　3). YYYYMM、YYYYDD、YYYYWEEK、MM、DD、MMDD、WEEK

　　4). STR_HASH