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 status\G #查看状态 ,有两个 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; # 但是此时可以读