MySQL之读写分离

简介

作用

• MySQl 作为目前世界上使用最广泛的免费数据库。但在实际的生产环境中，由单台 MySQL 作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。
• 因此，一般来说都是通过 主从复制（ Master-Slave）的方式来同步数据，再通过读写分离（ MySQL-Proxy/Amoeba）来提升数据库的并发负载能力，这样的方案来进行部署与实施的

底层原理

• 主数据库实现事务增删改
• 从数据库实现查
• 数据库复制将主服务器中事物的的变更同步到从数据库中

优点

• 面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载
• 主从只负责各自的写和读，极大程度的缓解 X 锁和 S 锁争用
• 从库可配置 myisam 引擎，提升查询性能以及节约系统开销
• 增加冗余，提高可用性

MyCAT

作用

• 一个彻底开源的，面向企业应用开发的大数据库集群
• 支持事务、 ACID、可以替代 MySQL 的加强版数据库
• 一个可以视为 MySQL 集群的企业级数据库，用来替代昂贵的 Oracle 集群
• 一个融合内存缓存技术、 NoSQL 技术、 HDFS 大数据的新型 SQL Server
• 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

基础环境配置

MyCat 有提供编译好的安装包，支持 Windows、 Linux、 Mac、 Solaris 等系统上安装与运行。官方下载主页 http://www.mycat.io

jdk

 # 解压到/usr/local     
[root@MyCAT ~]# tar -xvf jdk-8u191-linux-x64.tar.gz -C /usr/local/

# 配置jdk的环境变量
[root@MyCAT ~]# vim /etc/profile.d/java.sh
      JAVA_HOME=/usr/local/jdk1.8.0_191
      PATH=$JAVA_HOME/bin:$PATH
      CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
      export PATH JAVA_HOME CLASSPATH
[root@MyCAT ~]# source /etc/profile.d/java.sh      # 使配置文件生效

[root@MyCAT local]# java -version      # 配置完成
      java version "1.8.0_191"
      Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
      Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

mycat

[root@MyCAT ~]# tar -xvf  Mycat-server-1.5-RELEASE-20160301083012-linux.tar.gz -C /usr/local/

# 配置mycat的专属用户以及权限
[root@MyCAT local]# cd /usr/local/      # 进入mycat的解压目录
[root@MyCAT local]# ls /usr/local/mycat/      # 查看当前目录
      bin  catlet  conf  lib  logs  version.txt
[root@MyCAT local]# useradd mycat && passwd mycat      # 给mycat专门创建一个用户

[root@MyCAT local]# ll /usr/local/mycat/
    总用量 12
    drwxr-xr-x. 2 root root  190 7月  26 19:51 bin
    drwxrwxrwx. 2 root root    6 12月 13 2015 catlet
    drwxrwxrwx. 2 root root 4096 7月  26 19:51 conf
    drwxr-xr-x. 2 root root 4096 7月  26 19:51 lib
    drwxrwxrwx. 2 root root    6 12月 13 2015 logs
    -rwxrwxrwx. 1 root root  217 3月   1 2016 version.txt

[root@MyCAT local]# chown -R mycat:mycat /usr/local/mycat/      # 修改属主/组
[root@MyCAT local]# ll /usr/local/mycat/
    总用量 12
    drwxr-xr-x. 2 mycat mycat  190 7月  26 19:51 bin
    drwxrwxrwx. 2 mycat mycat    6 12月 13 2015 catlet
    drwxrwxrwx. 2 mycat mycat 4096 7月  26 19:51 conf
    drwxr-xr-x. 2 mycat mycat 4096 7月  26 19:51 lib
    drwxrwxrwx. 2 mycat mycat    6 12月 13 2015 logs
    -rwxrwxrwx. 1 mycat mycat  217 3月   1 2016 version.txt


# 配置mycat的环境变量
[root@MyCAT ~]# vim /etc/profile.d/mycat.sh
   MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH
[root@MyCAT ~]# source /etc/profile.d/mycat.sh

[root@MyCAT ~]# mycat start      # 启动mycat

DNS映射

# 如果是在多台 Linux 系统中组建的 MyCAT 集群，那需要在 MyCAT Server 所在的服务器上配置对其他 IP 和主机名的映射，配置方式如下：
[root@MyCAT ~]# vim /etc/hosts
      10.1.1.1 test1.cn
      10.1.1.2 test2.cn
      10.1.1.5 test5.cn

配置mycat

server.xml

mycat的账户信息以及号授权信息

[root@MyCAT ~]# cd  /usr/local/mycat/conf/      # 进入mycat的配置文件

[root@MyCAT conf]# cp server.xml server.xml.bak      # 备份server.xml 防止后期出错

[root@MyCAT conf]# echo " " > server.xml      # 清空server.xml的内容 添加我们自己书写的内容
[root@MyCAT conf]# vim server.xml
    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
        <property name="defaultSqlParser">druidparser</property>
</system>

# 以下设置为应用帐号权限
<user name="root">
        <property name="password">root123</property>
        <property name="schemas">test</property>      # 逻辑的虚拟库 可以映射到mysql中真正的数据库
</user>

# 以下设置为应用只读帐号权限
<user name="user">
        <property name="password">user</property>
        <property name="schemas">test</property>
        <property name="readOnly">true</property>
</user>
</mycat:server>

schema.xml

对数据库的操作

[root@MyCAT conf]# cp schema.xml schema.xml.bak      # 备份

[root@MyCAT conf]# echo "" > schema.xml      # 清空原有信息

[root@MyCAT conf]# vim schema.xml
      
      <?xml version="1.0"?>
      <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
      <mycat:schema xmlns:mycat="http://org.opencloudb/">
	<schema name="test" checkSQLschema="false" sqlMaxLimit="100"dataNode='dn1'></schema>
	<dataNode name="dn1" dataHost="dthost" database="test"/>
	<dataHost name="dthost" maxCon="500" minCon="10" balance="1"
	writeType="0" dbType="mysql" dbDriver="native" switchType="-1"
	slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<writeHost host="test1.cn" url="10.1.1.1:3306" user="mycat"
	password="root123">
	</writeHost>
	<writeHost host="test2.cn" url="10.1.1.2:3306" user="mycat"
	password="root123">
	</writeHost>
	</dataHost>
      </mycat:schema>

scheme参数解释

schema 标签用于定义 MyCat 实例中的逻辑库， name:后面就是逻辑库名 MyCat 可以有多个逻辑库，每个逻辑库都有自己的相关配置。可以使用 schema 标签来划分这些不同的逻辑库。
checkSQLschema 这个属性默认就是 false，官方文档的意思就是是否去掉表前面的数据库的名称， ”select * from db1.testtable” ，设置为 true 就会去掉 db1。但是如果 db1 的名称不是

schema 的名称，那么也不会被去掉，因此官方建议不要使用这种语法。同时默认设置为 false。
sqlMaxLimit 当该值设置为某个数值时。每条执行的 SQL 语句，如果没有加上 limit 语句，
MyCat 也会自动的加上所对应的值。例如设置值为 100，执行”select * from test_table”,则效果“selelct * from test_table limit 100”.
dataNode 标签定义了 MyCat 中的数据节点，也就是我们通常说所的数据分片。一个dataNode 标签就是一个独立的数据分片.
Name 定义数据节点的名字，这个名字需要是唯一的
dataHost 该属性用于定义该分片属于哪个数据库实例
Database 该属性用于定义该分片属性哪个具体数据库实例上的具体库
dataHost 该标签定义了具体的数据库实例、读写分离配置和心跳语句
name 标签 说明 唯一标识 dataHost 标签，供上层的标签使用
maxCon 指定每个读写实例连接池的最大连接。
minCon 指定每个读写实例连接池的最小连接，初始化连接池的大小。
balance

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

0:不开启读写分离机制，所有读操作都发送到当前可用的 writeHost上。

1:全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。

2:所有读操作都随机的在 writeHost、 readhost 上分发。

3:所有读请求随机分发到 wiriterHost 对应的 readhost 执行。writerHost 不负担读压
writeType

1:writeType=”0” , 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个 writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
2:writeType=”1” ，所有写操作都随机的发送到配置的 writeHost， 1.5 以后废弃不推荐。默认 0 就好了！
switchType

-1:表示不自动切换；

1:默认值，自动切换；

2:基于 MySQL主从同步的状态决定是否切换心跳语句为 show slave status “3” 基于 MySQL alary cluster 的切换机制（适合集群）（ 1.4.1）心跳语句为 show status like 'wsrep%'
dbType

指定后端连接的数据库类型，目前支持二进制的 mysql 协议，还有其他使用JDBC 连接的数据库。例如： mongodb、 oracle、 spark 等.
dbDriver

指定连接后端数据库使用的 Driver，目前可选的值有 native 和 JDBC。使用native 的话，因为这个值执行的是二进制的 mysql 协议，所以可以使用 mysql和 maridb。其他类型的数据库则需要使用 JDBC 驱动来支持。
heartbeat 标签指明用于和后端数据库进行心跳检查的语句。
writeHost /readHost 这两个标签都指定后端数据库的相关配置，用于实例化后端连接池。唯一不同的是， writeHost 指定写实例、 readHost 指定读实例。 在一个 dataHost 内可以定义多个

writeHost 和 readHost。但是，如果 writeHost 指定的后端数据库宕机，那么这个 writeHost 绑定的所有 readHost 都将不可用。另一方面，由于这个 writeHost 宕机，系统会自动的检测到，并切

换到备用的 writeHost 上去。这两个标签的属性相同.
url 后端实例连接地址。 Native：地址：端口 JDBC： jdbc 的 url
password 后端存储实例需要的密码
user 后端存储实例需要的用户名字
weight 权重 配置在 readhost 中作为读节点的权重

mycat启动

[root@MyCAT ~]# /usr/local/mycat/bin/mycat start

[root@MyCAT ~]# cat /usr/local/mycat/logs/wrapper.log | grep successfully      # 出现successfully表示成功了
      INFO   | jvm 2    | 2020/07/26 20:16:32 | MyCAT Server startup <successfully>. see logs in logs/mycat.log

MySQL主从

master

[root@master~]# mysql -uroot -p

# 创建数据库
mysql> create database test;
mysql> use test;
mysql> create table test(id int,name varchar(254));

# 授权给 mycat 登陆数据库使用的帐号
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "root123";
mysql> grant replication slave on *.* to slave@"10.1.1.%" identified by "root123";
mysql> flush privileges;

[root@master~]# vim /etc/my.cnf
      server-id=1
      log-bin=master-log-bin
      binlog-do-db=test
      binlog-ignore-db=mysql
[root@master~]# systemctl restart mysqld

# 数据库导出
[root@master~]#  mysqldump -uroot -p -B test>test.sql
[root@master~]# scp test.sql 10.1.1.2:/root/      # 传递给slave

slave

[root@slave~]# mysql -uroot -p < test.sql      # 导入表结构

[root@slave~]# vim /etc/my.cnf
      server-id=2
[root@slave~] systemctl restart mysqld

[root@slave~]# mysql -uroot -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "root123";

mysql> flush privileges;

mysql> stop slave; #停止 slave

mysql> change master tomaster_host='10.1.1.1',master_user='slave',master_password='root123';

mysql> start slave; #启动 slave

mysql> show slave statusG #查看状态 ，有两个 yes 主从同步成功！

数据测试

模拟slave故障

[root@slave ~]# systemctl stop mysqld.service

[root@client~]# mysql -uroot -p123456 -h 10.1.1.1 -P8066      # 连接mycat
mysql> use test;
mysql> select * from test;

模拟master故障

[root@master~]# systemctl stop mysqld.service

[root@client~]# mysql -uroot -p123456 -h 10.1.1.1 -P8066      # 连接mycat

mysql> use test;

mysql> create table test1(id int);
ERROR 1184 (HY000): 拒绝连接 #主数据库停止了，是无法写操作的

mysql> select * from test;      # 但是此时可以读