阮胜昌的技术记录站-LinuxMySQL数据库运维

数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置。

Mycat读写分离和自动切换机制，需要mysql的主从复制机制配合。

·       MyCat的安装请参考：Linux 下 Mycat 的安装配置

·       Mysql主从复制的配置请参考：MySql 主从复制的配置（GTID 方式）

一、MyCat 的配置

从 Mycat 1.4 开始支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

方法一:不绑定主从延迟的读写分离：
读写分离的第一种配置方式

配置文件的意思是：

·      逻辑库：small。逻辑表：tb_item；

·       Master服务器上创建了一个数据库：db1；

·      Slave服务器同步Master服务器的 db1 数据库；

·      写请求都发送到Master服务器；

·      读请求都发送到Slave服务器。

[root@mycat-server conf]# vim schema.xml

 

 <?xmlversion="1.0"?>

<!DOCTYPE mycat:schema SYSTEM"schema.dtd">

<mycat:schemaxmlns:mycat="http://io.mycat/">

 

        <schemaname="hexin" checkSQLschema="false"sqlMaxLimit="10000" dataNode="dn1">

        </schema>

        <dataNodename="dn1" dataHost="localhost1" database="hexin"/>

 

        <dataHostname="localhost1" maxCon="10000" 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="192.168.67.153"url="192.168.67.153:3306" user="hexin_rw"password="Hexin123.">

                       <!-- can have multi read hosts -->

                       <readHost host="192.168.67.152"url="192.168.67.152:3306" user="hexin_r"password="Hexin123." />

               </writeHost>

        </dataHost>
</mycat:schema>

缺点： 当主库挂了,从库读不可用

附 1、读写分离的第二种配置方式

  <?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM"schema.dtd">

<mycat:schemaxmlns:mycat="http://io.mycat/">

        <schemaname="hexin" checkSQLschema="false"sqlMaxLimit="10000" dataNode="dn1">

       </schema>

        <dataNodename="dn1" dataHost="localhost1" database="hexin"/>

 

        <dataHostname="localhost1" maxCon="10000" 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="192.168.67.153"url="192.168.67.153:3306" user="hexin_rw"password="Hexin123.">

               </writeHost>

                      <writeHosthost="192.168.67.152" url="192.168.67.152:3306"user="hexin_r" password="Hexin123." />

       </dataHost>
</mycat:schema>

以上两种配置方式，第一种当主库挂了，从库读不可用，第二种当主库挂了，从库可以继续使用。另外，事务内部的一切操作都会走写节点，所以读操作不要加事务。

 

测试主库挂了：

当主库挂了，从库还可以进行读取：DML也会发送到从库，但报read-only错误

但是当主库恢复后，DML不会自动切换到主库：

解决方法：
如何让MYCAT将DML操作切换到主库的方法：

      1.主库故障修复后，将主库启动

      2.将从库停止

      3.将MYCAT 重新启动一下

      4.再将从库启动

这时MYCAT会再进行切换一次，恢复DML走主库，SELECT走从库

 

测试从库挂了：

当从库挂了后，会有三个请求超时报错，当第四个请求SELECT到来时，会直接将请求到主库上
                    

当从库故障恢复，重启后，所有的读操作会直接走从库

 

2、强制走写或读节点

如果读延时较大，我们可以使用MyCat注解的方式强制走写节点。

继续上面测试部分的第四小节：测试读写分离。接下来测试MyCat的注解方式。

图中可能看不清楚，强制走写节点的语句如下：

/*#mycat:db_type=master*/ SELECT *FROM tb_item ;

另外，测试时发现强制走读节点失败，不知道为什么，也没有深究（实在没有多少时间）：

/*#mycat:db_type=slave*/ SELECT *FROM tb_item ;

 

 

附 2、根据主从延时切换

1、根据主从延时切换的配置

1.4 开始支持 MySQL 主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

1.MyCAT 心跳检查语句配置为 show slavestatus ；

2.dataHost 上定义两个新属性：switchType="2"　和　slaveThreshold="100"，此时意味着开启 MySQL 主从复制状态绑定的读写分离与切换机制；

Mycat 心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master"，"Slave_IO_Running"，"Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master 主从复制时延。当 Seconds_Behind_Master > slaveThreshold 时，读写分离筛选器会过滤掉此Slave 机器，防止读到很久之前的旧数据。

而当主节点宕机后，切换逻辑会检查 Slave 上的Seconds_Behind_Master 是否为 0，为 0 时则表示主从同步，可以安全切换，否则不会切换。

从库只读用户要配置以下权限：主库不需要配置

mysql> grant REPLICATION CLIENT on *.* tohexin_r@&apos;%&apos;;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for hexin_r@&apos;%&apos;;

+--------------------------------------------------+

| Grants for hexin_r@%                            |

+--------------------------------------------------+

| GRANT REPLICATION CLIENT ON *.* TO&apos;hexin_r&apos;@&apos;%&apos; |

| GRANT SELECT ON `hexin`.* TO&apos;hexin_r&apos;@&apos;%&apos;       |

+--------------------------------------------------+

2 rows in set (0.00 sec)

 

vim schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM"schema.dtd">

<mycat:schemaxmlns:mycat="http://io.mycat/">

 

        <schemaname="hexin" checkSQLschema="false"sqlMaxLimit="10000" dataNode="dn1">

        </schema>

        <!-- <dataNodename="dn1$0-743" dataHost="localhost1"database="db$0-743"

                />-->

        <dataNodename="dn1" dataHost="localhost1" database="hexin"/>

 

        <dataHostname="localhost1" maxCon="10000" minCon="10"balance="1"

                         writeType="0"dbType="mysql" dbDriver="native"switchType="2"  slaveThreshold="100">

               <heartbeat>show slave status</heartbeat>

               <!-- can have multi write hosts -->

               <writeHost host="192.168.67.153"url="192.168.67.153:3306" user="hexin_rw"password="Hexin123.">

                       <!-- can have multi read hosts -->

               </writeHost>

               <writeHost host="192.168.67.152"url="192.168.67.152:3306" user="hexin_r"password="Hexin123."/>

        </dataHost>

2、测试

当io_thread 或 sql_thread  停止时，从库的读取会直接走主库，不会到从库来读取数据

当Seconds_Behind_Master 大于 slaveThreshold的值时，从库的读取会直接走主库，不会到从库来读取数据

 

另外，注意：将主节点重新启动，MyCat 不会重新将其加入进来。（已亲自试验）

官方文档中有这么一段：

writeType="0",所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个

writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .

 

 [root@node154 conf]# cat dnindex.properties

#update

#Wed Jul 24 17:00:11 CST 2019

localhost1=0  #0:表示第一个writeHost   1:表示第二个writeHost   

 

当主库挂了之后，数据由0变为1：

[root@node154 conf]# cat dnindex.properties

#update

#Wed Jul 24 17:00:11 CST 2019

localhost1=0

[root@node154 conf]# cat dnindex.properties

#update

#Wed Jul 24 17:17:41 CST 2019

localhost1=1

 

当主库恢复之后，按以下操作步骤恢复主库的DML:

1.修改以下文件，将1修改为0,表示第一个writeHost为主库

[root@node154 conf]# cat dnindex.properties

#update

#Wed Jul 24 17:17:41 CST 2019

localhost1=0

2.生启MYCAT服务：

mycat restart

3.重启后恢复正常主库DML操作

 

 

数据库切换日志文件：

[root@node154 logs]# pwd

/usr/local/mycat/logs

[root@node154 logs]# cat switch.log

2019-07-24 15:47:34: [dataHost=localhost1,dataSource=192.168.67.153,statue=ok status -> error status]

2019-07-24 15:47:34: [dataHost=localhost1,dataSource=192.168.67.152,statue=ok status -> error status]

2019-07-24 16:26:28: [dataHost=localhost1,dataSource=192.168.67.153,statue=ok status -> error status]

2019-07-24 16:26:28: [Host=localhost1,result=[192.168.67.153->192.168.67.152],reason=heartbeaterror]

2019-07-24 16:27:58: [dataHost=localhost1,dataSource=192.168.67.153,statue=error status -> ok status]

2019-07-24 16:50:12: [dataHost=localhost1,dataSource=192.168.67.153,statue=ok status -> error status]

2019-07-24 16:50:52: [dataHost=localhost1,dataSource=192.168.67.153,statue=error status -> ok status]

2019-07-24 16:55:23: [dataHost=localhost1,dataSource=192.168.67.153,statue=ok status -> error status]

2019-07-24 16:55:23: [Host=localhost1,result=[192.168.67.153->192.168.67.152],reason=heartbeaterror]

2019-07-24 16:56:53: [dataHost=localhost1,dataSource=192.168.67.153,statue=error status -> ok status]

2019-07-24 17:00:31: [dataHost=localhost1,dataSource=192.168.67.152,statue=ok status -> error status]

2019-07-24 17:02:51: [dataHost=localhost1,dataSource=192.168.67.152,statue=error status -> ok status]

2019-07-24 17:07:41: [dataHost=localhost1, dataSource=192.168.67.152,statue=okstatus -> error status]

2019-07-24 17:10:01: [dataHost=localhost1,dataSource=192.168.67.152,statue=error status -> ok status]

2019-07-24 17:17:41: [dataHost=localhost1,dataSource=192.168.67.153,statue=ok status -> error status]

2019-07-24 17:17:41:[Host=localhost1,result=[192.168.67.153->192.168.67.152],reason=heartbeaterror]

2019-07-24 17:18:41: [dataHost=localhost1,dataSource=192.168.67.153,statue=error status -> ok status]