54
Mycat 数据库分库分表中间件系列文章之三：Mycat分库配置
source link: https://www.ydstudio.net/archives/91.html?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
本文是Mycat 数据库分库分表中间件系列文章的第三篇，平时工作太忙，加上又忙着从PHP转Java，平日的空闲时间都去研究Java了。什么Spring MVC、Spring Boot、Spring Cloud、Dubbo，东西真尼玛的多！正好国庆一人没事干，再次拾起来Mycat的研究。
首先，先按照实现MySQL主从复制 上的步骤配置一个简单的MySQL主从环境，先贴一下相关的配置信息：
MySQL中Master的配置：
[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的配置：
[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上创建用于同步数据的账户：
grant replication client,replication slave on *.* to slave3307@'%' identified by 'slave3307'; #刷新权限，立即生效 flush privileges;
在Master执行命令查看log_pos和log_file
show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000062 | 8233| | | +------------------+----------+--------------+------------------+
注意执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
在slave上执行下面的命令，讲master和slave关联起来：
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 线程都在正常运行。正常的表现如下：
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中的配置信息：
<?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中的配置信息：
<?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中的配置信息：
<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
<property name="count">3</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
</function>
注意下面的地方：
<!--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上看到效果：
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上看效果：
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上查看数据也是如此，此处就贴出部分数据：
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)
最后更新于 2018-10-03 21:49:01 并被添加「mycat mysql」标签，已有 2 位童鞋阅读过。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK