一、MySQL运维
1、日志管理
1. 错误日志
- 错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
- 该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：
mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
5 rows in set (0.01 sec)
2. 二进制日志
- 二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。
- 作用：
- 灾难时的数据恢复；
- MySQL的主从复制。在MySQL8版本中，二进制日志默认开启，涉及到的参数如下：
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)
-
参数说明：
- log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文件名需要再该basename的基础上加上编号(编号从000001开始)。
- log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。
-
MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：
| 日志格式 | 含义 |
|---|---|
| STATEMENT | 基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。 |
| ROW | 基于行的日志记录，记录的是每一行的数据变更。（默认） |
| MIXED | 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。 |
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
- 如果我们需要配置二进制日志的格式，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。
- 查看 二进制日志命令： mysqlbinlog
- 删除：
| 指令 | 含义 |
|---|---|
| reset master | 删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始 |
| purge master logs to ‘binlog.*’ | 删除 * 编号之前的所有日志 |
| purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ | 删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志 |
- 也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。
mysql> show variables like '%binlog_expire_logs_seconds%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1 row in set (0.00 sec)
3. 查询日志
- 查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下， 查询日志是未开启的。
mysql> show variables like '%general%';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/2d4f764b116d.log |
+------------------+---------------------------------+
2 rows in set (0.01 sec)
- 如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：
# 该选项用来开启查询日志，可选值：0-关闭，1-开启
general_log=1
# 设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file=mysql_query.log
- 开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/ 目录下就会出现 mysql_query.log 文件。
- 之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。
4. 慢查询日志
- 慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于
min_examined_row_limit 的所有的SQL语句的日志，默认未开启。
mysql> show variables like 'slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/2d4f764b116d-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
- long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
- 如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数：
# 慢查询日志
slow_query_log=1
# 执行时间参数
long_query_time=2
- 默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。
- 可以使用log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes，如下所述。
# 记录执行较慢的管理语句
log_slow_admin_statements =1
# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1
2、主从复制
1. 概述
- 主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫日志重做），从而使得从库和主库的数据保持同步。
- MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。
- MySQL 复制的优点主要包含以下三个方面：
- 主库出现问题，可以快速切换到从库提供服务；
- 实现读写分离，降低主库的访问压力；
- 可以在从库中执行备份，以避免备份期间影响主库服务。
2. 原理
- MySQL主从复制的核心就是 二进制日志，具体的过程如下：
- 从上图来看，复制分成三步：
- Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中；
- 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log；
- slave重做中继日志中的事件，将改变反映它自己的数据。
3. 搭建
3.1 准备
- 准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备(安装、密码配置等操作)工作。 其中：
- 172.16.196.144 作为主服务器master
- 172.16.196.142 作为从服务器slave
3.2 主库配置
- 修改配置文件 /etc/my.cnf
# mysql 服务ID：保证整个集群环境中唯一，取值范围：1 – 232-1，默认为 1
server-id=1
# 是否只读：1 代表只读, 0 代表读写
read-only=0
# 忽略的数据, 指不需要同步的数据库
# binlog-ignore-db=mysql
# 指定同步的数据库
# binlog-do-db=db01
- 重启MySQL服务器
systemctl restart mysqld
- 登录mysql，创建远程连接的账号，并授予主从复制权限
# 密码安全级别低
set global validate_password.policy=0;
# 密码长度最低4位即可
set global validate_password.length=4;
# 创建 erer 用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'erer'@'%' IDENTIFIED WITH mysql_native_password BY '1992.12.24';
# 为'erer'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'erer'@'%';
- 通过指令，查看二进制日志坐标
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> show master status ;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000007 | 660 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- file : 从哪个日志文件开始推送日志文件
- position ： 从哪个位置开始推送日志
- binlog_ignore_db : 指定不需要同步的数据库
3.3 从库配置
- 修改配置文件 /etc/my.cnf
# mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
server-id=2
# 是否只读,1 代表只读, 0 代表读写
read-only=1
- 重启MySQL服务器
systemctl restart mysqld
- 登录mysql，设置主库配置
# mysql 8.0.23版本
CHANGE REPLICATION SOURCE TO SOURCE_HOST='172.16.196.144', SOURCE_USER='erer', SOURCE_PASSWORD='1992.12.24', SOURCE_LOG_FILE='binlog.000007', SOURCE_LOG_POS=660;
# mysql 8.0.23之前的版本
CHANGE MASTER TO MASTER_HOST='172.16.196.144', MASTER_USER='erer', MASTER_PASSWORD='1992.12.24', MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=660;
| 参数名 | 含义 | 8.0.23之前 |
|---|---|---|
| SOURCE_HOST | 主库IP地址 | MASTER_HOST |
| SOURCE_USER | 连接主库的用户名 | MASTER_USER |
| SOURCE_PASSWORD | 连接主库的密码 | MASTER_PASSWORD |
| SOURCE_LOG_FILE | binlog日志文件名 | MASTER_LOG_FILE |
| SOURCE_LOG_POS | binlog日志文件位置 | MASTER_LOG_POS |
- 开启同步操作
# 8.0.22之后
start replica;
# 8.0.22之前
start slave;
- 查看主从同步状态
# 8.0.22之后
show replica status;
# 8.0.22之前
show slave status;
mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State:
Source_Host: 172.16.196.144
Source_User: erer
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000007
Read_Source_Log_Pos: 660
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Source_Log_File: binlog.000007
Replica_IO_Running: No
Replica_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_Source_Log_Pos: 660
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13117
Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID:
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp: 230825 03:32:47
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
- 报错：Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
- 主从数据库 uui相同，find -name auto.cnf 找到文件位置，修改uuid值。
vim /var/lib/mysql/auto.cnf
- 测试：在主库 172.16.196.144 上创建数据库、表，并插入数据，在从库 172.16.196.142 中查询数据，验证主从是否同步
create database if not exists erer default charset utf8mb4;
use erer;
create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO erer.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');
3、读写分离
1. 介绍
- 读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。
- 主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。
- 通过MyCat即可轻易实现上述功能，不仅可以支持MySQL，也可以支持Oracle和SQL Server。
2. 一主一从
- MySQL的主从复制，是基于二进制日志（binlog）实现的。
| 主机 | 角色 | 用户名 | 密码 |
|---|---|---|---|
| 172.16.196.151 | mycat | root | 1992.12.24 |
| 172.16.196.144 | master | root | 1992.12.24 |
| 172.16.196.142 | slave | root | 1992.12.24 |
3. 读写分离
- MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制。
3.1 安装MyCat
-
Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。
-
我们需要在准备好的服务器中安装如下软件：MySQL、JDL、MyCat
-
下载：下载地址：http://dl.mycat.org.cn/
-
上传Mycat压缩包到服务器
- Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz
- jdk-8u171-linux-x64.tar.gz
# 安装JDK解压缩
[root@localhost ~]# tar -zxvf jdk-8u381-linux-aarch64.tar.gz -C /usr/local
# 配置环境变量
[root@localhost ~]# ：vim /etc/profile
# 添加如下信息
JAVA_HOME=/usr/local/jdk1.8.0_381
PATH=$PATH:$JAVA_HOME/bin
# 重新加载profile文件
[root@localhost ~]# source /etc/profile
# 检查安装是否成功
[root@localhost ~]# java -version
java version "1.8.0_381"
Java(TM) SE Runtime Environment (build 1.8.0_381-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.381-b09, mixed mode)
# 安装MyCat
[root@localhost ~]# tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/mycat/lib/
# 删除原有的低版本 java 驱动
[root@localhost lib]# rm -rf mysql-connector-java-5.1.35.jar
# 上传 新版的驱动,并修改权限
[root@localhost lib]# chmod 777 mysql-connector-java-8.0.22.jar
# 下载arm环境所需依赖，地址：https://download.tanukisoftware.com/wrapper/3.5.40/
# wrapper-linux-armhf-64-3.5.40.tar.gz
# 将wrapper解压后的/bin文件夹下的wrapper文件复制到mycat/bin目录下
# 将wrapper解压后的/lib文件夹下的libwrapper.so文件复制到mycat/lib目录下
# 修改权限
[root@localhost mycat]# chmod 755 ./bin/*
[root@localhost mycat]# chmod 777 ./lib/*
[root@localhost mycat]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
# 查看日志
[root@localhost mycat]# tail -f /usr/local/mycat/logs/wrapper.log
# 停止
[root@localhost mycat]# /usr/local/mycat/bin/mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
3.2 schema.xml配置
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库:ERER(dataNode="dn1"：直接通过逻辑库绑定数据结点) -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- 数据节点：database-绑定数据库 -->
<dataNode name="dn1" dataHost="dhost1" database="erer" />
<!-- 结点主机 -->
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://172.16.196.144:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" >
<readHost host="slave"
url="jdbc:mysql://172.16.196.142:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</writeHost>
</dataHost>
</mycat:schema>
- writeHost代表的是写操作对应的数据库，readHost代表的是读操作对应的数据库。 所以我们要想实现读写分离，就得配置writeHost关联的是主库，readHost关联的是从库。
- 而仅仅配置好了writeHost以及readHost还不能完成读写分离，还需要配置一个非常重要的负责均衡的参数 balance，取值有4种，具体含义如下：
| 参数值 | 含义 |
|---|---|
| 0 | 不开启读写分离机制 , 所有读操作都发送到当前可用的writeHost上 |
| 1 | 全部的 readHost 与 备用的 writeHost 都参与select 语句的负载均衡（主要针对于双主双从模式） |
| 2 | 所有的读写操作都随机在writeHost , readHost上分发 |
| 3 | 所有的读请求随机分发到writeHost对应的readHost上执行, writeHost不负担读压力 |
- 在一主一从模式的读写分离中，balance配置1或3都是可以完成读写分离的。
3.3 server.xml配置
- 配置root用户可以访问 ERER 逻辑库。
<user name="root" defaultAccount="true">
<property name="password">1992.12.24</property>
<property name="schemas">ERER</property>
</user>
3.3 测试
- 配置完毕后，重新启动MyCat。
- 在执行增删改操作时，对应的主库及从库的数据同时修改。
- 在执行查询操作时，检查主库及从库对应的数据变化。
- balance配置为 3，当主节点 Master 宕机之后，业务系统就只能够读，而不能写入数据了。
# 重新启动 MyCat
[root@localhost ~]# /usr/local/mycat/bin/mycat stop
[root@localhost ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
# 查看启动日志
[root@localhost ~]# tail -f /usr/local/mycat/logs/wrapper.log
# 登陆 MyCat
[root@localhost mycat]# mysql -h 172.16.196.147 -P 8066 -uroot -p1992.12.24
mysql> show databases;
+----------+
| DATABASE |
+----------+
| ERER |
+----------+
1 row in set (0.01 sec)
mysql> use ERER;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_erer |
+----------------+
| tb_sku |
| tb_user |
+----------------+
2 rows in set (0.01 sec)
4. 双主双从
4.1 准备
- 一个主机 Master1 用于处理所有写请求,它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后, Master2 主机负责写请求, Master1 、Master2 互为备机。 架构图如下:
- 准备5台服务器,具体的服务器及软件安装情况如下:
| 编号 | IP | 预装软件 | 角色 |
|---|---|---|---|
| 1 | 172.16.196.151 | mycat、mysql | mycat |
| 2 | 172.16.196.144 | mysql | M1 |
| 3 | 172.16.196.153 | mysql | M2 |
| 4 | 172.16.196.142 | mysql | S1 |
| 5 | 172.16.196.152 | MyCat | S2 |
4.2 主库M1配置
- 修改配置文件
[root@localhost ~]# vim /etc/my.cnf
# mysql 服务ID, 保证整个集群环境中唯一, 取值范围: 1 – 2^32-1, 默认为1
server-id=1
# 指定同步的数据库
# binlog-do-db=erer
# 在作为从数据库的时候, 有写入操作也要更新二进制日志文件
log-slave-updates
- 重启服务
[root@localhost ~]# systemctl restart mysqld
- 登陆数据库创建原创登陆账户
[root@localhost ~]# mysql -uroot -p
Enter password:
# 密码安全级别低
mysql> set global validate_password.policy=0;
# 密码长度最低4位即可
mysql> set global validate_password.length=4;
# 创建 erer 用户，并设置密码，该用户可在任意主机连接该MySQL服务
mysql> CREATE USER 'erer'@'%' IDENTIFIED WITH mysql_native_password BY '1992.12.24';
# 为'erer'@'%' 用户分配主从复制权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'erer'@'%';
- 通过指令，查看主库的二进制日志坐标
mysql> show master status ;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000016 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.3 主库M2配置
- 修改配置文件
[root@localhost ~]# vim /etc/my.cnf
# mysql 服务ID, 保证整个集群环境中唯一, 取值范围: 1 – 2^32-1, 默认为1
server-id=3
# 指定同步的数据库
# binlog-do-db=erer
# 在作为从数据库的时候, 有写入操作也要更新二进制日志文件
log-slave-updates
- 重启MySQL服务器
[root@localhost ~]# systemctl restart mysqld
- 登陆数据库创建原创登陆账户
[root@localhost ~]# mysql -uroot -p
Enter password:
# 密码安全级别低
mysql> set global validate_password.policy=0;
# 密码长度最低4位即可
mysql> set global validate_password.length=4;
# 创建 erer 用户，并设置密码，该用户可在任意主机连接该MySQL服务
mysql> CREATE USER 'erer'@'%' IDENTIFIED WITH mysql_native_password BY '1992.12.24';
# 为'erer'@'%' 用户分配主从复制权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'erer'@'%';
- 通过指令，查看主库的二进制日志坐标
mysql> show master status ;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 660 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.4 从库S1配置
- 修改配置文件
[root@localhost ~]# vim /etc/my.cnf
# mysql 服务ID, 保证整个集群环境中唯一, 取值范围: 1 – 232-1, 默认为1
server-id=2
- 重新启动MySQL服务器
[root@localhost ~]# systemctl restart mysqld
- 配置关联的主库M1
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> CHANGE MASTER TO MASTER_HOST='172.16.196.144', MASTER_USER='erer', MASTER_PASSWORD='1992.12.24', MASTER_LOG_FILE='binlog.000016', MASTER_LOG_POS=157;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
- 开启同步操作，查看同步状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status \G;
- 解决 UUID 冲突问题
[root@localhost ~]# vim /var/lib/mysql/auto.cnf
4.5 从库S2配置
- 修改配置文件
[root@localhost ~]# vim /etc/my.cnf
# mysql 服务ID, 保证整个集群环境中唯一, 取值范围: 1 – 232-1, 默认为1
server-id=4
- 重新启动MySQL服务器
[root@localhost ~]# systemctl restart mysqld
- 从库配置关联的主库M2
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> CHANGE MASTER TO MASTER_HOST='172.16.196.153', MASTER_USER='erer', MASTER_PASSWORD='1992.12.24', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=660;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
- 开启同步操作，查看同步状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status \G;
4.5 主库相互复制
- 主库M1配置关联的主库M2
mysql> CHANGE MASTER TO MASTER_HOST='172.16.196.153', MASTER_USER='erer', MASTER_PASSWORD='1992.12.24', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=660;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
- 主库M2配置关联的主库M1
mysql> CHANGE MASTER TO MASTER_HOST='172.16.196.144', MASTER_USER='erer', MASTER_PASSWORD='1992.12.24', MASTER_LOG_FILE='binlog.000016', MASTER_LOG_POS=157;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
- 开启同步操作，查看同步状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status \G;
4.6 测试
mysql> create database erer;
Query OK, 1 row affected (0.01 sec)
mysql> use erer;
Database changed
mysql> create table tb_user(
-> id int(11) not null primary key ,
-> name varchar(50) not null,
-> sex varchar(1)
-> )engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.03 sec)
insert into tb_user(id,name,sex) values(1, 'Tom', '1');
insert into tb_user(id,name,sex) values(2, 'Trigger', '0');
insert into tb_user(id,name,sex) values(3, 'Dawn', '1');
insert into tb_user(id,name,sex) values(4, 'Jack Ma', '1');
insert into tb_user(id,name,sex) values(5, 'Coco', '0');
insert into tb_user(id,name,sex) values(6, 'Jerry', '1');
5. 读写分离
5.1 schema.xml配置
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库:ERER -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- 数据节点：database-绑定数据库 -->
<dataNode name="dn1" dataHost="dhost1" database="erer" />
<!-- 结点主机 -->
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master1"
url="jdbc:mysql://172.16.196.144:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" >
<readHost host="slave1"
url="jdbc:mysql://172.16.196.142:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</writeHost>
<writeHost host="master2"
url="jdbc:mysql://172.16.196.153:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" >
<readHost host="slave2"
url="jdbc:mysql://172.16.196.153:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</writeHost>
</dataHost>
</mycat:schema>
- balance：
- 1：代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1, M2->S2,并且 M1 与 M2 互为主备) ,正常情况下M2,S1,S2 都参与 select 语句的负载均衡 ;
- writeType：
- 0：写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;
- 1：所有的写操作都随机地发送到配置的writeHost上 ;
- switchType：
- -1：不自动切换；
- 1：自动切换，writeHost1挂了, 会切换到writeHost2上
5.2 user.xml配置
<user name="root" defaultAccount="true">
<property name="password">1992.12.24</property>
<property name="schemas">ERER</property>
</user>
5.3 测试
# 重新启动 MyCat
[root@localhost ~]# /usr/local/mycat/bin/mycat stop
[root@localhost ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
# 查看启动日志
[root@localhost ~]# tail -f /usr/local/mycat/logs/wrapper.log
# 登陆 MyCat
[root@localhost mycat]# mysql -h 172.16.196.147 -P 8066 -uroot -p1992.12.24
二、分库分表
1、面临问题
- 随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：
- IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。
- CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。
- 为了解决上述问题，我们需要对数据库进行分库分表处理。
- 分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。
2、拆分策略
- 分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：
1. 垂直拆分
- 垂直分库：以表为依据，根据业务将不同表拆分到不同库中。
- 每个库的表结构都不一样；
- 每个库的数据也不一样；
- 所有库的并集是全量数据。
- 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。
- 每个表的结构都不一样；
- 每个表的数据也不一样，一般通过一列（主键/外键）关联；
- 所有表的并集是全量数据。
2. 水平拆分
- 水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
- 每个库的表结构都一样；
- 每个库的数据都不一样；
- 所有库的并集是全量数据。
- 水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
- 每个表的表结构都一样；
- 每个表的数据都不一样；
- 所有表的并集是全量数据。
在业务系统中，为了缓解磁盘IO及CPU的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。
3. 实现技术
- shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理；需要自行编码配置实现，只支持java语言，性能较高。
- MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。
3、MyCat概述
1. 软件介绍
- Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在。
- 开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。 具体的分库分表的策略，只需要在MyCat中配置即可。
- 优势：性能可靠稳定、强大的技术团队、体系完善、社区活跃
2. 下载安装
-
Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。
-
我们需要在准备好的服务器中安装如下软件：MySQL、JDL、MyCat
-
下载：下载地址：http://dl.mycat.org.cn/
-
上传Mycat压缩包到服务器
- Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz
- jdk-8u171-linux-x64.tar.gz
# 安装JDK解压缩
[root@localhost ~]# tar -zxvf jdk-8u381-linux-aarch64.tar.gz -C /usr/local
# 配置环境变量
[root@localhost ~]# ：vim /etc/profile
# 添加如下信息
JAVA_HOME=/usr/local/jdk1.8.0_381
PATH=$PATH:$JAVA_HOME/bin
# 重新加载profile文件
[root@localhost ~]# source /etc/profile
# 检查安装是否成功
[root@localhost ~]# java -version
java version "1.8.0_381"
Java(TM) SE Runtime Environment (build 1.8.0_381-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.381-b09, mixed mode)
# 安装MyCat
[root@localhost ~]# tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/mycat/lib/
# 删除原有的低版本 java 驱动
[root@localhost lib]# rm -rf mysql-connector-java-5.1.35.jar
# 上传 新版的驱动,并修改权限
[root@localhost lib]# chmod 777 mysql-connector-java-8.0.22.jar
- /usr/local/mycat目录下文件：
- bin : 存放可执行文件，用于启动停止mycat
- conf：存放mycat的配置文件
- lib：存放mycat的项目依赖包（jar）
- logs：存放mycat的日志文件
3. 概念介绍
- 在MyCat的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。
- 在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的。
- 在后面讲解MyCat入门以及MyCat分片时，还会讲到上面所提到的概念。
4、MyCat入门
1. 需求介绍
- 由于 tb_order 表中数据量很大，磁盘IO及容量都到达了瓶颈，现在需要对 tb_order 表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上, 具体的结构，参考下图：
2. 环境准备
- 准备3台服务器：（并且在3台数据库中创建数据库 erer）
- 172.16.196.148：MyCat中间件服务器，同时也是第一个分片服务器。
- 172.16.196.149：第二个分片服务器。
- 172.16.196.150：第三个分片服务器。
3. 配置参数
- 进入配置文件所在目录：
cd /usr/local/mycat/conf/
- 在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 逻辑表：rule—分片规则 -->
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>
<!-- 数据节点：database-绑定数据库 -->
<dataNode name="dn1" dataHost="dhost1" database="erer" />
<dataNode name="dn2" dataHost="dhost2" database="erer" />
<dataNode name="dn3" dataHost="dhost3" database="erer" />
<!-- 结点主机 -->
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://172.16.196.147:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://172.16.196.149:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://172.16.196.150:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</dataHost>
</mycat:schema>
- 在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：
<?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">1992.12.24</property>
<property name="schemas">ERER</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">1992.12.24</property>
<property name="schemas">ERER</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
- 上述的配置表示，定义了两个用户 root 和 user ，
- 这两个用户都可以访问 DB01 这个逻辑库，访问密码都是123456，
- 但是root用户访问DB01逻辑库，既可以读，又可以写，但是 user用户访问DB01逻辑库是只读的。
4. 测试
- 配置完毕后，先启动涉及到的3台分片服务器，然后启动MyCat服务器。切换到Mycat的安装目录，执行如下指令，启动Mycat：
# 启动
[root@localhost mycat]# /usr/local/mycat/bin/mycat start
Unable to locate any of the following operational binaries:
/usr/local/mycat/bin/./wrapper-linux-aarch64-64
/usr/local/mycat/bin/./wrapper-linux-aarch64-32
/usr/local/mycat/bin/./wrapper
# 解决问题
# 下载arm环境所需依赖，地址：https://download.tanukisoftware.com/wrapper/3.5.40/
# wrapper-linux-armhf-64-3.5.40.tar.gz
# 将wrapper解压后的/bin文件夹下的wrapper文件复制到mycat/bin目录下
# 将wrapper解压后的/lib文件夹下的libwrapper.so文件复制到mycat/lib目录下
# 修改权限
[root@localhost mycat]# chmod 755 ./bin/*
[root@localhost mycat]# chmod 777 ./lib/*
[root@localhost mycat]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
# 查看日志
[root@localhost mycat]# tail -5f ./logs/wrapper.log
INFO | jvm 1 | 2023/08/28 00:09:45 | SLF4J: Found binding in [jar:file:/usr/local/mycat/lib/log4j-slf4j-impl-2.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO | jvm 1 | 2023/08/28 00:09:45 | SLF4J: Found binding in [jar:file:/usr/local/mycat/lib/log4j-slf4j-impl-2.13.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO | jvm 1 | 2023/08/28 00:09:45 | SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
INFO | jvm 1 | 2023/08/28 00:09:45 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO | jvm 1 | 2023/08/28 00:10:15 | MyCAT Server startup successfully. see logs in logs/mycat.log
# 停止
[root@localhost mycat]# /usr/local/mycat/bin/mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
- Mycat启动之后，占用端口号 8066。
- 启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。
- 登陆MyCat
mysql -h 172.16.196.147 -P 8066 -uroot -p1992.12.24
mysql> show databases;
+----------+
| DATABASE |
+----------+
| ERER |
+----------+
1 row in set (0.00 sec)
mysql> use ERER;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables in ERER |
+----------------+
| tb_order |
+----------------+
1 row in set (0.00 sec)
- 然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况。
CREATE TABLE tb_orders (
id BIGINT(20) NOT NULL,
title VARCHAR(100) NOT NULL ,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO tb_orders(id,title) VALUES(1,'goods1');
INSERT INTO tb_orders(id,title) VALUES(2,'goods2');
INSERT INTO tb_orders(id,title) VALUES(3,'goods3');
INSERT INTO tb_orders(id,title) VALUES(5000000,'goods5000000');
INSERT INTO tb_orders(id,title) VALUES(10000000,'goods10000000');
INSERT INTO tb_orders(id,title) VALUES(10000001,'goods10000001');
INSERT INTO tb_orders(id,title) VALUES(15000000,'goods15000000');
INSERT INTO tb_orders(id,title) VALUES(15000001,'goods15000001');
mysql> CREATE TABLE tb_orders (
-> id BIGINT(20) NOT NULL,
-> title VARCHAR(100) NOT NULL ,
-> PRIMARY KEY (id)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
OK!
mysql> select * from TB_ORDER;
+------+--------+
| id | title |
+------+--------+
| 1 | goods1 |
| 2 | goods2 |
| 3 | goods3 |
+------+--------+
3 rows in set (0.03 sec)
mysql> INSERT INTO tb_orders(id,title) VALUES(15000001,'goods15000001');
ERROR 1064 (HY000): can't find any valid datanode :TB_ORDER -> ID -> 15000001
- MyCat 创建 TB_ORDER 表，在三个服务器的数据库同时创建数据结构相同的表
- 在往 TB_ORDER 表中插入数据时：
- 如果id的值在1-500w之间，数据将会存储在第一个分片数据库中；
- 如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中；
- 如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中；
- 如果id的值超出1500w，在插入数据时，将会报错。
5、MyCat配置
1. schema.xml
- schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规则、分片节点及数据源的配置。
1.1 schema标签
- schema 定义逻辑库：schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念 , 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。
- name：指定自定义的逻辑库库名（区分大小写）
- checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
- sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录
- schema 中的table定义逻辑表：table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。
- name：定义逻辑表表名，在该逻辑库下唯一
- dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
- rule：分片规则的名字，分片规则名字是在rule.xml中定义的
- primaryKey：逻辑表对应真实表的主键
- type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为 global
1.2 datanode标签
- 核心属性：
- name：定义数据节点名称
- dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
- database：定义分片所属数据库
1.3 datahost标签
- 该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
- 核心属性：
- name：唯一标识，供上层标签使用
- maxCon/minCon：最大连接数/最小连接数
- balance：负载均衡策略，取值 0,1,2,3
- writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
- dbDriver：数据库驱动，支持 native、jdbc
2. rule.xml
- rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签：tableRule、Function。
3. server.xml
- server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。
3.1 system标签
- 主要配置MyCat中的系统配置信息，对应的系统配置项及其含义，如下：
| 属性 | 取值 | 含义 |
|---|---|---|
| charset | utf8 | 设置Mycat的字符集, 字符集需要与MySQL的字符集保持一致 |
| nonePasswordLogin | 0,1 | 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户 |
| useHandshakeV10 | 0,1 | 使用该选项主要的目的是为了能够兼容高版本的jdbc驱动, 是否采用HandshakeV10Packet来与client进行通信, 1:是, 0:否 |
| useSqlStat | 0,1 | 开启SQL实时统计, 1 为开启 , 0 为关闭 ; 开启之后, MyCat会自动统计SQL语句的执行情况 ; mysql -h 127.0.0.1 -P 9066 -u root -p 查看MyCat执行的SQL, 执行效率比较低的SQL , SQL的整体执行情况、读写比例等 ; show @@sql ; show @@sql.slow ; show @@sql.sum ; |
| useGlobleTableCheck | 0,1 | 是否开启全局表的一致性检测。1为开启 ，0为关闭 。 |
| sqlExecuteTimeout | 1000 | SQL语句执行的超时时间 , 单位为 s ; |
| sequnceHandlerType | 0,1,2 | 用来指定Mycat全局序列类型，0 为本地文件，1 为数据库方式，2 为时间戳列方式，默认使用本地文件方式，文件方式主要用于测试 |
| sequnceHandlerPattern | 正则表达式 | 必须带有MYCATSEQ或者 mycatseq进入序列匹配流程 注意MYCATSEQ_有空格的情况 |
| subqueryRelationshipCheck | true,false | 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false |
| useCompression | 0,1 | 开启mysql压缩协议 , 0 : 关闭, 1 : 开启 |
| fakeMySQLVersion | 5.5,5.6 | 设置模拟的MySQL版本号 |
| defaultSqlParser | 由于MyCat的最初版本使用了FoundationDB的SQL解析器, 在MyCat1.3后增加了Druid解析器, 所以要设置defaultSqlParser属性来指定默认的解析器; 解析器有两个 : druidparser 和 fdbparser, 在MyCat1.4之后,默认是druidparser, fdbparser已经废除了 | |
| processors | 1,2… | 指定系统可用的线程数量, 默认值为CPU核心 x 每个核心运行线程数量; processors 会影响processorBufferPool, processorBufferLocalPercent, processorExecutor属性, 所有, 在性能调优时, 可以适当地修改processors值 |
| processorBufferChunk | 指定每次分配Socket Direct Buffer默认值为4096字节, 也会影响BufferPool长度, 如果一次性获取字节过多而导致buffer不够用, 则会出现警告, 可以调大该值 | |
| processorExecutor | 指定NIOProcessor上共享 businessExecutor固定线程池的大小; MyCat把异步任务交给 businessExecutor线程池中, 在新版本的MyCat中这个连接池使用频次不高, 可以适当地把该值调小 | |
| packetHeaderSize | 指定MySQL协议中的报文头长度, 默认4个字节 | |
| maxPacketSize | 指定MySQL协议可以携带的数据最大大小, 默认值为16M | |
| idleTimeout | 30 | 指定连接的空闲时间的超时长度;如果超时,将关闭资源并回收, 默认30分钟 |
| txIsolation | 1,2,3,4 | 初始化前端连接的事务隔离级别,默认为 REPEATED_READ , 对应数字为3 READ_UNCOMMITED=1; READ_COMMITTED=2; REPEATED_READ=3; SERIALIZABLE=4; |
| sqlExecuteTimeout | 300 | 执行SQL的超时时间, 如果SQL语句执行超时,将关闭连接; 默认300秒; |
| serverPort | 8066 | 定义MyCat的使用端口, 默认8066 |
| managerPort | 9066 | 定义MyCat的管理端口, 默认9066 |
3.2 user标签
- 配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及配置说明如下：
- 在测试权限操作时，我们只需要将 privileges 标签的注释放开。 在 privileges 下的schema 标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性
中配置逻辑表的权限。
6、MyCat分片
1. 垂直拆分
1.1 场景
- 在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。
- 现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下：
1.2 准备
- 准备三台服务器：172.16.196.147、172.16.196.1479、172.16.196.150 并且在上面创建数据库 shopping。
create database if not exists shopping default charset utf8mb4;
1.3 配置
- schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库 -->
<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
<!-- 逻辑表：rule—分片规则 -->
<table name="tb_goods_base" dataNode="dn1" primaryKey="id"/>
<table name="tb_goods_brand" dataNode="dn1" primaryKey="id"/>
<table name="tb_goods_cat" dataNode="dn1" primaryKey="id"/>
<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id"/>
<table name="tb_goods_item" dataNode="dn1" primaryKey="id"/>
<table name="tb_order_item" dataNode="dn2" primaryKey="id"/>
<table name="tb_order_master" dataNode="dn2" primaryKey="order_id"/>
<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no"/>
<table name="tb_user" dataNode="dn3" primaryKey="id"/>
<table name="tb_user_address" dataNode="dn3" primaryKey="id"/>
<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
<table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
<table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
</schema>
<!-- 数据节点：database-绑定数据库 -->
<dataNode name="dn1" dataHost="dhost1" database="shopping" />
<dataNode name="dn2" dataHost="dhost2" database="shopping" />
<dataNode name="dn3" dataHost="dhost3" database="shopping" />
<!-- 结点主机 -->
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://172.16.196.147:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://172.16.196.149:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://172.16.196.150:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1992.12.24" />
</dataHost>
</mycat:schema>
- server.xml
<?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">1992.12.24</property>
<property name="schemas">SHOPPING</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">1992.12.24</property>
<property name="schemas">ERER</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
1.4 测试
- 上传测试SQL脚本到服务器的/root目录
- 重新启动MyCat后，在mycat的命令行中，通过source指令导入表结构，以及对应的数据，查看数据分布情况。
- 将表结构及对应的测试数据导入之后，可以检查一下各个数据库服务器中的表结构分布情况。 检查是否和我们准备工作中规划的服务器一致。
# 停止 mycat 服务
[root@localhost ~]# /usr/local/mycat/bin/mycat stop
# 启动 mycat 服务
[root@localhost ~]# /usr/local/mycat/bin/mycat start
# 查看日志验证 mycat 服务是否启动成功
[root@localhost ~]# tail -f /usr/local/mycat/logs/wrapper.log
# 登陆 mycat
[root@localhost mycat]# mysql -h 172.16.196.147 -P 8066 -uroot -p1992.12.24
# 切换逻辑库
mysql> use SHOPPING;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
# 查看逻辑表
mysql> show tables;
+--------------------+
| Tables in SHOPPING |
+--------------------+
| tb_areas_city |
| tb_areas_provinces |
| tb_areas_region |
| tb_goods_base |
| tb_goods_brand |
| tb_goods_cat |
| tb_goods_desc |
| tb_goods_item |
| tb_order_item |
| tb_order_master |
| tb_order_pay_log |
| tb_user |
| tb_user_address |
+--------------------+
13 rows in set (0.00 sec)
# 创建真实的表结构，并添加数据
mysql> source /root/shopping-table.sql;
mysql> source /root/shopping-insert.sql;
- 查询用户的收件人及收件人地址信息(包含省、市、区)。
- 在MyCat的命令行中，当我们执行以下多表联查的SQL语句时，可以正常查询出数据。
select ua.user_id, ua.contact, p.province, c.city, r.area, ua.address
from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r
where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid;
mysql> select ua.user_id, ua.contact, p.province, c.city, r.area, ua.address
-> from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r
-> where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid;
+-----------+-----------+-----------+-----------+-----------+--------------------+
| user_id | contact | province | city | area | address |
+-----------+-----------+-----------+-----------+-----------+--------------------+
| deng | 叶问 | 北京市 | 市辖区 | 西城区 | 咏春武馆总部 |
| deng | 李小龙 | 北京市 | 市辖区 | 崇文区 | 永春武馆 |
| java00001 | 李佳红 | 北京市 | 市辖区 | 崇文区 | 修正大厦 |
| zhaoliu | 赵三 | 北京市 | 市辖区 | 宣武区 | 西直门 |
| java00001 | 李佳星 | 北京市 | 市辖区 | 朝阳区 | 中腾大厦 |
| java00001 | 李嘉诚 | 北京市 | 市辖区 | 朝阳区 | 金燕龙办公楼 |
+-----------+-----------+-----------+-----------+-----------+--------------------+
6 rows in set (0.07 sec)
- 查询每一笔订单及订单的收件地址信息(包含省、市、区)。实现该需求对应的SQL语句如下：
select order_id, payment, receiver, province, city, area
from tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r
where o.receiver_province = p.provinceid and o.receiver_city = c.cityid and o.receiver_region = r.areaid;
mysql> select order_id, payment, receiver, province, city, area
-> from tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r
-> where o.receiver_province = p.provinceid and o.receiver_city = c.cityid and o.receiver_region = r.areaid;
ERROR 1064 (HY000): invalid route in sql, multi tables found but datanode has no intersection sql:select order_id, payment, receiver, province, city, area
from tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r
where o.receiver_province = p.provinceid and o.receiver_city = c.cityid and o.receiver_region = r.areaid
- 但是现在存在一个问题，订单相关的表结构、省市区的数据库表是在 不同数据库服务器中，SQL语句执行报错。
1.5 全局表
- 对于省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。
- 修改schema.xml中的逻辑表的配置，修改 tb_areas_provinces、tb_areas_city、
tb_areas_region 三个逻辑表，增加 type 属性，配置为global，就代表该表是全局表，就会在所涉及到的dataNode中创建给表。
<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
- 删除原来每一个数据库服务器中的所有表结构
-- 删除 erer 数据库
drop database if exists shopping;
-- 创建名为 erer 的数据，字符集为 utf8mb4
create database if not exists shopping default charset utf8mb4;
- 重新启动服务，并通过source指令，导入表及数据
# 停止 mycat 服务
[root@localhost ~]# /usr/local/mycat/bin/mycat stop
# 启动 mycat 服务
[root@localhost ~]# /usr/local/mycat/bin/mycat start
# 查看日志验证 mycat 服务是否启动成功
[root@localhost ~]# tail -f /usr/local/mycat/logs/wrapper.log
# 登陆 mycat
[root@localhost mycat]# mysql -h 172.16.196.147 -P 8066 -uroot -p1992.12.24
# 切换逻辑库
mysql> use SHOPPING;
# 创建真实的表结构，并添加数据
mysql> source /root/shopping-table.sql
mysql> source /root/shopping-insert.sql
- 检查每一个数据库服务器中的表及数据分布，看到三个节点中都有这三张全局表
- 然后再次执行上面的多表联查的SQL语句
select order_id, payment, receiver, province, city, area
from tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r
where o.receiver_province = p.provinceid and o.receiver_city = c.cityid and o.receiver_region = r.areaid;
mysql> select order_id, payment, receiver, province, city, area
-> from tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r
-> where o.receiver_province = p.provinceid and o.receiver_city = c.cityid and o.receiver_region = r.areaid;
+--------------------+---------+-----------+-----------+-----------+-----------+
| order_id | payment | receiver | province | city | area |
+--------------------+---------+-----------+-----------+-----------+-----------+
| 992605539282190336 | 0.03 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992578873906429952 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992571196308455424 | 0.02 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992554565456887808 | 0.02 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992197263067447296 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992197105567137792 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992196968627306496 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992196116772552704 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992195664538501120 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992194631628226560 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992193598722146304 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992192064319913984 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992191916919488512 | 0.01 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992190947183820800 | 0.02 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 992190237968957440 | 0.17 | 叶问 | 北京市 | 市辖区 | 海淀区 |
| 919059760869863424 | 0.02 | 李嘉诚 | 北京市 | 市辖区 | 海淀区 |
| 919055624854081536 | 0.01 | 李佳星 | 北京市 | 市辖区 | 海淀区 |
| 918835712441212928 | 0.01 | 李佳星 | 北京市 | 市辖区 | 海淀区 |
| 918833485639081984 | 0.01 | 李佳星 | 福建省 | 福州市 | 晋安区 |
| 918806410454654976 | 0.01 | 李佳星 | 福建省 | 福州市 | 晋安区 |
| 918780408353546240 | 0.01 | 李小龙 | 福建省 | 福州市 | 晋安区 |
| 918773289399160832 | 200.00 | 李小龙 | 福建省 | 福州市 | 晋安区 |
| 918334996698148864 | 1798.00 | 李小龙 | 福建省 | 福州市 | 晋安区 |
| 918334996291301376 | 2004.00 | 李小龙 | 福建省 | 福州市 | 晋安区 |
| 918159799198212096 | 400.00 | 叶问 | 福建省 | 福州市 | 晋安区 |
+--------------------+---------+-----------+-----------+-----------+-----------+
25 rows in set (0.05 sec)
- 当在MyCat中更新全局表的时候，所有分片节点中的数据都发生了变化，每个节点的全局表数据时刻保持一致。
2. 水平拆分
2.1 场景
- 在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分。
2.2 准备
- 准备三台服务器：172.16.196.147、172.16.196.1479、172.16.196.150 并且在上面创建数据库 erer。
2.3 配置
- schema.xml
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 逻辑表：rule—分片规则 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
</schema>
<!-- 数据节点：database-绑定数据库(tb_log 表最终落在3个节点中，分别是 dn4、dn5、dn6,而具体的数据分别存储在 dhost1、dhost2、dhost3的erer数据库中。) -->
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- server.xml
<user name="root" defaultAccount="true">
<property name="password">1992.12.24</property>
<!-- 配置root用户既可以访问 SHOPPING 逻辑库，又可以访问 ERER 逻辑库。-->
<property name="schemas">SHOPPING,ERER</property>
</user>
2.4 测试
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
CREATE TABLE tb_log (
id bigint(20) NOT NULL COMMENT 'ID',
model_name varchar(200) DEFAULT NULL COMMENT '模块名',
model_value varchar(200) DEFAULT NULL COMMENT '模块值',
return_value varchar(200) DEFAULT NULL COMMENT '返回值',
return_class varchar(200) DEFAULT NULL COMMENT '返回值类型',
operate_user varchar(20) DEFAULT NULL COMMENT '操作用户',
operate_time varchar(20) DEFAULT NULL COMMENT '操作时间',
param_and_value varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值',
operate_class varchar(200) DEFAULT NULL COMMENT '操作类',
operate_method varchar(200) DEFAULT NULL COMMENT '操作方法',
cost_time bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
source int(1) DEFAULT NULL COMMENT '来源 : 1 PC , 2 Android , 3 IOS',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source)
VALUES('1','user','insert','success','java.lang.String','10001','2022-01-06 18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source)
VALUES('2','user','insert','success','java.lang.String','10001','2022-01-06 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source)
VALUES('3','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('4','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source)
VALUES('5','user','insert','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','insert','29',3);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source)
VALUES('6','user','find','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);
3. 分片规则
3.1 范围分片
- 根据指定的字段及其配置的范围与数据节点的对应情况， 来决定该数据属于哪一个分片。
- schema.xml逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml分片规则配置：
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| mapFile | 对应的外部配置文件 |
| type | 默认值为0 ; 0 表示Integer , 1 表示String |
| defaultNode | 默认节点 默认节点的所用:枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点 ; 如果没有默认值,碰到不识别的则报错 。 |
- 在rule.xml中配置分片规则时，关联了一个映射配置文件 autopartition-long.txt，该配置文件的配置如下：
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
- 0-500万之间的值，存储在0号数据节点(数据节点的索引从0开始) ；
- 500万-1000万之间的数据存储在1号数据节点 ；
- 1000万-1500万的数据节点存储在2号节点 ；
- 该分片规则，主要是针对于数字类型的字段适用。 在MyCat的入门程序中，我们使用的就是该分片规则。
3.2 取模分片
- 根据指定的字段值与节点数量进行求模运算，根据运算结果， 来决定该数据属于哪一个分片。
- schema.xml逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml分片规则配置：
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">3</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| count | 数据节点的数量 |
该分片规则，主要是针对于数字类型的字段适用。 在前面水平拆分的演示中，我们选择的就是取模分片。
3.3 一致性hash分片
- 所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置，有效的解决了分布式数据的拓容问题。
- schema.xml中逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
<!-- 分片规则：一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml中分片规则配置：
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">3</property>
<property name="virtualBucketTimes">160</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| seed | 创建murmur_hash对象的种子，默认0 |
| count | 要分片的数据库节点数量，必须指定，否则没法分片 |
| virtualBucketTimes | 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍; virtualBucketTimes*count就是虚拟结点数量 ; |
| weightMapFile | 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 |
| bucketMapPath | 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 |
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
create table tb_order(
id varchar(100) not null primary key,
money int null,
content varchar(200) null
);
INSERT INTO tb_order (id, money, content) VALUES ('b92fdaaf-6fc4-11ec-b831-482ae33c4a2d', 10, 'b92fdaf8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93482b6-6fc4-11ec-b831-482ae33c4a2d', 20, 'b93482d5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b937e246-6fc4-11ec-b831-482ae33c4a2d', 50, 'b937e25d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93be2dd-6fc4-11ec-b831-482ae33c4a2d', 100, 'b93be2f9-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93f2d68-6fc4-11ec-b831-482ae33c4a2d', 130, 'b93f2d7d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9451b98-6fc4-11ec-b831-482ae33c4a2d', 30, 'b9451bcc-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9488ec1-6fc4-11ec-b831-482ae33c4a2d', 560, 'b9488edb-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94be6e6-6fc4-11ec-b831-482ae33c4a2d', 10, 'b94be6ff-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94ee10d-6fc4-11ec-b831-482ae33c4a2d', 123, 'b94ee12c-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b952492a-6fc4-11ec-b831-482ae33c4a2d', 145, 'b9524945-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95553ac-6fc4-11ec-b831-482ae33c4a2d', 543, 'b95553c8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9581cdd-6fc4-11ec-b831-482ae33c4a2d', 17, 'b9581cfa-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95afc0f-6fc4-11ec-b831-482ae33c4a2d', 18, 'b95afc2a-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95daa99-6fc4-11ec-b831-482ae33c4a2d', 134, 'b95daab2-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9667e3c-6fc4-11ec-b831-482ae33c4a2d', 156, 'b9667e60-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96ab489-6fc4-11ec-b831-482ae33c4a2d', 175, 'b96ab4a5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96e2942-6fc4-11ec-b831-482ae33c4a2d', 180, 'b96e295b-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b97092ec-6fc4-11ec-b831-482ae33c4a2d', 123, 'b9709306-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b973727a-6fc4-11ec-b831-482ae33c4a2d', 230, 'b9737293-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b978840f-6fc4-11ec-b831-482ae33c4a2d', 560, 'b978843c-6fc4-11ec-b831-482ae33c4a2d');
3.4 枚举分片
- 通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性别、状态拆分数据等业务 。
- schema.xml中逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
<!-- 分片规则：一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
<!-- 分片规则：枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml中分片规则配置：
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<!-- 自己增加 tableRule -->
<tableRule name="sharding-by-intfile-enumstatus">
<rule>
<columns>status</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="defaultNode">2</property>
<property name="mapFile">partition-hash-int.txt</property>
</function>
- partition-hash-int.txt，内容如下（枚举值:数据结点的索引） :
1=0
2=1
3=2
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| mapFile | 对应的外部配置文件 |
| type | 默认值为0 ; 0 表示Integer , 1 表示String |
| defaultNode | 默认节点 ; 小于0 标识不设置默认节点 , 大于等于0代表设置默认节点 ; 默认节点的所用:枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点 ; 如果没有默认值,碰到不识别的则报错 。 |
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
CREATE TABLE tb_user (
id bigint(20) NOT NULL COMMENT 'ID',
username varchar(200) DEFAULT NULL COMMENT '姓名',
status int(2) DEFAULT '1' COMMENT '1: 未启用, 2: 已启用, 3: 已关闭',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into tb_user (id,username ,status) values(1,'Tom',1);
insert into tb_user (id,username ,status) values(2,'Cat',2);
insert into tb_user (id,username ,status) values(3,'Rose',3);
insert into tb_user (id,username ,status) values(4,'Coco',2);
insert into tb_user (id,username ,status) values(5,'Lily',1);
insert into tb_user (id,username ,status) values(6,'Tom',1);
insert into tb_user (id,username ,status) values(7,'Cat',2);
insert into tb_user (id,username ,status) values(8,'Rose',3);
insert into tb_user (id,username ,status) values(9,'Coco',2);
insert into tb_user (id,username ,status) values(10,'Lily',1);
insert into tb_user (id,username ,status) values(11,'Lily',5);
3.5 应用指定算法
- 运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串（必须是数字）计算分片号。
- schema.xml中逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
<!-- 分片规则：一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
<!-- 分片规则：枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus" />
<!-- 分片规则：应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml中分片规则配置：
<tableRule name="sharding-by-substring">
<rule>
<columns>id</columns>
<algorithm>sharding-by-substring</algorithm>
</rule>
</tableRule>
<function name="sharding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString">
<!-- 开始索引 -->
<property name="startIndex">0</property>
<!-- 截取长度 -->
<property name="size">2</property>
<!-- 分片数量 -->
<property name="partitionCount">3</property>
<!-- 默认分片 -->
<property name="defaultPartition">0</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| startIndex | 字符子串起始索引 |
| size | 字符长度 |
| partitionCount | 分区(分片)数量 |
| defaultPartition | 默认分片(在分片数量定义时, 字符标示的分片编号不在分片数量内时, 使用默认分片) |
id=05-100000002 , 在此配置中代表根据id中从 startIndex=0，开始，截取siz=2位数字即05，05就是获取的分区，如果没找到对应的分片则默认分配到defaultPartition 。
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
CREATE TABLE tb_app (
id varchar(10) NOT NULL COMMENT 'ID',
name varchar(200) DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into tb_app (id,name) values('0000001','Testx00001');
insert into tb_app (id,name) values('0100001','Test100001');
insert into tb_app (id,name) values('0100002','Test200001');
insert into tb_app (id,name) values('0200001','Test300001');
insert into tb_app (id,name) values('0200002','TesT400001');
3.6 固定分片hash算法
- 该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与1111111111 进行位 & 运算，位与运算最小值为 0000000000，最大值为1111111111，转换为十进制，也就是位于0-1023之间。
- 如果是求模，连续的值，分别分配到各个不同的分片；
- 此算法会将连续的值可能分配到相同的分片，降低事务处理的难度；
- 可以均匀分配，也可以非均匀分配；
- 分片字段必须为数字类型。
- schema.xml中逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
<!-- 分片规则：一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
<!-- 分片规则：枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus" />
<!-- 分片规则：应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />
<!-- 分片规则：固定分片hash算法 -->
<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml中分片规则配置：
<tableRule name="sharding-by-long-hash">
<rule>
<columns>id</columns>
<algorithm>sharding-by-long-hash</algorithm>
</rule>
</tableRule>
<!-- 分片总长度为1024，count与length数组长度必须一致； -->
<function name="sharding-by-long-hash" class="io.mycat.route.function.PartitionByLong">
<!-- 分片长度默认1024=2*256+1*512，count、length数组长度必须一致 -->
<property name="partitionCount">2,1</property>
<property name="partitionLength">256,512</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段名 |
| columns | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| partitionCount | 分片个数列表 |
| partitionLength | 分片范围列表 |
1). 分片长度 : 默认最大2^10 , 为 1024 ;
2). count, length的数组长度必须是一致的 ;
以上分为三个分区:0-255,256-511,512-1023
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
CREATE TABLE tb_longhash (
id int(11) NOT NULL COMMENT 'ID',
name varchar(200) DEFAULT NULL COMMENT '名称',
firstChar char(1) COMMENT '首字母',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into tb_longhash (id,name,firstChar) values(1,'七匹狼','Q');
insert into tb_longhash (id,name,firstChar) values(2,'八匹狼','B');
insert into tb_longhash (id,name,firstChar) values(3,'九匹狼','J');
insert into tb_longhash (id,name,firstChar) values(4,'十匹狼','S');
insert into tb_longhash (id,name,firstChar) values(5,'六匹狼','L');
insert into tb_longhash (id,name,firstChar) values(6,'五匹狼','W');
insert into tb_longhash (id,name,firstChar) values(7,'四匹狼','S');
insert into tb_longhash (id,name,firstChar) values(8,'三匹狼','S');
insert into tb_longhash (id,name,firstChar) values(9,'两匹狼','L');
3.7 字符串hash解析算法
- 截取字符串中的指定位置的子字符串, 进行hash算法， 算出分片。
- schema.xml中逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
<!-- 分片规则：一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
<!-- 分片规则：枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus" />
<!-- 分片规则：应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />
<!-- 分片规则：固定分片hash算法 -->
<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />
<!-- 分片规则：字符串hash解析算法 -->
<table name="tb_strhash" dataNode="dn4,dn5" rule="sharding-by-stringhash" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml中分片规则配置：
<tableRule name="sharding-by-stringhash">
<rule>
<columns>name</columns>
<algorithm>sharding-by-stringhash</algorithm>
</rule>
</tableRule>
<function name="sharding-by-stringhash" class="io.mycat.route.function.PartitionByString">
<!-- 分片长度：总的长度1024，0-511在第一个分片，512-1024在第二个分片 -->
<property name="partitionLength">512</property>
<!-- 分片数量 -->
<property name="partitionCount">2</property>
<!-- 截取字符串 0-2 -->
<property name="hashSlice">0:2</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| partitionLength | hash求模基数 ; length*count=1024 (出于性能考虑) |
| partitionCount | 分区数 |
| hashSlice | hash运算位 , 根据子字符串的hash运算 ; 0 代表 str.length() , -1 代表 str.length()-1 , 大于0只代表数字自身 ; 可以理解为substring（start，end），start为0则只表示0 |
- 示例说明：
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
create table tb_strhash(
name varchar(20) primary key,
content varchar(100)
)engine=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tb_strhash (name,content) VALUES('T1001', UUID());
INSERT INTO tb_strhash (name,content) VALUES('ROSE', UUID());
INSERT INTO tb_strhash (name,content) VALUES('JERRY', UUID());
INSERT INTO tb_strhash (name,content) VALUES('CRISTINA', UUID());
INSERT INTO tb_strhash (name,content) VALUES('TOMCAT', UUID());
3.8 按天分片算法
- 按照日期及对应的时间周期来分片。
- schema.xml中逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
<!-- 分片规则：一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
<!-- 分片规则：枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus" />
<!-- 分片规则：应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />
<!-- 分片规则：固定分片hash算法 -->
<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />
<!-- 分片规则：字符串hash解析算法 -->
<table name="tb_strhash" dataNode="dn4,dn5" rule="sharding-by-stringhash" />
<!-- 按天分片 -->
<table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-date" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml中分片规则配置：
<tableRule name="sharding-by-date">
<rule>
<columns>create_time</columns>
<algorithm>sharding-by-date</algorithm>
</rule>
</tableRule>
<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<!-- 配置表的 dataNode 的分片，必须和分片规则数量一致，
例如 2022-01-01 到 2022-12-31 ，每10天一个分片，一共需要37个分片。 -->
<property name="sBeginDate">2022-01-01</property>
<property name="sEndDate">2022-01-30</property>
<!-- 从开始时间开始，每10天为一个分片，到达结束时间之后，会重复开始分片插入 -->
<property name="sPartionDay">10</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| dateFormat | 日期格式 |
| sBeginDate | 开始日期 |
| sEndDate | 结束日期，如果配置了结束日期，则代码数据到达了这个日期的分片后，会重复从开始分片插入 |
| sPartionDay | 分区天数，默认值 10 ，从开始日期算起，每个10天一个分区 |
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
create table tb_datepart(
id bigint not null comment 'ID' primary key,
name varchar(100) null comment '姓名',
create_time date null
);
insert into tb_datepart(id,name ,create_time) values(1,'Tom','2022-01-01');
insert into tb_datepart(id,name ,create_time) values(2,'Cat','2022-01-10');
insert into tb_datepart(id,name ,create_time) values(3,'Rose','2022-01-11');
insert into tb_datepart(id,name ,create_time) values(4,'Coco','2022-01-20');
insert into tb_datepart(id,name ,create_time) values(5,'Rose2','2022-01-21');
insert into tb_datepart(id,name ,create_time) values(6,'Coco2','2022-01-30');
insert into tb_datepart(id,name ,create_time) values(7,'Coco3','2022-01-31');
3.9 自然月分片
- 使用场景为按照月份来分片, 每个自然月为一个分片。
- schema.xml中逻辑表配置：
<!-- 逻辑库 -->
<schema name="ERER" checkSQLschema="true" sqlMaxLimit="100">
<!-- 分片规则：范围分片 -->
<table name="tb_orders" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
<!-- 分片规则：取模分片 -->
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
<!-- 分片规则：一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
<!-- 分片规则：枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus" />
<!-- 分片规则：应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />
<!-- 分片规则：固定分片hash算法 -->
<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />
<!-- 分片规则：字符串hash解析算法 -->
<table name="tb_strhash" dataNode="dn4,dn5" rule="sharding-by-stringhash" />
<!-- 按天分片 -->
<table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-date" />
<!-- 按自然月分片 -->
<table name="tb_monthpart" dataNode="dn4,dn5,dn6" rule="sharding-by-month" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="erer" />
<dataNode name="dn5" dataHost="dhost2" database="erer" />
<dataNode name="dn6" dataHost="dhost3" database="erer" />
- rule.xml中分片规则配置：
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<!-- 从开始时间开始，一个月为一个分片，到达结束时间之后，会重复开始分片插入；
配置表的 dataNode 的分片，必须和分片规则数量一致，
例如 2022-01-01 到 2022-12-31 ，一共需要12个分片。 -->
<property name="sBeginDate">2022-01-01</property>
<property name="sEndDate">2022-03-31</property>
</function>
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| dateFormat | 日期格式 |
| sBeginDate | 开始日期 |
| sEndDate | 结束日期，如果配置了结束日期，则代码数据到达了这个日期的分片后，会重复从开始分片插入 |
- 配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。
create table tb_monthpart(
id bigint not null comment 'ID' primary key,
name varchar(100) null comment '姓名',
create_time date null
);
insert into tb_monthpart(id,name ,create_time) values(1,'Tom','2022-01-01');
insert into tb_monthpart(id,name ,create_time) values(2,'Cat','2022-01-10');
insert into tb_monthpart(id,name ,create_time) values(3,'Rose','2022-01-31');
insert into tb_monthpart(id,name ,create_time) values(4,'Coco','2022-02-20');
insert into tb_monthpart(id,name ,create_time) values(5,'Rose2','2022-02-25');
insert into tb_monthpart(id,name ,create_time) values(6,'Coco2','2022-03-10');
insert into tb_monthpart(id,name ,create_time) values(7,'Coco3','2022-03-31');
insert into tb_monthpart(id,name ,create_time) values(8,'Coco4','2022-04-10');
insert into tb_monthpart(id,name ,create_time) values(9,'Coco5','2022-04-30');
7、MyCat监控管理
1. MyCat原理
- 在MyCat中，当执行一条SQL语句时，MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作，最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库，
- 数据库将数据执行完毕后，如果有返回的结果，则将结果返回给MyCat，最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作，最终再将结果返回给客户端。
- 而在MyCat的使用过程中，MyCat官方也提供了一个管理监控平台MyCat-Web（MyCat-eye）。
- Mycat-web 是 Mycat 可视化运维的管理和监控平台，弥补了 Mycat 在监控上的空白，帮 Mycat 分担统计任务和配置管理任务。
- Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。
- Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等，具备 IP 白名单、邮件告警等模块，还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
2. MyCat管理
- Mycat默认开通2个端口，可以在server.xml中进行修改。
- 8066 数据访问端口，即进行 DML 和 DDL 操作。
- 9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理mycat的整个集群状态。
- 连接MyCat的管理控制台：
[root@localhost ~]# mysql -h 172.16.196.147 -P 9066 -uroot -p1992.12.24
| 命令 | 含义 |
|---|---|
| show @@help | 查看Mycat管理工具帮助文档 |
| show @@version | 查看Mycat的版本 |
| reload @@config | 重新加载Mycat的配置文件 |
| show @@datasource | 查看Mycat的数据源信息 |
| show @@datanode | 查看MyCat现有的分片节点信息 |
| show @@threadpool | 查看Mycat的线程池信息 |
| show @@sql | 查看执行的SQL |
| show @@sql.sum | 查看执行的SQL统计 |
mysql> show @@version;
+---------------------------------------------+
| VERSION |
+---------------------------------------------+
| 5.6.29-mycat-1.6.7.3-release-20210913163959 |
+---------------------------------------------+
1 row in set (0.01 sec)
mysql> show @@datasource;
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | master | mysql | 172.16.196.147 | 3306 | W | 0 | 10 | 1000 | 19 | 3 | 6 |
| dn3 | master | mysql | 172.16.196.150 | 3306 | W | 0 | 10 | 1000 | 16 | 3 | 3 |
| dn2 | master | mysql | 172.16.196.149 | 3306 | W | 0 | 10 | 1000 | 16 | 3 | 3 |
| dn5 | master | mysql | 172.16.196.149 | 3306 | W | 0 | 10 | 1000 | 16 | 3 | 3 |
| dn4 | master | mysql | 172.16.196.147 | 3306 | W | 0 | 10 | 1000 | 19 | 3 | 6 |
| dn6 | master | mysql | 172.16.196.150 | 3306 | W | 0 | 10 | 1000 | 16 | 3 | 3 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
6 rows in set (0.00 sec)
mysql> show @@threadpool;
+------------------+-----------+--------------+-----------------+----------------+------------+
| NAME | POOL_SIZE | ACTIVE_COUNT | TASK_QUEUE_SIZE | COMPLETED_TASK | TOTAL_TASK |
+------------------+-----------+--------------+-----------------+----------------+------------+
| Timer | 2 | 0 | 0 | 3705 | 3705 |
| BusinessExecutor | 4 | 0 | 0 | 82 | 82 |
+------------------+-----------+--------------+-----------------+----------------+------------+
2 rows in set (0.01 sec)
mysql> reload @@config;
Query OK, 1 row affected (0.05 sec)
Reload config success
3. MyCat-eye
3.1 介绍
- Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。
- 他通过JDBC连接对Mycat、Mysql监控，监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。
- Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。
3.2 安装
- zookeeper安装
# 上传安装包 zookeeper-3.4.6.tar.gz
# 解压
[root@localhost ~]# tar -zxvf zookeeper-3.4.6.tar.gz -C /usr/local/
# 创建数据存放目录
[root@localhost ~]# cd /usr/local/zookeeper-3.4.6/
[root@localhost zookeeper-3.4.6]# mkdir data
# 修改配置文件名称并配置
[root@localhost zookeeper-3.4.6]# cd conf/
[root@localhost conf]# mv zoo_sample.cfg zoo.cfg
[root@localhost conf]# vim zoo.cfg
# 配置数据存放目录
dataDir=/usr/local/zookeeper-3.4.6/data
# 启动Zookeeper
[root@localhost zookeeper-3.4.6]# bin/zkServer.sh start
JMX enabled by default
Using config: /usr/local/zookeeper-3.4.6/bin/../conf/zoo.cfg
Starting zookeeper ... STARTED
[root@localhost zookeeper-3.4.6]# bin/zkServer.sh status
JMX enabled by default
Using config: /usr/local/zookeeper-3.4.6/bin/../conf/zoo.cfg
Mode: standalone
- Mycat-web安装
# 上传安装包 Mycat-web.tar.gz
# 解压
[root@localhost ~]# tar -zxvf Mycat-web.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/mycat-web/
[root@localhost mycat-web]# ll
total 28
drwxr-xr-x. 2 root root 45 Oct 20 2015 etc # jetty配置文件
drwxr-xr-x. 3 root root 135 Oct 20 2015 lib # 依赖jar包
drwxr-xr-x. 7 root root 168 Jan 2 2017 mycat-web # mycat-web项目
-rwxr-xr-x. 1 root root 116 Oct 20 2015 readme.txt
-rwxr-xr-x. 1 root root 17125 Oct 20 2015 start.jar # 启动jar
-rwxr-xr-x. 1 root root 381 Oct 20 2015 start.sh # linux启动脚本
# 启动
[root@localhost mycat-web]# sh start.sh
nohup: ignoring input and appending output to 'nohup.out'
3.3 访问
- http://172.16.196.147:8082/mycat/
3.4 配置
- 开启MyCat的实时统计功能(server.xml)
<!-- 1为开启实时统计、0为关闭 -->
<property name="useSqlStat">1</property>
- 在Mycat监控界面配置服务地址
469
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
