MyCat搭建读写分离

一主一从读写分离模式。

1 搭建MySQL数据库主从复制

1.1 MySQL主从复制原理

1.2 主机配置

修改配置文件：vim /etc/my.cnf
#主服务器唯一ID
server-id=1
 #启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
1
2
3
4
5
6
7
8
9
10
11
12

1.3 binlog日志三种格式

1.Statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数，last_insert_id()，以及user-defined functions(udf)会出现问题).

2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

3.Mixedlevel:

是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row
level模式也被做了优化，并不是所有的修改都会以row
level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

1.4.从机配置

修改配置文件：vim /etc/my.cnf
#从服务器唯一ID
server-id=2
 #启用中继日志
relay-log=mysql-relay
1
2
3
4
5

1.5.主机、从机重启MySQL服务

1.6.主机从机都关闭防火墙

1.7.在主机上建立帐户并授权slave

#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
flush privileges;
#查询master的状态
show master status;
#记录下File和Position的值
#执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化
1
2
3
4
5
6
7

1.8.在从机上配置需要复制的主机

#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

#启动从服务器复制功能
start slave;
#查看从服务器状态
show slave status\G;
#下面两个参数都是Yes，则说明主从配置成功！
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
1
2
3
4
5
6
7
8
9
10
11
12
13

1.9.主机新建库、新建表、insert记录，从机复制

1.10.如何停止从服务复制功能

stop slave; 
功能说明：停止I/O 线程和SQL线程的操作
1
2

1.11.如何重新配置主从

reset slave;
用于删除功能说明：SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件；
reset master;
功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作；
1
2
3
4

2 修改Mycat的配置文件schema.xml

1)之前的配置已分配了读写主机，是否已实现读写分离？
默认：balance="0"
2)修改的balance属性，通过此属性配置读写分离的类型
负载均衡类型，目前的取值有4 种：

（1）balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
（2）balance=“1”，全部的 readHost 与 stand by writeHost 参与 select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。 （3）balance=“2”，所有读操作都随机的在 writeHost、readhost 上分发。
（4）balance=“3”，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

3)为了能看到读写分离的效果，把balance设置成2，会在两个主机间切换查询 (2只限于测试，生产环境请选择1或3)

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

3 重启Mycat

./mycat console
1

多次执行查询语句即可看到效果

select * from mytbl ;
1