Mysql读写分离

发表于 更新于 分类于
Mysql读写分离

前言

读写分离本身是没有主从复制的，我们要配置主从复制后再配置读写分离。

现在常见的两种方式

  • MyCat
  • MySQL Router

MyCat

是基于阿里巴巴的Cobar方案优化而来，支持半自动化分片，join。为什么叫”半自动化”呢？因为需要DBA对每个表的分片策略进行配置和干涉。

MySQL Router

MySQL Proxy 和 MySQL Router 是官方提供的两个玩具，不推荐使用。

MySQL Proxy：应用程序连接 MySQL Proxy 后，MySQL Proxy 会自动将写请求和读请求分离，分别发送给 Master 和 Slave。但是官方不建议在生产环境使用 MySQL Proxy。

MySQL Router：是 MySQL Proxy 的替代方案。但是 MySQL Router 启动后，包含读端口和写端口，因此就需要应用程序自己将读和写进行分离，分别发送到 MySQL Router 相应的端口上。应用程序需要额外将读写操作进行分流，麻烦。

MyCat文档

MyCat已经废弃，新的为Mycat 2

http://mycatone.top/

https://github.com/MyCATApache/Mycat2

文档：

https://www.yuque.com/ccazhw/ml3nkf/ob0u6a

配置注意项目

  1. 本文读写分离的数据库为zdb。
  2. mycat服务和主库在同一服务器上。

JDK

官方下载JDK 网址

链接: https://pan.baidu.com/s/1COHU0dKhBb9x_wWxIwuibw 提取码: psvm

安装

1
sudo tar -zxvf jdk-8u341-linux-x64.tar.gz -C /usr/local

安装后的路径为/usr/local/jdk1.8.0_341

1
cd /etc/profile.d/

创建配置文件

1
sudo vi /etc/profile.d/jdk.sh

加入：

1
2
3
export JAVA_HOME=/usr/local/jdk1.8.0_341
export PATH=$JAVA_HOME/bin:$PATH   
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

配置立即生效

1
source /etc/profile

查询java版本

1
java -version

查看java-home

1
echo $JAVA_HOME

MyCat下载

MyCat是基于Java开发的，要安装JDK。

1
2
wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
wget http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar

百度云下载

链接：https://pan.baidu.com/s/1bgYByp0Gyv0z-xHIxrsdbg
提取码：psvm

MyCat安装

创建目录

1
mkdir -p /data/tools/

解压

1
2
3
unzip mycat2-install-template-1.21.zip -d /data/tools/

cp mycat2-1.21-release-jar-with-dependencies.jar /data/tools/mycat/lib/

查看是否成功

1
cd /data/tools/mycat/lib/

添加权限

1
2
cd /data/tools/mycat/bin
chmod +x *

服务配置

以下的配置服务只用配置一次就行。

修改 Mycat 登录用户信息

1
vi /data/tools/mycat/conf/users/root.user.json

内容如下：

1
2
3
4
5
6
7
{
  "dialect":"mysql",
  "ip":null,
  "password":"123456",
  "transactionType":"xa",
  "username":"root"
}

只需要修改用户名密码，别的不用更改。

这是远程连接Mycat使用的用户名和密码。

修改 Mycat 服务

1
2
cd /data/tools/mycat/conf/
vi server.json

内容

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
37
38
39
40
{
  "loadBalance":{
    "defaultLoadBalance":"BalanceRandom",
    "loadBalances":[]
  },
  "mode":"local",
  "properties":{},
  "server":{
    "bufferPool":{

    },
    "idleTimer":{
      "initialDelay":3,
      "period":60000,
      "timeUnit":"SECONDS"
    },
    "ip":"0.0.0.0",
    "mycatId":1,
    "port":8066,
    "serverVersion":"5.7.39-mycat-2.0",
    "reactorNumber":8,
    "tempDirectory":null,
    "timeWorkerPool":{
      "corePoolSize":0,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":2,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    },
    "workerPool":{
      "corePoolSize":1,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":1024,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    }
  }
}

主要添加了serverVersion配置

serverVersion中前面是MySQL的版本后面是Mycat的版本

查看Mysql的版本

1
select version();

mycatId是保证多个mycat公用存储节点的时候必须配置这个值,并且唯一,他用于生成序列号,Xid等.

原型库连接

只用设置一次。

在主节点创建 Mycat 使用的数据库 mycat

1
CREATE DATABASE IF NOT EXISTS mycat;

这个库称为 Mycat 的原型库（prototype），Mycat 在启动时，会自动在原型库下创建其运行时所需的数据表。

1
cd /data/tools/mycat/conf/datasources

把mycat带的数据源配置正确

修改配置文件

1
vi prototypeDs.datasource.json

内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
{
  "dbType":"mysql",
  "idleTimeout":60000,
  "initSqls":[],
  "initSqlsGetConnection":true,
  "instanceType":"READ_WRITE",
  "maxCon":1000,
  "maxConnectTimeout":3000,
  "maxRetryCount":5,
  "minCon":1,
  "name":"prototypeDs",
  "password":"123456",
  "type":"JDBC",
  "url":"jdbc:mysql://localhost:3306/mycat?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
  "user":"root",
  "weight":0
}

主要修改

1
2
3
"password":"123456",
"url":"jdbc:mysql://localhost:3306/mycat?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",

主从库添加

1
2
3
4
5
6
7
8
9
10
11
12
CREATE DATABASE IF NOT EXISTS zdb; 

use zdb;
CREATE TABLE IF NOT EXISTS `t_user`(
   `userid` INT UNSIGNED AUTO_INCREMENT,
   `username` VARCHAR(100) NOT NULL,
   `password` VARCHAR(40) NOT NULL,
   `createtime` DATETIME,
   PRIMARY KEY ( `userid` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t_user (username, password,createtime) VALUES ( "zhangsan", "123456",now());

主从配置

主从连接

这里用到的数据库

  • mycat Mycat使用的原型库
  • zdb 主库
  • zdb 从库

主库连接

新添加其他配置，

1
2
3
cd /data/tools/mycat/conf/datasources
cp prototypeDs.datasource.json zdb-master.datasource.json
vi zdb-master.datasource.json

内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
{
  "dbType":"mysql",
  "idleTimeout":60000,
  "initSqls":[],
  "initSqlsGetConnection":true,
  "instanceType":"WRITE",
  "maxCon":1000,
  "maxConnectTimeout":3000,
  "maxRetryCount":5,
  "minCon":1,
  "name":"zdb-master",
  "password":"123456",
  "type":"JDBC",
  "url":"jdbc:mysql://localhost:3306/zdb?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
  "user":"root",
  "weight":0
}

注意修改其中的

  • instanceType 配置实例只读还是读写，可选值：READ_WRITE,READ,WRITE
  • name
  • user
  • password
  • url

从库连接

编辑

1
2
cp zdb-master.datasource.json zdb-slave-01.datasource.json
vi zdb-slave-01.datasource.json

内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{
    "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    // 数据库读写类型。在数据库集群时，Mycat 对从节点都是只读的
    "instanceType":"READ",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    // 数据源名称。在后面配置数据库集群时会用到
    "name":"zdb-slave-01",
    // 数据库密码
    "password":"123456",
    "type":"JDBC",
    // 从节点数据库连接
    "url":"jdbc:mysql://172.17.0.3:3306/zdb?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    // 数据库用户
    "user":"root",
    "weight":0
}

注意修改其中的

  • instanceType
  • name
  • user
  • password
  • url

配置集群信息

1
2
3
4
cd /data/tools/mycat/conf/clusters
# 注意：这里不要删除 prototype.cluster.json，否则启动 Mycat 时会报错
cp prototype.cluster.json zdb.cluster.json
vi zdb.cluster.json

内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
  // 集群类型：SINGLE_NODE（单节点）、MASTER_SLAVE（普通主从）、GARELA_CLUSTER（garela cluster/PXC集群）等
  "clusterType":"MASTER_SLAVE",
  "heartbeat":{
    "heartbeatTimeout":1000,
    "maxRetry":3,
    "minSwitchTimeInterval":300,
    "slaveThreshold":0
  },
  "masters":[
    // 主节点数据源名称
    "zdb-master"
  ],
  "replicas":[
    // 从节点数据源名称
    "zdb-slave-01"
  ],
  "maxCon":200,
  // 集群名称。在后面配置schema时会用到
  "name":"zdb-cluster",
  "readBalanceType":"BALANCE_ALL",
  // NOT_SWITCH（不进行主从切换）、SWITCH（进行主从切换）
  "switchType":"NOT_SWITCH"
}

配置Schema

配置物理库（schema）和 Mycat 中数据源/数据源集群的关系

1
2
cd /data/tools/mycat/conf/schemas
vi zdb.schema.json

内容

1
2
3
4
5
{
    "schemaName": "zdb",
    "targetName": "zdb-cluster",
    "normalTables": {}
}

其中

  • schemaName schema名称 可以随便配置。

  • targetName 上面配置的集群的名称。

  • normalTables 这里可以配置数据表相关的信息，在物理表已存在或需要启动时自动创建物理表时配置此项。

启动Mycat

1
2
3
4
5
cd /data/tools/mycat/bin/
./mycat start

# 检测
lsof -i:8066

停止Mycat

1
2
3
4
5
cd /data/tools/mycat/bin/
./mycat stop

# 检测
lsof -i:8066

查看Mycat日志

1
2
cd /data/tools/mycat/logs
cat wrapper.log

连接MyCat

1
mysql -uroot -p -h127.0.0.1 -P8066

注意端口号是Mycat端口号8066

查看数据库

1
show databases;

验证读写分离

在主从节点均开启日志记录

1
2
3
4
5
# 把日志输出到表；开启日志记录
SET GLOBAL log_output = 'TABLE'; 
SET GLOBAL general_log = 'ON';
# 清空 mysql.general_log 日志表中的记录
TRUNCATE TABLE mysql.general_log;

在 Mycat 中分别执行插入和查询语句

1
2
3
4
show databases;
use zdb;
INSERT INTO t_user (username, password,createtime) VALUES ( "lisi", "123456",now());
SELECT * FROM t_user;

分别在主从节点执行如下语句，查询 SQL 执行历史

1
2
3
4
5
6
7
8
9
-- 可以看到主节点上有一条 INSERT 和一条 SELECT；从节点上只有一条 SELECT
SELECT event_time,
       user_host,
       thread_id,
       server_id,
       command_type,
       CAST(argument AS CHAR(500) CHARACTER SET utf8mb4) argument
FROM mysql.general_log
ORDER BY event_time DESC;

在主从节点关闭日志记录

1
2
# 把日志输出到文件（默认设置）；关闭日志记录
SET GLOBAL log_output = 'FILE'; SET GLOBAL general_log = 'OFF';