Mycat搭建读写分离(一主一从、双主双从)

微枫Micromaple

5628人浏览 · 2022-06-20 16:25:42

微枫Micromaple · 2022-06-20 16:25:42 发布

我们通过 Mycat 和 MySQL的主从复制配合搭建数据库的读写分离，实现 MySQL的高可用性。我们将搭建一主一从、双主双从两种读写分离模式。

一、搭建一主一从

一个主机用于处理所有写请求，一台从机负责所有读请求，架构图如下：

在这里插入图片描述

主从复制原理：

在这里插入图片描述

编号	角色	`IP`地址	机器名
1	`Master`	192.168.110.146	`ubuntu-mysql-master`
2	`Slaver`	192.168.110.147	`ubuntu-mysql-slaver`

1.1、安装 MySQL

Ubuntu 在线安装

更新仓库
```
sudo apt update
```
安装 MySQL5.7
```
sudo apt install mysql-server -y
```
安装期间会要求输入root登录密码
安装完成，查看数据库版本
```
mysql --version
```

查看数据库是否正常启动

# 查看启动状态
systemctl status mysql.service
# 停止MySQL服务
systemctl status mysql.service
# 启动MySQL服务
systemctl status mysql.service

# 登录MySQL
mysql -uroot -p123456
# 切换mysql数据库
use mysql;
# 修改允许使用root账户连接的ip为任意
update user set host='%' where user='root';
# 刷新权限
flush privileges;

无法远程连接MySQL

检查配置文件

查看端口开放情况

netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

开放端口并重新启动

# 编辑MySQL配置
vi /etc/mysql/mysql.conf.d/mysqld.cnf
#注释掉这行
# bind-address          = 127.0.0.1
# 重新启动MySQL
service mysql restart
# 查看端口监听状态
netstat -an | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN

检查防火墙

# 查看防火墙状态
systemctl status firewalld
# 关闭防火墙
systemctl stop firewalld
# 开启防火墙
systemctl start firewalld

1.2、主机配置

修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

具体配置如下：

# 主机配置-主机配置
# 主服务唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
# 设置 logbin 格式
binlog_format=STATEMENT

binlog日志三种格式

STATEMENT
ROW
MIXED

1.3、从机配置

修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

具体配置如下：

# 主机配置-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

1.4、注意事项

主机、从机配置好后需要重启MySQL 服务

# 查看启动状态
systemctl status mysql.service
# 停止MySQL服务
systemctl stop mysql.service
# 启动MySQL服务
systemctl start mysql.service
# 重启MySQL服务
systemctl restart mysql.service

主机、从机需要关闭防火墙

# 查看防火墙状态
systemctl status firewalld
# 关闭防火墙
systemctl stop firewalld
# 开启防火墙
systemctl start firewalld

在主机上建立账户并授权 slave

# 登录MySQL
mysql -uroot -p123456
# 切换数据库
use mysql;
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';

1.5、主从配置

登录主机，查询master的状态

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      430 | testdb       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

记录 File 和Position的值。

执行完次步骤后不要再操作主服务器MySQL，防止主服务器状态值发生变化

登录从机，配置需要复制的主机

复制主机的命令

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能

start slave;

如果该服务配置过主从复制，则需要先进行重置

# 停止从服务复制功能
stop slave;
# 重新配置主从
stop slave;
reset master;

查看从服务器状态

show slave status\G;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.146
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 430
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 430
              Relay_Log_Space: 523
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 41b59ebe-ea5d-11ec-9dd9-000c2930ff90
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

Slave_IO_Running、Slave_SQL_Running都为Yes，则说明主从配置成功。

1.6、验证

登录主数据库

mysql -uroot -p123456

创建数据库

create database testdb;

创建表

use testdb;
create table test_table(id int, name varchar(255));

插入数据

insert into test_table values (1, 'Micromaple');

查询数据

select * from test_table;

1.7、配置 Mycat 读写分离

修改<dataHost>的balance(负载均衡)属性，通过此属性配置读写分离的类型

balance(负载均衡)类型目前有4种：

balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的 writeHost上。
balance="1"：全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2 都参与select语句的负载均衡。
balance="2"：所有读操作都随机的在writeHost、readhost 上分发。
balance="3"：所有读请求随机的分发到readhost执行，writerHost不负担读压力。

为了能看到效果，把balance设置成2，会在两个主机之间切换查询。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="testdb" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="2"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.110.146:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.110.147:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

1.7.1、验证配置

在写主机插入数据带系统变量数据，造成主从数据不一致
```
insert into test_table values(1, @@hostname)
```
- @@hostname：主机名称
- 如果主机和从机名称一样可以修改主机名称，让主从插入数据不一致
```
hostnamectl set-hostname mysql-master
hostnamectl set-hostname mysql-slaver
```
启动Mycat
```
 ./mycat console
```

远程登录Mycat

mysql -umycat -p123456 -h192.168.110.145 -P8066

切换数据库
```
use TESTDB
```
执行查询语句，观察是否随机切换查询
```
select * from test_table;
```

二、搭建双主双从

一个主机master1用于处理所有写请求，它的从机slave1和另一台主机master2还有它的从机slave2负责所有读请求。当master1主机宕机后，master2主机负责写请求，master1，master2互为备机。架构图如下：
在这里插入图片描述

编号	角色	`IP`地址	机器名
1	`Master1`	192.168.110.146	`ubuntu-mysql-master`
2	`Slaver1`	192.168.110.147	`ubuntu-mysql-slaver`
3	`Master2`	192.168.110.148	`ubuntu-mysql-master2`
4	`Slaver2`	192.168.110.149	`ubuntu-mysql-slaver2`

2.1、双主机配置

2.2.1、主机`Master1`配置

修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

具体配置如下：

# 主机配置-主机配置
# 主服务唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
# 设置 logbin 格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1~65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，取值范围是1~65535
auto-increment-offset=1

2.2.2、主机`Master2`配置

修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

具体配置如下：

# 主机配置-主机配置
# 主服务唯一ID
server-id=3
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
# 设置 logbin 格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1~65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1~65535
auto-increment-offset=2

2.2、双从机配置

2.2.1、从机`Slaver1`配置

修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

具体配置如下：

# 主机配置-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

2.2.2、从机`Slaver2`配置

修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

具体配置如下：

# 主机配置-从机配置
# 从服务器唯一ID
server-id=4
# 启用中继日志
relay-log=mysql-relay

2.3、注意事项

主机、从机配置好后需要重启MySQL 服务

# 查看启动状态
systemctl status mysql.service
# 停止MySQL服务
systemctl stop mysql.service
# 启动MySQL服务
systemctl start mysql.service
# 重启MySQL服务
systemctl restart mysql.service

主机、从机需要关闭防火墙

# 查看防火墙状态
systemctl status firewalld
# 关闭防火墙
systemctl stop firewalld
# 开启防火墙
systemctl start firewalld

在Master1和Master2主机上建立账户并授权 slave

# 登录MySQL
mysql -uroot -p123456
# 切换数据库
use mysql;
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';

2.4、主从配置

Master1

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000012 |      154 | testdb       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

Master2

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000014 |      154 | testdb       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

分别记录记录 File 和Position的值。

执行完次步骤后不要再操作主服务器MySQL，防止主服务器状态值发生变化

登录从机，配置需要复制的主机

Slaver1复制Master1

复制主机的命令

CHANGE MASTER TO MASTER_HOST='主机1的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能

start slave;

Slaver2复制Master2

复制主机的命令

CHANGE MASTER TO MASTER_HOST='主机2的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能

start slave;

如果该服务配置过主从复制，则需要先进行重置

# 停止从服务复制功能
stop slave;
# 重新配置主从
stop slave;
reset master;

查看从服务器Slaver1和Slaver2的状态

show slave status\G;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.148
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 523
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3
                  Master_UUID: 47ed0cd2-eb8f-11ec-82bf-000c29c63f01
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

Slave_IO_Running、Slave_SQL_Running都为Yes，则说明主从配置成功。

2.5、两个主机互相复制

Master2复制Master1、Master1复制Master2

Master2复制Master1命令

CHANGE MASTER TO MASTER_HOST='主机1的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

Master1复制Master2命令

CHANGE MASTER TO MASTER_HOST='主机2的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能
```
start slave;
```
查看从服务器状态
```
show slave status\G;
```

2.6、验证

Master1主机新建库、新建表、insert记录，Master2和从机复制。

# 创建数据库testdb
create database testdb;
# 切换数据库
use testdb;
# 创建表test_table
create table test_table(id int, name varchar(255));
# 插入数据
insert into test_table values(1, 'Micromaple');

2.7、配置`Mycat`读写分离

修改<dataHost>的balance(负载均衡)属性，通过此属性配置读写分离的类型

balance(负载均衡)类型目前有4种：

balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的 writeHost上。
balance="1"：全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2 都参与select语句的负载均衡。
balance="2"：所有读操作都随机的在writeHost、readhost 上分发。
balance="3"：所有读请求随机的分发到readhost执行，writerHost不负担读压力。

为了能看到效果，把balance设置成1，会在两个主机之间切换查询。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="testdb" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.110.146:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.110.147:3306" user="root" password="123456" />
                </writeHost>
               <writeHost host="hostM2" url="192.168.110.148:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.110.149:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

writeType=0：所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个。
writeType=1：所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐。
writeHost，重新启动后以切换后的为准，切换记录在配置文件中：dnindex.properties 。
switchType：
- 1 默认值，自动切换。
- -1 表示不自动切换。
- 2基于MySQL主从同步的状态决定是否切换。

2.7.1、验证配置

在写主机Master1插入数据带系统变量数据，造成主从数据不一致

insert into test_table values(3, @@hostname)

@@hostname：主机名称

如果主机和从机名称一样可以修改主机名称，让主从插入数据不一致

hostnamectl set-hostname ubuntu-mysql-master
hostnamectl set-hostname ubuntu-mysql-slaver
hostnamectl set-hostname ubuntu-mysql-master2
hostnamectl set-hostname ubuntu-mysql-slaver2

启动Mycat
```
 ./mycat console
```

远程登录Mycat

mysql -umycat -p123456 -h192.168.110.145 -P8066

切换数据库
```
use TESTDB
```
执行查询语句，观察是否在Master2、Slaver1、Slaver2之间随机切换查询
```
select * from test_table;
```

2.8、抗风险能力

停止数据库Master1

systemctl stop mysql.service
systemctl status mysql.service

在Mycat里面插入数据依然成功，Master2自动切换为写主机

insert into test_table values(4, @@hostname);

启动数据库Master1

systemctl start mysql.service
systemctl status mysql.service

在Mycat里查询表test_table，可以看到查询语句在Master1、Slaver1、Slaver2之间随机切换查询

Master1、Master2互做备机，负责写的主机宕机，备机切换负责写操作，保证数据库读写分离高可用性。

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能