MyCat 读写分离

运维的坑 2017-04-10 06:00 暂无评论

实验机两台,分别做主从读写分离。大部分满足一般需求。后期可以扩展到多主多从分库分表去做。

Master IP: 192.168.1.101
Slave IP : 192.168.1.102

这里设置两台 MySQL 服务器提供 Master/Slave 服务。MySQL 版本均为社区版 5.7.11 保持 MySQL 版本一致。

下载 JDK 以及 MyCat 解压并且配置环境变量。

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
http://dl.mycat.io/1.6-RELEASE/

环境变量:

export JAVA_HOME=/usr/local/jdk
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin

修改 MySQL 的配置文件 /etc/my.cnf[mysqld] 节点下添加及修改 server-id

Master 服务器:

[mysqld]
server-id   = 1
binlog-do-db=mycats # 同步的库
binlog-ignore-db=mysql # 不同步的库

Slave 服务器:

[mysqld]
server-id   = 2
replicate-do-db=mycats # 同步的库
replicate-ignore-db=mysql # 不同步的库

重启 Master-Slave 服务器的 MySQL 并且创建用户赋予所有的权限。

在 Master 和 Slave 创建一个 mycats 数据库,主要作用于后面的读写分离和主从同步的库。

mysql> create database mycats default character set utf8;
Query OK, 1 row affected (0.01 sec)

mysql> create user mx_user IDENTIFIED by '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON mycats.* to mx_user;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for mx_user;
+-----------------------------------------------------+
| Grants for mx_user@%                                |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mx_user'@'%'                 |
| GRANT ALL PRIVILEGES ON `mycats`.* TO 'mx_user'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

分别查询一下 Master 是否 1 以及 Slave 是否 2。

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

记录 Master 日志位置。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      364 | mycats       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在 Slave 上进行配置同步,Slave_IO_Running: YesSlave_SQL_Running: Yes 均显示为 Yes 说明主从配置成功。

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_user='mx_user', master_password='123456', master_host='192.168.1.101', master_port=3306, master_log_file='mysql-bin.000007', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: mx_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 502
               Relay_Log_File: MyCat-02-Slave-relay-bin.000002
                Relay_Log_Pos: 668
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mycats
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 502
              Relay_Log_Space: 884
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: feb86588-1d25-11e7-9463-da7afba76e30
             Master_Info_File: /usr/local/mysql/var/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

主从配置完后,配置 MyCat

配置 MyCat 下面的 server.xml user 节点的用户权限

<user name="root">
    <property name="password">123456</property>
    <property name="schemas">mycats</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>

配置 schema.xml 文件,配置数据库名,表配置,以及节点和负载均衡等。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="mycats" checkSQLschema="false" sqlMaxLimit="100">
        <table name="mycats" dataNode="dn1"/>

        <table name="travelrecord" dataNode="dn1" rule="auto-sharding-long" />
    </schema>

    <dataNode name="dn1" dataHost="localhost" database="mycats" />
    <dataHost name="localhost" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="Master" url="192.168.1.101:3306" user="mycat"
                   password="123456">
            <!-- can have multi read hosts -->
            <readHost host="Slave" url="192.168.1.102:3306" user="mycat" password="123456" />
        </writeHost>

    </dataHost>

</mycat:schema>

这里只做了一主一从,balance="1" 配置为所有读操作都随机的发送到 readHostwriteType="0" 配置为所有写操作都发送到可用的 writeHost 上,switchType="2" 配置为主从复制状态绑定的读写分离与切换机制。

启动 MyCat

[root@MyCat-01-Master ~]# ps aux | grep "mycat"
root     28493  0.0  0.1  17820  1852 ?        Sl   05:58   0:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root     28495  0.0 21.5 7814332 282132 ?      Sl   05:58   0:01 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/mysql-binlog-connector-java-0.4.1.jar:lib/asm-4.0.jar:lib/druid-1.0.26.jar:lib/log4j-api-2.5.jar:lib/log4j-1.2-api-2.5.jar:lib/commons-collections-3.2.1.jar:lib/curator-client-2.11.0.jar:lib/mapdb-1.0.7.jar:lib/velocity-1.7.jar:lib/hamcrest-core-1.3.jar:lib/log4j-1.2.17.jar:lib/mongo-java-driver-2.11.4.jar:lib/commons-lang-2.6.jar:lib/minlog-1.2.jar:lib/fastjson-1.2.12.jar:lib/jsr305-2.0.3.jar:lib/joda-time-2.9.3.jar:lib/jline-0.9.94.jar:lib/hamcrest-library-1.3.jar:lib/Mycat-server-1.6-RELEASE.jar:lib/slf4j-api-1.6.1.jar:lib/zookeeper-3.4.6.jar:lib/libwrapper-linux-x86-64.so:lib/reflectasm-1.03.jar:lib/kryo-2.10.jar:lib/guava-19.0.jar:lib/wrapper.jar:lib/sequoiadb-driver-1.12.jar:lib/leveldb-0.7.jar:lib/log4j-core-2.5.jar:lib/curator-framework-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/log4j-slf4j-impl-2.5.jar:lib/libwrapper-linux-ppc-64.so:lib/ehcache-core-2.6.11.jar:lib/univocity-parsers-2.2.1.jar:lib/libwrapper-linux-x86-32.so:lib/leveldb-api-0.7.jar:lib/curator-recipes-2.11.0.jar:lib/dom4j-1.6.1.jar:lib/objenesis-1.2.jar:lib/netty-3.7.0.Final.jar -Dwrapper.key=5aELBE7u1gBwYrId -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=28493 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start
root     28545  0.0  0.0   9044   812 pts/3    S+   05:58   0:00 grep --color=auto mycat

连接 MyCat 使用 MySQL 客户端即可,默认数据接口是 8066 管理端口是 9066 注意连接不要输错端口!两者功能不同。

暂无评论