目录

夜雨飘零

记录精彩的程序人生

X

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

前言

笔者在《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地址任务角色数据库
node1192.168.204.121Mycat
node2192.168.204.122master1MySQL
node3192.168.204.123slave1MySQL
node4192.168.204.124master2MySQL
node5192.168.204.125slave2MySQL

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中间件实现一主一从和双主双从的读写分离
作者:yeyupiaoling
地址:https://yeyupiaoling.cn/articles/1584972913914.html