MySQL--读写分离与分布式存储

前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

一、读写分离

1、什么是读写分离

在数据库集群架构中，让主库负责处理写入操作，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将数据变更同步到从库中， 也就是写操作。

2、读写分离的好处

• 分摊服务器压力，提高机器的系统处理效率
• 在写入不变，大大分摊了读取，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能
• 增加冗余，提高服务可用性，当一台数据服务器宕机后可以调整另一台从库以最快速度恢复服务

二、Mycat数据库中间件

1、了解Mycat

Mycat 是一个开源的数据库系统，但是由于真正的数据库需要存储引擎，而 Mycat 并没有存储引擎，所以并不是完全意义的数据库系统。 那么 Mycat 是什么？Mycat 是数据库中间件，就是介于数据库与应用之间，进行数据处理与交互的中间服务是实现对主从数据库的读写分离、读的负载均衡。

常见的数据库中间件有：

数据库中间件	所属
MySQL Proxy	MySQL官方
Atlas	奇虎360
DBProxy	美团点评
Amoeba	早期阿里巴巴
cober	阿里巴巴
Mycat	阿里巴巴

2、Mycat的架构：

3、配置文件详解--schema.xml

逻辑库和分表设置：

 <schema name="testdb"           # 逻辑库名称,与server.xml的一致
         checkSQLschema="false"  # 不检查sql
         sqlMaxLimit="100"       # 最大连接数
         dataNode="dn1">         # 数据节点名称
 <!--这里定义的是分表的信息-->
 </schema>

数据节点:

 <dataNode name="dn1" // 此数据节点的名称
         dataHost="localhost1" // 主机组虚拟的
         database="testdb" />  // 真实的数据库名称

主机组:

 <dataHost name="localhost1" // 主机组
         maxCon="1000" minCon="10" // 连接
         balance="0" // 负载均衡
         writeType="0" // 写模式配置
         dbType="mysql" dbDriver="native" // 数据库配置
         switchType="1" slaveThreshold="100">
 <!--这里可以配置关于这个主机组的成员信息，和针对这些主机的健康检查语句-->
 </dataHost>


 # balance 属性
 # 负载均衡类型,目前的取值有 3 种:
 1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
 2. balance="1", 全部的 readHost 与 writeHost 参与 select 语句的负载均衡,简单的说,
 当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与
 select 语句的负载均衡。
 3. balance="2", 所有读操作都随机的在 writeHost、readhost 上分发。
 4. balance="3", 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost
 不负担读压力, #注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。
 writeType 属性
 负载均衡类型
 1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切换到还生存的第二
 个writeHost,重新启动后已切换后的为准.
 2. writeType="1",所有写操作都随机的发送到配置的 writeHost,#版本1.5 以后废弃不推荐。

健康检查：

 <heartbeat>select user()</heartbeat>        # 对后端数据进行检测，执行一个sql语句，user()内部函数

读写配置：

 <writeHost host="hostM1" url="192.168.246.135:3306" user="mycat"
 password="Qf@12345!">
 <!-- can have multi read hosts -->
 <readHost host="hostS2" url="192.168.246.136:3306" user="mycat"
 password="Qf@12345!" />
 </writeHost>

了解完个部分的作用以及名称后，下面实现一份完整的配置文件：

 [root@mycat ~]# cd /usr/local/mycat/conf/
 [root@mycat conf]# cp schema.xml schema.xml.bak
 [root@mycat conf]# vim schema.xml
 <?xml version="1.0"?>
 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
 <mycat:schema xmlns:mycat="http://io.mycat/">
     <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100"
 dataNode="dn1">
     </schema>
     <dataNode name="dn1" dataHost="localhost1" database="testdb" />
     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
         <heartbeat>select user()</heartbeat>
         <!-- can have multi write hosts -->
         <writeHost host="mysql-master" url="mysql-master:3306" user="mycat" password="Qf@1234!">
         <!-- can have multi read hosts -->
         <readHost host="mysql-slave" url="mysql-slave:3306" user="mycat" password="Qf@1234!" />
         </writeHost>
     </dataHost>
 </mycat:schema>

三、实验：实现读写分离

3.1、设置server_id（主从库）

# 主库mysql135
[root@mysql135 ~]# vim /etc/my.cnf
serve_id=135
log_bin=135

# 从库mysql136
[root@mysql136 ~]# vim /etc/my.cnf
serve_id=136

# 从库mysql137
[root@mysql137 ~]# vim /etc/my.cnf
serve_id=137

[root@mysql ~]# systemctl restart mysqld

3.2、开启binlog日志，查看binlog日志信息-（主库）

[root@mysql135 ~]# mysql
mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| 135.000001 |      154 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.3、授权（主库）

# 允许用户名为"user1"，来自10.0.0.*网段的用户使用密码"123"登录到MySQL服务器，并拥有读取二进制日志的权限
mysql> grant replication slave on *.* to "user1"@"10.0.0.%" identified by "123";
mysql> flush privileges;		# 刷新权限

3.4、在从库指定主库信息

mysql> change master to master_host="10.0.0.135",master_user="user1",master_password="123",master_log_file="135.000001" ,master_log_pos=154;

3.5、启动slave

mysql> start slave;

3.6、查看从库状态，验证

mysql> show slave status\G   查看从库状态
...
   Slave_IO_Running: Yes    io线程
   Slave_SQL_Running: Yes   sql 线程

3.7、编辑配置文件

在mycat服务器上编辑schema.xml文件

[root@mycat ~]# cd /usr/local/mycat
[root@mycat conf]# cp schema.xml schema.xml.bak
[root@mycat conf]# vim schema.xml
# 不写表的话，代表可以创建任意表
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="DXFL" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="student" primaryKey="ID" type="global" dataNode="dn1" />	
	</schema>
    
	<dataNode name="dn1" dataHost="mysql135" database="db1" />
    
	<dataHost name="mysql135" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="mysql135" url="mysql135:3306" user="user1" password="123">
  		<!-- can have multi read hosts -->
		<readHost host="mysql136" url="mysql136:3306" user="user1" password="123" />
        <readHost host="mysql137" url="mysql137:3306" user="user1" password="123" />
		</writeHost>
	</dataHost>
    
</mycat:schema>

3.8、启动mycat

[root@mycat conf]# /usr/local/mycat/bin/mycat start
[root@mycat conf]# netstat -tunlp |grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      52918/java 

3.9、验证读写分离

在真实的 master 数据库上给用户授权

mysql> create database db1; //创建一个测试库
mysql> use db1;
mysql> grant all on testdb.* to user1@'%' identified by '123'; /授权
mysql> flush privileges;

在客户端上远程登录上配置有mycat的中间件的主机的mysql中：

[root@client ~]# mysql -h 10.0.0.138 -P8066 -uroot -p123456

# 发现数据库有我们自定义的DXFL
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DXFL     |
+----------+
1 row in set (0.00 sec)
# 通样的，DXFL库中有我们自定义的逻辑表student，当然此表不可使用，需要我们按配置规则创建
mysql> use DXFL;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)
mysql> create table student (id int primary key,sharding_id int);
Query OK, 0 rows affected (0.02 sec)

# 实验：往创建好的student表中插入数据;
mysql> insert into student (id,sharding_id) values(1,99),(2,67),(3,45);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

接下来，在三台mysql服务器上验证

# mysql135
mysql> use db1;
mysql> select * from student;
+----+-------------+
| id | sharding_id |
+----+-------------+
|  1 |          99 |
|  4 |          67 |
|  5 |          45 |
+----+-------------+
# 因为三个库是主-从-从架构，因此mysql136与mysql137也是通用的表与结构

至此，本实验成功实现读写分离；

四、实验：实现分布式存储

1、环境准备

IP地址	充当角色	用处
10.0.0.135	mysql135	mysql数据库
10.0.0.136	mysql136	mysql数据库
10.0.0.137	mysql137	mysql数据库
10.0.0.138	mycat
10.0.0.139	client

2、实现步骤

（1）改主机名、关闭防火墙、关闭selinux（每台主机执行）、写hosts文件：

[root@135 ~]# hostnamectl set-hostname mysql135
[root@135 ~]# systemctl stop firewalld
[root@135 ~]# systemctl enable firewalld
[root@135 ~]# setenforce 0		# 临时关闭selinux
# 只在mycat服务器上编写hosts文件
[root@mycat ~]# vim /etc/hosts
192.168.1.135 mysql135
192.168.1.136 mysql136
192.168.1.137 mysql137

（2）135、136、137安装MySQL服务并授权：

# 安装完成后建库
135: 
mysql> create database db1
136:
mysql> create database db2
137: 
mysql> create database db3

# 授权
mysql> grant all on *.* to "mycat"@"10.0.0.%" identified by "123";

（3）mycat安装jdk和mycat

# 配置阿里yum
[root@mycat ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo

[root@mycat ~]# yum -y install java-1.8.0-openjdk
# 上传mycat安装包与解压
[root@mycat ~]# rz
[root@mycat ~]# tar xf xxx -C /usr/local/
[root@mycat ~]# cd /usr/local/mycat

（4）配置mycat：

server.xml：主配置文件，定义连接mycat的用户信息和逻辑库，无需修改

schema.xml：定义库和表以及读写数据库的信息

[root@mycat conf]# vim schema.xml

rule.xml：分片规则

partiton-hash-int.txt：hash-int的规则，添加一行

[root@mycat conf]# vim partition-hash-int.txt 
10000=0
10010=1
10020=2

（5）启动mycat

[root@mycat conf]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
Mycat-server is already running.
[root@mycat conf]# netstat -tunlp |grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      52918/java 

（6）验证

[root@client ~]# mysql -h 10.0.0.138 -P8066 -uroot -p123456

mysql> create database TESTDB;
mysql> use TESTDB;
mysql> create table employee(id int primary key,sharding_id int,name char(25));
mysql> insert into employee (id,sharding_id,name) values(1,10000,'zhangsan'),(2,10010,'lisi'),(3,10020,'wangwu');

# 然后分别在135/136/137中查看表
# 135:
mysql> use db1;
mysql> select * from emploee;
+----+-------------+----------+
| id | sharding_id | name     |
+----+-------------+----------+
|  1 |       10000 | zhangsan |
+----+-------------+----------+

# 136:
mysql> use db2;
mysql> select * from emploee;
+----+-------------+----------+
| id | sharding_id | name     |
+----+-------------+----------+
|  2 |       10010 |     lisi |
+----+-------------+----------+

# 137:
mysql> use db3;
mysql> select * from emploee;
+----+-------------+----------+
| id | sharding_id | name     |
+----+-------------+----------+
|  3 |       10020 |   wangwu |
+----+-------------+----------+

3、自定义库和表

（7）目前只有TESTDB库，我们可以自定义库和表，下面新建一个YJS库

# 1、修改server.xml的逻辑库
[root@mycat conf]# vim server.xml
...
<user name="root">
	<property name="password">123456</property>
	<property name="schames">TESTDB,YJS</property>
</user>
<user name="user">
	<property name="password">user</property>
	<property name="schames">TESTDB,YJS</property>
	<property name="readOnly">True</property>
</user>
...

# 2、在shecma.xml文件中添加一对schema标签
[root@mycat conf]# vim schema.xml
  <schema name="YJS" checkSQLschema="false" sqlMaxLimit="100">
            <table name="mysql" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
            <table name="docker" primaryKey="ID"  dataNode="dn1,dn2,dn3" rule="mod-long" />
    </schema>
# 因为mysql表的类型为global，因此每个节点都会存储数据
mysql> use YJS;
mysql> create table mysql (id int primary key,sharding_id int,name char(255));
mysql> insert into mysql (id,sharding_id,name) values(1,10000,'10'),(2,10010,'20'),

# 而docker表类型为mod-long，因此会计算取模值而存储到结果节点之中
mysql> create table docker (id int primary key,sharding_id int,score int);
mysql> select * from docker;
mysql> insert into docker (id,sharding_id,score) values(1,4,100),(2,5,99),(3,6,88);

4、分片规则

rule.xml 里面定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法，或者对表使用相同的算法但具体的参数不同。这个文件里面主要有 tableRule 和 function 这两个标签。

4.1、rule.xml

（1）<tableRule>标签

<tableRule name="rule1">
    <rule>
        <columns>id</columns>
        <algorithm>func1</algorithm>
    </rule>
</tableRule>

• name：属性指定唯一的名字，标识不同的表规则。内嵌的rule标签则指定对物理表中的哪一列进行拆分和使用什么路由算法
• columns：内指定要拆分的列名字

• argorihm：使用function标签中的name属性。连接表规则和具体路由算法。

（2）<function>标签

<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
</function>

• name：指定算法的名字
• class：指定路由算法具体的类名字

• property：为算法需要用到的一些属性

4.2、常用的分片规则

（1）分片枚举---sharding-by-intfile

通过在配置文件中配置可能的枚举 id，自己配置分片

rule.xml配置如下：

<tableRule name="sharding-by-intfile">
    <rule>
        <columns>region_id</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>

<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
    <property name="type">1</property>
    <property name="defaultNode">0</property>
</function> 

partition-hash-int.txt 配置：

# 这里的0,1,2等是data node index
850=0
851=1
852=2
853=3
DEFAULT_NODE=0

（2）取模---mod-long

为对分片字段求模运算

<tableRule name="mod-long">
    <rule>
        <columns>user_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">3</property>
</function>

致谢

在此，我要对所有为知识共享做出贡献的个人和机构表示最深切的感谢。同时也感谢每一位花时间阅读这篇文章的读者，如果文章中有任何错误，欢迎留言指正。 

学习永无止境，让我们共同进步！！