Mycat中间件实现一主一从和双主双从的读写分离

  |   0 评论   |   0 浏览   |   夜雨飘零

前言

笔者在《MySQL 数据库实现主从复制》这一篇文章中有提到读写分离这个技术,这个技术时基于主从复制之后的一种技术。在数据库主从复制中,一个主数据库有一个或者多个从数据库,我们可以对主数据库进行写入操作(insert,delete,update),对一个或者多个从数据库进行读取操作(select)。这个的操作方式,就是大量的查询请求也会分布到各个从数据库上,达到负载均衡,比如 3 个从数据库,有 9 条查询请求,那么每一个从数据库只要处理 3 条查询请求就可以了,大大减少了数据库的查询压力。

在 Mycat 中,读写分离可以说有两种,一种是一主一从,另一种是一主多从。我们分别来介绍这两种方式:

一主一从: 是一个主数据库一个从数据库,如果我们对主数据库进行写入操作,那么从数据库也会执行相应的操作。笔者把一主多从也归为这种方式,因为一主多从其实也就是增加多几个从数据库而已,具体的结构每个变动太大。在一主一从中,最大的好处是实现了读写分离,在 Mycat 的调度下,把所有写的操作都是在主数据库中,把所有的读的操作都放在从数据库中。可能有读者可能会有疑问,这中读取分离也可以使用后端代码实现,为什么要使用 Mycat 呢。主要是使用 Mycat 配置会更加简单,更容易维护,当数据库的位置发生了变化,如果是使用代码实现的,那么网站的后端代码要全部修改了,对于分布式的后端服务器来说,那工作量是非常大的。再且使用后端代码实现的读写分离也很难实现数据库的分布式。而使用 Mycat 都能解决这些问题。

如图所示:
这里写图片描述

双主双从: 这个主要是保证数据的安全,保证数据库的高可用。在这篇文章《MySQL 数据库实现主从复制》中,笔者也介绍到主从复制最大的作用就是保证数据的安全。在一主一从中,我们的从数据库在数据安全性上并没有发挥到最好,只是为了提供读写分离和查询负载均衡。当主数据库服务器挂掉了,那么就无法进行写入数据,整个数据库就无法正常工作了。所以双主双从或者多主多从的出现就是为了解决这个问题的,在双主双从模式下(M1 ->S1 , M2->S2,M1->M2,M2->M1 并且 M1 与 M2 互为主备),这种双主双从的模型,只要我们对 M1 或者 M2 任意一个主数据库插入数据,其他 3 个数据库也会作相应的改动。因为 M1 和 M2 是互为主从数据库,所以两个数据库是互相同步的,另外两个数据库是他们的从数据库,写入操作也会更新从数据库。当 M1 挂掉了,会自动启动 M2 作为该数据库的主数据库,保证了网站的正确运行。同时在正常情况下,M2,S1,S2 都参与查询的负载均衡。

如图所示:
这里写图片描述

那么下面就介绍 Mycat 中间件实现一主一从和双主双从的读写分离,在阅读下面教程之前,读者应该先阅读笔者之前的两篇文章,分别是《CentOS 下安装和使用 Mycat 实现分布式数据库》《MySQL 数据库实现主从复制》,在接下来的教程中会运用到这两篇文章的知识,所以读者要阅读并理解这两篇文件的知识。好,我们现在开始吧。

一主一从读写分离

我们一共使用 5 个虚拟机,每个机器的作用如下:

主机名 IP 地址 任务角色 数据库
node1 192.168.204.121 Mycat
node2 192.168.204.122 master1 MySQL
node3 192.168.204.123 slave1 MySQL
node4 192.168.204.124 master2 MySQL
node5 192.168.204.125 slave2 MySQL

MySQL 数据库配置

首先我们要在 node2、node3、node4 和 node5 上安装 MySQL 数据库,安装方式 可以参考《MySQL 数据库实现主从复制》的安装 MySQL 部分,这里就不展开讲了。安装完成之后要对数据库做一些配置,如下:
node2 的数据库是 node3 的数据库的主数据库,所以 node2 数据库的配置文件 /etc/my.cnf 如下:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-character-set=utf8   # 设置编码方式
lower_case_table_names=1     # 不区分字母大小写

log-bin=mysql-bin  # 开启二进制日志
server-id=1        # 设置server-id

# 不同步哪些数据库  
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库,除此之外,其他不同步  
# binlog-do-db = game  


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

node3 的数据库是 node2 上数据库的从数据库,所以不用修改太多。node3 数据库的配置文件 /etc/my.cnf 如下:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-character-set=utf8
lower_case_table_names=1

server-id=2 #设置server-id,必须唯一

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

node4 的数据库是 node5 的数据库的主数据库,所以 node4 数据库的配置文件 /etc/my.cnf 如下:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-character-set=utf8   # 设置编码方式
lower_case_table_names=1     # 不区分字母大小写

log-bin=mysql-bin  # 开启二进制日志
server-id=3        # 设置server-id

# 不同步哪些数据库  
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库,除此之外,其他不同步  
# binlog-do-db = game  


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

node5 的数据库是 node4 上数据库的从数据库,所以不用修改太多。node5 数据库的配置文件 /etc/my.cnf 如下:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-character-set=utf8
lower_case_table_names=1

server-id=4 #设置server-id,必须唯一

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

方便之后的操作,我们关闭了全部虚拟机的防火墙:

service iptables stop

全部都修改了配置文件,所以全部都要重启 MySQL 数据库:

service mysqld restart

我们进入到 node2 数据库中,输入以下命令查看相关信息:

mysql> show master status;

得到的信息如下:

mysql> show master status;
+------------------+----------+--------------+-------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+----------+--------------+-------------------------------+
| mysql-bin.000007 |      106 |              | mysql,test,information_schema |
+------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

根据 node2 这个主数据库的信息,开始配置 node3 的从数据库,进入到 node3 数据库 中,输入以下命令,注意 master_log_filemaster_log_pos 来自于上一步获取到的 node2 主数据库的信息:

mysql> change master to master_host='192.168.204.122', master_user='root', master_password='root', master_log_file='mysql-bin.000007', master_log_pos=106;

在执行上一条命令之前,要保证 slave 是关闭的,如果没用关闭,使用以下命令关闭:

mysql> stop slave;

配置完成之后,要启动 slave,执行下面的命令启动 slave:

mysql> start slave;

其中完成之后使用以下命令查看启动的情况:

mysql> show slave status\G;

正常情况下输出以下内容,要保证 Slave_IO_RunningSlave_SQL_RunningYes

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.204.122
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000007
             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: 106
              Relay_Log_Space: 407
              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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

接下来开始配置 node4 数据库中,进入到 node4 数据库并输入以下命令查看相关信息:

mysql> show master status;

得到的信息如下:

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

根据 node4 这个主数据库的信息,开始配置 node5 的从数据库,进入到 node5 数据库 中,输入以下命令,注意 master_log_filemaster_log_pos 来自于上一步获取到的 node4 主数据库的信息:

mysql> change master to master_host='192.168.204.124', master_user='root', master_password='root', master_log_file='mysql-bin.000001', master_log_pos=106;

在执行上一条命令之前,要保证 slave 是关闭的,如果没用关闭,使用以下命令关闭:

mysql> stop slave;

配置完成之后,要启动 slave,执行下面的命令启动 slave:

mysql> start slave;

其中完成之后使用以下命令查看启动的情况:

mysql> show slave status\G;

正常情况下输出以下内容,要保证 Slave_IO_RunningSlave_SQL_RunningYes

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.204.124
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        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: 106
              Relay_Log_Space: 407
              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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

Mycat 的配置

关于 Mycat 的安装可以参考《CentOS 下安装和使用 Mycat 实现分布式数据库》安装和配置 Mycat,这里就不再介绍 Mycat 的安装了,这一部分主要是介绍 Mycat 配置读写分离。

安装完成之后,开始配置 schema.xml 文件,我们的读写分离主要是在这里配置的。

vim $MYCAT_HOME/conf/schema.xml

该配置文件如下,我们创建了两个数据库,分别是 yeyupiaoling1yeyupiaoling2,主要是分布式数据库,数据采用分片存储。这个两个数据库都有同一张 employee 表,也可以配置多个表。我们的读写分离主要是在 dataHost 里配置。主要的配置有三个 balance="1" writeType="0" switchType="1",它们的作用分别是:

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", 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有, 1.3 没有。

writeType 属性,负载均衡类型,目前的取值有 3 种:

  • writeType="0",所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个 writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
  • writeType="1",所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐。
  • writeType="2",官方文档没有介绍。

switchType 属性:

  • -1 表示不自动切换
  • 1 默认值,自动切换
  • 2 基于 MySQL 主从同步的状态决定是否切换

下面就是 schema.xml 文件全部配置信息:

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

    <!-- 设置表的存储方式.schema name="JamesMycatSchema" 与 server.xml中的 JamesMycatSchema 设置一致  -->
    <schema name="JamesMycatSchema" checkSQLschema="false" sqlMaxLimit="100">
        <table name="employee" primaryKey="ID" dataNode="dn2,dn3" rule="sharding-by-intfile" />
    </schema>

    <!--数据节点dn1,对应的主机c1,对应是数据库db1 -->
    <dataNode name="dn2" dataHost="node2" database="yeyupiaoling1" />
    <dataNode name="dn3" dataHost="node4" database="yeyupiaoling2" />

    <!-- 主机C2-->
    <dataHost name="node2" maxCon="1000" minCon="10" balance="1" writeType="0" switchType="1"
        dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>

        <!--MySQL的写的数据,master1 -->
        <writeHost host="hostM2" url="192.168.204.122:3306" user="root" password="root">
            <!--MySQL读的数据库,slave1 -->
            <readHost host="hostM3" url="192.168.204.123:3306" user="root" password="root"/>
        </writeHost>
    </dataHost>

    <!-- 主机C3-->
    <dataHost name="node4" maxCon="1000" minCon="10" balance="1" writeType="0" switchType="1"
        dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>

        <!--MySQL的写的数据,master2 -->
        <writeHost host="hostM4" url="192.168.204.124:3306" user="root" password="root">
            <!--MySQL读的数据库,slave2 -->
            <readHost host="hostM5" url="192.168.204.125:3306" user="root" password="root"/>
        </writeHost>
    </dataHost>

</mycat:schema>

然后配置 server.xml,这里主要是配置 Mycat 的登录账户和密码的,其中 schemas 的值要对应 schema.xml 中的 schemaname 的值。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
                <property name="defaultSqlParser">druidparser</property>
        </system>

        <user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">JamesMycatSchema</property>
        </user>
</mycat:server>

配置完成之后,可以重启 Mycat:

mycat restart

启动之后,可以分别查看输出的日志信息,观察是否正常启动了:

tail -f $MYCAT_HOME/logs/wrapper.log
tail -f $MYCAT_HOME/logs/mycat.log

测试

正常其中之后,可以测试整个数据库是否符合了分布式切片存储和读写分离。笔者使用的是 NavicatForMySQL 连接数据库,读者可以使用自己熟悉的连接工具,或者是直接在命令行上直接操作。

首先连接 node2 数据库并在上面创建一个 yeyupiaoling1 数据库:
这里写图片描述

然后在 node2 的 yeyupiaoling1 数据库上创建一张 employee 表:

create table employee(id int not null primary key,name varchar(100),sharding_id int not null);

可以在新建查询上执行这个 SQL 语句,其中字段 sharding_id 非常重要,我们选择的规则就是 Mycat 是根据这个字段来分片存储数据库。
这里写图片描述

同样连接 node4 数据库并在上面创建一个 yeyupiaoling2 数据库:
这里写图片描述

然后在 node4 的 yeyupiaoling2 数据库上创建一张 employee 表,同样的执行方式,就不重复介绍了。

create table employee(id int not null primary key,name varchar(100),sharding_id int not null);

这里写图片描述

然后连接 node1 的 Mycat,记得 Mycat 默认的端口是 8066,账号和密码是 server.xml 中设置的。连接之后可以看到一个 JamesMycatSchema 数据库,这个就是我们在 schema.xml 配置的,可以看到有一张 employee 表,我们新建查询在这里插入一些数据:

insert into employee(id,name,sharding_id) values(1, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(2, 'I am db2',10010);
insert into employee(id,name,sharding_id) values(3, 'I am db3',10010);
insert into employee(id,name,sharding_id) values(4, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(5, 'I am db2',10010);
insert into employee(id,name,sharding_id) values(6, 'I am db3',10010);
insert into employee(id,name,sharding_id) values(7, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(8, 'I am db2',10010);
insert into employee(id,name,sharding_id) values(9, 'I am db3',10010);
insert into employee(id,name,sharding_id) values(10, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(11, 'I am db2',10010);
insert into employee(id,name,sharding_id) values(12, 'I am db3',10010);
insert into employee(id,name,sharding_id) values(13, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(14, 'I am db2',10010);
insert into employee(id,name,sharding_id) values(15, 'I am db3',10010);
insert into employee(id,name,sharding_id) values(16, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(17, 'I am db2',10010);
insert into employee(id,name,sharding_id) values(18, 'I am db3',10010);

这里写图片描述

在 Mycat 中可以可以看到 employee 表已经插入的全部数据。
这里写图片描述

然后在 node2 和 node3 的数据库中都可以看到插入的数据库,根据分片规则存储方式,这里是就是这个数据库存储的数据。
这里写图片描述

同样在 node4 和 node5 上也存储了另一部分的数据。
这里写图片描述
从上面来看,分布式存储和主从复制已经没有问题了,那么我们如何支持是不是读写分离呢,那么我们接下来就测试一下是不是读写分离。

首先我们把 node5 数据库的数据库全部清空,也就是 node4 的从数据库。
这里写图片描述

node5 的数据库的数据清空了,但 node5 的主数据库 node4 数据库并没清空,数据还是存在的。如果在 Mycat 上查询不到 node4 数据库的数据,那么就证明在查询的时候只是查询的从数据库的数据,也就是在 schema.xmldataHost 配置 dataHost 指定的数据库。
这里写图片描述

经过在 Mycat 上查询,发现结果如预想的一样,所以证明了是读写分离的。
这里写图片描述

双主双从读写分离

在这一部分,我们来实现双主双从读写分离,主要在上一步的基础之上做一些修改。在上一部分的一主一从读写分离中,M1->S1,M2->S2,但是 M1 和 M2 是没有关系的。而在双主双从上 M1 和 M2 是互为主从关系,它们各自是对方的主从数据库,这样的话,无论是 M1 或者 M2 发生数据变化,其他的数据库也会发生相应的变化。在开始之前,先清空数库之前的数据,保证各个数据库一致。

MySQL 数据库配置

在上一部分的 MySQL 的配置基础上,这部分的修改并不多,但为了读者更清楚了解各个配置文件的内容,笔者将会展示所有的配置信息:

node2(master1)的 MySQL 配置文件 /etc/my.cnf,主要是增加了 log-slave-updates,增加这个的作用主要是为了当 node2(master1)的数据库作为 node4(master2)的从数据库时,node4(master2)的数据发生更改时,作为从数据库的 node2(master1)也会发生相应改变,但是如果没有添加 log-slave-updates 的话,作为从数据库的 node2(master1)在跟着 node4 进行写入操作时,这个写入操作 node2(master1)并不会添加到 bin 二进制文件中,所以 node2(master1)的从数据库 node3(slave1)就不会跟着改变,这样的话就 node3 就不会跟 node4(master2)的数据同步。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-character-set=utf8   # 设置编码方式
lower_case_table_names=1     # 不区分字母大小写

log-bin=mysql-bin  # 开启二进制日志
server-id=1        # 设置server-id
log-slave-updates  # 在作为从数据库的时候,有写入操作也要更新二进制日志文件

# 不同步哪些数据库  
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库,除此之外,其他不同步  
# binlog-do-db = game  


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

node3 数据库的配置文件 /etc/my.cnf,这个配置不用做修改:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-character-set=utf8
lower_case_table_names=1

server-id=2 #设置server-id,必须唯一

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

node4 的配置跟 node2 一样,主要不同的是 server-id

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-character-set=utf8   # 设置编码方式
lower_case_table_names=1     # 不区分字母大小写

log-bin=mysql-bin  # 开启二进制日志
server-id=3        # 设置server-id
log-slave-updates  # 在作为从数据库的时候,有写入操作也要更新二进制日志文件

# 不同步哪些数据库  
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库,除此之外,其他不同步  
# binlog-do-db = game  


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

node5 数据库的配置文件 /etc/my.cnf,也是一样只要修改 server-id

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-character-set=utf8
lower_case_table_names=1

server-id=4 #设置server-id,必须唯一

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

node2 的 MySQL 的信息如下:

mysql> show master status;
+------------------+----------+--------------+-------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+----------+--------------+-------------------------------+
| mysql-bin.000008 |      106 |              | mysql,test,information_schema |
+------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

配置 node2 的从数据库,需要配置的是 node3 和 node4 的数据库,配置如下:

首先要关闭 slave:

mysql> stop slave;

配置信息,注意 master_log_filemaster_log_pos 的值:

mysql> change master to master_host='192.168.204.122', master_user='root', master_password='root', master_log_file='mysql-bin.000008', master_log_pos=106;

然后启动 slave:

mysql> start slave;

node4 的 MySQL 的信息如下:

mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+-------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+----------+--------------+-------------------------------+
| mysql-bin.000002 |      106 |              | mysql,test,information_schema |
+------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

配置 node4 的从数据库,需要配置的是 node2 和 node5 的数据库,配置如下:

首先要关闭 slave:

mysql> stop slave;

配置信息,注意 master_log_filemaster_log_pos 的值:

mysql> change master to master_host='192.168.204.124', master_user='root', master_password='root', master_log_file='mysql-bin.000002', master_log_pos=106;

然后启动 slave:

mysql> start slave;

配置完成就可以查看各个数据库的配置信息了,观察是否配置成功了,以下是 node2 的配置信息:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.204.124
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000002
             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: 106
              Relay_Log_Space: 407
              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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

然后我们可以使用 NavicatForMySQL 连接 node2 数据库,我们可以测试只对 master1 数据库执行写操作,其他的数据库是不是也会做相应的改变。

比如我们在 node2 的数据库创建一个 yeyupiaoling 数据库:
这里写图片描述

然后在这个数据库上创建一张 employee 表:

create table employee(id int not null primary key,name varchar(100),sharding_id int not null);

然后我们发现其它的数据库也会做相应的改变:
这里写图片描述

证明了我们的配置是正确的。

Mycat 的配置

我们首先配置 schema.xml

vim $MYCAT_HOME/conf/schema.xml

配置信息如下:

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

    <!-- 设置表的存储方式.schema name="JamesMycatSchema" 与 server.xml中的 JamesMycatSchema 设置一致  -->
    <schema name="JamesMycatSchema" checkSQLschema="false" sqlMaxLimit="100">
        <table name="employee" primaryKey="ID" dataNode="dn2" rule="sharding-by-intfile" />
    </schema>

    <!--数据节点dn1,对应的主机c1,对应是数据库db1 -->
    <dataNode name="dn2" dataHost="node2" database="yeyupiaoling" />

    <!-- 主机C2-->
    <dataHost name="node2" maxCon="1000" minCon="10" balance="1" writeType="0" switchType="1"
        dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>

        <!--MySQL的写的数据,master1 -->
        <writeHost host="hostM2" url="192.168.204.122:3306" user="root" password="root">
            <!--MySQL读的数据库,slave1 -->
            <readHost host="hostM3" url="192.168.204.123:3306" user="root" password="root"/>
        </writeHost>

        <!--MySQL的写的数据,master2 -->
        <writeHost host="hostM4" url="192.168.204.124:3306" user="root" password="root">
            <!--MySQL读的数据库,slave2 -->
            <readHost host="hostM5" url="192.168.204.125:3306" user="root" password="root"/>
        </writeHost>

    </dataHost>

</mycat:schema>

然后配置 server.xml

vim $MYCAT_HOME/conf/server.xml

配置信息如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
                <property name="defaultSqlParser">druidparser</property>
        </system>

        <user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">JamesMycatSchema</property>
        </user>
</mycat:server>

配置 partition-hash-int.txt

vim $MYCAT_HOME/conf/partition-hash-int.txt

因为我们选择的分片规则是 sharding-by-intfile,所以 Mycat 会根据 sharding_id 进行分片存储到不同的数据库节点的上,因为我们上面配置的数据库只是配置了一个数据库节点,所以这个文件只是配置了一个:

10000=0

配置完成之后,要重新启动 Mycat:

mycat restart

启动之后,可以分别查看输出的日志信息,观察是否正常启动了:

tail -f $MYCAT_HOME/logs/wrapper.log
tail -f $MYCAT_HOME/logs/mycat.log

正常启动启动之后,测试是否一切正常了。

测试

首先我们连接 Mycat,然后在 Mycat 上对 employee 表添加数据:

insert into employee(id,name,sharding_id) values(1, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(2, 'I am db2',10000);
insert into employee(id,name,sharding_id) values(3, 'I am db3',10000);
insert into employee(id,name,sharding_id) values(4, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(5, 'I am db2',10000);
insert into employee(id,name,sharding_id) values(6, 'I am db3',10000);
insert into employee(id,name,sharding_id) values(7, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(8, 'I am db2',10000);
insert into employee(id,name,sharding_id) values(9, 'I am db3',10000);
insert into employee(id,name,sharding_id) values(10, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(11, 'I am db2',10000);
insert into employee(id,name,sharding_id) values(12, 'I am db3',10000);
insert into employee(id,name,sharding_id) values(13, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(14, 'I am db2',10000);
insert into employee(id,name,sharding_id) values(15, 'I am db3',10000);
insert into employee(id,name,sharding_id) values(16, 'I am db1',10000);
insert into employee(id,name,sharding_id) values(17, 'I am db2',10000);
insert into employee(id,name,sharding_id) values(18, 'I am db3',10000);

操作如下:
这里写图片描述

然后我们连接 4 个数据库,查看它们的数据,发现是跟预期的一样,每个数据库都插入了数据:
这里写图片描述

读写分离在上一部分已经测试过了,这里主要是测试一下查询负载均衡,这里笔者删除了 node5(slave2)的 id 为 10 之后的数据:
这里写图片描述

node3(slave1)数据库的删除 id 为 10 之前的:
这里写图片描述

node4(master2)的数据库的数据不做修改:
这里写图片描述

然后我们在 Mycat 上不断进行查询操作,观察查询到的结果,就可以判断是不是 slave1、master2、slave2 都查询。从查询的结果来看,的确 slave1、master2、slave2 都查询。
这里写图片描述

这里写图片描述

这里写图片描述

然后测试高可用作用,我们手动关闭 node2(master1)数据库,看看是不是自动启用 node4(master2)作为主数据库了。当我们关闭了 node2(master1)的数据库,发现 Mycat 还能正常工作,但是无论我们怎么刷新查询操作,都只能查询到一下的数据,这个是 node5(slave2)数据库中的数据。这种情况证明了两件事情,一是当 node2(master)数据库挂掉了,会自动启用 node4(master2)作为主数据库,二是如果主数据库挂掉了,其的从数据库也不会参与查询操作,这样保证了数据的一致性。

关闭数据库命令:

service mysqld stop

这里写图片描述

然后我们在 node4 数据库上插入一条数据,主要是观察当再次启动 node2 的数据库时,会不会保证数据库的数据一致。

insert into employee(id,name,sharding_id) values(20, 'hello',10000);

这里写图片描述

然后我们再启动 MySQL 数据库,观察到在 node4 添加的数据也被添加到这里了。

启动数据库命令:

service mysqld start

这里写图片描述

最后有一点要说的是,在真实的项目中,不应该对从数据库(slave)做写入操作,这样会破坏数据的一致性的。上面笔者的操作主要是为了测试使用的。

参考资料

  1. https://blog.csdn.net/ydyang1126/article/details/70224185
  2. https://www.cnblogs.com/biglittleant/p/7059569.html
  3. https://blog.csdn.net/qq_33200967/article/details/80939040
  4. https://blog.csdn.net/qq_33200967/article/details/80863305

标题:Mycat中间件实现一主一从和双主双从的读写分离
作者:夜雨飘零
地址:https://blog.doiduoyi.com/articles/1584972913914.html

评论

发表评论