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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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> 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 \| 李佳星 \| 北京市 \| 市辖区 \| 朝阳区 \| 中腾大厦 \|
	+-----------+-----------+-----------+-----------+-----------+--------------------+

# 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集群