架构之旅-集群与管理-数据-Mysql_读写分离/主从复制/分库分表

发表于 | 分类于

简述

本文中，首先配置mysql主从复制，然后使用mycat进行读写分离，最后使用mycat来做分库分表。在数据库访问层)，仅将mysql连接信息修改为mycat连接信息。

源代码

iarc-data

环境

IDE: IntelliJ IDEA
JDK: 1.8.0_u162
Framework: spring boot, dubbo
Middleware: mycat
DB: MySql
WebServer: Tomcat (本地端口8088)

开发

mysql主从复制

采用1主1从的结构

mysql节点信息

1
2
192.168.56.103 master
192.168.56.101 slave

master配置

  1. /etc/my.cnf，添加如下内容

    1
    2
    3
    server_id=1         # server_id必须保证主从不一样
    log-bin=mysql-bin   # mysql二进制日志
    binlog-do-db=ijava  # 指定只对ijava数据库进行二进制日志

  2. 重启mysql(systemctl restart mysql)

  3. 登入mysql查看master信息($ mysql> show master status;)
    看到如下内容

    1
    2
    3
    4
    5
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000002 |      120 | ijava        |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+

  4. 创建同步用的mysql用户(也可忽略该步骤，直接使用mysql用户root进行登录)

    1
    2
    $ mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl422';
    $ mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

slave配置

  1. /etc/my.cnf，添加如下内容

    1
    2
    server_id=2            # server_id必须保证主从不一样
    replicate-do-db=ijava  # 指定只对ijava数据库进行主从复制

  2. slave中使用不同的uuid（虚拟机复制时uuid是一样的，主从复制会报错1593）
    mysql中生成新的唯一uuid

    1
    $ mysql> select uuid();

然后vim /var/lib/mysql/auto.cnf

1
2
[auto]
server-uuid=***

  1. 重启mysql(systemctl restart mysql)
  2. 指定master，启动并查看slave
    1
    2
    3
    $ mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.103',MASTER_USER='repl',MASTER_PASSWORD='repl422',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=120;
    $ mysql> start slave;
    $ mysql> show slave status \G;

关键信息如下

1
2
3
4
5
6
7
8
9
10
11
12
13
       Slave_IO_State: Waiting for master to send event
          Master_Host: 192.168.56.103
          Master_User: repl
          Master_Port: 3306
        Connect_Retry: 60
      Master_Log_File: mysql-bin.000002
  Read_Master_Log_Pos: 411
       Relay_Log_File: mysqld-relay-bin.000002
        Relay_Log_Pos: 574
Relay_Master_Log_File: mysql-bin.000002
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB: ijava

mycat读写分离

mycat安装配置

  1. 前往mycat官网下载1.6.5-release版本的tar.gz

  2. 解压至/opt/mycat并修改环境变量

    1
    2
    export MYCAT_HOME=/opt/mycat
    export PATH=$MYCAT_HOME/bin:$PATH

  3. 配置(/opt/mycat/conf目录中)
    server.xml

    1
    2
    3
    4
    5
    6
    7
    8
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mycat:server SYSTEM "server.dtd">
    <mycat:server xmlns:mycat="http://io.mycat/">
        <user name="root" defaultAccount="true">
            <property name="password">***</property>
            <property name="schemas">ijava</property>
        </user>
    </mycat:server>

schema.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="ijava" checkSQLschema="false" sqlMaxLimit="100">
        <table name="user" dataNode="dn1" />
        <table name="order" dataNode="dn1" />
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="ijava" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.56.103:3306" user="root" password="***">
            <readHost host="hostS2" url="192.168.56.101:3306" user="root" password="***" />
        </writeHost>
    </dataHost>
</mycat:schema>

  1. 启动/停止mycat(mycat start/stop)
  2. 使用firewall-cmd开放mycat端口(8066,9066)

配置mysql使其忽略表大小写

/etc/my.cnf中，添加如下配置节并重启mysql

1
lower_case_table_names=1

修改数据访问层

仅需修改连接字符串

1
spring.datasource.druid.ijava.url=jdbc:mysql://192.168.56.103:8066/ijava***

mycat分库分表

mysql库与表

新建2个库，并在其中新建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE SCHEMA isharding1;
CREATE TABLE isharding1.user
(
  id   INT         NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE isharding1.inbound
(
  id   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
) ENGINE = InnoDB;
CREATE SCHEMA isharding2;
CREATE TABLE isharding2.user
(
  id   INT         NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE isharding2.outbound
(
  id   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
) ENGINE = InnoDB;

server.xml

添加isharding schema

1
2
3
4
5
6
7
<mycat:server>
    ...
    <user name="root" defaultAccount="true">
        <property name="password">***</property>
        <property name="schemas">ijava,isharding</property>
    </user>
</mycat:server>

schema.xml

user表用于展示水平切分，而inbound和outbound则用于垂直切分（不同表放在不同切片）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<mycat:schema xmlns:mycat="http://io.mycat/">
    ...
    <schema name="isharding" checkSQLschema="false" sqlMaxLimit="100">
        <table name="user" dataNode="dn01,dn02" rule="mod-long" primaryKey="id" autoIncrement="true"/>
        <table name="inbound" dataNode="dn01" />
        <table name="outbound" dataNode="dn02" />
    </schema>
    ...
    <dataNode name="dn01" dataHost="isharding" database="isharding1" />
    <dataNode name="dn02" dataHost="isharding" database="isharding2" />
    ...
    <dataHost name="isharding" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM2" url="192.168.56.103:3306" user="root" password="***">
        </writeHost>
    </dataHost>
</mycat:schema>

rule.xml

由于仅有2个切片库，所以规则mod-long的count设置为2

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

重启mycat

mycat restart

调试

读写分离与主从同步

  1. IntelliJ IDEA中启动数据与服务站点（DataApplication:8002, ServiceApplication:8001）

  2. 使用Postman来发起对http://localhost:8001/user/insertUser的请求
    requestbody(format: json)

    1
    {"name": "nothing is true"}

  3. 使用DataGrip连接mycat端口8066，验证数据是否正确插入，并连接mysql master/slave来检查数据是否已进行主从同步

分库分表

sql

user的数据被拆分到2个不同的库中同名的表中，而inbound/outbound的数据则进入了不同库的不同表

1
2
3
4
USE isharding;
INSERT INTO user (id, name) VALUES (1, 'ivan'), (2, 'du'), (3, 'eming'), (4, 'he');
INSERT INTO inbound (name) VALUES ("inbound");
INSERT INTO outbound (name) VALUES ("outbound");

部署

部署到linux下tomcat中

参考数据访问层中部署部分

参考