主从复制+读写分离

为什么

1. 公司数据库遇到瓶颈，考虑使用这套技术；
2. 技术预研；

技术选型

1. Mycat2
2. Mysql5.7.31
3. Docker

主从复制（一主一从）

一、 准备工作
下载Mysql镜像，使用docker安装mysql的原因：

1. 没有测试服务器
2. 虚拟机linux安装mysql麻烦
3. docker方式安装mysql简单方便

docker pull mysql:5.7.31
1

二、Mysql配置文件

创建一个master_slave文件夹建立目录结构如下:

在master和slave的文件夹conf文件中各创建一个my.cnf文件配置内容如下：

master:

[mysqld]
#启用二级制日志
log-bin=mysql-bin   
#服务id必须唯一 
server-id=1
1
2
3
4
5

slave:

[mysqld]
log-bin=slave-bin   
server-id=2 
1
2
3

三、启动主从数据库

master:

#启动主数据库
docker run -p 13306:3306 -d --name mysql-master -e MYSQL_ROOT_PASSWORD=mysql -v /Users/amxliuli/amx/learn/mysql/master_slave/master/conf/my.cnf:/etc/my.cnf mysql:5.7.31
#进入容器
docker exec -it mysql-master bash
#登录mysql
mysql -uroot -p
#输入密码
mysql

1
2
3
4
5
6
7
8
9

slave:

#启动
docker run -p 13307:3306 -d --name mysql-slave -e MYSQL_ROOT_PASSWORD=mysql -v /Users/amxliuli/amx/learn/mysql/master_slave/slave/conf/my.cnf:/etc/my.cnf mysql:5.7.31
#进入容器
docker exec -it mysql-slave bash
#登录mysql
mysql -uroot -p
#输入密码
mysql
1
2
3
4
5
6
7
8

四、Mysql主从配置

主库配置

  在主容器的mysql中输入以下命令：

# 授权所有ip可以作为主mysql的从库
mysql>GRANT REPLICATION SLAVE ON *.* to 'root'@'%' identified by 'mysql';（所有ip）

# 查看主库的数据库状态
show master status;
1
2
3
4
5

  记录File和Position的内容，后续配置从库需要；

  注意事项：

  此时不要在主库做任何操作，否则会导致状态变化

从库配置

1. 配置从库的master
# 配置从库的master
change master to
# 主库ip
master_host='x.x.x.x',
# 主库用户名
master_user='root',
# 主库的log名，即刚才主库状态记录的File
master_log_file='mysql-bin.000003',
# 主库log的位置，即刚才主库状态记录的Position
master_log_pos=1201,
# 主库端口，容器映射的端口即1
master_port=32768,
master_password='mysql';
2. 启动slave
start slave;
3. 检查从库的状态
show slave status;

其中如果 
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两项如果都为Yes说明配置成功，如果其他可以stop slave;重新配置。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

五、测试主从复制

1. 在主库中通过以下命令创建数据库，表，和插入数据

# 创建数据库
create database amx;
# 使用数据库amx
use amx;
# 创建表user
 create table user(id int(3),name char(4));
# 插入一条数据
insert into user values(1,'amx');
# 查询数据
select * from user;
1
2
3
4
5
6
7
8
9
10

2. 进入从库验证
     如果进入从库，查看是否已经存在数据库amx和表user以及插入数据，如果存在说明已经实现主从复制；

# 查看所有数据库 
show databases;
# 使用数据库amx
use amx;
# 查看数据
select * from user;
1
2
3
4
5
6

至此，MySQL的主从配置已经配置成功；
需要注意的是，主从复制是读写分离的基础，所以必须保证主从复制配置没有问题。

读写分离（一主一从）

  由于网上大多数是关于mycat1 的例子，所以想尝试一下mycat2

一、安装mycat2

# 下载安装包
wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
# 下载 Mycat 2 所需依赖 jar
wget http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
# 解压安装包
unzip mycat2-install-template-1.21.zip
# 将依赖包复制到lib下
cp mycat2-1.20-jar-with-dependencies.jar mycat/bin/

# 授予 bin 目录下所有命令可执行权限
chmod +x mycat/bin/*
1
2
3
4
5
6
7
8
9
10
11

二、配置

1. 配置数据源

  这里主要是配置mycat连接真正数据库的信息目录在/mycat/conf/datasources
1). 配置原型库的数据源信息prototypeDs.datasource.json

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

  主要是 url、user、password三个参数，需要提前在数据库中创建这样一个数据库，需要可连接。

2). 配置主库和从库的数据源信息

  首先将原型库配置文件拷贝两份，并修改名称

cp prototypeDs.datasource.json master.datasource.json
cp prototypeDs.datasource.json slave-01.datasource.json
1
2

配置主库

        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        #数据源名称，配置集群使用
        "name":"master",
        "password":"mysql",
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.100.18:13306/master_slave?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}
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",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        #数据源名称，配置集群使用
        "name":"slave-01",
        "password":"mysql",
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.100.18:13307/master_slave?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2. 配置集群

在目录/mycat/conf/clusters

1). 复制文件
将原来的集群配置文件拷贝一份并修改文件名

cp prototype.cluster.json master-slave.cluster.json
1

2). 修改配置

{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetry":3,
                "minSwitchTimeInterval":300,
                "slaveThreshold":0
        },
        "masters":[
            #主节点数据源名称
                "master"
        ],
        "replicas":[
            #从节点数据源名称
                "slave-01"
        ],
        "maxCon":200,
        #集群名称，配置schema中会使用到
        "name":"master-slave",
        "readBalanceType":"BALANCE_ALL",
        #  NOT_SWITCH（不进行主从切换）、SWITCH（进行主从切换）
        "switchType":"SWITCH"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

目录/mycat/conf/schemas

1). 创建配置文件

vim master_slave.schema.json
1

2). 修改内容

{
    #物理库，mycat连接中的表明
    "schemaName": "master_slave",
    #集群 刚才配置的集群配置文件的前缀
    "targetName": "master-slave",
    "normalTables": {}
}
1
2
3
4
5
6
7

4. Mycat用户配置

文件目录及配置文件

cd /path/to/mycat2/mycat/conf/users
vim root.user.json
1
2

只需要配置用户名和密码

{
        "dialect":"mysql",
        "ip":null,
        #密码
        "password":"123456",
        "transactionType":"xa",
        #用户名
        "username":"root"
}
1
2
3
4
5
6
7
8
9

5. Mycat服务端口配置

文件目录及文件

cd /path/to/mycat2/mycat/conf
vim server.json

1
2
3

配置内容如下：

暂时只需要配置ip和端口

  "loadBalance":{
    "defaultLoadBalance":"BalanceRandom",
    "loadBalances":[]
  },
  "mode":"local",
  "properties":{},
  "server":{
    "bufferPool":{

    },
    "idleTimer":{
      "initialDelay":3,
      "period":60000,
      "timeUnit":"SECONDS"
    },
    # 所有ip可以访问
    "ip":"0.0.0.0",
    "mycatId":1,
    # 端口
    "port":8066,
    "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"
    }
  }
}
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

6. Mycat的命令

./bin/mycat start

# 查看状态
./bin/mycat status
# 停止
./bin/mycat stop
# 暂停
./bin/mycat pause
# 重启
./bin/mycat restart
# 前台运行
./bin/mycat console

# 日志文件
tail -f logs/wrapper.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

7. 验证读写分离

1). 连接登录mycat
首先使用mysql 命令登录mycat,或者使用Navcat工具链接

mysql -uroot -p123456 -P8066 -h127.0.0.1

1
2

2). 开启主从的日志

#日志输出到表mysql.general_log
SET GLOBAL log_output = 'TABLE'; 
#打开general_log
SET GLOBAL general_log = 'ON';
1
2
3
4

3). 在mycat中执行插入和查询操作

SELECT * from user;

INSERT INTO `user` VALUES(2,'amx','123')
1
2
3

4). 查看日志

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
3
4
5
6
7
8

  通过查看日志可以看出，主库中执行insert操作，从库执行了select 操作，说明配置成功。

  这里只是配置了一个最简单的基于mysql的主从复制和读写分离的例子，里面有些配置的具体深入的用法暂时还没有过多的研究，后续在实际中使用后，再来补充。