Mycat+MySql 主从复制-读写分离

? 通过mycat和mysql的主从复制配合搭建数据库的读写分离，可以实现mysql的高可用性，下面我们来搭建mysql的读写分离。

1、一主一从

1、在node01上修改/etc/my.cnf的文件

<code class="language-yaml">#mysql服务唯一id，不同的mysql服务必须拥有全局唯一的id
server-id=1
#启动二进制日期
log-bin=mysql-bin
#设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information-schema
#设置需要复制的数据库
binlog-do-db=msb
#设置binlog的格式
binlog_format=statement
</code>

2、在node02上修改/etc/my.cnf文件

<code class="language-yaml">#服务器唯一id
server-id=2
#启动中继日志
relay-log=mysql-relay
</code>

3、重新启动mysql服务

4、在node01上创建账户并授权slave

<code class="language-sql">grant replication slave on *.* to ‘root‘@‘%‘ identified by ‘123456‘;
--在进行授权的时候，如果提示密码的问题，把密码验证取消
set global validate_password_policy=0;
set global validate_password_length=1;
</code>

5、查看master的状态

<code>show master status
</code>

6、在node02上配置需要复制的主机

<code>CHANGE MASTER TO MASTER_HOST=‘192.168.85.111‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=437;
</code>

7、启动从服务器复制功能

<code>start slave;
</code>

8、查看从服务器状态

<code>show slave status\G
</code>

? 当执行完成之后，会看到两个关键的属性Slave_IO_Running，Slave_SQL_Running，当这两个属性都是yes的时候，表示主从复制已经准备好了，可以进行具体的操作了

2、一主一从验证

? 下面我们通过实际的操作来验证主从复制是否完成。

<code class="language-sql">--在node01上创建数据库
create database msb;
--在node01上创建具体的表
create table mytbl(id int,name varchar(20));
--在node01上插入数据
insert into mytbl values(1,‘zhangsan‘);
--在node02上验证发现数据已经同步成功，表示主从复制完成
</code>

? 通过mycat实现读写分离

? 在node01上插入如下sql语句，

<code class="language-sql">-- 把主机名插入数据库中
insert into mytbl values(2,@@hostname);
-- 然后通过mycat进行数据的访问，这个时候大家发现无论怎么查询数据，最终返回的都是node01的数据,为什么呢？
select * from mytbl;
</code>

? 在之前的mycat基本配置中，其实我们已经配置了读写分离，大家还记得readHost和writeHost两个标签吗？

<code class="language-xml"><writeHost host="hostM1" url="192.168.85.111:3306" user="root"
   password="123456">
<readHost host="hostS1" url="192.168.85.112:3306" user="root" password="123456"></readHost>
</writeHost>
</code>

? 其实我们已经配置过了这两个标签，默认情况下node01是用来完成写入操作的，node02是用来完成读取操作的，但是刚刚通过我们的验证发现所有的读取都是node01完成的，这是什么原因呢？

? 原因很简单，就是因为我们在进行配置的时候在 dataHost 标签中缺失了一个非常重要的属性balance，此属性有四个值，用来做负载均衡的，下面我们来详细介绍

? 1、balance=0 :不开启读写分离机制，所有读操作都发送到当前可用的writehost上

? 2、balance=1:全部的readhost和stand by writehost参与select 语句的负载均衡，简单的说，当双主双从模式下，其他的节点都参与select语句的负载均衡

? 3、balance=2:所有读操作都随机的在writehost，readhost上分发

? 4、balance=3:所有读请求随机的分发到readhost执行，writehost不负担读压力

? 当了解了这个参数的含义之后，我们可以将此参数设置为2，就能够看到在两个主机上切换执行了。

3、双主双从

? 在上述的一主一从的架构设计中，很容易出现单点的问题，所以我们要想让生产环境中的配置足够稳定，可以配置双主双从，解决单点的问题。

? 在此架构中，可以让一台主机用来处理所有写请求，此时，它的从机和备机，以及备机的从机复制所有读请求，当主机宕机之后，另一台主机负责写请求，两台主机互为备机。

? 主机分布如下：

? 下面开始搭建双主双从。

? 1、修改node01上的/etc/my.cnf文件

<code class="language-yaml">#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=msb
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候， 有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1， 取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1
</code>

? 2、修改node03上的/etc/my.cnf文件

<code class="language-yaml">#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=msb
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2
</code>

? 3、修改node02上的/etc/my.cnf文件

<code class="language-yaml">#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
</code>

? 4、修改node04上的/etc/my.cnf文件

<code class="language-yaml">#从服务器唯一ID
server-id=4
#启用中继日志
relay-log=mysql-relay
</code>

? 5、所有主机重新启动mysql服务

? 6、在两台主机node01,node03上授权同步命令

<code class="language-sql">GRANT REPLICATION SLAVE ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘123456‘;
</code>

? 7、查看两台主机的状态

<code class="language-sql">show master status;
</code>

? 8、在node02上执行要复制的主机

<code class="language-sql">CHANGE MASTER TO MASTER_HOST=‘192.168.85.111‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=154;
</code>

? 9、在node04上执行要复制的主机

<code class="language-sql">CHANGE MASTER TO MASTER_HOST=‘192.168.85.113‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=154;
</code>

? 10、启动两个从机的slave并且查看状态，当看到两个参数都是yes的时候表示成功

<code class="language-sql">start slave;
show slave status;
</code>

? 11、完成node01跟node03的相互复制

<code class="language-sql">--在node01上执行
CHANGE MASTER TO MASTER_HOST=‘192.168.85.113‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=442;
--开启slave
start slave
--查看状态
show slave status\G
--在node03上执行
CHANGE MASTER TO MASTER_HOST=‘192.168.85.111‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘mysql-bin.000002‘,MASTER_LOG_POS=442;
--开启slave
start slave
--查看状态
show slave status\G
</code>

4、双主双从验证

在node01上执行如下sql语句：

<code class="language-sql">create database msb;
create table mytbl(id int,name varchar(20));
insert into mytbl values(1,‘zhangsan‘);
--完成上述命令之后可以去其他机器验证是否同步成功
</code>

? 当上述操作完成之后，我们可以验证mycat的读写分离，此时我们需要进行重新的配置，修改schema.xml文件。

? 在当前mysql架构中，我们使用的是双主双从的架构，因此可以将balance设置为1

? 除此之外我们需要注意，还需要了解一些参数：

? 参数writeType，表示写操作发送到哪台机器，此参数有两个值可以进行设置：

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

? writeType=1:所有写操作都随机的发送到配置的writehost中，1.5之后废弃，

? 需要注意的是：writehost重新启动之后以切换后的为准，切换记录在配置文件dnindex.properties中

? 参数switchType:表示如何进行切换：

? switchType=1:默认值，自动切换

? switchType=-1:表示不自动切换

? switchType=2：基于mysql主从同步的状态决定是否切换

<code class="language-xml"><?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="msb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.85.111:3306" user="root"
   password="123456">
<readHost host="hostS1" url="192.168.85.112:3306" user="root" password="123456"></readHost>
</writeHost>
<writeHost host="hostM2" url="192.168.85.113:3306" user="root"
   password="123456">
<readHost host="hostS2" url="192.168.85.114:3306" user="root" password="123456"></readHost>
</writeHost>
</dataHost>
</mycat:schema>
</code>

? 下面开始进行读写分离的验证

<code class="language-sql">--插入以下语句，使数据不一致
insert into mytbl values(2,@@hostname);
--通过查询mycat表中的数据，发现查询到的结果在node02,node03,node04之间切换，符合正常情况
select * from mytbl;
--停止node01的mysql服务
service mysqld stop
--重新插入语句
insert into mytbl values(3,@@hostname);
--开启node01的mysql服务
service mysqld start
--执行相同的查询语句，此时发现在noede01，node02,node04之间切换，符合情况
</code>

? 通过上述的验证，我们可以得到一个结论，node01,node03互做备机，负责写的宕机切换，其他机器充作读请求的响应。

? 做到此处，希望大家能够思考一个问题，在上述我们做的读写分离操作，其实都是基于主从复制的，也就是数据同步，但是在生产环境中会存在很多种情况造成主从复制延迟问题，那么我们应该如何解决延迟问题，这是一个值得思考的问题，到底如何解决呢？

Mycat+MySql 主从复制-读写分离

标签：creat   默认值   statement   mysq   设置   slave   详细介绍   nat   create