一佳互联
虚拟化容器,大数据,DBA,中间件,监控。

MySQL 读写分离

25 03月
作者:admin|分类:DBA运维

MySQL 读写分离

MySQL Proxy 和 MySQL Router 实现读写分离

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

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

MySQL-Proxy

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

MySQL-Router.drawio

参考文档

Mycat 2 实现读写分离

  1. 首先需要准备 MySQL 主从复制/MySQL 组复制 环境

    节点类型 服务节点IP:端口 读写类型 主从同步的数据库名
    Mycat 2 172.17.0.1:8066（宿主机）
    主节点 172.17.0.2:3306（MySQL 5.7 in Docker） 可读可写 master_slave
    从节点 172.17.0.3:3306（MySQL 5.7 in Docker） 只读 master_slave

    读写分离

  2. 安装 Java8+ 环境

  3. 在主从节点均创建给 Mycat 程序使用的用户并授权

    1 2 3 4 5 6 
    CREATE USER 'mycat'@'172.17.0.1' IDENTIFIED BY 'password'; -- MySQL 8 必须赋予的权限 GRANT XA_RECOVER_ADMIN ON *.* to 'mycat'@'172.17.0.1'; -- 视情况赋予权限 GRANT ALL PRIVILEGES ON *.* to 'mycat'@'172.17.0.1'; FLUSH PRIVILEGES;

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

    1 
    CREATE DATABASE IF NOT EXISTS `mycat`;

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

    这里为了省事，把 Mycat 服务用的 mycat 库和后面主从同步的 master_slave 库都放在了主节点上。

  5. 下载 并安装 Mycat

     1  2  3  4  5  6  7  8  9 10 11 12 
    mkdir -p /path/to/mycat2
cd /path/to/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
cd mycat
# 复制 Mycat 2 所需依赖 jar 到 mycat 的 lib 文件夹
cp ../mycat2-1.21-release-jar-with-dependencies.jar lib/
# 授予 bin 目录下所有命令可执行权限
chmod +x bin/*

  6. 配置 Mycat 原型库的数据源（datasource）信息

    这个库必须配置，否则在启动 Mycat 时会报错

    1 2 
    cd /path/to/mycat2/mycat/conf/datasources
vim prototypeDs.datasource.json

     1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 
    {
    // 数据库类型     "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    // 数据库读写类型：READ、WRITE、READ_WRITE。Mycat 对数据库需要是可读可写的     "instanceType":"READ_WRITE",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    // 数据源名称，这里不要修改     "name":"prototypeDs",
    // 数据库密码     "password":"password",
    "type":"JDBC",
    // 数据库连接     "url":"jdbc:mysql://172.17.0.2:3306/mycat?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    // 数据库用户     "user":"mycat",
    "weight":0
}

  7. 添加 master_slave 数据库的数据源信息

    1 2 3 
    cp prototypeDs.datasource.json master.datasource.json
cp prototypeDs.datasource.json slave-01.datasource.json
vim master.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_WRITE",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    // 数据源名称。在后面配置数据库集群时会用到     "name":"master",
    // 数据库密码     "password":"password",
    "type":"JDBC",
    // 主节点数据库连接     "url":"jdbc:mysql://172.17.0.2:3306/master_slave?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    // 数据库用户     "user":"mycat",
    "weight":0
}

    1 
    vim 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":"slave-01",
    // 数据库密码     "password":"password",
    "type":"JDBC",
    // 从节点数据库连接     "url":"jdbc:mysql://172.17.0.3:3306/master_slave?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    // 数据库用户     "user":"mycat",
    "weight":0
}

  8. 配置 master_slave 数据源的集群（cluster）信息

    1 2 3 4 
    cd /path/to/mycat2/mycat/conf/clusters
# 注意：这里不要删除 prototype.cluster.json，否则启动 Mycat 时会报错
cp prototype.cluster.json master-slave.cluster.json
vim master-slave.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":[
        // 主节点数据源名称         "master"
    ],
    "replicas":[
        // 从节点数据源名称         "slave-01"
    ],
    "maxCon":200,
    // 集群名称。在后面配置物理库（schema）时会用到     "name":"master-slave",
    "readBalanceType":"BALANCE_ALL",
    // NOT_SWITCH（不进行主从切换）、SWITCH（进行主从切换）     "switchType":"NOT_SWITCH"
}

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

    1 2 
    cd /path/to/mycat2/mycat/conf/schemas
vim master_slave.schema.json

    1 2 3 4 5 6 7 8 
    {
    // 物理库     "schemaName": "master_slave",
    // 指向集群，或者数据源     "targetName": "master-slave"
    // 这里可以配置数据表相关的信息，在物理表已存在或需要启动时自动创建物理表时配置此项     "normalTables": {}
}

  10. 修改 Mycat 登录用户信息

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

    1 2 3 4 5 6 7 8 
    {
    "dialect":"mysql",
    // ip 为 null，允许任意 ip 登录
    "ip":null,
    "password":"123456",
    "transactionType":"xa",
    "username":"root"
}

  11. 修改 Mycat 服务端口等信息

    1 2 
    cd /path/to/mycat2/mycat/conf
vim 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 
    {
    "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,
        "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"
        }
    }
}

  12. 启动 Mycat

     1  2  3  4  5  6  7  8  9 10 11 12 13 14 
    ./bin/mycat start

# 查看状态
./bin/mycat status
# 停止
./bin/mycat stop
# 暂停
./bin/mycat pause
# 重启
./bin/mycat restart
# 前台运行
./bin/mycat console
# 查看日志文件
tail -f /path/to/mycat2/mycat/logs/wrapper.log

  13. 使用 mysql 命令连接 Mycat

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

  14. 验证读写分离

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

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

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

    1 2 3 
    INSERT INTO test VALUES(1, 'a'); SELECT * FROM test; SELECT * FROM test;

    分别在主从节点执行如下语句，查询 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';

参考文档

更多文章推荐

历史上的今天
03月
25
浏览581 评论0
返回
目录 返回
首页
SonarQube系列一、Linux安装与部署 MySQL慢查询日志总结
↑