MySQL运维DBA应用与实践

# MySQL 运维 DBA 应用与实践

# 1. 日志

在任何一种数据库中，都会有各种各样的日志，这些日志记录了数据库运行的各个方面。可以帮助数据库管理员追踪数据库曾经发生的一些事情。

对于 MySQL 数据库，提供了四种不同的日志帮助我们追踪。

错误日志
二进制日志
查询日志
慢查询日志

# 1.1 错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld (MySQL 服务) 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录 /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_verbosity | 3                   |
+---------------------+---------------------+

# 1.2 二进制日志

二进制日志 (BINLOG) 记录了所有的 DDL (数据定义语言) 语句和 DML (数据操纵语言) 语句，但不包括数据查询（SELECT、 SHOW）语句。

作用:

①. 灾难时的数据恢复；

②. MySQL 的主从复制。

在 MySQL5.7 版本中，默认二进制日志是关闭着的，涉及到的参数如下:

# 1.2.1 开启 bin-log 记录

1.1改修配置文件
[root@db01 ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
max_binlog_size=500M
expire_logs_days=15

1.2查看是否开启binlog.
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| log_bin                                | ON                             |
| log_bin_basename                       | /var/lib/mysql/mysql-bin       |
| log_bin_index                          | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                            |
| log_bin_use_v1_row_events              | OFF                            |
| log_builtin_as_identified_by_password  | OFF                            |
| log_error                              | /var/log/mysqld.log            |
| log_error_verbosity                    | 3                              |
| log_output                             | FILE                           |
| log_queries_not_using_indexes          | OFF                            |
| log_slave_updates                      | OFF                            |
| log_slow_admin_statements              | OFF                            |
| log_slow_slave_statements              | OFF                            |
| log_statements_unsafe_for_binlog       | ON                             |
| log_syslog                             | OFF                            |
| log_syslog_facility                    | daemon                         |
| log_syslog_include_pid                 | ON                             |
| log_syslog_tag                         |                                |
| log_throttle_queries_not_using_indexes | 0                              |
| log_timestamps                         | UTC                            |
| log_warnings                           | 2                              |
+----------------------------------------+--------------------------------+

1.3查看binlog
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     36825 |
| mysql-bin.000002 |    200464 |
| mysql-bin.000003 |    419809 |
+------------------+-----------+

1.4查看binlog日志保存天数 
# 0表示永久保留，expire_logs_days：保留指定日期范围内的binlog历史日志，上示例设置的15天内
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 15    |
+------------------+-------+
1 row in set (0.00 sec)

1.5查看binlog日志保存大小
#max_binlog_size：bin log日志每达到设定大小后，会使用新的bin log日志。如mysql-bin.000002达到500M后，创建并使用mysql-bin.000003文件作为日志记录。
mysql> show variables like 'max_binlog_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| max_binlog_size | 524288000 |
+-----------------+-----------+

1.6手动执行flush logs
#将会new一个新文件用于记录binlog
mysql> flush logs;

1.7手动清理binlog
#将mysql-bin.000010之前的日志清理掉
mysql> purge binary logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.01 sec)

#删除2022-04-21 18:08:00之前的binlog日志
mysql> purge binary logs before '2022-04-21 18:08:00';

#清除全部binlog
mysql> reset master;

# 1.2.2 日志格式

MySQL 服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式	含义
STATEMENT	基于 SQL 语句的日志记录，记录的是 SQL 语句，对数据进行修改的 SQL 都会记录在日志文件中。
ROW	基于行的日志记录，记录的是每一行的数据变更。(默认)
MIXED	混合了 STATEMENT 和 ROW 两种格式，默认采用 STATEMENT, 在某些特殊情况下会自动切换为 ROW 进行记录。

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法:

mysqlbinlog [ 参数选项] logfilename
参数选项:
	-d			指定数据库名称，只列出指定的数据库相关操作。
	-o			忽略掉日志中的前n行命令。
	-v			将行事件(数据变更)重构为SQL语句
	-vv			将行事件(数据变更)重构为SQL语句，并输出注释信息

mysql> use zh;
Database changed
mysql> show tables;
+----------------+
| Tables_in_zh   |
+----------------+
| account        |
| course         |
| dept           |
| emp            |
| score          |
| student        |
| student_course |
| tb_user        |
| tb_user_edu    |
| user           |
| user1          |
+----------------+
11 rows in set (0.00 sec)

mysql>  update tb_user_edu set university = "北京大学";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

#二进制日志查看
[root@db01 ~]# mysqlbinlog -v /var/lib/mysql/mysql-bin.000001

# 1.2.3 修改 binlog 格式

[root@db01 ~]# vim /etc/my.cnf
...
binlog_format=STATEMENT
...
[root@db01 ~]# systemctl restart mysqld

mysql>  update tb_user_edu set university = '清华大学';
[root@db01 ~]# mysqlbinlog -v /var/lib/mysql/mysql-bin.000002 
...
SET TIMESTAMP=1701440373/*!*/;
update tb_user_edu set university = '清华大学'
...

# 1.3 查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的 SQL 语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置︰

mysql> show variables like '%general%';
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| general_log      | OFF                     |
| general_log_file | /var/lib/mysql/db01.log |
+------------------+-------------------------+
2 rows in set (0.00 sec)

#开启查询日志功能
[root@db01 ~]# cat /etc/my.cnf
general_log=1
general_log_file=/var/lib/mysql/mysql_query.log 
[root@db01 ~]# systemctl restart mysqld

[root@db01 ~]# tail -f /var/lib/mysql/mysql_query.log 
2023-12-01T14:31:28.554384Z	    2 Field List	student 
2023-12-01T14:31:28.554743Z	    2 Field List	student_course 
2023-12-01T14:31:35.737041Z	    2 Query	show variables like '%general%'
2023-12-01T14:31:37.345179Z	    2 Query	show variables like '%general%'
2023-12-01T14:32:17.593471Z	    2 Query	SELECT DATABASE()
2023-12-01T14:32:17.593651Z	    2 Init DB	zh
2023-12-01T14:32:25.249258Z	    2 Query	select * from emp

# 1.4 慢查询日志

慢查询日志记录了所有执行时间超过参数 long_ query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的 SQL 语句的日志，默认未开启。 long_query_time 默认为 10 秒，最小为 0，精度可以到微秒。

[root@db01 ~]# vim /etc/my.cnf
#慢查询日志
slow_query_log=on
##执行时间参数
long_query_time=2
# 若没有指定，默认名字为hostname_slow.log
slow_query_log_file = /var/lib/mysql/slow-query.log
[root@db01 ~]# systemctl restart mysqld

#制造慢查询并执行
mysql> select sleep(3);
[root@db01 ~]# tail -f /var/lib/mysql/slow-query.log 
/usr/sbin/mysqld, Version: 5.7.43-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2023-12-01T14:47:57.763735Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 3.001229  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use zh;
SET timestamp=1701442077;
select sleep(3);

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用 log_slow_admin_statements 和更改此行为 log_queries_not_using_indexes , 如下所述。

#记录执行较慢的管理语句
log_slow_admin_statements = 1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

# 2. 主从复制

# 2.1 主从复制的概述

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行 (也叫重做) ，从而使得从库和主库的数据保持同步。

MySQL 支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制的有点主要包含以下三个方面：

主库出现问题，可以快速切换到从库提供服务；
实现读写分离，降低主库的访问压力；（如果增删改对主库查询对从库）
可以在从库中执行备份，以避免备份期间影响主库服务。

# 2.2 主从复制的原理

从上图来看，复制分成三步：

Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
从库 IO 线程读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log。
slave 重做中继日志中的事件，SQL 线程将改变反映它自己的数据。

# 2.3 主从复制的搭建

主从复制的搭建步骤：

准备主从复制服务器环境
完成主库配置
完成从库配置

# 2.3.1 服务器准备

# 2.3.2 主库配置

#1. 安装 MySQL

#1、关闭防火墙、selinux、环境配置
[root@db01 ~]# hostnamectl set-hostname db01
[root@db01 ~]# systemctl stop firewalld
[root@db01 ~]# systemctl disable firewalld
[root@db01 ~]# sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
[root@db01 ~]# sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[root@db01 ~]# yum install net-tools vim tree lrzsz wget unzip dos2unix bash-completion  lsof ntp ntpdate git -y
[root@db01 ~]# yum update -y --exclude=kernel* && reboot
[root@db01 ~]# echo 'Asia/Shanghai' >/etc/timezone
[root@db01 ~]# ntpdate time2.aliyun.com
[root@db01 ~]# crontab -e
*/5 * * * * /usr/sbin/ntpdate time2.aliyun.com &> /dev/nul
[root@db01 ~]# mkdir /soft /data /scripts /backup

#2、安装Mysql5.7
[root@db01 ~]# yum install -y mysql-community-server
[root@db01 ~]# systemctl start mysqld && systemctl enable mysqld

[root@db01 ~]# mysql -uroot -p$(awk '/temporary password/{print $NF}' /var/log/mysqld.log)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'passwd';
mysql> grant all on *.* to 'root'@'192.168.1.%' identified by 'passwd';

#3、允许root用户在任何地方进行远程登录，并具有所有库任何操作权限，具体操作如下：
mysql -u root -p"youpass"
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#4.配置主库
[root@db01 ~]# vim /etc/my.cnf
server-id=1                #mysql服务ID，保证整个集群环境中唯一， 取值范围: 1 - 2^{32}-1
log-bin=mysql-bin          #启动二进制日志
read-only=0                #是否只读,1代表只读, 0代表读写
#binlog-ignore-db=mysql    #忽略的数据，指不需要同步的数据库
#binlog-do-db=db01         #指定同步的数据库
[root@db01 ~]# systemctl restart mysqld

#5.创建repl用户，并设置密码，该用户可在任意主机连接该MySQL服务
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'passwd';

#6.查看master位置点
mysql> show master status;        
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      889 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 2.3.3 从库配置

参数名	含义	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

#1.配置从库
[root@db02 ~]# vim /etc/my.cnf
server-id=2           #mysql服务ID
read-only=1           #是否只读,1代表只读, 0代表读写
[root@db02 ~]# systemctl restart mysqld

#2..配置从服务器，连接主服务器
mysql> change master to master_host='192.168.40.150',master_user='repl',master_password='passwd',master_log_file='mysql-bin.000006',master_log_pos=889;

#3.开启从库
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#4.检查主从复制状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.150
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 889
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000006
             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: 889
              Relay_Log_Space: 526
              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: 9b911bea-43e6-11ee-b239-000c29074f5d
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

# 3. 分库分表

# 3.1 分库分表介绍

# 3.1.1 现在的问题

单数据库

所有数据都是存放在一个数据库文件里的，经过常年累月，内存不足了怎么办？

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

IO 瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘 IO，效率较低。请求数据太多，带宽不够，网络 IO 瓶颈。
CPU 瓶颈：排序、分组、连接查询、聚合统计等 SQL 会耗费大量的 CPU 资源，请求数太多，CPU 出现瓶颈。

分库分表的中心思想：
将数据分散存储，使得单一数据库 / 表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

# 3.1.2 拆分策略

# 3.1.3 垂直拆分策略

特点:

每个库的表结构都不一样。
每个库的数据也不一样。
所有，库的并集是全量数据。

特点:

每个表的结构都不一样。
每个表的数据也术一样，一般通过一列 (主键 / 外键) 关联。
所有表的并集是全量数据。

# 3.1.4 水平拆分策略

水平分库：以 “字段” 为依据，改为以 “行（记录）” 为依据。讲一个库的数据拆分到多个库

特点：

每个库的表结构都一样。
每个库的数据都不一样。
所有库的并集是全量数据。

特点：

每个表的表结构都一样。
每个表的数据都不一样。
所有表的并集是全量数据。

shardingJDBC：基于 AOP 原理，在应用程序中对本地执行的 SQL 进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持 java 语言，性能较高。
MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

# 3.2 Mycat 概述

Mycat 是开源的、活跃的、基于 Java 语言编写的 MySQL 数据库中间件。可以像使用 mysql 一样来使用 mycat，对于开发人员来说根本感觉不到 mycat 的存在。

优势：

性能可靠稳定
强大的技术团队
体系完善
社区活跃

Mycat 是采用 java 语言开发的开源的数据库中间件，支持 Windows 和 Linux 运行环境，下面介绍 MyCat 的 Linux 中的环境搭建。我们需要在准备好的服务器中安装如下软件。

服务器	安装软件	说明
192.168.40.213	JDK、Mycat	MyCat 中间件服务器
192.168.40.210	MySQL	分片服务器
192.168.40.211	MySQL	分片服务器
192.168.40.212	MySQL	分片服务器

JDK 安装

#解压jdk
[root@mycat ~]# tar xf jdk-8u371-linux-x64.tar.gz -C /usr/local
[root@mycat ~]# ln -s /usr/local/jdk1.8.0_371/ /usr/local/jdk

# 添加环境变量
[root@mycat ~]# vim /etc/profile.d/jdk.sh 
export JAVA_HOME=/usr/local/jdk
export PATH=$PATH:$JAVA_HOME/bin
export JRE_HOME=$JAVA_HOME/jre 
export CLASSPATH=$JAVA_HOME/lib/:$JRE_HOME/lib/

[root@mycat ~]# source /etc/profile
[root@mycat ~]# java -version

Mycat 安装

[root@mycat ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
[root@mycat ~]# ll /usr/local/mycat/
total 12
drwxr-xr-x 2 root root  190 Dec  2 22:15 bin
drwxrwxrwx 2 root root    6 Mar  1  2016 catlet
drwxrwxrwx 4 root root 4096 Dec  2 22:15 conf
drwxr-xr-x 2 root root 4096 Dec  2 22:15 lib
drwxrwxrwx 2 root root    6 Oct 28  2016 logs
-rwxrwxrwx 1 root root  217 Oct 28  2016 version.txt

#上传jar包
[root@mycat ~]# rz /usr/local/mycat/lib/mysql-connector-java-8.0.25.jar
[root@mycat lib]# chmod 777 mysql-connector-java-8.0.25.jar

# 3.2.1 Mycat 入门

由于 tb_gorder 表中数据量很大，磁盘 IO 及容量都到达了瓶颈，现在需要对 tb_order 表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上，具体的结构，参考下图：

# 3.2.2 Mycat 配置

[root@mycat ~]# cat /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="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
	
	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />
	
	<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://192.168.40.210:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.211:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.212:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</dataHost>
</mycat:schema>

[root@mycat mycat]# cat /usr/local/mycat/conf/server.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!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>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
		<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
		<property name="processorBufferPoolType">0</property>
		<!--默认是65535 64K 用于sql解析时最大文本长度 -->
		<!--<property name="maxStringLiteralLength">65535</property>-->
		<!--<property name="sequnceHandlerType">0</property>-->
		<!--<property name="backSocketNoDelay">1</property>-->
		<!--<property name="frontSocketNoDelay">1</property>-->
		<!--<property name="processorExecutor">16</property>-->
		<!--
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</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>
	
	<!-- 全局SQL防火墙设置 -->
	<!-- 
	<firewall> 
	   <whitehost>
	      <host host="127.0.0.1" user="mycat"/>
	      <host host="127.0.0.2" user="mycat"/>
	   </whitehost>
       <blacklist check="false">
       </blacklist>
	</firewall>
	-->
	
	<user name="root">
		<property name="password">Superman*2023</property>
		<property name="schemas">DB01</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">Superman*2023</property>
		<property name="schemas">DB01</property>
		<property name="readOnly">true</property>
	</user>

</mycat:server>

# 3.2.3 Mycat 启动

#1.启动mycat
[root@mycat mycat]# ./bin/mycat restart

#2.wrapper.log日志中常见错误
ERROR | wrapper | 2021/1/10 13:31:05 | Startup failed: Timed out waiting for signal from JVM.
ERROR | wrapper | 2021/1/10 13:31:05 | JVM did not exit on request, terminated

#3.启动Mycat超时,前往wrapper.conf配置超时策略
[root@mycat mycat]# vim /usr/local/mycat/conf/wrapper.conf
...
wrapper.startup.timeout=300     //添加此行，超时时间300秒
wrapper.ping.timeout=120

#4.查看mycat是否启动
[root@mycat mycat]# tail -f logs/wrapper.log
...
INFO   | jvm 1    | 2023/12/02 22:53:44 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat mycat]# netstat -lntp|grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      18028/java

# 3.2.4 分片测试

[root@db3 ~]#  mysql -h 192.168.40.213 -P 8066 -uroot -p'Superman*2023'
mysql: [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 (OpenCloundDB)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> show databases;
+----------+
| DATABASE |
+----------+
| DB01     |
+----------+
1 row in set (0.00 sec)

mysql> use DB01;
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 DB01 |
+----------------+
| tb_order       |
+----------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE TB_ORDER(
    -> id BIGINT(20) NOT NULL,
    -> title VARCHAR(100) NOT NULL,
    -> PRIMARY KEY (id)
    -> )ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
 OK!
mysql>INSERT INTO TB_ORDER(id,title) VALUES(1,'guods1');
mysql>INSERT INTO TB_ORDER(id,title) VALUES(2,'guods2');
mysql>INSERT INTO TB_ORDER(id,title) VALUES(3,'guods3');
mysql>INSERT INTO TB_ORDER(id,title) VALUES(4,'guods4');
mysql> select * from TB_ORDER;
+------+--------+
| id   | title  |
+------+--------+
|    1 | guods1 |
|    2 | guods2 |
|    3 | guods3 |
|    4 | guods4 |
+------+--------+
4 rows in set (0.03 sec)

数据写入到 db1 中，因为 mycat 分片规则为 0-50000000 存入节点 1,5000001-10000000 存入节点 2,10000001-15000000 存入节点 3，15000001 以上无法插入数据，需要增加数据节点。

[root@mycat mycat]# vim conf/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>
        </function>

...

[root@mycat mycat]# cat conf/autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1

#5000001-10000000存入节点2 
mysql> INSERT INTO TB_ORDER(id,title) VALUES(5000001,'guods5000001');
Query OK, 1 row affected (0.01 sec)
 OK!
 
#10000001-15000000存入节点3 
mysql> INSERT INTO TB_ORDER(id,title) VALUES(10000001,'guods10000001');
Query OK, 1 row affected (0.00 sec)
 OK!

#15000001以上无法插入数据，需要增加数据节点
mysql> INSERT INTO TB_ORDER(id,title) VALUES(15000001,'guods15000001');
ERROR 1064 (HY000): can't find any valid datanode :TB_ORDER -> ID -> 15000001

# 3.3 Mycat 配置

# 3.3.1 Schema 标签

schema 标签用于定义 MyCat 实例中的逻辑库，一个 MyCat 实例中，可以有多个逻辑库，可以通过 schema 标签来划分不同的逻辑库。MyCat 中的逻辑库的概念，等同于 MySQL 中的 database 概念，需要操作某个逻辑库下的表时也需要切换逻辑库 (use xxx)。

# 3.3.2 Datanode 标签

# 3.3.3 Datahost 标签

# 3.3.4 rule.xml

rule.xml 中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签： tableRule 、 Function 。

# 3.3.5 server.xml

# 3.4 Mycat 分片

# 3.4.1 分库分表 - MyCat 分片 - 垂直分库

场景：在业务系统中，涉及以下表结构，但是由于用户与订单每天都会产生大量的数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分，原有的数据库表如下。

ps: 分库不需要指定 rule，涉及分表需要使用 rule；

环境准备

①如图所示准备三台 Linux 服务器（ip 为：192.168.40.210、192.168.40.211、192.168.40.212）可以根据自己的实际情况进行准备。
②三台服务器上都安装 MySQL，在 192.168.40.213 服务器上安装 MyCat。
③三台服务器关闭防火墙或者开放对应的端口。
④分别在三台 MySQL 中创建数据库 shopping。

schema.xml 文件配置如下：

[root@mycat ~]# cat /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="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<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="id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="goods_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>
	
	<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://192.168.40.210:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.211:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.212:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</dataHost>
</mycat:schema>

server.xml 文件配置如下：

[root@mycat ~]# cat /usr/local/mycat/conf/server.xml 
...
	<user name="root">
		<property name="password">Superman*2023</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">Superman*2023</property>
		<property name="schemas">SHOPPING</property>
		<property name="readOnly">true</property>
	</user>

</mycat:server>

分库分表 - MyCat 分片 - 垂直分库 - 测试

垂直分库 - 测试

#1.重启mycat
[root@mycat ~]# /usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@mycat ~]# tail -f  /usr/local/mycat/logs/wrapper.log 
...
INFO   | jvm 1    | 2023/12/03 15:29:02 | MyCAT Server startup successfully. see logs in logs/mycat.log
create database shopping default charset utf8mb4;

#2.在3台节点创建shopping数据库
mysql> create database shopping default charset utf8mb4;
mysql> create database shopping default charset utf8mb4;
mysql> create database shopping default charset utf8mb4;

#3.登入mycat
[root@db3 ~]# mysql -h 192.168.40.213 -P 8066 -uroot -p'Superman*2023'
mysql> show databases;
+----------+
| DATABASE |
+----------+
| SHOPPING |
+----------+
1 row in set (0.01 sec)

#4.查看逻辑库
mysql> show databases;
+----------+
| DATABASE |
+----------+
| SHOPPING |
+----------+
1 row in set (0.01 sec)

#5.切换到SHOPPING数据库
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

#6.查看逻辑表
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)

#7.上传shopping-table.sql表结构文件与shopping-insert.sql数据文件

#8.执行shopping-table.sql文件
mysql> source /root/shopping-table.sql

#9.执行shopping-insert.sql文件
mysql> source /root/shopping-insert.sql

#10.查看三个数据库可以发现（根据schema.xml配置文件的配置进行了实现）
①192.168.40.210的数据库中存放了 tb_goods_base、tb_goods_brand、tb_goods_cat、tb_goods_desc、tb_goods_item这五张表
②192.168.40.211的数据库中存放了 tb_order_item、tb_order_master、tb_order_pay_log这三张表；
③192.168.40.212的数据库中存放了 tb_user、tb_user_address、tb_areas_provinces、tb_areas_city、tb_areas_region这五张表

exam1: 查询用户的收件人及收件人地址信息 (包含省、市、区)。

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      | 叶问      | 北京市    | 市辖区    | 西城区    | 咏春武馆总部       |
| java00001 | 李佳红    | 北京市    | 市辖区    | 崇文区    | 修正大厦           |
| deng      | 李小龙    | 北京市    | 市辖区    | 崇文区    | 永春武馆           |
| zhaoliu   | 赵三      | 北京市    | 市辖区    | 宣武区    | 西直门             |
| java00001 | 李嘉诚    | 北京市    | 市辖区    | 朝阳区    | 金燕龙办公楼       |
| java00001 | 李佳星    | 北京市    | 市辖区    | 朝阳区    | 中腾大厦           |
+-----------+-----------+-----------+-----------+-----------+--------------------+

ps: 此查询语句只涉及了一个分片所以查询成功

exam2: 查询每一笔订单及订单的收件地址信息 (包含省、市、区)。

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

ps: 此查询语句涉及多个分片所以查询报错，为了解决这个问题需要进行全局表配置

全局表配置

对于省、市、区 / 县表 tb_areas_provinces，tb_areas_city，tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。

1. 修改 MyCat—schema.xml 文件配置

schema.xml 文件配置如下：

[root@mycat ~]# cat /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="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<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="id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="goods_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="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" />
	</schema>
	
	<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://192.168.40.210:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.211:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.212:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</dataHost>
</mycat:schema>

2. 全局表测试

#1.删除3个节点上原有表

#2.重启mycat
[root@mycat ~]# /usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@mycat ~]# tail -f  /usr/local/mycat/logs/wrapper.log 
...
INFO   | jvm 1    | 2023/12/03 15:29:02 | MyCAT Server startup successfully. see logs in logs/mycat.log
create database shopping default charset utf8mb4;

#3.执行shopping-table.sql文件
[root@db3 ~]# mysql -h 192.168.40.213 -P 8066 -uroot -p'Superman*2023'
mysql> source /root/shopping-table.sql

#4.执行shopping-insert.sql文件
mysql> source /root/shopping-insert.sql

#5 exam1:查询用户的收件人及收件人地址信息(包含省、市、区)。
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;

#6 exam2:查询每一笔订单及订单的收件地址信息(包含省、市、区)
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;

# 3.4.2 分库分表 - MyCat 分片 - 水平分表

水平分表

场景：在业务系统中，有一张表（日志表），业务系统每天都会产生大量的日志数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分。

准备环境：

1. 三台 MySQL 中创建数据库 itcast

mysql> create database itcast default charset utf8mb4;
mysql> create database itcast default charset utf8mb4;
mysql> create database itcast default charset utf8mb4;

2.MyCat—server.xml 文件配置

server.xml 文件配置如下：

[root@mycat ~]# cat /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="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<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="id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="goods_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="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" />
	</schema>

        <schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
        	<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
        </schema>
	
	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
	<dataNode name="dn3" dataHost="dhost3" database="shopping" />

	<dataNode name="dn4" dataHost="dhost1" database="itcast" />
	<dataNode name="dn5" dataHost="dhost2" database="itcast" />
	<dataNode name="dn6" dataHost="dhost3" database="itcast" />
	
	<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://192.168.40.210:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.211:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.212:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</dataHost>
</mycat:schema>

3.MyCat—server.xml 文件配置

server.xml 文件配置如下：

[root@mycat ~]# cat /usr/local/mycat/conf/server.xml 
...
	<user name="root">
		<property name="password">Superman*2023</property>
		<property name="schemas">SHOPPING,ITCAST</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">Superman*2023</property>
		<property name="schemas">SHOPPING,ITCAST</property>
		<property name="readOnly">true</property>
	</user>

</mycat:server>

4.MyCat 启动

#1.重启mycat
[root@mycat ~]# /usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@mycat ~]# tail -f  /usr/local/mycat/logs/wrapper.log 
...
INFO   | jvm 1    | 2023/12/03 15:29:02 | MyCAT Server startup successfully. see logs in logs/mycat.log
create database shopping default charset utf8mb4;

#2.登入mycat
[root@db3 ~]# mysql -h 192.168.40.213 -P 8066 -uroot -p'Superman*2023'
mysql> show databases;
+----------+
| DATABASE |
+----------+
| ITCAST   |
| SHOPPING |
+----------+
2 rows in set (0.00 sec)

mysql> use ITCAST;
mysql> show tables;
+------------------+
| Tables in ITCAST |
+------------------+
| tb_log           |
+------------------+

#3.创建表结构及数据导入
mysql> 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;
Query OK, 0 rows affected (0.09 sec)
 OK!
查看三个数据库可以发现表和表结构都有了

#4.添加数据
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);

查看三个数据库内的tb_log表发现有数据了，数据的分布规则是 id模以3的结果为0的数据分布在第一个节点，id模以3的结果为1的数据分布在第二个节点，id模以3的结果为2的数据分布在第三个节点

# 3.3 分库分表 - 分片规则

# 3.3.1 分库分表 - 分片规则 - 范围分片

范围分片：根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片。

[root@mycat ~]# cat /usr/local/mycat/conf/autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

# 3.3.2 分库分表 - 分片规则 - 取模分片

取模分片：根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定该数据属于哪一个分片。

# 3.3.3 分库分表 - 分片规则 - 一致性 hash 算法

一致性 hash 算法：所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置。

一致性 hash 测试

schema.xml 配置

[root@mycat ~]# cat /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="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<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="id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="goods_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="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" />
	</schema>

        <schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
        	<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
        	<table name="tb_order" dataNode="dn4,dn5,dn6" primaryKey="id" rule="sharding-by-murmur" />
        </schema>
	
	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
	<dataNode name="dn3" dataHost="dhost3" database="shopping" />

	<dataNode name="dn4" dataHost="dhost1" database="itcast" />
	<dataNode name="dn5" dataHost="dhost2" database="itcast" />
	<dataNode name="dn6" dataHost="dhost3" database="itcast" />
	
	<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://192.168.40.210:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.211:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</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://192.168.40.212:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="Superman*2023" />
	</dataHost>
</mycat:schema>

rule.xml 配置

[root@mycat ~]# cat /usr/local/mycat/conf/rule.xml 
...
	<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><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
		<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
		<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
			用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
	</function>
...

重启 mycat 并插入数据测试

[root@mycat ~]# /usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

[root@mycat ~]# tail -f  /usr/local/mycat/logs/wrapper.log
...
INFO   | jvm 1    | 2023/12/03 22:17:47 | MyCAT Server startup successfully. see logs in logs/mycat.log

[root@db3 ~]# mysql -h 192.168.40.213 -P 8066 -uroot -p'Superman*2023'
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

mysql> show databases;
+----------+
| DATABASE |
+----------+
| ITCAST   |
| SHOPPING |
+----------+
2 rows in set (0.00 sec)

mysql> use ITCAST;
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 ITCAST |
+------------------+
| tb_log           |
| tb_order         |
+------------------+
2 rows in set (0.00 sec)

#创建表结构
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');

PS：数据按一致性 hash 分布在不同节点

MySQL

# MySQL 运维 DBA 应用与实践

# 1. 日志

# 1.1 错误日志

# 1.2 二进制日志

# 1.2.1 开启 bin-log 记录

# 1.2.2 日志格式

# 1.2.3 修改 binlog 格式

# 1.3 查询日志

# 1.4 慢查询日志

# 2. 主从复制

# 2.1 主从复制的概述

# 2.2 主从复制的原理

# 2.3 主从复制的搭建

# 2.3.1 服务器准备

# 2.3.2 主库配置

# 2.3.3 从库配置

# 3. 分库分表

# 3.1 分库分表介绍

# 3.1.1 现在的问题

# 3.1.2 拆分策略

# 3.1.3 垂直拆分策略

# 3.1.4 水平拆分策略

# 3.2 Mycat 概述

# 3.2.1 Mycat 入门

# 3.2.2 Mycat 配置

# 3.2.3 Mycat 启动

# 3.2.4 分片测试

# 3.3 Mycat 配置

# 3.3.1 Schema 标签

# 3.3.2 Datanode 标签

# 3.3.3 Datahost 标签

# 3.3.4 rule.xml

# 3.3.5 server.xml

# 3.4 Mycat 分片

# 3.4.1 分库分表 - MyCat 分片 - 垂直分库

# 3.4.2 分库分表 - MyCat 分片 - 水平分表

# 3.3 分库分表 - 分片规则

# 3.3.1 分库分表 - 分片规则 - 范围分片

# 3.3.2 分库分表 - 分片规则 - 取模分片

# 3.3.3 分库分表 - 分片规则 - 一致性 hash 算法

云原生K8s安全专家CKS认证考题详解

二进制高可用安装K8S集群