Mycat读写分离配置实践

本文讲述如何实践Mycat读写分离，首先要配置好MYSQL的主从，然后验证Mycat的读写分离功能。

本文的例子是基于单机Windows版本的读写分离例子。

1 MySQL读写分离配置

Windows配置主从

3307为主，3308为从

C:\mymaster

C:\myslave

1.1安装master 3307

1）My.ini

[mysql]

#设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

server_id=1273307

#设置3307端口

port= 3307

#设置mysql的安装目录

basedir=C:\mymaster

#设置mysql数据库的数据的存放目录

datadir=C:\mymaster\data

#允许最大连接数

max_connections=200

#服务端使用的字符集默认为8比特编码的utf8字符集

character-set-server=utf8

#创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

 

####:for binlog

binlog_format                           = ROW

log-bin                                 =C:\mymaster\log\mysql-bin

log-bin-index                           =C:\mymaster\log\mysql-bin.index

 

[WinMySQLAdmin]

C:\mymaster\bin\mysqld.exe

 

binlog-do-db=db1#指定要同步的数据库，必须的

binlog-do-db=db2#指定要同步的数据库，必须的

binlog-do-db=db3#指定要同步的数据库，必须的

2）安装服务mymaster3307

cd C:\mymaster\bin

mysqld install mymaster3307 --defaults-file=c:\mymaster\my.ini

3）设置root用户密码

 mysql –uroot

 mysql>use mysql;
    mysql>UPDATE user SET password=PASSWORD("123456") WHERE user='root';
    mysql>FLUSH PRIVILEGES;
    mysql>QUIT

4）登录3307

Mysql –u root –p –P3307

1.2安装slave3308

1）My.ini

[mysql]

#设置mysql客户端默认字符集

default-character-set=utf8

[client]

port=3308

default-character-set=utf8

[mysqld]

server_id=1273308

#设置3308端口

port= 3308

#设置mysql的安装目录

basedir=C:\myslave

#设置mysql数据库的数据的存放目录

datadir=C:\myslave\data

#允许最大连接数

max_connections=200

#服务端使用的字符集默认为8比特编码的utf8字符集

character-set-server=utf8

#创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

####:for binlog

binlog_format                           = ROW

log-bin                                 =C:\myslave\log\mysql-bin

log-bin-index                           =C:\myslave\log\mysql-bin.index

[WinMySQLAdmin]

C:\myslave\bin\mysqld.exe

replicate-do-db=db1

replicate-do-db=db2

replicate-do-db=db3

2）安装服务myslave3308

C:\myslave\bin
mysqld install myslave3308 --defaults-file=c:\myslave\my.ini

3）设置root密码

4）登录3308

Mysql –u root –p –P3308

1.3 主从设置

把3307设为主，3308设为从

1）3307

Show master status

 

 创建复制用户：

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO'slave'@'127.0.0.1' IDENTIFIED BY '123456';

2）3308

注意：log_file与上文的master的file保持一致，pos与上文的master的Position保持一致。

change master to master_host='127.0.0.1',
master_user='slave',
master_password='123456',
master_port=3307,
master_log_file='mysql-bin.000007',
master_log_pos=436;

--启动slave
start slave
--查看slave状态：
show slave status \G

Slave连接master失败，在3307重置slave密码
mysql>use mysql;
    mysql>UPDATE user SET password=PASSWORD("123456") WHERE user='slave';
    mysql>FLUSH PRIVILEGES;
    mysql>QUIT

3）MySQL主从验证

只在3307上创建表，结果可看到，3307和3308同时创建了相同的表。
CREATE TABLE `testcreatein07` (`id` bigint not null,`name` varchar(60) NULL ,PRIMARY KEY (`id`));

只在3307上插入数据，可以看到数据同时写入了3307和3308。
insert into testcreatein07 (id,name) values(110,'sq1');

2 MYCAT读写分离验证

balance  为读写分离属性，switchType为主从切换属性

heartbeat为心跳切换语句

1）Schema.xml

               <table name="writereadtest" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="mod-long" />

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

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

                                <heartbeat>selectuser()</heartbeat>

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

                                <writeHosthost="hostM1" url="localhost:3307" user="root"

                                                                   password="123456">

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

                                                <readHosthost="hostS2" url="localhost:3308" user="root"password="123456" />

                                </writeHost>

                </dataHost>

log4j2.xml设为 debug

<?xml version="1.0"encoding="UTF-8"?>

<Configurationstatus="DEBUG">

   <Appenders>

       <Console name="Console" target="SYSTEM_OUT">

           <PatternLayout pattern="%d [%-5p][%t] %m %throwable{full}(%C:%F:%L) %n"/>

       </Console>

 

       <RollingFile name="RollingFile"fileName="${sys:MYCAT_HOME}/logs/mycat.log"

                    filePattern="${sys:MYCAT_HOME}/logs/$${date:yyyy-MM}/mycat-%d{MM-dd}-%i.log.gz">

       <PatternLayout>

                <Pattern>%d{yyyy-MM-ddHH:mm:ss.SSS} %5p [%t] (%l) - %m%n</Pattern>

           </PatternLayout>

           <Policies>

               <OnStartupTriggeringPolicy/>

               <SizeBasedTriggeringPolicy size="250 MB"/>

               <TimeBasedTriggeringPolicy/>

           </Policies>

       </RollingFile>

      

   </Appenders>

   <Loggers>

       <!--<AsyncLogger name="io.mycat" level="info"includeLocation="true" additivity="false">-->

           <!--<AppenderRef ref="Console"/>-->

           <!--<AppenderRef ref="RollingFile"/>-->

       <!--</AsyncLogger>-->

       <asyncRoot level="debug"includeLocation="true">

 

           <AppenderRef ref="Console" />

           <AppenderRef ref="RollingFile"/>

 

       </asyncRoot>

                                                <loggername="org.opencloudb.mysql.nio.MySQLConnection"additivity="false">

                                       <level value="debug" />

                                       <AppenderRef ref="Console"/>

                                                                 <AppenderRef ref="RollingFile"/>

                                                </logger>

 

   </Loggers>

</Configuration>

2）9066端口

reload @@config_all;

查看数据源

3）8066create table and insert data

CREATE TABLE `writereadtest` (`id` int not null,`name` varchar(60) NULL, province varchar(60) NULL ,PRIMARY KEY (`id`));
MYCAT 8066端口创建 表，3307和3308的物理库里，同步创建了该表

insert into writereadtest (id,name,province) values(1,'sq1',"beijing");

insert into writereadtest (id,name,province)values(2,'sq2',"shanghai");

insert into writereadtest (id,name,province)values(3,'sq3',"wuhan");

8066执行路由操作和读操作

 

这里可以看到 -select read source hostS2 for dataHost:localhost1

 

致此，读写分离验证完成，写的时候，先写3307，然后同步到3308.读的时候，读的是3308.