docker搭建mysql读写分离

0.132字数 398阅读 546

写在前面

当mysql数据量不大且达到读写瓶颈的时候,读的性能消耗远大于写,我们可以将读和写分离开。本实例采用docker容器搭建,通过mycat中间件做分发

准备mysql主从服务

  1. 准备mysql容器
$ docker pull mysql:5.7
  1. 创建主从数据映射目录
// 数据和配置不至于丢失
/usr/local/docker_app/mysql/master/conf.d
/usr/local/docker_app/mysql/slave/conf.d 
  1. 创建master配置文件/usr/local/docker_app/mysql/master/conf.d/my.cnf
[mysqld]
log-bin=master-bin
server-id=1
  1. 创建slave配置文件/usr/local/docker_app/mysql/slave/conf.d/my.cnf
[mysqld]
log-bin=slave-bin
server-id=2
  1. 启动master服务
$ docker run -d --name mysql-master \
    -p 3306:3306 \
    -v /usr/local/docker_app/mysql/master/conf.d/data:/var/lib/mysql \
    -v /usr/local/docker_app/mysql/master/conf.d:/etc/mysql/conf.d \
    -e MYSQL_ROOT_PASSWORD=root \
    -d mysql:5.7
  1. 启动slave服务
$ docker run -d --name mysql-slave \
    -p 3307:3306 \
    -v /usr/local/docker_app/mysql/slave/conf.d/data:/var/lib/mysql \
    -v /usr/local/docker_app/mysql/slave/conf.d:/etc/mysql/conf.d \
    -e MYSQL_ROOT_PASSWORD=root \
    -d mysql:5.7
  1. 进入容器修改master slave的读写权限
# 进入容器shell
$ docker exec -it mysql-master /bin/sh
// mysql shell
// 用户名:root
// 密码:root
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
  1. 进入mysql-master查看master状态
// mysql shell
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     154  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
  1. 获取master容器的host
$ docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master
  1. 进入mysql-slave关联主从模式
// mysql shell
change master to master_host='172.17.0.2', master_user='root',master_password='root', master_log_file='master-bin.000003',master_log_pos=154;
// mysql shell
// 启用slave
start slave;
  1. 测试主从
    在master创建testdb数据库,再到slave中查看是否存在testdb库,若存在则基本完成,若未成功检查binlog、master host、position是否正确;目前已经可以给前台使用,只不过要区分读写数据源,挺麻烦!

mycat中间件代理mysql

  1. 准备mycat镜像
// jre8
http://download.oracle.com/otn-pub/java/jdk/8u152-b16/aa0333dd3019491ca4f6ddbe78cdb6d0/server-jre-8u152-linux-x64.tar.gz
# Dockerfile
FROM debian:jessie

# install jre
COPY server-jre-8u152-linux-x64.tar.gz /usr/local/
RUN cd /usr/local && tar -zxvf server-jre-8u152-linux-x64.tar.gz && rm -f server-jre-8u152-linux-x64.tar.gz

ENV JAVA_HOME /usr/local/jdk1.8.0_152
ENV CLASSPATH ${JAVA_HOME}/lib/dt.jar:$JAVA_HOME/lib/tools.jar
ENV PATH $PATH:${JAVA_HOME}/bin

# install mycat
ADD http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz /usr/local/
RUN cd /usr/local && tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz && rm -f Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

VOLUME /usr/local/mycat/conf
EXPOSE 8066 9066
CMD ["/usr/local/mycat/bin/mycat", "console"]
$ docker build -t mysql/mycat:1.6 .
  1. 创建mycat本地映射目录
/usr/local/docker_app/mycat/conf
/usr/local/docker_app/mycat/logs
  1. 解压mycat压缩包拷贝conf目录下文件到/usr/local/docker_app/mycat/conf
  2. 修改/usr/local/docker_app/mycat/conf/schema.xml文件
<!-- 第一种方案 master挂了slave不可用 -->

<?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>
        <!-- testdb为库名称 -->
    <dataNode name="dn1" dataHost="localhost1" database="testdb" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="0"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="mysql-master" url="172.17.0.2:3306" user="root" password="root">
            <!-- can have multi read hosts -->
            <readHost host="mysql-slave" url="172.17.0.3:3306" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>
<!-- 第二种方案,master挂slave可自动切换,重启也会使用slave,原master为旧数据 -->

<?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>
        <!-- testdb为库名称 -->
    <dataNode name="dn1" dataHost="localhost1" database="testdb" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="0"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="mysql-master" url="172.17.0.2:3306" user="root" password="root"></writeHost>

        <writeHost host="mysql-slave" url="172.17.0.3:3306" user="root" password="root"></writeHost>
    </dataHost>
</mycat:schema>
  1. 修改/usr/local/docker_app/mycat/conf/server.xml文件
<user name="root">
        <property name="password">root</property>
                <!-- 对应上面schema名称 -->
        <property name="schemas">TESTDB</property>
        
        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>
    <user name="user">
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
    </user>
  1. 启动mycat容器
# 8066:mysql协议端口
# 9066:管理端口
$ docker run -d --name mysql-mycat \
    -v /usr/local/docker_app/mycat/conf:/usr/local/mycat/conf  \
    -v /usr/local/docker_app/mycat/logs:/usr/local/mycat/logs \
    -p 8066:8066 -p 9066:9066 mysql/mycat:1.6
  1. mycat测试
    mysql工具链接mycat的8066端口,创建表插入数据之后,分别查看master和slave端数据变化;若不成功,查看mycat配置是否正确,注意ip地址为docker容器内部ip

写在最后

初次实验,若有误解欢迎指正!

推荐阅读更多精彩内容