Mycat搭建读写分离(一主一从、双主双从)-阿里云开发者社区

搭建读写分离

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

搭建一主一从

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

005.Mycat_搭建一主一从.jpg

主从复制原理：

006.Mycat_主从复制原理.jpg

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

安装`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

主机配置

修改配置文件/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

从机配置

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

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

具体配置如下：

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

注意事项

主机、从机配置好后需要重启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';

主从配置

登录主机，查询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，则说明主从配置成功。

验证

登录主数据库

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;

配置`Mycat`读写分离

修改的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>

验证配置

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

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互为备机。架构图如下：

007.Mycat_双主双从架构图.jpg

编号	角色	`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`

双主机配置

主机`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

主机`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

双从机配置

从机`Slaver1`配置

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

vi /etc/mysql/mysql.conf.d/mysqld.cnf
具体配置如下：
# 主机配置-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

从机`Slaver2`配置

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

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

具体配置如下：

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

注意事项

主机、从机配置好后需要重启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';

主从配置

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，则说明主从配置成功。

两个主机互相复制

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;

验证

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');

配置`Mycat`读写分离

修改的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主从同步的状态决定是否切换。

验证配置

在写主机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;

抗风险能力

停止数据库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之间随机切换查询

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

搭建读写分离

搭建一主一从

安装`MySQL`

主机配置

从机配置

注意事项

主从配置

验证

配置`Mycat`读写分离

验证配置

搭建双主双从

双主机配置

主机`Master1`配置

主机`Master2`配置

双从机配置

从机`Slaver1`配置

从机`Slaver2`配置

注意事项

主从配置

两个主机互相复制

验证

配置`Mycat`读写分离

验证配置

抗风险能力

热门文章

最新文章

相关课程

相关电子书

相关实验场景

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

搭建读写分离

搭建一主一从

安装MySQL

主机配置

从机配置

注意事项

主从配置

验证

配置Mycat读写分离

验证配置

搭建双主双从

双主机配置

主机Master1配置

主机Master2配置

双从机配置

从机Slaver1配置

从机Slaver2配置

注意事项

主从配置

两个主机互相复制

验证

配置Mycat读写分离

验证配置

抗风险能力

热门文章

最新文章

相关课程

相关电子书

相关实验场景

安装`MySQL`

配置`Mycat`读写分离

主机`Master1`配置

主机`Master2`配置

从机`Slaver1`配置

从机`Slaver2`配置

配置`Mycat`读写分离