MySQL读写分离实战

准备工作

• 搭建主从复制环境，可以参考上一篇MySQL主从复制环境搭建

读写分离原理

读写分离是指主服务器执行写操作，从服务器只执行读操作，通过将读操作和写操作分流到不同的服务器，可以减轻单台服务器的负载压力，提高其可用性。当主数据库发生故障或不可用时，读写分离可以自动将读操作切换到备用数据库上，保证系统的可用性，提高容灾能力。

在MySQL中，可以引入Mycat中间件来实现读写分离。

读写分离配置

1. 安装Mycat

• 安装jdk1.7

• 解压Mycat安装包

• 建立Mycat软链接

  # ln -s mycat文件的路径/bin/mycat /usr/bin/mycat
  # ll /usr/bin/mycat 

2. 配置Mycat

/mycat/conf/server.xml：配置登录Mycat的账号信息

<user name="root">
        <property name="password">123456</property>
        <property name="schemas">USERDB</property>
</user>

/mycat/conf/schema.xml：配置逻辑库、数据源和读写分离等信息

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 逻辑数据库  -->
    <schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
    <!-- 存储节点 -->
    <dataNode name="dn1" dataHost="node1" database="school" />
    <!-- 数据库主机 -->
    <dataHost name="node1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="192.168.18.131" url="192.168.18.131:3306" user="root" password="123456">
                    <!-- can have multi read hosts -->
                    <readHost host="10.24.27.30" url="10.24.27.30:3306" user="root" password="123456" />
            </writeHost>
            <writeHost host="10.24.27.30" url="10.24.27.30:3306" user="root" password="123456" />
    </dataHost>
</mycat:schema>

参数说明：

balance：

• “0”：不开启读写分离
• “1”：全部的readHost和stand by writeHost参与select语句的负载
• “2”：所有读操作随机在readHost和writeHost上分发
• “3”：所有读请求随机分发到writeHost对应的readHost上执行

writeType：“0”：所有写操作发送到配置的第一个writeHost，第一个挂掉切换到还生存的第二个writeHost

switchType：

• “-1”：不自动切换
• “1”：自动切换，发送心跳检测命令select user()
• “2”：基于MySQL的主从同步状态决定是否进行切换，发送心跳检测命令：show slave status

3. 启动Mycat服务

# cd mycat/bin
# ./mycat start

4. 查看Mycat服务是否启动成功

# sudo netstat -tnap |grep 66 

如果启动成功，会显示mycat的8066和9066端口号。如果启动不成功，可以查看Mycat的错误日志文件wapper.log。

登录9066管理端口：

# mysql -uroot -h 192.168.18.131 -P 9066 -p
> show @@help; // 查看所有命令

登录8066数据端口：

# mysql -uroot -h 192.168.18.131 -P 8066 -p

登录后，就可以对数据库表进行增删改查操作了。

验证读写分离

通过开启查询日志验证。查询日志会记录所有执行的SQL语句，通过登录Mycat的数据端口进行操作select或者insert操作，观察主服务器和从服务器的查询日志信息是否有变化，判断读写分离是否生效。

授予用户远程登录权限：

# GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.18.131' IDENTIFIED BY '123456' WITH GRANT OPTION;
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.24.27.30' IDENTIFIED BY '123456' WITH GRANT OPTION;
# FLUSH PRIVILEGES;

查看查询日志是否开启：show variables like 'general%';

开启查询日志：set global general_log=on;

登录Mycat8066端口，执行一条插入/查询操作，查看主库和从库的查询日志信息。

参考文章

• 施磊. 后台开发MySQL技术