mycat 读写分离

官网：

https://github.com/MyCATApache

DockFile

 - FROM openjdk:8

   ARG mycat_version=1.6

   MAINTAINER 82019137

   ENV TINI_VERSION v0.18.0

   ENV MYCAT_VERSION=$mycat_version \
       MYCAT_HOME=/opt/mycat

   ENV PATH=${PATH}:${MYCAT_HOME}/bin

   RUN rm -rf /etc/localtime RUN cp -f /usr/share/zoneinfo/Asia/Shanghai
   /etc/localtime    

   RUN apt-get update && \
       apt-get install -y wget procps && \
       wget https://github.com/krallin/tini/releases/download/${TINI_VERSION}/tini
   -O /tini && \
       chmod +x /tini && \
       wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
   -O mycat-${MYCAT_VERSION}.tar.gz && \
       mkdir -p ${MYCAT_HOME} && \
       tar -zxvf mycat-${MYCAT_VERSION}.tar.gz && \
       cp -r mycat/* ${MYCAT_HOME} && \
       rm mycat-${MYCAT_VERSION}.tar.gz && \
       rm -r mycat && \
       sed 's/MaxDirectMemorySize=4G/MaxDirectMemorySize=2G/g; s/-Xmx4G/-Xmx2G/g; s/-Xmx1G/-Xmx512M/g;'
   ${MYCAT_HOME}/conf/wrapper.conf

   WORKDIR ${MYCAT_HOME}

   VOLUME ${MYCAT_HOME}/conf VOLUME ${MYCAT_HOME}/logs

   EXPOSE 3306 EXPOSE 8066 EXPOSE 9066

   ENTRYPOINT ["/tini"]

   CMD ["mycat", "console"]

docker build . -t mycat:1.6

初始 conf 信息

docker run -id –name mycat_temp mycat:1.6

docker cp mycat_temp:/opt/mycat/conf /root/mycat/conf

docker rm -f mycat_temp

docker run –name mycat_dev -id –restart=always -p 3300:8066 -p 3310:9066 -v /root/mycat/conf:/opt/mycat/conf -v /root/mycat/logs:/opt/mycat/logs mycat:1.6

注意 如果 mycat -> schema xml->dataHost->switchType =2 基于主从复制 那么 mysql 的配置需要log_bin = mysql-bin #打开主库二进制日志功能.

log-slave-updates=1 #并将其写入从服务器的二进制日志.

relay_log=mysql-relay-bin #打开从库二进制日志功能.
expire_logs_days = 7 #超过7天的binlog删除###
replicate_ignore_db=mysql #设置忽略库
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=sys步账号

CREATE USER ‘slave’@’%’ IDENTIFIED BY ‘slave’;
SELECT * FROM mysql.user;

GRANT ALL PRIVILEGES ON . TO SLAVE@’%’;
FLUSH PRIVILEGES;

CHANGE MASTER TO MASTER_HOST=’192.168.1.62’,MASTER_PORT = 3301,MASTER_USER=’slave’,MASTER_PASSWORD=’slave’; #从库连接主库
START SLAVE; #启动从库

SHOW SLAVE STATUS; #显示从库状态

调整主从复制位置

stop slave;
change master to master_log_file='mysql-bin.000001',master_log_pos=107;
start slave;


SET auto_increment_increment=2;
SET auto_increment_offset=2;   
SHOW VARIABLES LIKE 'auto_inc%';

为了区分是主库还是从库插入的数据，M1、M2设置不同自增步长，通过id的奇偶性来判断，避免通过查debug日志的方式来区分。
MySQL_M1：auto_increment_increment=2，auto_increment_offset=1
MySQL_M2：auto_increment_increment=2，auto_increment_offset=2

注意：读写分离时 使用注解方式时，只读 事务的传播属性需要配置成 propagation=Propagation.SUPPORTS,readOnly=true，统一使用事务配置文件管理的 需要在相关配置上添加传播属性和只读属性

TransactionDefinition.PROPAGATION_SUPPORTS：如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。

@Transactional(propagation=Propagation.SUPPORTS,readOnly=true)

transaction.xml

         <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">

    <aop:config>
         <aop:pointcut expression="execution(* com.**..service..*.*(..))"  id="servicePointcut"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="servicePointcut"/>
    </aop:config>

    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="get*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="query*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="find*" propagation="SUPPORTS" read-only="true"  />
            <tx:method name="search*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="list*"  propagation="SUPPORTS" read-only="true"  />
            <tx:method name="count*" propagation="SUPPORTS" read-only="true"  />
            <tx:method name="check*" propagation="SUPPORTS" read-only="true"  />
           <!--  <tx:method name="add*" propagation="REQUIRED"/>
            <tx:method name="create*" propagation="REQUIRED"/>
            <tx:method name="save*" propagation="REQUIRED"/>
            <tx:method name="insert*" propagation="REQUIRED"/>
            <tx:method name="update*" propagation="REQUIRED"/>
            <tx:method name="delete*" propagation="REQUIRED"/>
            <tx:method name="set*" propagation="REQUIRED"/>
            <tx:method name="send*" propagation="REQUIRED"/>
            <tx:method name="process*" propagation="REQUIRED"/>
            <tx:method name="complete*" propagation="REQUIRED"/>   -->
            <!-- 通用配置 -->
            <tx:method name="*" propagation="REQUIRED"/> 
        </tx:attributes>
    </tx:advice>

</beans>