Mycat 数据库分库分表中间件的分库配置

本文是Mycat 数据库分库分表中间件系列文章的第三篇，平时工作太忙，加上又忙着从PHP转Java，平日的空闲时间都去研究Java了。什么Spring MVC、Spring Boot、Spring Cloud、Dubbo，东西真尼玛的多！正好国庆一人没事干，再次拾起来Mycat的研究。

首先，先按照 实现MySQL主从复制 上的步骤配置一个简单的MySQL主从环境，先贴一下相关的配置信息：
MySQL中Master的配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[client]
port=3306
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="C:/phpStudy/MySQL/"
datadir="C:/phpStudy/MySQL/data/"
character-set-server=utf8
collation-server=utf8_general_ci 
default-storage-engine=MyISAM
### add
server-id=1
log-bin =mysql-bin
binlog-format = mixed
binlog-do-db=db1 #此参数表示只记录指定数据库的二进制日志。
binlog-do-db=db2
binlog-do-db=db3
#binlog-ignore-db=api,mysql,performance_schema #此参数表示忽略指定的数据库的二进制日志。
#其他配置省略

MySQL中slave的配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[client]
port=3307
[mysql]
default-character-set=utf8

[mysqld]
port=3307
basedir="C:/phpStudy/MySQL3307/"
datadir="C:/phpStudy/MySQL3307/data/"
character-set-server=utf8
collation-server=utf8_general_ci 
default-storage-engine=MyISAM

server-id=3307
log-bin = mysql-bin
binlog-format =mixed
relay-log=mysql-relay
slave-skip-errors=all #跳过所有错误
#其他配置省略

在master上创建用于同步数据的账户：

1
2
3
grant replication client,replication slave on *.* to slave3307@'%' identified by 'slave3307';
#刷新权限，立即生效
flush privileges;

在Master执行命令查看log_pos和log_file

1
2
3
4
5
6
7
show master status;

   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000062 |      8233|              |                  |
   +------------------+----------+--------------+------------------+

注意 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

在slave上执行下面的命令，讲master和slave关联起来：

1
2
3
4
5
hange master to master_host='127.0.0.1',master_port=3306,master_user='slave3307',
master_password='slave3307',master_log_file='mysql-bin.000062',master_log_pos=8233;

start slave #启动从服务器复制功能
show slave status\G

其中 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 “Yes”，表明 Slave 的 I/O 和 SQL 线程都在正常运行。正常的表现如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: slave3307
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 24536
               Relay_Log_File: mysql-relay.000005
                Relay_Log_Pos: 24699
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 24536
              Relay_Log_Space: 25318
              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: fe1b230d-6ee9-11e8-b0c6-9c5c8e103115
             Master_Info_File: C:\phpStudy\MySQL3307\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

友情提示：如果是直接复制MySQL的安装目录配置的主从，注意MySQL5.6开始的UUID的问题。
下面开始Mycat的配置，Mycat中有三个重要的配置文件：server.xml、schema.xml、rule.xml，下面就分别贴一下对应的配置信息：
server.xml中的配置信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<?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>
 
		<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
		<property name="processorBufferPoolType">0</property>
		 
		<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
		<property name="handleDistributedTransactions">0</property>
		
		<!--off heap for merge/order/group/limit  1开启   0关闭-->
		<property name="useOffHeapForMerge">1</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">mycat</property>
        <!--多个数据库逗号隔开 -->
		<property name="schemas">testdb</property> 
	</user>

	<user name="mycatread">
		<property name="password">mycatread</property>
		<property name="schemas">testdb</property>
		<property name="readOnly">true</property>
	</user>

</mycat:server>

schema.xml中的配置信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100">
            <table name="t_user" dataNode="dn1,dn2,dn3" rule="crc32slot">
            <!-- 父表为t_user，子表为t_admin。t_admin表中的字段user_id引用t_user表中的id主键字段。 -->
                <childTable name="t_admin" joinKey="user_id" parentKey="id" />
            </table>
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />
    <dataNode name="dn3" dataHost="localhost1" database="db3" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
           <heartbeat>show slave status</heartbeat>
           <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="123456">
                <!--<readHost  host="hostS1" url="127.0.0.1:3307" user="root" password="123456"/>-->
           </writeHost>
    </dataHost>
</mycat:schema>

rule.xml中的配置信息：

1
2
3
4
<function name="crc32slot"
		  class="io.mycat.route.function.PartitionByCRC32PreSlot">
	<property name="count">3</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
</function>

注意下面的地方：

1
2
3
4
5
6
7
8
<!--schema.xml中-->
<table name="t_user" dataNode="dn1,dn2,dn3" rule="crc32slot">

<!--rule.xml中-->
<function name="crc32slot"
			  class="io.mycat.route.function.PartitionByCRC32PreSlot">
		<property name="count">3</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
	</function>

使用crc32lot进行分片，分片的数据库节点的数量默认给了2，如果你修改了这里，请一定要删除conf/ruledata/crc32slot_T_USER.properties这个文件，并重新启动Mycat，不然你的数据是不会分到db3这个数据库节点的。
到这里Mycat分库配置就完成了，你只需要在Mycat的管理上操作，就能在master、slave上看到效果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql>mysql -uroot -pmycat -P8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloun

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> insert into t_user(id, name) values(1, 'aaa');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_user(id, name) values(2, 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id, name) values(3, 'ccc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id, name) values(4, 'ddd');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id, name) values(5, 'eee');
Query OK, 1 row affected (0.00 sec)

在master上看效果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
C:\Users\nick>mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10628
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from db1.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|  3 | ccc  | 32411 |
|  5 | eee  | 27566 |
+----+------+-------+
2 rows in set (0.00 sec)


mysql> select * from db2.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|  1 | aaa  | 44983 |
|  2 | bbb  | 65037 |
+----+------+-------+
2 rows in set (0.00 sec)


mysql> select * from db3.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|  4 | ddd  | 68408 |
+----+------+-------+
1 row in set (0.00 sec)

在slave上查看数据也是如此，此处就贴出部分数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

C:\Users\nick>mysql -uroot -p -P3307
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10412
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from db1.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|  3 | ccc  | 32411 |
|  5 | eee  | 27566 |
+----+------+-------+
2 rows in set (0.00 sec)
  • 作者: Sam
  • 发布时间: 2018-10-03 23:04:44
  • 最后更新: 2019-12-09 23:03:26
  • 文章链接: https://ydstudios.gitee.io/post/dcd836f7.html
  • 版权声明: 本网所有文章除特别声明外, 禁止未经授权转载，违者依法追究相关法律责任!