MyCat实现MySQL读写分离（双主双从多库）

在数据库集群架构中，主数据库负责处理事务性查询（写入操作），而从数据库只负责处理select查询（读操作），这样可以提高数据库整体读写性能。主数据库另外一个功能就是负责将数据变更同步到从数据库中。

读写分离的作用

1）分摊服务器压力，提高机器的系统处理效率。

2）当写入操作不变时，就可以大大分摊读取操作的压力，提高系统性能。另外，当读取操作的压力被分摊后，又间接提高了写入的性能。因此，数据库整体读写性能提高了。

3）增加冗余，提高服务可用性。当一台数据库服务器宕机后，可以调整另外一台从数据库以最快速度恢复服务。

准备工作

第一步，需要5台以上装有CentOS7.7版本以上的64位系统的服务器。

第二步，在四台服务器（双主双从）即公网IP分别为X主.X主.X主.X主、X从.X从.X从.X从、Y主.Y主.Y主.Y主和Y从.Y从.Y从.Y从的服务器上配置相同的环境。都进行源码安装mysql-5.6.40。

Mysql数据库主从复制

第一步，主从复制的基本原理。

mysql复制过程分成三步：

1）master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件（binary log events）。

2）slave将master的binary log events拷贝到它的中继日志（relay log）。

3）slave重做中继日志中的事件，将改变应用到自己的数据库中。

第二步，主从复制的基本原则。

1）每个slave只有一个master；

2）每个slave只能有一个唯一的服务器ID；

3）每个master可以有多个slave。

第三步，配置。

主从服务器上安装的mysql，版本要求一致且后台以服务运行。

主从服务器上安装的mysql都配置在[mysqld]结点下，都是小写。

第四步，主服务器修改my.cnf配置文件。

对于公网IP为X主.X主.X主.X主的服务器，在my.cnf文件中添加下面的代码：

#主服务器唯一Id[必填]

server-id=1

#启用二进制日志[必填]

log-bin=/data/mysql/data/mysqlbin

#binlog保留时间7天

expire_logs_days=7

#主机，读写都可以

read-only=0

#忽略同步的数据库

replicate-ignore-db=mysql,information_schema,performance_schema

#设置需要复制的数据库[可选]

binlog-do-db=test

binlog-do-db=test2

#设置logbin格式

binlog-format=STATEMENT

#主键递增的步长，由于双主，如果是自动递增的主键的话，会出现主键冲突的问题

auto_increment_increment=2

#主键自动递增的初始值，双主的起始值设置的时候需要错开

auto_increment_offset=1

#双主的实例从对方实例中同步过来的数据进行二进制日志文件记录，这样才能让从节点进行同步

log-slave-updates=1

对于公网IP为Y主.Y主.Y主.Y主的服务器，在my.cnf文件中添加下面的代码：

#主服务器唯一Id[必填]

server-id=3

#启用二进制日志[必填]

log-bin=/data/mysql/data/mysqlbin

#binlog保留时间7天

expire_logs_days=7

#主机，读写都可以

read-only=0

#忽略同步的数据库

replicate-ignore-db=mysql,information_schema,performance_schema

#设置需要复制的数据库[可选]

binlog-do-db=test

binlog-do-db=test2

#设置logbin格式

binlog-format=STATEMENT

 #主键递增的步长，由于双主，如果是自动递增的主键的话，会出现主键冲突的问题

auto_increment_increment=2

 #主键自动递增的初始值，双主的起始值设置的时候需要错开

auto_increment_offset=2

#双主的实例从对方实例中同步过来的数据进行二进制日志文件记录，这样才能让从节点进行同步

log-slave-updates=1

第五步，从服务器修改my.cnf配置文件。

对于公网IP为X从.X从.X从.X从的服务器，在my.cnf文件中添加下面的代码：

#从服务器唯一Id

server-id=2

#启用二进制日志[必填]

log-bin=/data/mysql/data/mysqlbin

#只读，对拥有super权限的账号是不生效的

read_only = 1

对于公网IP为Y从.Y从.Y从.Y从的服务器，在my.cnf文件中添加下面的代码：

#从服务器唯一Id

server-id=4

#启用二进制日志[必填]

log-bin=/data/mysql/data/mysqlbin

#只读，对拥有super权限的账号是不生效的

read_only = 1

第六步，配置完，主从服务器都重启mysql服务。

命令：service mysql restart

第七步，建立账户并授权。

1）在主机上建立账户并授权slave。

命令：mysql -uroot -p123456

# 创建用户

mysql > CREATE USER root@'%' IDENTIFIED BY '123456';

# 在公网IP为X主.X主.X主.X主的服务器授权slave并更新权限

mysql > GRANT REPLICATION SLAVE ON *.* TO root@'172.25.217.107' IDENTIFIED BY '123456';

mysql > GRANT REPLICATION SLAVE ON *.* TO root@'172.19.212.248' IDENTIFIED BY '123456';

mysql > flush privileges;

# 在公网IP为Y主.Y主.Y主.Y主的服务器授权slave并更新权限

mysql > GRANT REPLICATION SLAVE ON *.* TO root@'172.19.212.249' IDENTIFIED BY '123456';

mysql > GRANT REPLICATION SLAVE ON *.* TO root@'172.25.217.108' IDENTIFIED BY '123456';

mysql > flush privileges;

# 查询master的状态

mysql > show master status;

若结果显示下面的列表，则说明主服务器配置成功。

2）从服务器上配置需要复制的主服务器。

命令：mysql -uroot -p123456

# 在公网IP为X从.X从.X从.X从和Y主.Y主.Y主.Y主的服务器上配置

mysql > change master to master_host = '172.25.217.108',

master_port = 3306,

master_user = 'root',

master_password = '123456',

master_log_file = 'mysqlbin.000001',

master_log_pos = 636;

# 在公网IP为Y从.Y从.Y从.Y从和X主.X主.X主.X主的服务器上配置

mysql > change master to master_host = '172.19.212.248',

master_port = 3306,

master_user = 'root',

master_password = '123456',

master_log_file = 'mysqlbin.000001',

master_log_pos = 636;

若结果显示“ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first”，则需执行命令“mysql > STOP slave;”，然后执行CHANGE MASTER命令。

# 启动从服务器复制功能

mysql > start slave;

mysql > show slave status\G;

若结果显示“Slave_IO_Running: Yes Slave_SQL_Running: Yes”，则说明主从配置成功。

第八步，测试Mysql数据库主从复制。

1）先在公网IP为X主.X主.X主.X主的主服务器上创建需要复制的数据库test和test2。

命令：mysql -uroot -p123456

mysql> show databases;

mysql> create database test;

mysql> create database test2;

mysql> show databases;

2）接着在这两个库中创建需要复制的表格user，并在数据表中插入数据。

命令：mysql -uroot -p123456

mysql> use test;

mysql> show tables;

mysql> CREATE TABLE IF NOT EXISTS `user`(

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(30) NOT NULL,

`age` INT(3) NOT NULL,

PRIMARY KEY ( `id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show tables;

mysql> insert into user set id='110',name='赵六',age='20';

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> CREATE TABLE IF NOT EXISTS `user`(

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(30) NOT NULL,

`age` INT(3) NOT NULL,

PRIMARY KEY ( `id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show tables;

mysql> insert into user set id='110',name='赵六',age='20';

mysql> select * from user;

3）然后在另一台主服务器和从服务器上检查是否会得到与上面一样的结果。若得到与上面一样的结果，则说明Mysql数据库主从复制配置成功。

Mycat数据库中间件

1）Mycat是一个开源的数据库系统，但是并没有存储引擎。

2）Mycat是数据库中间件，就是介于客户端和服务器之间，进行数据处理与交互的中间服务。接到客户端的请求，通过判断后，转发到后端数据库。

3）Mycat在使用之前，需要先安装JAVA运行环境(JRE),由于Mycat中使用了JDK7中的一些特性，所以要求必须在JDK7以上的版本上运行。

部署mycat

部署在公网IP为z.z.z.z的服务器上

第一步，下载server-jre-8u202-linux-x64.tar.gz安装包。

登录网址：https://www.oracle.com/java/technologies/javase/javase8-archive-downloads.html下载server-jre-8u202-linux-x64.tar.gz安装包，然后使用WinSCP将安装包上传到/usr/local/src文件夹中。也可以使用wget命令下载。

命令：cd /usr/local/src

wget https://www.oracle.com/java/technologies/javase/javase8-archive-downloads.html/jre-8u202-linux-x64.tar.gz

第二步，解压server-jre-8u202-linux-x64.tar.gz安装包。

命令：cd /usr/local/src

tar -zxvf server-jre-8u202-linux-x64.tar.gz -C /usr/local

第三步，配置环境变量。

配置/etc/profile文件，在文件添加下面的代码：

export JAVA_HOME=/usr/local/jdk1.8.0_202

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH

第四步，测试server-jre是否配置成功。

# 刷新profile文件

命令：source /etc/profile

# 查看jdk版本

命令：java -version

若结果显示server-jre的版本，则说明server-jre配置成功。

第五步，下载Mycat-server-1.6.5.tar.gz安装包。

到地址：Index of /1.6.5/下载Mycat-server-1.6.5.tar.gz安装包。然后使用WinSCP将安装包上传到/usr/local/src文件夹中。也可以使用wget命令下载。

命令：cd /usr/local/src

wget http://dl.mycat.org.cn/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

第六步，解压Mycat-server-1.6.5.tar.gz安装包。

命令：cd /usr/local/src

tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/

第七步，配置mycat。

Mycat-server-1.6.5中有两个配置文件，分别为：

# 定义用户以及系统相关变量，如端口等。

/usr/local/mycat/conf/server.xml

# 定义逻辑库，表、分片节点等。

/usr/local/mycat/conf/schema.xml

1）配置server.xml。

在/usr/local/mycat/conf/server.xml中，代码修改为：

    <user name="root" defaultAccount="true">

        <property name="password">123456</property>

        <property name="schemas">test,test2</property>
    
    </user>

    <user name="user">

        <property name="password">123456</property>

        <property name="schemas">test,test2</property>

        <property name="readOnly">true</property>

    </user>

</mycat:server>

2）配置schema.xml。

在/usr/local/mycat/conf/schema.xml中写入下面的代码：

<?xml version="1.0"?>

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

<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" ></schema>

    <schema name="test2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2" ></schema>

    <dataNode name="dn1" dataHost="testhost" database="test" />

    <dataNode name="dn2" dataHost="testhost" database="test2" />

    <dataHost name="testhost" maxCon="1000" minCon="10" balance="1" writeType="0"     
             dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">

        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM1" url="X主.X主.X主.X主:3306" user="root"  password="123456">

            <readHost host="hostS1" url="X从.X从.X从.X从:3306" user="root"  password="123456"/>

            <readHost host="hostM2" url="Y主.Y主.Y主.Y主:3306" user="root"  password="123456"/>

        </writeHost>

        <writeHost host="hostM2" url="Y主.Y主.Y主.Y主:3306" user="root"  password="123456">

            <readHost host="hostS2" url="Y从.Y从.Y从.Y从:3306" user="root"  password="123456"/>

            <readHost host="hostM1" url="X主.X主.X主.X主:3306" user="root"  password="123456"/>

        </writeHost>

    </dataHost>

</mycat:schema>

注意：

1）schema节点中的“name”的值和server.xml文件中user节点中的“schemas”值一样。

2）dataNode节点中的“database”为需要复制的数据库名称。

3）dataHost节点中的“balance”和“switchType”。

balance指负载均衡类型，目前有4种取值：

balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

balance="1"：当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

balance="2"：所有读操作都随机的在writeHost、readhost上分发。

balance="3"：所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力。

switchType指切换的模式，目前也有4种取值：

switchType="-1"：不自动切换。

switchType="1"：默认值，表示自动切换。

switchType="2"：基于MySQL主从同步的状态决定是否切换。

switchType="3"：基于MySQL galary cluster的切换机制（适合集群）。

因此，一般balance设置balance="1"(双主双从)或balance="3"(单主单从)。switchType设置switchType="-1"。因为单纯的MySQL主从集群并不允许将数据读进从服务器中。

第八步，在主服务器数据库上给用户授权。

命令：mysql -uroot -p123456

mysql> grant all on *.* to root@'%' identified by '123456';

mysql> flush privileges;

第九步，在mycat的服务器上测试mycat用户登录。

1）安装并配置mysql-5.6.20。

注意：安装的mysql与/usr/local/mycat/conf/server.xml文件中要求的一致。

2）远程登录主服务器数据库。

命令：mysql -uroot -p123456 -hX主.X主.X主.X主 -P3306

mysql -uroot -p123456 -hY主.Y主.Y主.Y主 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

第十步，调整JVM。

在/usr/local/mycat/conf/wrapper.conf文件后面的“wrapper.ping.timeout=120”上面添加下面的代码：

wrapper.startup.timeout=300

第十一步，启动Mycat服务。

命令：/usr/local/mycat/bin/mycat start

# 查看mycat服务是否启动

命令：jps

netstat -lntp | grep java

若Mycat服务没有启动，则把/usr/local/mycat/conf/wrapper.conf中的“wrapper.java.additional.3=-XX:MaxPermSize=64M”注释掉，然后启动Mycat服务，因为该代码限制了大小，导致Mycat服务无法启动。

第十二步，测试mycat。

# 在主服务器连接mycat服务器或在mycat服务器上登录

命令：mysql -uroot -p123456 -hz.z.z.z -P8066

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

# 在mycat服务器上登录，查看数据源状态，配置了主从或者多主，可以切换数据源状态

命令：mysql -uroot -p123456 -hz.z.z.z -P9066

mysql> show @@datasource;

mysql> switch @@datasource dn1:0;

注意：name是schema.xml文件中，dataNode节点中的name值。

index是schema.xml文件中，dataNode节点中的dataHost的writeHost的索引，按照从上往下的配置顺序，索引从0开始。

测试mysql读写分离

第一步，主服务器数据库宕机，MyCAT不会自动调度写请求至从服务器。在mycat服务器上连接从服务器，可以正常查询及写入数据。启动主服务器数据库服务，在mycat服务器上连接主服务器，主服务器数据库中不会自动添加刚刚从服务器上写入的数据。

# 主服务器数据库服务关闭

命令：service mysql stop

# 在mycat服务器上登录，测试是否可以正常查询及写入数据

命令：mysql -uroot -p123456 -hz.z.z.z -P8066

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> use test2;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

若结果显示“ERROR 1184 (HY000): Connection refused”，则说明不可以正常查询及写入数据。

 

# 在mycat服务器上连接从服务器，可以正常查询及写入数据

命令：mysql -uroot -p123456 -hX从.X从.X从.X从 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

命令：mysql -uroot -p123456 -hY从.Y从.Y从.Y从 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

 

# 启动主服务器数据库服务

命令：service mysql start

# 在mycat服务器上连接主服务器，主服务器数据库中不会自动添加刚刚从服务器上写入的数据

命令：mysql -uroot -p123456 -hX主.X主.X主.X主 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

命令：mysql -uroot -p123456 -hY主.Y主.Y主.Y主 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

 

第二步，主服务器数据库服务启动，关闭两台从服务器，MyCAT自动调度读请求至主服务器。在mycat服务器上连接主服务器，可以正常查询及写入数据。启动从服务器数据库服务，在mycat服务器上连接从服务器，从服务器数据库中会自动添加刚刚主服务器上写入的数据。

# 启动主服务器数据库服务

命令：service mysql start

# 从服务器数据库服务关闭

命令：service mysql stop

# 在mycat服务器上登录，测试是否可以正常查询及写入数据

命令：mysql -uroot -p123456 -hz.z.z.z -P8066

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

 

# 在mycat服务器上连接主服务器，可以正常查询及写入数据

命令：mysql -uroot -p123456 -hX主.X主.X主.X主 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> insert into user set id='100',name='张三',age='25';

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> insert into user set id='100',name='张三',age='25';

mysql> select * from user;

命令：mysql -uroot -p123456 -hY主.Y主.Y主.Y主 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> insert into user set id='100',name='张三',age='25';

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> insert into user set id='100',name='张三',age='25';

mysql> select * from user;

 

# 启动从服务器数据库服务

命令：service mysql start

# 在mycat服务器上连接从服务器，从服务器数据库中会自动添加刚刚主服务器上写入的数据

命令：mysql -uroot -p123456 -hX从.X从.X从.X从 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;

命令：mysql -uroot -p123456 -hY从.Y从.Y从.Y从 -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> use test2;

mysql> show tables;

mysql> select * from user;