Mysql读写分离方案

准备工作

Docker、Mycat、Mysql5.7 基于Docker进行Mysql主从配置。

Mysql主从配置

基于Docker Mysql5.7版本镜像构建主从配置

获取并创建主、从数据库容器

获取mysql镜像docker pull mysql:5.7，通过mysql镜像，创建对应的主、从数据库窗口。
主： docker run -p 3339:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
从： docker run -p 3340:3306 --name mysql_slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

修改配置文件

进入已经创建的mysql主（mysql_master）、从（mysql_slave）容器中，
docker exec -it mysql_master /bin/bash，修改主、从中的Mysql配置文件信息，位置：/etc/mysql/mysql.conf.d/mysqld.cnf 主库增加：

server-id=1 #全局唯一
log-bin=mysql-bin #开启log-bin

从库增加：

server-id=2 #全局唯一
log-bin=mysql-slave-bin #开启log-bin
relay_log=mysql-relay-bin

注意：修改配置文件，需要使用vim命令，但是容器内一般未安装，所以需要先执行： apt-get update然后在执行安装apt-get install vim，安装之后在对配置文件进行悠主库配置：

binlog-do-db=xxxx   二进制日志记录的数据库
binlog-ignore-db=xxxx 二进制日志中忽略数据库

以上任意指定其中一行参数就行，如果需要忽略多个库，则添加多行从库配置：

replicate-do-db    设定需要复制的数据库
replicate-ignore-db 设定需要忽略的复制数据库
replicate-do-table  设定需要复制的表
replicate-ignore-table 设定需要忽略的复制表
replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符
replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

配置从库同步账号

进入主库镜像中，并在mysql中创建用于主从库之间同步的用户，并授权slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于同步数据；

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

注意：首次进入mysql会提示mysql登录问题Access denied for user 'root'@'localhost' (using password: YES)，需要先在mysql配置中添加skip-grant-tables，重启mysql 服务（service mysql restart）,由于重启mysql服务，会造成容器停止的，所以还需要对容器进行重启；在次进入容器中，执行以下命令：

crmsvr@crmsvr-09:~/data$ sudo docker exec -it mysql-slave /bin/bash
root@6ee4f7cd7bc2:/# mysql
mysql> use mysql
mysql> update user set authentication_string=password('1234') where user='root'; 
mysql> flush privileges;
mysql> quit;
root@6ee4f7cd7bc2:/# service mysql restart;

mysql5.7修改密码由password变为authentication_string

链接主从

进入主库镜像中，并在mysql中执行show master status，展示以下内容：

主库暂时不做任何操作，以免引起其它变化，然后进入从库，同时在mysql中输入以下内容：先执行stop slave,在执行以下命令

change master to master_host='192.195.0.3', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002, master_log_pos= 22041, master_connect_retry=30;

master_host 主库IP
master_user 主库中配置的用于同步的账户
master_password 主库中配置的用于同步的账户密码
master_port 主库数据库端口
master_log_file 主库中的执行show master status 展示内容中的file
master_log_pos 主库中的执行show master status 展示内容中的Position
master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒
注意:
由于使用Docker构建mysql主从库，IP并不是本机的IP，而是Docker分配的镜像容器的IP，如果部署在不同的实体服务器上，可以改为服务器IP。使用docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称|容器id查看容器的IP；例：

crmsvr@crmsvr-09:~$ sudo docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_master
192.195.0.3

获取的IP则为命令中所对应的容器的IP

主从同步测试

进入从库 slave 中的mysql终端执行show slave status \G可查看主从状态配置，默认为

Slave_IO_Running: No 
Slave_SQL_Running: No

，使用使用start slave便可开户主从复制；在主库中创建test_master，然后可在从库中查看，是否同步创建数据库，如已创建说明主从复制已完成； stop slave停止主从复制；

MyCat配置

同样使用Docker进行构建，采用Mycat dokcer官方镜像：longhronshens/mycat-docker

docker pull longhronshens/mycat-docker;

mycat配置

创建conf文件夹，并将以下文件进行拷贝或创建在conf文件夹中

rule.xml（无需修改）

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">

    <tableRule name="role1">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>

    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
    </function>
</mycat:rule>

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
        <property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
    <!-- <property name="processorBufferChunk">40960</property> -->
    <!-- 
    <property name="processors">1</property> 
    <property name="processorExecutor">32</property> 
     -->
        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
        <property name="processorBufferPoolType">0</property>
        <!--默认是65535 64K 用于sql解析时最大文本长度 -->
        <!--<property name="maxStringLiteralLength">65535</property>-->
        <!--<property name="sequnceHandlerType">0</property>-->
        <!--<property name="backSocketNoDelay">1</property>-->
        <!--<property name="frontSocketNoDelay">1</property>-->
        <!--<property name="processorExecutor">16</property>-->
        <!--
            <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
            <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
            <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
        <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
        <property name="handleDistributedTransactions">0</property>
        <!-- off heap for merge/order/group/limit   1开启  0关闭  -->
        <property name="useOffHeapForMerge">0</property>
        <!--单位为m -->
        <property name="memoryPageSize">1m</property>
        <!--单位为k-->
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <!--
            单位为m
        -->
        <property name="systemReserveMemorySize">384m</property>
        <!--是否采用zookeeper协调切换  -->
        <property name="useZKSwitch">true</property>
    </system>

    <!-- 这部分是我们通过客户端连接需要设置的帐号密码 -->
    <user name="root">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
    </user>
    <user name="user">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>

schema.xml

<?xml version="1.0"?>  
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">  
<!-- 设置表的存储方式.schema name="TESTDB" 与 server.xml中的 TESTDB 设置一致  -->  
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">  
    </schema>
    <!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->  
    <dataNode name="dn1" dataHost="dataHost01" database="test_master" />  
    <!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->  
    <dataHost name="dataHost01" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  
        <heartbeat>select user()</heartbeat>  
   <!-- write是读的意思 host是别名可以自己随便起 url:是主机的ip地址和端口号
 -->
        <writeHost host="hostM1" url="10.26.24.75:3339" user="mycat" password="123456">
            <readHost host="hostS1" url="10.26.24.75:3340" user="mycat" password="123456"/>
        </writeHost>
    </dataHost>  
</mycat:schema>

创建mycat容器

docker run -d -p 8066:8066 -v /home/crmsvr/data/mycat/conf/schema.xml:/usr/local/mycat/conf/schema.xml -v /home/crmsvr/data/mycat/conf/rule.xml:/usr/local/mycat/conf/rule.xml -v /home/crmsvr/data/mycat/conf/server.xml:/usr/local/mycat/conf/server.xml --name mycat longhronshens/mycat-docker

6、navicat链接测试通过Navicat进行测试，能创建连接并测试连接成功，并且数据库为配置文件中指定的TESTDB数据库名；

测试

1、调整项目中的链接

url: jdbc:mysql://10.26.24.75:8066/TESTDB?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: 123456

数据库地址：由原来的数据库地址，调整为mycat容器的地址；端口：mycat服务的端口；数据库：则为mycat服务配置中的schema中的name； 2、数据测试

参考资料： www.cnblogs.com/songwenjie/… www.jianshu.com/p/67abdb6ad… www.cnblogs.com/omsql/p/964…

startup error java.lang.NumberFormatException: Size must be specified as bytes (b), kibibytes (k), m

基于docker mycat 配置mysql主从复制