【Mycat】Mycat主从复制

1. 什么是Mycat.

Mycat是数据库中间件,所谓中间件,是一类连接软件组件和应用的计算机软件,以便软件各部件之间的通信。

例如 tomcat，web的中间件。而数据库中间件是连接Java应用程序和数据库中间的软件。

2. 为什么要用Mycat

• Java与数据库的紧密耦合
• 高访问量高并发对数据库的压力
• 读写请求数据不一致

我们现在普遍的Java应用程序都是直接连接了MySQL软件进行读写操作，也就是我们在Java中的配置文件等定义了mysql的数据源，直接连接到了我们的mysql软件，但是当某些情况下我们可能需要用到了多个数据库，这个时候我们可能就需要配多个数据源去连接我们的多个数据库，这个时候我们进行sql操作的时候就会很麻烦，因为Java与数据库有了一个紧密的耦合度，但是如果我们在Java应用程序与mysql中间使用了mycat，我们只需要访问mycat就可以了，至于数据源等问题，mycat会直接帮我们搞定。

再来说一下高访问量高并发，我们都知道mysql数据库实际上在数据查询上是有一个瓶颈的，当我们的数据太多的时候，对于互联网上有高并发的请求的时候，这个时候对我们mysql的压力是非常大的，当访问量一大，就可能会出现查不出数据，响应的时间太长等，这个时候我们可能需要有多个服务器对数据库进行读写分离，以及对数据库进行集群，这个时候我们的sql语句要进行分类，哪个sql语句要访问哪个数据库，这个时候只要交给mycat就可以了。


最后说一下，使用多个数据库的时候我们就会遇到一个读写数据不一致的问题，这个时候同样mycat可以进行主从复制，保证了数据的一致性。

数据库中间件的种类:

​ 1、Cobar；2、DRDS；3、MyCat；4、Atlas；5、OneProxy。 6. Sharding-JDBC

1、读写分离

2、数据分片

垂直拆分（分库）、水平拆分（分表）

首先我们的数据库有多个表

当我们的表足够多的时候，也会造成整个数据库的瓶颈，这个时候查询是非常慢的，这个时候我们可能要对这个数据库进行垂直拆分，也就是分库

我们需要垂直拆分了表4 5 6 放到另外一个库中。

当我们垂直拆分了之后，可能又会出现单个表中的数据达到千万以上，这个时候对表造成了一个瓶颈，这个时候我们对表进行拆分。

我们可以把表的一部分数据拆分到另外的一个数据库。

原理

Mycat 的原理中最重要的一个动词是 “拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发 往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

准备环境

2台服务器—主节点192.168.179.131 从节点192.168.179.132

mysql的安装步骤
http://t.csdn.cn/dAUx2

大家准备两台mysql服务器—主节点—从节点。

搭建mysql集群–主从模式

① MySQL 主从复制原理

从上层来看，复制分成三步：

• Master 主库在事务提交时，会把数据变更作为事件Events 记录在二进制日志文件 Binlog 中。

• 主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log 。

• slave重做中继日志中的事件，将改变反映它自己的数据。

②复制优势

MySQL 复制的优点主要包含以下三个方面：

• 主库出现问题，可以快速切换到从库提供服务。

• 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。

• 可以在从库中执行备份，以避免备份期间影响主库的服务。

③搭建步骤

【1】master

1） 在master 的配置文件（/etc/my.cnf）中，配置如下内容：

#mysql 服务ID,保证整个集群环境中唯一
server-id=1

#mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin

#错误日志,默认已经开启
#log-err

#mysql的安装目录
#basedir

#mysql的临时目录
#tmpdir

#mysql的数据存放目录
#datadir

#是否只读,1 代表只读, 0 代表读写
read-only=0

#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2） 执行完毕之后，需要重启Mysql：

systemctl restart mysqld
1

3） 创建同步数据的账户，并且进行授权操作：
设置远程访问。(如果设置过允许mysql远程访问—那么该步骤可以省略)

grant replication slave on *.* to 'root'@'192.168.179.131' identified by 'root';	

flush privileges;
1
2
3

4） 查看master状态：

show master status;
1

字段含义：

File : 从哪个日志文件开始推送日志文件 
Position ： 从哪个位置开始推送日志
Binlog_Ignore_DB : 指定不需要同步的数据库
1
2
3

【2】slave

1） 在 slave 端配置文件中，配置如下内容：

#mysql服务端ID,唯一
server-id=2

#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin
1
2
3
4
5

2） 执行完毕之后，需要重启Mysql：

systemctl restart mysqld
1

主机和从机的UUID不能一致。
查询auto.cnf文件的位置

find / -name auto.cnf
1

去文件夹/var/lib/mysql将auto.cnf文件删除

rm -rf auto.cnf
1

删除就行了，下次还会自动重新创建。否则两个uuid一样，会报错。
详细参考：https://blog.csdn.net/cnds123321/article/details/117925881

3） 执行如下指令 ：

change master to master_host= '192.168.179.131', master_user='root', master_password='123456', master_log_file='mysqlbin.000002', master_log_pos=154;
1

master_host:主节点的ip

master_user:主节点账户

master_password：主节点的密码

master_log_file:主节点二进制文件名，与主节点show master status;查询的结果一致

master_log_pos：同步的位置

指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。

4） 开启同步操作

start slave;

show slave status\G;
1
2
3

5） 停止同步操作

stop slave;

reset master;
1
2
3

验证:

在主节点创建一个库—从节点也会自动创建一个相应的库

通过mycat完成读写分离

上传mycat到相应的服务器并解压

解压

tar -zxvf Mycat-server.......（文件名）
1

conf目录：

1. server.xml 配置虚拟账户和密码
2. schema.xml 定义虚拟库和真实库的对应关系
3. rule.xml分片规则

1. 修改schema.xml文件的内容

DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    	
        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
        schema>

        
        <dataNode name="dn1" dataHost="host1" database="mydb" />

    
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            
                <heartbeat>select user()heartbeat>
                
                <writeHost host="hostM1" url="192.168.179.131:3306" user="root"
                                   password="123456">
                                <readHost host="hostS1" url="192.168.179.132:3306" user="root" password="123456"/>
                writeHost>
        dataHost>

mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

其中，balance指的负载均衡类型，目前的取值有4种：

1. balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
2. **balance=“1”，**全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
3. **balance=“2”，**所有读操作都随机的在writeHost、readhost上分发。
4. **balance=“3”，**所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

一般在企业中balance设置要么是1(双主双从)要么是3(单主单从)

如果想让mycat完成sql的读写分离操作。 把balance改为3。

2. 修改server.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">

	<!-- user:定义虚拟账户
	        name: 虚拟账户的名称
	 -->
	<user name="mycat">
		<property name="password">123456</property>
		<property name="schemas">TESTDB</property>
	</user>

</mycat:server>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3. 启动mycat

bin目录下

4. java中测试

配置数据源：

spring:
  # 数据源
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    # 192.168.179.130：mycat的服务器地址 8066：mycat的默认端口 TESTDB：虚拟数据库
    url: jdbc:mysql://192.168.179.130:8066/TESTDB?serverTimezone=Asia/Shanghai
    # username：虚拟数据库名称 password：虚拟数据库密码（二者在mycat中配置）
    username: mycat
    password: 123456
1
2
3
4
5
6
7
8
9

使用时正常使用

    @Autowired
    private StudentMapper studentMapper;
    @Test
    void contextLoads() {
        List<Student> students = studentMapper.selectList(null);
        System.out.println("student = " + students);
    }
1
2
3
4
5
6
7

垂直分库

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类， 分布到不同 的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图

如何划分表

一个问题：在两台主机上的两个数据库中的表，能否关联查询？
答案：不可以关联查询。

分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。

DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
		  <table name="tbl_consumer" dataNode="dn2">table>
	schema>
	
	<dataNode name="dn1" dataHost="host1" database="orders" />
	
	<dataNode name="dn2" dataHost="host2" database="consumer" />
	
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()heartbeat>
		<writeHost host="hostM1" url="192.168.179.131:3306" user="root"
				   password="123456">
		writeHost>
	dataHost>
	
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()heartbeat>
		<writeHost host="hostM2" url="192.168.179.132:3306" user="root"
				   password="123456">
		writeHost>
	dataHost>

mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

192.168.179.131中创建orders数据库，192.168.179.132中创建consumer数据库。

要先创建好数据库，否则好像不太行（通过navicat连接mycat时打不开好像）

通过mycat来创建相应的表。必须在mycat中创建表，因为是在mycat中指定的分库。

#客户表 rows:20万
CREATE TABLE tbl_consumer(
 id INT AUTO_INCREMENT,
 NAME VARCHAR(200),
 PRIMARY KEY(id)
);
#订单表 rows:600万
CREATE TABLE tbl_orders(
 id INT AUTO_INCREMENT,
 order_type INT,
 customer_id INT,
 amount DECIMAL(10,2),
 PRIMARY KEY(id) 
); 
#订单详细表 rows:600万
CREATE TABLE tbl_orders_detail(
 id INT AUTO_INCREMENT,
 detail VARCHAR(2000),
 order_id INT,
 PRIMARY KEY(id)
);
#订单状态字典表 rows:20
CREATE TABLE tbl_dict_order_type(
 id INT AUTO_INCREMENT,
 order_type VARCHAR(200),
 PRIMARY KEY(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

指定的tbl_consumer表创建到了192.168.179.132中，没有指定的表默认在192.168.179.131