MD原文档下载:百度网盘
MySQL
一、相关概念
1.1 主流开源数据库
- 关系数据库: MySQL、PostgreSQL
- 非关系数据库: Redis、Elasticsearc、MongoDB
1.2 MySQL 分支
- MySQL 社区版
- Mariadb
- Percona Server
1.3 数据库结构
- 数据库 : 一台数据库服务器可以创建多个数据库,每个数据库对应一套独立业务
- Schema :
- 标准数据库结构 : 数据库实例 → 数据库 → Schema → 数据表
- MySQL : 把 数据库 和 Schema 合并成同一个东西，没有分层
- 表 : 数据库里存放同类数据的二维表格
- 行(row) : 表里一整条完整的数据
- 列(column) : 表里的单个字段,规定数据的类型
- 主键(Primary key) : 用于唯一标识单行记录,字段值不允许为空且全局唯一,每个表只能有 1 个主键,InnoDB 存储引擎会自动根据主键生成索引
- 唯一键(Unique key) : 用于唯一标识单行记录,字段值允许为空但不允许重复,每个表可以有多个唯一键,自动生成索引
- 索引 : 基于 B+Tree 实现的查询加速存储结构,包含普通索引、主键索引、唯一索引三类
1.4 MySQL 存储引擎
MySQL 每个数据库在系统里表现为目录,每个表表现为文件
# 查看 MySQL 数据目录
show variables like "data%";
# 默认数据存储目录
ls /var/lib/mysql
InnoDB 存储引擎 表文件 : .ibd 数据 + 索引文件
1.4.1 InnoDB 存储引擎
MySQL 5.5 版本之后默认存储引擎,面向高并发读写、需要事务保障的业务场景
# 查看 MySQL 支持的存储引擎
show engines;
1.4.1.1 InnoDB 存储引擎特点
- 事务 : 支持 ACID 四大特性
- 行级锁 : 修改数据时仅锁定被操作的单行记录
- MVCC 多版本并发控制 : 通过 Undo Log 生成数据历史快照，实现快照读不加锁
1.4.1.2 Buffer Pool、Redo Log、Undo Log 存储逻辑
- Buffer Pool : 核心内存缓冲池,缓存表数据页、索引页，减少磁盘 IO
- Redo Log : 重做日志,事务修改先写入 Redo Log 再异步刷盘;包含 日志缓冲（redo log buffer）和 磁盘上的日志文件（redo logfile）两部分
- Undo Log : 回滚日志,保存数据修改前的原始快照
1.4.2 MyISAM 存储引擎
- 不支持事务
- 表级锁
- 无崩溃安全恢复机制
仅适合 数据几乎不修改、仅批量查询的静态业务
二、安装
2.1 包安装
# Ubuntu 24.04
apt update
apt install -y mysql-server
# 查看状态
ss -ntlp | grep 3306
systemctl status mysql
# 卸载
apt purge -y mysql-*
# Ubuntu 24.04
apt update
apt install -y mariadb-server
# 卸载
apt purge -y mariadb-*
apt purge -y mysql-*
2.2 官方软件源安装
官方网站: MySQL
以 Ubuntu 为例
# 下载软件源包
wget https://dev.mysql.com/get/mysql-apt-config_0.8.39-1_all.deb
# 安装软件源包
dpkg -i mysql-apt-config_0.8.39-1_all.deb
# 更新软件源
apt update
# 过滤查看选择版本
apt list | grep mysql-server
mysql-server/unknown 8.4.11-1ubuntu24.04 all [upgradable from: 8.0.46-0ubuntu0.24.04.3]
# 安装
apt install -y mysql-server
2.3 二进制包安装(已编译)
官方下载: MySQL
# 下载 二进制包
wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.11-linux-glibc2.28-x86_64.tar.xz
# 安装依赖
apt install -y libaio-dev numactl libnuma-dev libncurses-dev
curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb
dpkg -i libaio1_0.3.113-4_amd64.deb
# 创建用户
useradd -r -s /sbin/nologin mysql
# 解压缩
tar xf mysql-8.4.11-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
# 创建软链接
ln -s /usr/local/mysql-8.4.11-linux-glibc2.28-x86_64/ /usr/local/mysql
# 创建环境变量
echo 'PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
# 加载环境变量
source /etc/profile.d/mysql.sh
# 验证
echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin:/usr/local/mysql/bin
whereis mysql
mysql: /usr/local/mysql /usr/local/mysql-8.4.11-linux-glibc2.28-x86_64/bin/mysql
mysql --version
mysql Ver 8.4.11 for Linux on x86_64 (MySQL Community Server - GPL)
# 创建主配置文件
mkdir /usr/local/mysql/etc
cat > /usr/local/mysql/etc/my.cnf << 'EOF'
[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log
EOF
# 创建数据目录
mkdir /usr/local/mysql/{data,log}
# 更改文件属主属组
chown -R mysql:mysql /usr/local/mysql/
# 使用空密码初始化
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
# 确认登录密码,提示使用的空密码
tail /usr/local/mysql/log/error.log
[Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
systemctl daemon-reload
systemctl start mysql
2.4 脚本安装(基于官方软件源)
# 安装脚本
vim install_mysql84_ubuntu24.sh
#!/bin/bash
# *************************************
# * 功能: Mysql8.4版本自动化部署脚本
# * 作者:
# * 联系:
# * 版本: 2026-08-14
# *************************************
#
set -euo pipefail
# 全局常量定义
readonly MYSQL_ROOT_FINAL_PWD="Dengtest@123"
readonly SOFT_DIR="/data/softs"
readonly APT_DEB="mysql-apt-config_0.8.39-1_all.deb"
readonly APT_DOWNLOAD_URL="https://dev.mysql.com/get/${APT_DEB}"
readonly MYSQL_APT_CONF_DIR="/etc/mysql-apt-config.d"
readonly SILENT_CONF_FILE="${MYSQL_APT_CONF_DIR}/90-mysql84-silent"
readonly MYSQL_DATA_DIR="/var/lib/mysql"
readonly MYSQL_SOCK="/var/run/mysqld/mysqld.sock"
# 函数1：初始化基础目录
function func_prepare_env() {
echo -e "\033[32m========== 步骤1：初始化软件目录 ==========\033[0m"
mkdir -pv "${SOFT_DIR}"
mkdir -pv "${MYSQL_APT_CONF_DIR}"
cd "${SOFT_DIR}"
}
# 函数2：安装MySQL官方APT软件源
function func_install_mysql_apt_source() {
echo -e "\033[32m========== 步骤2：部署MySQL官方APT源 ==========\033[0m"
if [[ ! -f "${APT_DEB}" ]]; then
wget "${APT_DOWNLOAD_URL}"
fi
DEBIAN_FRONTEND=noninteractive dpkg -i "${APT_DEB}"
cat > "${SILENT_CONF_FILE}" <<EOF
mysql-apt-config mysql-apt-config/select-server select mysql-8.4
mysql-apt-config mysql-apt-config/select-tools select none
mysql-apt-config mysql-apt-config/select-preview select none
mysql-apt-config mysql-apt-config/select-product select server
EOF
apt update -y
}
# 函数3：静默安装MySQL服务端
function func_install_mysql_server() {
echo -e "\033[32m========== 步骤3：静默安装mysql-community-server ==========\033[0m"
debconf-set-selections <<EOF
mysql-community-server mysql-community-server/root-pass password ""
mysql-community-server mysql-community-server/re-root-pass password ""
mysql-community-server mysql-community-server/remove-test-db select false
EOF
DEBIAN_FRONTEND=noninteractive apt install mysql-community-server -y
sleep 5
if ! systemctl is-active --quiet mysql; then
echo -e "\033[31mERROR：MySQL服务启动失败！终止脚本\033[0m"
exit 1
fi
echo "MySQL服务当前状态：$(systemctl is-active mysql)"
}
# 函数4：跳过授权表启动，重置root密码
function func_reset_root_password() {
echo -e "\033[32m========== 步骤4：跳过权限校验重置root账号密码 ==========\033[0m"
systemctl stop mysql
/usr/sbin/mysqld --skip-grant-tables --skip-networking --datadir=${MYSQL_DATA_DIR} --user=mysql &
local MYSQL_PID=$!
sleep 4
mysql -S ${MYSQL_SOCK} -e "
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '${MYSQL_ROOT_FINAL_PWD}';
FLUSH PRIVILEGES;
SELECT user,host,plugin,account_locked,password_expired FROM mysql.user WHERE user='root';
"
kill ${MYSQL_PID}
wait ${MYSQL_PID} 2>/dev/null || true
sleep 2
systemctl start mysql
sleep 3
echo "root密码重置完成，目标密码：${MYSQL_ROOT_FINAL_PWD}"
}
# 函数5：纯SQL无交互安全加固（替代交互式mysql_secure_installation）
function func_mysql_secure() {
echo -e "\033[32m========== 步骤5：执行纯SQL一键安全加固（无交互） ==========\033[0m"
mysql -uroot -p"${MYSQL_ROOT_FINAL_PWD}" -e "
-- 1. 删除匿名用户
DELETE FROM mysql.user WHERE User='';
-- 2. 禁止root远程登录
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
-- 3. 删除test数据库
DROP DATABASE IF EXISTS test;
-- 4. 回收test库所有权限
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
-- 5. 启用密码强度校验组件（可选，生产推荐）
INSTALL COMPONENT 'file://component_validate_password';
-- 刷新权限立即生效
FLUSH PRIVILEGES;
SELECT user,host FROM mysql.user;
SHOW DATABASES;
"
echo "安全加固全部执行完成：删除匿名账号、禁止root远程、删除test库、开启密码校验组件"
}
# 函数6：部署完成校验
function func_verify_install() {
echo -e "\033[32m========== 步骤6：部署结果校验 ==========\033[0m"
mysql -uroot -p"${MYSQL_ROOT_FINAL_PWD}" -e "SELECT VERSION() AS mysql_version;"
echo -e "\033[36m=============================================="
echo "MySQL8.4 自动化安装加固全部完成！"
echo "Root账号最终密码：${MYSQL_ROOT_FINAL_PWD}"
echo "本地登录命令：mysql -uroot -p${MYSQL_ROOT_FINAL_PWD}"
echo -e "\033[36m==============================================\033[0m"
}
# 主执行入口
function main() {
func_prepare_env
func_install_mysql_apt_source
func_install_mysql_server
func_reset_root_password
func_mysql_secure
func_verify_install
}
# 脚本执行入口
main
# 配套卸载脚本
cat > uninstall_mysql84_ubuntu24.sh << 'EOF'
#!/bin/bash
# *************************************
# * 功能: Mysql8.4版本自动化部署脚本
# * 作者:
# * 联系:
# * 版本: 2026-08-14
# *************************************
#
set -euo pipefail
# 全局常量，和安装脚本保持一致
readonly MYSQL_ROOT_FINAL_PWD="Dengtest@123"
readonly SOFT_DIR="/data/softs"
readonly APT_DEB="mysql-apt-config_0.8.39-1_all.deb"
readonly MYSQL_APT_CONF_DIR="/etc/mysql-apt-config.d"
readonly SILENT_CONF_FILE="${MYSQL_APT_CONF_DIR}/90-mysql84-silent"
readonly MYSQL_DATA_DIR="/var/lib/mysql"
readonly MYSQL_CONF_ROOT="/etc/mysql"
readonly MYSQL_LOG_DIR="/var/log/mysql"
# 函数0：预配置debconf，卸载自动选删除数据目录，屏蔽弹窗
function func_debconf_silent_uninstall() {
echo -e "\033[34m========== 预配置debconf静默卸载，自动确认删除数据目录 ==========\033[0m"
# 关键：自动选择Yes，删除/var/lib/mysql数据目录，不再弹出交互窗口
debconf-set-selections <<EOF
mysql-community-server mysql-community-server/remove-data-dir boolean true
mysql-community-server mysql-community-server/remove-test-db select true
EOF
}
# 函数1：停止MySQL服务、杀死残留进程
function func_stop_mysql() {
echo -e "\033[31m========== 步骤1：停止MySQL服务与残留进程 ==========\033[0m"
# 停止systemd服务
if systemctl list-unit-files | grep -q mysql.service;then
systemctl disable --now mysql || true
fi
# 强制杀死所有mysqld相关进程
pkill -f mysqld || true
sleep 2
echo "MySQL进程全部终止完成"
}
# 函数2：卸载MySQL官方软件包
function func_uninstall_mysql_pkg() {
echo -e "\033[31m========== 步骤2：卸载MySQL全套APT软件包 ==========\033[0m"
# 卸载服务端、客户端、apt源配置包
# DEBIAN_FRONTEND=noninteractive 彻底关闭所有弹窗
DEBIAN_FRONTEND=noninteractive apt purge -y mysql-* || true
# 自动清理无用依赖
apt autoremove -y || true
# 清理本地缓存安装包
apt clean
echo "MySQL软件包卸载完成"
}
# 函数3：删除所有MySQL配置、源、数据、日志目录
function func_clean_mysql_dir() {
echo -e "\033[31m========== 步骤3：清理MySQL所有数据、配置、日志目录 ==========\033[0m"
# 删除apt源锁定配置
rm -rf "${SILENT_CONF_FILE}"
rmdir "${MYSQL_APT_CONF_DIR}" 2>/dev/null || true
# 删除数据库数据目录（核心业务数据）
rm -rf "${MYSQL_DATA_DIR}"
# 删除全局配置目录
rm -rf "${MYSQL_CONF_ROOT}"
# 删除日志目录
rm -rf "${MYSQL_LOG_DIR}"
# 删除下载的deb安装包
rm -rf "${SOFT_DIR}/${APT_DEB}"
echo "MySQL目录文件全部清理完成"
}
# 函数4：重载systemd，清理残留服务单元
function func_reload_systemd() {
echo -e "\033[31m========== 步骤4：重载systemd清理服务残留 ==========\033[0m"
systemctl daemon-reload
systemctl reset-failed mysql || true
echo "systemd服务重置完成"
}
# 函数5：校验清理结果
function func_verify_clean() {
echo -e "\033[32m========== 步骤5：清理完成校验 ==========\033[0m"
local flag=0
# 校验进程
if pgrep -f mysqld >/dev/null;then
echo -e "\033[33m警告：仍存在mysqld进程\033[0m"
flag=1
else
echo "✅ 无mysqld运行进程"
fi
# 校验软件包
if dpkg -l | grep -q mysql-community;then
echo -e "\033[33m警告：仍残留mysql-community软件包\033[0m"
flag=1
else
echo "✅ 无MySQL相关软件包"
fi
# 校验数据目录
if [ -d "${MYSQL_DATA_DIR}" ];then
echo -e "\033[33m警告：数据目录${MYSQL_DATA_DIR}未删除\033[0m"
flag=1
else
echo "✅ 数据目录已清空"
fi
if [ $flag -eq 0 ];then
echo -e "\033[36m=============================================="
echo "MySQL8.4 全套环境彻底清理完毕！可重新执行安装脚本部署"
echo -e "\033[36m==============================================\033[0m"
else
echo -e "\033[31m=============================================="
echo "环境未完全清理，建议手动检查上述警告项"
echo -e "\033[31m==============================================\033[0m"
fi
}
# 主执行入口
function main() {
func_debconf_silent_uninstall
func_stop_mysql
func_uninstall_mysql_pkg
func_clean_mysql_dir
func_reload_systemd
func_verify_clean
}
# 脚本执行入口
main
EOF
三、MySQL 基本环境
3.1 部署测试环境
使用脚本安装部署 MySQL
# 创建命令别名简化登录命令
cat > /etc/profile.d/mysqld.sh <<EOF
# MySQL全局登录别名,免输账号密码,同时避免因为-p明文密码 导致登录提示信息
alias mysql='mysql -uroot -pDengtest@123'
EOF
source /etc/profile.d/mysqld.sh
3.2 客户端常用选项
| 选项 | 说明 |
|---|---|
-V | 查看客户端版本 |
-u | 指定连接用户名,默认为当前终端用户名 |
-p | 指定连接密码,默认为空 |
-h | 指定连接服务端主机,默认本机 |
-P | 指定端口,默认 3306 |
-S | 指定连接的 socket 文件 |
-D | 指定连接的数据库 |
-e | 非交互式执行命令,执行完成退出 |
3.3 客户端命令
远程登录 mysql 后可执行的客户端命令
| 命令 | 说明 |
|---|---|
\h | 客户端命令帮助 |
\s | 查看状态信息 |
\r | 重新连接 |
\c | 清屏 |
\q | 退出 |
\! | 执行 Linux 命令 |
\. | 引用脚本文件 |
3.4 非交互式执行命令
格式一 : mysql -e "命令1;命令2;..."
格式二 : mysql < 文件
# 查看当前登录用户
mysql -e "select user()"
# 查看用户列表
mysql -e 'SELECT user,host FROM mysql.user;'
# 修改全局环境变量
mysql -e 'SET GLOBAL server_id=222;'
四、SQL 语法
4.1 SQL 语法规范
- 除数据源外,不区分大小写,关键字推荐大写
- 不支持单词拆分
- 以 ; 结尾
4.2 SQL 语句
4.2.1 SQL 语句分类
- DDL 数据定义语言 :
CREATE、ALTER、DROP - DML 数据操作语言 :
INSERT、UPDATE、DELETE、TRUNCATE - DQL 数据查询语言 :
SELECT - DCL 数据控制语言 :
GRANT、REVOKE - TCL 事务控制语言 :
COMMIT、ROLLBACK
4.2.2 查看语句帮助
help SELECT;
4.2.3 字符集
MySQL 8.0 起默认字符集为 utf8mb4
# 查看默认字符集
show variables like 'character%';
4.3 数据库管理
# 查看数据库列表
show databases;
# 创建数据库
CREATE DATABASE testdb1;
# 查看数据库创建细节
SHOW CREATE DATABASE testdb1;
# 删除数据库,危险命令
DROP DATABASE testdb1;
# 如果不存在则创建
CREATE DATABASE if not exists testdb1;
# 如果存在则删除
DROP DATABASE if exists testdb1;
4.4 数据类型
根据数据表存储的数据选择合适的数据类型,一般由开发决定
4.5 表管理 (DDL 语句)
4.5.1 查看表
# 切换数据库
use mysql;
# 列出数据库中所有的表
show tables;
show tables from mysql;
# 查看表结构
desc mysql.user;
show columns from mysql.user;
# 查看表状态
show table status like 'user1'\G;
# 查看指定数据库中所有表信息
show table status from db1\G;
4.5.2 创建表
# 示例一 : 手动创建表
# 创建测试数据库
create database db1;
# 切换数据库
use db1;
# 创建表
create table test ( id int, name varchar (10), age int );
# 列出表
show tables;
# 查看表创建细节
show create table test;
# 查看表结构
desc test;
# 示例二 : 手动创建表
# 创建表
CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
#height DECIMAL(5,2),
gender ENUM('M','F') default 'M'
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
# 查看表结构
desc student;
# 示例三 : 模仿其他表创建表,包含表结构和数据
create table user1 select user,host from mysql.user;
# 查看表结构
desc user1;
# 查看表数据
select user,host from user1;
# 示例四 : 拷贝其他表结构创建新表,不包含数据
create table user2 like user1;
# 查看表结构
desc user2;
4.5.3 修改表结构
# 修改表名
ALTER TABLE student RENAME stu;
# 验证修改结果
show tables;
# 添加字段
alter table stu add phone varchar(11) after name;
# 查看表结构,验证修改结果
desc stu;
# 删除字段
alter table stu DROP phone;
# 修改字段数据类型
alter table stu add phone varchar(11) after name;
alter table stu MODIFY phone int;
# 同时修改字段名称和数据类型
alter table stu CHANGE phone mobile char(11);
# 修改字段默认值
alter table stu alter column age set default 18;
# 添加字段同时设置默认值
alter table stu add is_del bool default false;
4.5.4 主键操作
# 给 id 建立唯一索引
ALTER TABLE stu ADD UNIQUE INDEX uk_id(id);
# 删除原有主键
ALTER TABLE stu DROP PRIMARY KEY;
# 设置新主键
ALTER TABLE stu ADD PRIMARY KEY (mobile);
# 验证修改结果
desc stu;
# 创建表
create table stu2 select name,age from stu;
# 增加 id 字段且放置在第一列
alter table stu2 add column id int unsigned first;
# 设置 自增 和 主键
alter table stu2 modify column id int unsigned auto_increment primary key;
# 验证修改结果
desc stu;
# 删除 表 字段
alter table stu2 drop column id;
# 添加字段到第一列,无符号,自增,主键
alter table stu2 add column id int unsigned auto_increment primary key first;
4.5.5 删除表
# 删除表
drop table stu2;
# 验证结果
show tables;
4.6 数据操作 (DML 语句)
4.6.1 插入数据
# 查看表结构
desc stu;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| mobile | char(11) | YES | | NULL | |
| age | tinyint unsigned | YES | | 18 | |
| gender | enum('M','F') | YES | | M | |
| is_del | tinyint(1) | YES | | 0 | |
+--------+------------------+------+-----+---------+----------------+
# 插入数据
insert into stu (name,age) values ('zs','19');
# 查看数据
select * from stu;
# 插入多条数据
insert into stu (name,age) values ('zs','19'),('ls','20'),('ww','20');
# 将查询结果作为数据源插入
insert into stu (name,age) select name,age from stu;
# 插入指定数据
insert into stu (name,age) select name,age from stu where id=12;
4.6.2 更新数据
更新时必须指定更新的数据,否则将更改表中所有数据
可通过 2 种方式限制批量更新:
-
mysql登录时添加-U选项 -
编辑
/etc/mysql/mysql.cnf,添加客户端配置[client]safe-updates
开启后更新数据必须指定主键
# 更新指定数据
update stu set age=20 where id =12;
4.6.3 删除数据
# 根据条件删除数据
delete FROM stu where id>13;
# 清空表
truncate table stu;
# 验证
select * from stu;
4.7 数据查询 (DQL 语句)
基本语法 : select 显示内容 from 数据来源
# 测试环境准备
CREATE TABLE `stu` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` char(30) DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
`age` tinyint unsigned DEFAULT '18',
`is_del` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 插入测试数据
INSERT INTO stu (name,age)VALUES('test1',20),('test2',21),('test3',22);
INSERT INTO stu (name,mobile) values('user1',13812345678),('user2',11212345678);
INSERT INTO stu (name) VALUES ('user3'),('zhangsan'),('lisi'),('wangwu');
4.7.1 基础查询
# 字段使用别名显示
select user as 用户名,host as 客户端来源 from mysql.user;
# 查找 id<6 的数据
select * from stu where id<6;
# 支持算术操作符 + - * / %
select * from stu where id=6+1;
# 不连续查询
select * from stu where id in (3,6,9);
# 范围查询
select * from stu where id between 3 and 5;
# 空值查找
select * from stu where mobile is null;
# 非空查找
select * from stu where mobile is not null;
# 多条件查询
# id 大于 3 同时 小于 6
select * from stu where id<6 and id>3;
# id 小于 3 或 id 大于 8
select * from stu where id<3 or id>8;
# 取反, id 不小于 6
select * from stu where not id<6;
# 模糊匹配,支持 % (任意长度字符,约等于 Linux 的 *) 和 _ (任意单个字符,约等于 Linux 的 ?)
# name 字段以 t 开头的数据
select * from stu where name like "t%";
# name 字段以 2 结尾的数据
select * from stu where name like "%2";
# 查看以 sql 开头的系统变量
show variables like "sql%"\G;
4.7.2 统计
# 统计表的数据行数
select count(*) from stu;
# 查询 age 字段最小值,最大值,平均值
select min(age) from stu;
select max(age) from stu;
select avg(age) from stu;
# 求和
select sum(age) from stu;
4.7.3 分组统计
# 准备测试数据
UPDATE stu SET is_del='0' where id in (1,3,5,7);
UPDATE stu SET age='27' where id >= 3;
# 查看效果
select * from stu;
# 根据 is_del 分组统计数量
select count(*),is_del from stu group by is_del;
4.7.4 排序
# 按照 name 升序排序
select * from stu order by name;
# 按照 name 降序排序
select * from stu order by name desc;
# 按照 age 升序排序
select age from stu order by age;
# 去重
select distinct age from stu order by age;
select distinct(age) from stu order by age;
4.7.5 分页显示
# 测试数据装备
# 反复查询插入数据
insert into stu (name,mobile,age,is_del) select name,mobile,age,is_del from stu;
# 查看从 0 开始 10 条数据
select * from stu limit 0,10;
# 查看从 10 开始 10 条数据
select * from stu limit 10,10;
五、用户和权限管理
4.1 MySQL 配置文件
# MySQL 默认监听在本机,需要修改配置文件允许远程连接
ss -ntlp
LISTEN 0 151 127.0.0.1:3306 0.0.0.0:* users:(("mysqld",pid=3231,fd=23)
# 过滤 MySQL 相关配置文件
tree /etc/my*
/etc/mysql
├── conf.d
│ └── mysql.cnf
├── my.cnf -> /etc/alternatives/my.cnf
├── my.cnf.fallback
├── mysql.cnf # 客户端配置文件
└── mysql.conf.d
└── mysqld.cnf # 服务端配置文件
/etc/mysql-apt-config.d
└── 90-mysql84-silent
# 注释2行配置,使 MySQL 监听在所有地址
vim /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address = 127.0.0.1
# mysqlx-bind-address = 127.0.0.1
# 重启服务端
systemctl restart mysql
# 验证监听地址,监听在任意地址
ss -ntlp
*:3306
# 非交互修改监听地址
sed -i '/127.0.0.1/s/^/#/' /etc/mysql/mysql.conf.d/mysqld.cnf && systemctl restart mysql
4.2 MySQL 用户
MySQL 的用户独立存在,与 Linux 系统用户无关,仅用于 MySQL 服务的登录验证
MySQL 用户由 用户名、主机、密码等组成
host 限制可以登录的主机地址
host 支持 主机名、IP 地址、网段
# 登录 MySQL 查看用户列表,默认不包含可远程登录的用户,需要自行创建
select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost | # 默认 MySQL 的 root 用户仅允许本地登录
+------------------+-----------+
创建用户命令格式: CREATE USER '登录用户名'@'客户端来源范围' IDENTIFIED BY '登录密码';
# 创建用户
create user deng@'10.0.0.%' identified by '123456';
# 远程登录
mysql -udeng -p123456 -h 10.0.0.221
# 未授权用户无法创建数据库,生产环境一般使用 管理用户 创建数据库,再对业务用户授权该数据库的所有权限
create database db1;
ERROR 1044 (42000): Access denied for user 'deng'@'10.0.0.%' to database 'db1'
# 删除用户
drop user wp@'10.0.0.222';
4.3 权限管理
MySQL 用户权限决定了用户可以使用哪些 SQL 命令
生产环境一般根据业务需求创建数据库、创建用户并授权
# 授权命令格式
# 一、赋予所有权限,包括 grant
GRANT ALL ON 数据库.数据表 TO '登录用户名'@'客户端来源范围' WITH GRANT OPTION;
# 二、赋予所有权限,不包括 grant
GRANT ALL ON 数据库.数据表 TO '登录用户名'@'客户端来源范围';
# 三、赋予指定权限
# 数据库.数据表 常见样式
*.* # 整个数据库所有对象
db1.* # 整个 db1 数据库下的所有对象
db1.table1 # 只能操作 db1 数据库的 table1 表
# 给指定用户赋予所有权限,创建管理员
grant all on *.* to deng@'10.0.0.%' with grant option;
# 业务示例
create database wordpress;
create user wp@'10.0.0.222' identified by 'wordpress';
grant all on wordpress.* to wp@'10.0.0.222';
4.4 root 密码丢失
# 编辑服务端配置,跳过权限验证表,登录不需要密码,且只允许本地登录
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
skip-grant-tables
skip-networking
# 重启服务端
systemctl restart mysql
# 本地免密登录
mysql
# 清空原密码
update mysql.user set authentication_string='' where user='root' and host='localhost';
# 刷新权限,修改密码后必须执行
flush privileges;
# 删除前面添加的服务端配置
vim /etc/mysql/mysql.conf.d/mysqld.cnf
skip-grant-tables # 删除
skip-networking # 删除
# 重启服务端
systemctl restart mysql
# root 用户无密码登录
mysql
# 修改 root 密码
ALTER USER root@'localhost' IDENTIFIED BY 'Dengtest@123';
# 刷新权限
flush privileges;
# 验证
mysql -uroot -pDengtest@123
4.5 创建局域网远程登录管理员
# 创建用户
create user root@'10.0.0.%' identified by 'Dengtest@123';
# 给指定用户赋予所有权限
grant all on *.* to root@'10.0.0.%' with grant option;
# 刷新权限(创建用户可选执行,修改密码后必须执行)
flush privileges;
# 测试,如果不带 -u 参数,默认使用 root 登录
[root@ubuntu-240 ~ ]#mysql -pDengtest@123 -h10.0.0.221
六、navicat 图形化工具
Windows 安装软件
连接数据库
七、MySQL 配置文件及优先级
7.1 入口配置文件
/etc/mysql/my.cnf
顶层入口,仅包含 !includedir 指明子配置文件路径
不建议写入自定义参数
7.2 客户端配置文件
/etc/mysql/mysql.conf.d/mysql.cnf
存放 [client]、[mysql]、[mysqldump] 客户端连接相关参数
7.3 服务端配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf
存放 [mysqld] 服务端核心配置
7.4 静态全局参数
仅支持在配置文件中修改,修改后需要 重启 mysqld 服务 才能生效
# 创建自定义配置文件,写入静态参数
vim /etc/mysql/conf.d/port.cnf
[mysqld]
port = 3307
# 校验是否存在语法错误
mysqld --validate-config
# 重启 MySQL 服务端
systemctl restart mysql
# 验证修改
ss -ntl
*:3307
# 查看参数
mysql -e "show variables like 'port';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
# 恢复默认端口
rm /etc/mysql/conf.d/port.cnf
systemctl restart mysql
mysql -e "show variables like 'port';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
7.5 动态运行参数
临时修改: SET GLOBAL
持久修改: SET PERSIST, 写入 mysqld-auto.cnf ,重启不丢失 ; 该文件存放在 数据目录
# 默认最大连接数
mysql -e "show variables like 'max_connections';"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
# 临时修改最大连接数
SET GLOBAL max_connections = 2000;
# 持久修改最大连接数
SET PERSIST max_connections = 2000;
# 查看修改后最大连接数
mysql -e "show variables like 'max_connections';"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
# 查看持久修改生成配置文件
cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "2000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1786776556364672}}}}
7.6 用户自定义配置文件
/etc/mysql/conf.d/
在该目录下用户创建的 .cnf 配置文件
7.7 配置文件加载优先级
越晚加载的配置文件优先级越高
- 入口配置文件
/etc/mysql/my.cnf - 客户端配置文件
/etc/mysql/mysql.conf.d/mysql.cnf - 服务端配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf - 用户自定义配置文件
/etc/mysql/conf.d/ - 数据目录
mysqld-auto.cnf
同目录下多个 .cnf 配置文件时,按文件名顺序加载
生产环境推荐将业务调优、自定义参数统一放置在类似自定义配置文件 /etc/mysql/conf.d/99-tune.cnf,晚加载,避免被系统参数覆盖
八、MySQL 变量
8.1 变量作用域区分
8.1.1 GLOBAL 全局变量
作用于所有新客户端连接,已有连接不会自动更新参数,需要断开重连
临时修改: SET GLOBAL,重启 MySQL 失效
持久修改: SET PERSIST
修改全局变量需要用户拥有管理权限
# 临时修改示例
# 登录 MySQL
mysql
# 查看 慢查询 参数
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
# 临时修改全局变量 慢查询 参数
set global long_query_time=1;
# 由于是全局变量,当前连接未生效
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
# 重新连接
\r
# 查看 慢查询 参数,修改已生效
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 1.000000 |
+-------------------+
# 退出登录
\q
# 重启 MySQL 服务
systemctl restart mysql
# 重新登录
mysql
# 临时修改失效
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
# 持久修改示例
# 持久修改全局变量 慢查询 参数
set persist long_query_time=1;
# 由于是全局变量,当前连接未生效
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
# 重新连接
\r
# 查看 慢查询 参数,修改已生效
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 1.000000 |
+-------------------+
# 退出登录
\q
# 查看持久修改在数据目录生成的配置文件
cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_variables": {"long_query_time": {"Value": "1.000000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1786777629296421}}}
# 重启 MySQL 服务
systemctl restart mysql
# 确认 慢查询 参数修改重启有效
mysql -e "select @@long_query_time;"
+-------------------+
| @@long_query_time |
+-------------------+
| 1.000000 |
+-------------------+
8.1.2 会话变量
仅影响当前客户端连接,会话断开后自动销毁
语法格式 : SET 变量名 = 值;
# 查看 事务自动提交 是否启用
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
# 关闭当前会话 事务自动提交
set autocommit=0;
# 查看 事务自动提交 是否启用
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
# 重新连接
\r
# 查看 事务自动提交 是否启用
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
8.2 查看系统运行变量
# 模糊匹配查询参数
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE '%connection%';
# 查看 innodb 缓冲池大小
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
# 查看状态
# 查看 select 语句执行次数
SHOW GLOBAL STATUS LIKE 'Com_select';
# 查看 insert 语句执行次数
SHOW GLOBAL STATUS LIKE 'Com_insert';
# 查看 innodb 缓冲池命中情况
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
# 查看活跃连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';
8.3 InnoDB 参数调优
8.3.1 缓冲池大小
专用数据库服务根据物理内存总容量配置缓冲池大小
- 物理内存 ≤ 16G : 50% - 60%
- 物理内存 32G ~ 64G : 60% - 70%
- 物理内存 ≥ 128G : 70% - 80%
# 配置示例,物理内存 64G, 分配 40G 作为缓冲池
[mysqld]
innodb_buffer_pool_size = 40G
# 查看 innodb 缓冲池大小
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
# 查看 innodb 缓冲池命中情况
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
8.3.2 缓冲池多实例
将整块缓冲池拆分为多实例,避免锁竞争,提高高并发读写能力
缓冲池 > 1G ,建议配置多实例
推荐配置
- 缓冲池 1G ~ 16G : 1 ~ 4
- 缓冲池 16G ~ 64G : 4 ~ 8
- 缓冲池 64G : 最多不超过 16
# 配置示例,物理内存 64G, 分配 40G 作为缓冲池,拆分为 8 个实例
[mysqld]
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 8
# 重启 MySQL 服务
systemctl restart mysql.service
# 查看缓冲池实例数量
show variables like 'innodb_buffer_pool_instances';
九、索引
9.1 相关概念
- MySQL 索引独立于数据
- 索引为树结构
- MySQL 使用 B+ 树 索引结构
9.2 示例
9.2.1 准备测试环境
# 准备测试环境
# 创建数据库
create database testdb;
use testdb;
# 创建表
CREATE TABLE student (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT NULL,
gender ENUM('M', 'F') DEFAULT 'M',
PRIMARY KEY (id)
);
# 查看表结构
desc student;
# 默认会在主键上创建索引
show index from student\G
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.00 sec)
# 向表中插入数据
INSERT INTO student (name, age, gender) VALUES ('Alice', 20, 'F'),('Bob', 22,
'M'),('Charlie', 23, 'M'),('Diana', 21, 'F'),('Evan', 22, 'M'),('Fiona', 20,
'F'),('George', 23, 'M'),('Hannah', 21, 'F'),('Ian', 22, 'M'),('Jackie', 20,
'F'),('Kevin', 23, 'M'),('Laura', 21, 'F'),('Michael', 22, 'M'),('Nina', 20,
'F'),('Oliver', 23, 'M'),('Paula', 21, 'F'),('Quincy', 22, 'M'),('Rachel', 20,
'F'),('Steven', 23, 'M'),('Tamara', 21, 'F');
9.2.2 explain 检查语句是否使用索引
# 查看查询语句是否使用索引
explain select * from student where id=12\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: const # 访问类型,从好到坏依次是 NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
possible_keys: PRIMARY # 查询可能会用到的索引
key: PRIMARY # 用哪了主键索引来优化查询
key_len: 4
ref: const
rows: 1 # 为了找到所需的行而需要读取的行数
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
# 未使用索引
explain select * from student where name="Evan"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL # 访问类型,全表扫描
possible_keys: NULL # 没有可用索引
key: NULL # 未使用索引
key_len: NULL
ref: NULL
rows: 20 # 读取了 20 条数据,整个表就 20 条数据
filtered: 10.00
Extra: Using where # 在存储引擎检索后再进行过滤
1 row in set, 1 warning (0.00 sec)
9.2.3 创建索引
语法格式 : CREATE INDEX 索引名 ON 表名(字段名)
# 创建索引
create index idx_name on student(name);
9.2.4 验证索引
# 查看表的索引信息
show index from student\G
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY # 主键索引
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: idx_name # 二级索引
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 20
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.00 sec)
# 再次检查语句是否使用索引
explain select * from student where name="Evan"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ref
possible_keys: idx_name # 可用的索引
key: idx_name # 使用的索引
key_len: 82
ref: const
rows: 1 # 只读取了 1 条数据
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
# 左前缀(右模糊)匹配
explain select * from student where name like 'g%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: range
possible_keys: idx_name
key: idx_name # 使用的索引
key_len: 82
ref: NULL
rows: 1 # 只读取了 1 条数据
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
# 左模糊匹配,不支持索引
explain select * from student where name like '%g'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL # 全表扫描
possible_keys: NULL
key: NULL # 未使用索引
key_len: NULL
ref: NULL
rows: 20 # 读取了全部数据
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
# 包含匹配同左模糊,不支持索引
explain select * from student where name like '%g%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
9.2.5 联合索引
一个索引包含多个字段
语法格式 : CREATE INDEX 索引名 ON 表名(字段名,字段名...)
# 创建索引
CREATE INDEX idx_age_gender ON student(age, gender);
# 查看索引信息
show index from student\G
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: idx_age_gender # 相同索引名,表示是同一个索引
Seq_in_index: 1 # 字段在联合索引里的排序顺序,最左字段必须使用
Column_name: age
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 4. row ***************************
Table: student
Non_unique: 1
Key_name: idx_age_gender # 相同索引名,表示是同一个索引
Seq_in_index: 2 # 字段在联合索引里的排序顺序
Column_name: gender
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
# 使用索引
# 联合索引必须使用最左字段
# 使用最左字段查询
explain select * from student where age=22\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ref
possible_keys: idx_age_gender
key: idx_age_gender # 使用的索引
key_len: 2
ref: const
rows: 5
filtered: 100.00
Extra: NULL
# 联合条件查询
explain select * from student where age=22 and gender='M'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ref # 等值索引查找
possible_keys: idx_age_gender
key: idx_age_gender # 使用的索引
key_len: 4
ref: const,const # 2 个等值索引
rows: 5 # 读取的数据行数
filtered: 100.00
Extra: Using index condition
# 使用最左字段 + 第二字段模糊
explain select * from student where age=22 and gender like 'M%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ref
possible_keys: idx_age_gender
key: idx_age_gender # 使用的索引
key_len: 2
ref: const
rows: 5
filtered: 50.00
Extra: Using index condition
# 不使用最左字段,索引失效
explain select * from student where gender='M'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL # 未使用索引
key_len: NULL
ref: NULL
rows: 20
filtered: 50.00
Extra: Using where
# 最左字段使用范围查询,第二字段无法使用索引,结果依然使用全表扫描
explain select * from student where age>20 and gender='M'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL # 全表扫描
possible_keys: idx_age_gender
key: NULL
key_len: NULL
ref: NULL
rows: 20
filtered: 37.50
Extra: Using where
1 row in set, 1 warning (0.00 sec)
9.2.6 删除索引
# 删除索引
drop index idx_age_gender on student;
drop index idx_name on student;
# 验证,只剩下自动创建的主键索引
show index from student\G
# 再次测试,无法使用索引查询
explain select * from student where name="Evan"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL # 全表扫描
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20
filtered: 10.00
Extra: Using where
9.3 索引规范
9.3.1 设计规范
- 联合索引 :
- 左侧放等值字段,范围字段放右侧
- 高频查询字段放左侧
- 需要使用范围查询,模糊匹配的字段放右侧
-
高选择性字段适合建立索引
-
字段值单一,仅有少量固定值不适合单独建立索引,例如性别
-
经常变动的数据,即使常用,也不适合建立索引
9.3.2 使用规范
使用联合索引不能跳过左侧字段,左侧字段不能使用范围查询
十、事务
仅 InnoDB 存储引擎支持事务
10.1 ACID 事务四大特性
- 原子性 (Atomicity) : 事务是不可分割的最小单元,要么全部完成,要么全部失败回滚到起始状态
- 一致性 (Consistency) : 事务执行前后，数据的完整性约束不会被破坏;一致性是目标，原子性、隔离性、持久性是手段
- 隔离性 (Isolation) : 多个事务并发执行时，事务之间相互隔离，互不干扰,通过MVCC 多版本并发控制 + 锁机制实现
- 持久性 (Durability) : 事务提交成功后，对数据的修改是永久生效的
10.2 事务示例
# 复制会话,同时连接到 MySQL, \s 查看会话信息,两个窗口属于不同会话
# 在 会话1 操作
# 开启事务
begin;
# 修改数据
update student set age=30 where id=11;
# 插入数据
insert into student (name,age,gender)values('zhangsan',30,'F');
# 查询
select * from student;
# 在 会话2 查询数据, 会话1 的操作无法被 会话2 查询到
select * from student;
# 在 会话1 提交事务,事务结束
commit;
# 在 会话2 再次查询数据,可以看到数据的更改
select * from student;
# 事务回滚
# 在 会话1 操作
# 开启事务
begin;
# 修改数据
update student set age=26 where id=11;
insert into student (name,age,gender)values('lisi',30,'F');
# 查询
select * from student;
# 在 会话2 查询数据, 会话1 的操作无法被 会话2 查询到
select * from student;
# 在 会话1 撤销事务,事务结束
rollback;
# 查询,数据回归 begin 时状态
select * from student;
事务操作仅支持 DML 语句
truncate 和 drop 属于 DDL 语句不支持事务回滚
10.3 事务自动提交
MySQL 启用了自动提交能力,简单的增删改自动生效
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
autocommit 同时支持 全局 和 会话 级修改
# 会话1 临时关闭自动提交
set autocommit=0;
# 确认效果
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
# 会话1 更新数据
update student set age=36 where id=11;
# 会话2 无法查询到数据更新
select * from student where id=11;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 11 | Kevin | 30 | M |
+----+-------+------+--------+
# 会话1 手动提交
commit;
# 会话2 再次查询,数据更新
select * from student where id=11;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 11 | Kevin | 36 | M |
+----+-------+------+--------+
10.4 事务支持保存点
保存点可以让事务回滚到特定时间点
# 开启事务
begin;
# 插入数据
insert into student (name,age,gender)values('test1',21,'M');
# 创建保存点 p1
savepoint p1;
# 继续插入数据
insert into student (name,age,gender)values('test2',19,'F');
# 创建保存点 p2
savepoint p2;
# 继续插入数据
insert into student (name,age,gender)values('test3',25,'M');
# 创建保存点 p3
savepoint p3;
# 查询数据
select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 23 | test1 | 21 | M |
| 24 | test2 | 19 | F |
| 25 | test3 | 25 | M |
+----+----------+------+--------+
# 回滚到保存点 p2
rollback to p2;
# 查询数据
select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 23 | test1 | 21 | M |
| 24 | test2 | 19 | F |
+----+----------+------+--------+
# 确认无误,提交事务
commit;
10.5 查看事务
# 查看正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
10.6 事务死锁
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
在 MySQL 中出现死锁时,MySQL 服务会自行处理,等待超时时长,自行回滚一个事务,防止死锁的出现
# 查看事务锁的超时时长,默认50秒
show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
10.7 事务异常
10.7.1 并发事务可能出现的三大问题
- 脏读 : 读取到未提交数据 ; 只在 读未提交 隔离级别出现
- 不可重复读 : 同一事务内,多次读取同一数据,中间被别的事务修改并提交,读取的结果不一样 ; 在 读未提交 和 读已提交 隔离级别出现
- 幻读 : 同一事务多次范围查询，其他事务插入 / 删除数据，导致前后行数不一致 ; 在 读未提交、读已提交 和 可重复读 隔离级别出现
10.7.2 事务隔离级别
- 读未提交 (READ UNCOMMITTED)
- 读已提交 (READ COMMITTED)
- 可重复读 (REPEATABLE READ) : MySQL 默认隔离级别,范围操作时可通过临时加锁规避幻读,即可规避三大问题
- 可串行化 (SERIALIZABLE) : 可避免三大问题,但并行性能差
# 查看当前事务隔离级别
select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
十一、日志
11.1 事务日志
事务日志文件大小 :
- 测试 / 低并发业务：1G ~ 2G
- 普通线上 OLTP 业务：2G ~ 4G
- 高写入、大批量导入业务：4G ~ 8G
# 日志文件
ls /var/lib/mysql
'#innodb_redo'
'#innodb_temp'
# Innodb事务日志相关配置
# 事物日志文件大小,
innodb_log_file_size 4G
# 事物日志文件数量,循环写
innodb_log_files_in_group 2
# 事物日志文件路径
innodb_log_group_home_dir ./
事务日志刷盘策略 :
1: 默认策略,每次事务提交 写入系统缓冲区 + 调用 fsync 强制同步磁盘,数据无丢失,但性能最差,适合金融、支付、订单等强一致性业务2: 每次事务提交 写入系统缓冲区 ,每秒后台统一 fsync 同步磁盘,性能强,但最多丢失 1 秒已提交事务(服务器断电),适合普通电商、后台管理业务
# 查看当前刷盘策略
select @@innodb_flush_log_at_trx_commit;
# 普通业务修改默认刷盘策略
[mysqld]
innodb_flush_log_at_trx_commit = 2
11.2 错误日志
11.2.1 错误日志路径
Ubuntu 默认错误日志路径 : /var/log/mysql/error.log
# 修改错误日志路径
[mysqld]
log_error = /var/log/mysql/error.log
11.2.2 错误日志级别
1: ERROR2: ERROR,WARNING 默认级别3: ERROR,WARNING,INFORMATION
# 查看当前错误日志级别
select @@log_error_verbosity;
11.2.3 查看错误日志
# 实时跟踪日志
tail -f /var/log/mysql/error.log
# 查看最后 50 行
tail -n 50 /var/log/mysql/error.log
# 过滤出包含 ERROR 级别的行
grep -i ERROR /var/log/mysql/error.log
11.2.4 修改日志时间使用本地时间
[mysqld]
log_timestamps = SYSTEM
11.2.5 错误日志与故障排查
tail /var/log/mysql/error.log
11.2.5.1 服务启动阶段报错
检查端口占用
11.2.5.2 运行期间崩溃
内存不足、磁盘损坏、内核 OOM 杀死进程、 ibd 文件损坏
11.2.5.3 权限报错
数据目录未修改属主属组为 mysql
chown -R mysql:mysql /var/lib/mysql
11.2.5.4 ibdata1 文件
ibdata1 是 InnoDB 共享系统表空间,存放数据字典、undo 日志、锁信息、doublewrite 缓冲区;
.ibd 只存放单表数据索引;ibdata1 一旦损坏整个 MySQL 实例无法启动
默认存放路径 : /var/lib/mysql/ibdata1
11.3 二进制日志
11.3.1 相关概念
二进制日志是离线日志,记录已提交的 DDL 和 DML 语句,不记录查询语句
# 查看二进制日志相关变量
show variables like "binlog%";
# 查看二进制日志开启状态,日志路径
show variables like "%log_bin%%";
# 二进制日志文件
ll /var/lib/mysql
total 91612
-rw-r----- 1 mysql mysql 180 Aug 18 09:48 binlog.000001
-rw-r----- 1 mysql mysql 404 Aug 18 09:48 binlog.000002
-rw-r----- 1 mysql mysql 157 Aug 18 09:48 binlog.000003
11.3.2 属性变量
log_bin: 全局变量,在服务端配置文件配置,重启服务端生效sql_log_bin: 会话级动态变量,允许临时设置二进制日志开关
11.3.3 二进制日志文件格式
Statement: 语句模式,日志文件小,但记录内容不太准确,生产环境不推荐Row: 行模式,日志文件大,内容准确,MySQL 8 默认,生产推荐Mixed: 混合模式, MySQL 自行判断 SQL 语句使用哪种模式记录,mariadb 默认模式
# 查看当前二进制日志文件格式
select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
11.3.4 二进制日志相关配置
sql_log_bin=1|0 #是否开启二进制日志,可动态修改
log_bin=/path/file_name # 是否开启二进制日志,指定日志文件路径及文件前缀
log_bin_basename=/path/log_file_name # binlog 文件前缀
log_bin_index=/path/file_name # 索引文件路径
binlog_format=STATEMENT|ROW|MIXED # 二进制日志文件格式
max_binlog_size=1G # 单文件大小,超过大小自动生成新文件,重启服务也会生成新文件,默认!G
binlog_cache_size=4m # 二进制日志缓冲区大小,每个连接独占大小
max_binlog_cache_size=512m # 二进制日志缓冲区总大小,多个连接共享
sync_binlog=1|0 # 二进制日志落盘规则, 1 表示实时写, 0 表示先缓存,再批量写磁盘文件
expire_logs_days=N # 二进制文件自动保存的天数,超出会被删除,默认 0 表示不删除
binlog_expire_logs_seconds=N # 二进制文件自动保存的秒数,8.0.28 及后续版本推荐,7天 = 7*86400 = 604800 秒
11.3.5 二进制日志配置示例
# 编辑服务端配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_timestamps = SYSTEM
log_bin = /data/mysql/logs/binlog
binlog_expire_logs_seconds = 604800
# 创建 日志文件 目录并更改属组属主
mkdir -p /data/mysql/logs
chown -R mysql:mysql /data/mysql/logs
# 开放 apparmor 的规则
vim /etc/apparmor.d/usr.sbin.mysqld
# Allow log file access
/var/log/mysql.err rw,
/var/log/mysql.log rw,
/var/log/mysql/ r,
/var/log/mysql/** rw,
# 添加以下 2 行
/data/mysql/logs/ r,
/data/mysql/logs/** rw,
# 重新加载 AppArmor 配置使其生效
apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld
# 重启 mysql 服务端
systemctl restart mysql
# 确认效果
ls /data/mysql/logs/
binlog.000001 binlog.index
11.3.6 查看二进制日志内容
mysqlbinlog /var/lib/mysql/binlog.000003
选项:
-v: 解析 SQL 语句-vv: 显示更详细的信息,额外追加元数据注释- ``–no-defaults` : 客户端开启了安全 update 的能力,需要额外添加
--start-position=N: 开始位置--stop-position=N: 结束位置--start-datetime=VAL: 开始时间,格式: YYYY-MM-DD hh:mm:ss--stop-datetime=VAL: 结束时间--base64-output=never|decode-rows|auto: 是否输出base64编码过的内容,默认 auto--database: 指定数据库
11.3.7 二进制日志案例
# 准备环境
create database db1;
use db1;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`gender` enum('M','F') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F');
# 查看二进制日志文件信息
ll /data/mysql/logs/
-rw-r----- 1 mysql mysql 1221 Aug 18 11:06 binlog.000001
-rw-r----- 1 mysql mysql 31 Aug 18 10:49 binlog.index
# 查看二进制日志文件,-v 可以解析 DDL 语句
mysqlbinlog -v /data/mysql/logs/binlog.000001
### INSERT INTO `db1`.`student`
### SET
### @1=1
### @2='u11'
### @3=11
### @4=1
### INSERT INTO `db1`.`student`
### SET
### @1=2
### @2='u22'
### @3=22
### @4=2
# 使用事务插入数据
# 开启事务
begin;
# 插入数据
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F');
# 查询数据,数据插入成功
select * from student;
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u11 | 11 | M |
| 4 | u22 | 22 | F |
+----+------+-----+--------+
# 事务未提交,二进制日志无变化
ll /data/mysql/logs/
-rw-r----- 1 mysql mysql 1221 Aug 18 11:06 binlog.000001
-rw-r----- 1 mysql mysql 31 Aug 18 10:49 binlog.index
# 提交事务
commit;
# 二进制日志更新,说明二进制日志只记录已提交的事务
ll /data/mysql/logs/
-rw-r----- 1 mysql mysql 1532 Aug 18 11:13 binlog.000001
-rw-r----- 1 mysql mysql 31 Aug 18 10:49 binlog.index
# 查看二进制日志文件新插入语句
mysqlbinlog -v /data/mysql/logs/binlog.000001
### INSERT INTO `db1`.`student`
### SET
### @1=3
### @2='u11'
### @3=11
### @4=1
### INSERT INTO `db1`.`student`
### SET
### @1=4
### @2='u22'
### @3=22
### @4=2
# 指定 开始 和 结束 位置 查看 二进制日志
mysqlbinlog -v --start-position=1694 --stop-position=1822 /data/mysql/logs/binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 157
#260818 10:49:11 server id 1 end_log_pos 126 CRC32 0x45d05d7b Start: binlog v 4, server v 8.0.46-0ubuntu0.24.04.3 created 260818 10:49:11 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
J8iDag8BAAAAegAAAH4AAAABAAQAOC4wLjQ2LTB1YnVudHUwLjI0LjA0LjMAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAnyINqEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAXtd0EU=
'/*!*/;
# at 1694
#260818 11:28:21 server id 1 end_log_pos 1756 CRC32 0xf085ee84 Table_map: `db1`.`student` mapped to number 90
# at 1756
#260818 11:28:21 server id 1 end_log_pos 1822 CRC32 0x51e8e34b Update_rows: table id 90 flags: STMT_END_F
BINLOG '
VdGDahMBAAAAPgAAANwGAAAAAFoAAAAAAAEAA2RiMQAHc3R1ZGVudAAEAw8D/gT9AvcBAAEBAAIB
IYTuhfA=
VdGDah8BAAAAQgAAAB4HAAAAAFoAAAAAAAEAAgAE//8AAwAAAAMAdTExCwAAAAEAAwAAAAMAdTEx
EgAAAAFL4+hR
'/*!*/;
### UPDATE `db1`.`student`
### WHERE
### @1=3
### @2='u11'
### @3=11
### @4=1
### SET
### @1=3
### @2='u11'
### @3=18
### @4=1
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
11.3.8 二进制日志管理
11.3.8.1 查看二进制文件
# 查看二进制文件列表
SHOW MASTER LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1853 | No |
+---------------+-----------+-----------+
# 新版本查看二进制文件列表
SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1853 | No |
+---------------+-----------+-----------+
# 查看当前二进制日志文件及 Pos 位置
SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 1853 | | | |
+---------------+----------+--------------+------------------+-------------------+
# 8.4 及更新版本 查看当前二进制日志文件及 Pos 位置
SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 158 | | | |
+---------------+----------+--------------+------------------+-------------------+
11.3.8.2 查看二进制日志中的事件
# 查看二进制日志中的事件
show binlog events\G;
*************************** 1. row ***************************
Log_name: binlog.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 126
Info: Server ver: 8.0.46-0ubuntu0.24.04.3, Binlog ver: 4
*************************** 2. row ***************************
Log_name: binlog.000001
Pos: 126
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 157
Info:
*************************** 3. row ***************************
Log_name: binlog.000001
Pos: 157
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 234
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# 查找指定 pos 值之后的事件
show binlog events from 1694\G;
*************************** 1. row ***************************
Log_name: binlog.000001
Pos: 1694
Event_type: Table_map
Server_id: 1
End_log_pos: 1756
Info: table_id: 90 (db1.student)
*************************** 2. row ***************************
Log_name: binlog.000001
Pos: 1756
Event_type: Update_rows
Server_id: 1
End_log_pos: 1822
Info: table_id: 90 flags: STMT_END_F
*************************** 3. row ***************************
Log_name: binlog.000001
Pos: 1822
Event_type: Xid
Server_id: 1
End_log_pos: 1853
Info: COMMIT /* xid=17 */
3 rows in set (0.00 sec)
# 指定二进制文件及 pos 值
show binlog events in 'binlog.000001' from 1694\G;
11.3.8.3 刷新二进制文件
默认情况下,二进制日志达到设置大小后自动生成新的二进制文件,也可以手动生成
# 重启服务
systemctl restart mysql
show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1876 | No |
| binlog.000002 | 157 | No |
+---------------+-----------+-----------+
# 手动命令刷新
flush logs;
show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1876 | No |
| binlog.000002 | 201 | No |
| binlog.000003 | 157 | No |
+---------------+-----------+-----------+
SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
11.3.8.4 清理二进制文件
格式 : PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
-- 删除指定 二进制日志 之前 的日志
purge binary logs to 'binlog.000002';
show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000002 | 201 | No |
| binlog.000003 | 157 | No |
+---------------+-----------+-----------+
-- 清理全部,一般在 mater 主机首次启用时执行
reset master;
SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
-- 8.4 及更新版本使用
reset binary logs and gtids;
SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 158 | | | |
+---------------+----------+--------------+------------------+-------------------+
-- 清理指定 binlog 文件
PURGE BINARY LOGS TO 'mysql-bin.000005';
-- 清理指定时间之前的 binlog 文件
PURGE BINARY LOGS BEFORE '2026-07-01 00:00:00';
-- 清理 7 天前的二进制文件
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;
11.4 通用日志
记录全部 SQL 语句,性能损耗极高
仅开发调试、审计溯源临时开启
默认未开启
# 临时开启通用日志
set global general_log=1;
show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
# 查看通用日志文件,文件名为主机名
tail /var/lib/mysql/ubuntu-240.log
/usr/sbin/mysqld, Version: 8.0.46-0ubuntu0.24.04.3 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2026-08-18T14:10:44.841978+08:00 8 Query show variables like 'general_log'
# 远程 心跳检测,判断 MySQL 服务是否工作
mysql -pDengtest@123 -h10.0.0.221 -e 'select 1'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+
# 查看通用日志文件
tail /var/lib/mysql/ubuntu-221.log
/usr/sbin/mysqld, Version: 8.4.11 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2026-08-18T06:17:55.789831Z 10 Query show variables like 'general_log'
2026-08-18T06:17:58.654988Z 12 Connect root@10.0.0.240 on using SSL/TLS
2026-08-18T06:17:58.655476Z 12 Query select @@version_comment limit 1
2026-08-18T06:17:58.655894Z 12 Query select 1 # 查询语句也被记录
2026-08-18T06:17:58.656430Z 12 Quit
11.5 慢查询日志
记录在 MySQL 中执行时间超过指定时间的查询语句
默认未开启
11.5.1 慢查询时间阈值
# 查看 慢查询时间阈值
show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
开启未使用索引的查询也属于慢查询,高并发场景不建议开启
[mysqld]
log_queries_not_using_indexes = ON
限制慢查询日志
[mysqld]
# 高并发场景下的限流参数
min_examined_row_limit = 100 # 扫描行数低于 100 行的全表扫描不予记录
log_throttle_queries_not_using_indexes = 10 # 每分钟最多记录 10 条无索引 SQL，防止日志刷屏
11.5.2 临时启用慢查询日志
# 临时开启慢查询日志
SET GLOBAL slow_query_log = ON;
# 查看慢查询相关配置变量
show variables like "slow%";
+---------------------+------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/ubuntu-240-slow.log |
+---------------------+------------------------------------+
# 查看慢查询日志
tail -f /var/lib/mysql/ubuntu-240-slow.log
/usr/sbin/mysqld, Version: 8.0.46-0ubuntu0.24.04.3 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# 临时修改 慢查询时间阈值
SET GLOBAL long_query_time = 0.1;
# 重连会话
\r
# 查看 慢查询时间阈值
show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
11.5.3 永久配置慢查询
配置项及说明
[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 自定义慢查询日志存放路径
slow_query_log_file = /data/mysql/logs/slow.log
# 慢查询阈值设置为 0.1 秒（100ms）,传统业务可设置为 0.5 秒
long_query_time = 0.1
# 是否记录没有使用索引的 select 语句,生产环境关闭,测试环境开启
log_queries_not_using_indexes = ON
# 仅扫描行数超过 100 行的无索引 SQL 才判定记录
min_examined_row_limit = 100
# 每分钟最多记录 10 条未使用索引的 SQL,防止日志刷屏(log_queries_not_using_indexes 开启时生效)
log_throttle_queries_not_using_indexes = 10
vim /etc/mysql/mysql.conf.d/mysqld.cnf
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.1
log_queries_not_using_indexes = ON
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 10
systemctl restart mysql
show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
show variables like "slow%";
+---------------------+---------------------------+
| Variable_name | Value |
+---------------------+---------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/logs/slow.log |
+---------------------+---------------------------+
11.5.4 慢查询测试
# 准备测试环境
create database slowdb;
use slowdb;
# 创建表
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`gender` enum('M','F') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 创建存储过程插入数据
DELIMITER //
CREATE PROCEDURE batch_insert_student(IN total INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE g ENUM('M','F');
WHILE i <= total DO
-- 随机性别
IF MOD(i,2)=0 THEN SET g = 'F';
ELSE SET g = 'M';
END IF;
INSERT INTO student(name,age,gender) VALUES (CONCAT('user_',i),
FLOOR(RAND()*20+18), g);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
# 执行存储过程插入 1000 条数据
CALL batch_insert_student(1000);
# 清理存储过程
DROP PROCEDURE IF EXISTS batch_insert_student;
select count(*) from student;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
# 蠕虫复制,反复执行
INSERT INTO student(name,age,gender) SELECT CONCAT(name,id), FLOOR(RAND()*20+18), IF(MOD(id,2)=0,'F','M') FROM student;
# 数据量来到 50 万条
select count(*) from student;
+----------+
| count(*) |
+----------+
| 512000 |
+----------+
# 查看慢查询日志
tail -f /data/mysql/logs/slow.log
# Time: 2026-08-18T14:54:57.459974+08:00
# User@Host: root[root] @ localhost [] Id: 8
# Query_time: 0.503415 Lock_time: 0.000003 Rows_sent: 0 Rows_examined: 128000
SET timestamp=1787036096;
INSERT INTO student(name,age,gender) SELECT CONCAT(name,id), FLOOR(RAND()*20+18), IF(MOD(id,2)=0,'F','M') FROM student;
# Time: 2026-08-18T14:55:00.130773+08:00
# User@Host: root[root] @ localhost [] Id: 8
# Query_time: 1.221268 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 256000
SET timestamp=1787036098;
INSERT INTO student(name,age,gender) SELECT CONCAT(name,id), FLOOR(RAND()*20+18), IF(MOD(id,2)=0,'F','M') FROM student;
11.5.5 慢查询日志切割
ll /data/mysql/logs/slow.log
-rw-r----- 1 mysql mysql 3219 Aug 18 14:55 /data/mysql/logs/slow.log
# 重命名旧日志
mv /data/mysql/logs/slow.log /data/mysql/logs/slow-$(date +%Y%m%d).log
ll /data/mysql/logs/
-rw-r----- 1 mysql mysql 3219 Aug 18 14:55 slow-20260818.log
# 通知 MySQL 创建新的慢查询日志
FLUSH SLOW LOGS;
# 确认效果
ls /data/mysql/logs/slow*
/data/mysql/logs/slow-20260818.log /data/mysql/logs/slow.log
11.5.6 慢查询日志分析
使用 pt-query-digest 日志分析工具
由 Percona Toolkit 工具包 提供
# 安装依赖包
apt update && apt install -y gnupg2 wget apt-transport-https curl
# 下载 Percona 官方 GPG 密钥并导入系统可信密钥列表
curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
# 安装 deb 包,自动导入 GPG 密钥并生成 apt 源文件
dpkg -i percona-release_latest.generic_all.deb
# 启用 release 仓库,指定 Ubuntu 版本 noble
percona-release setup noble
# 安装 Percona Toolkit
apt update
apt install -y percona-toolkit
# 验证安装
pt-query-digest --version
pt-query-digest /data/mysql/logs/slow-20260818.log
11.5.7 慢查询优化思路
- 找到慢查询
- explain 分析
- 确认
- 索引问题
- 查询语句问题
十二、性能优化
12.1 缓冲池大小
专用数据库服务根据物理内存总容量配置缓冲池大小
- 物理内存 ≤ 16G : 50% - 60%
- 物理内存 32G ~ 64G : 60% - 70%
- 物理内存 ≥ 128G : 70% - 80%
# 配置示例,物理内存 64G, 分配 40G 作为缓冲池
[mysqld]
innodb_buffer_pool_size = 40G
# 查看 innodb 缓冲池大小
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
# 查看 innodb 缓冲池命中情况
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
12.2 缓冲池多实例
将整块缓冲池拆分为多实例,避免锁竞争,提高高并发读写能力
缓冲池 > 1G ,建议配置多实例
推荐配置
- 缓冲池 1G ~ 16G : 1 ~ 4
- 缓冲池 16G ~ 64G : 4 ~ 8
- 缓冲池 64G : 最多不超过 16
# 配置示例,物理内存 64G, 分配 40G 作为缓冲池,拆分为 8 个实例
[mysqld]
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 8
# 重启 MySQL 服务
systemctl restart mysql.service
# 查看缓冲池实例数量
show variables like 'innodb_buffer_pool_instances';
12.3 事务日志刷盘策略
1: 默认策略,每次事务提交 写入系统缓冲区 + 调用 fsync 强制同步磁盘,数据无丢失,但性能最差,适合金融、支付、订单等强一致性业务2: 每次事务提交 写入系统缓冲区 ,每秒后台统一 fsync 同步磁盘,性能强,但最多丢失 1 秒已提交事务(服务器断电),适合普通电商、后台管理业务
# 查看当前刷盘策略
select @@innodb_flush_log_at_trx_commit;
# 普通业务修改默认刷盘策略
[mysqld]
innodb_flush_log_at_trx_commit = 2
12.4 连接数管控
[mysqld]
max_connections = 2000
十三、备份还原
13.1 相关概念
| 关键词 | 说明 |
|---|---|
| 完全备份 | 备份数据库所有数据 |
| 部分备份 | 备份部分库或表 |
| 增量备份 | 备份最近一次完全备份或增量备份以来变化的数据,备份快,还原慢 |
| 差异备份 | 备份最近一次完全备份以来变化的数据,备份慢,还原快 |
| 冷备份 | 数据库停止服务,读写操作均不可 |
| 温备份 | 不读不可写 |
| 热备份 | 可读可写 |
| 物理备份 | 复制数据文件进行备份,速度快 |
| 逻辑备份 | 从数据库导出数据另存,速度慢 |
需要备份的内容
- 数据库中的数据
- 二进制日志,InnoDB 事务日志
- 用户账号,权限配置,程序代码等
- 配置文件
企业备份策略需要根据业务需求进行定制
备份文件需要异地存放
备份文件需要做还原测试
备份方案选择
- mysqldump 逻辑备份 : 数据库数据量较小 (<50G),日常定时备份,单表备份恢复,迁移不同 MySQL 版本
- xtrabackup 物理备份 : 大型数据库,高可用场景
13.2 冷备份案例
备份阶段:
- 关闭数据库服务,执行完全备份
- 完全备份完成启动数据库继续写入新数据,新数据保存在 二进制文件 binlog
- 还原时: 先恢复完全备份,再通过 mysqlbinlog 执行 binlog 增量恢复
# 准备 2 台 MySQL 节点,使用相同的环境
# 编辑服务端配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_timestamps = SYSTEM
log_bin = /data/mysql/logs/binlog
binlog_expire_logs_seconds = 604800
# 创建 日志文件 目录并更改属组属主
mkdir -p /data/mysql/logs
chown -R mysql:mysql /data/mysql/logs
# 开放 apparmor 的规则
vim /etc/apparmor.d/usr.sbin.mysqld
# Allow log file access
/var/log/mysql.err rw,
/var/log/mysql.log rw,
/var/log/mysql/ r,
/var/log/mysql/** rw,
# 添加以下 2 行
/data/mysql/logs/ r,
/data/mysql/logs/** rw,
# 重新加载 AppArmor 配置使其生效
apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld
# 重启 mysql 服务端
systemctl restart mysql
alias mysql='mysql -uroot -pDengtest@123'
# 确认效果
ls /data/mysql/logs/
binlog.000001 binlog.index
mysql -e "show variables like 'log_bin';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 | 158 | | | |
+---------------+----------+--------------+------------------+-------------------+
# 在 节点1 创建测试数据
mysql <<EOF
drop database if exists db1;
create database db1;
use db1;
CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
age int NOT NULL,
gender enum('M','F') NOT NULL,
PRIMARY KEY (id)
);
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F');
insert into student(name,age,gender)values('u33',13,'M'),('u44',24,'F');
EOF
# 确认数据插入
mysql -e 'select * from db1.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u33 | 13 | M |
| 4 | u44 | 24 | F |
+----+------+-----+--------+
# 确认写入二进制文件
ll /data/mysql/logs/
-rw-r----- 1 mysql mysql 1465 Aug 18 16:18 binlog.000001
-rw-r----- 1 mysql mysql 31 Aug 18 16:14 binlog.index
# 确认数据库数据文件
ll /var/lib/mysql
total 100452
-rw-r----- 1 mysql mysql 4194304 Aug 18 16:20 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql 12582912 Aug 18 16:07 '#ib_16384_1.dblwr'
drwxr-x--- 2 mysql mysql 4096 Aug 18 16:14 '#innodb_redo'/
drwxr-x--- 2 mysql mysql 4096 Aug 18 16:14 '#innodb_temp'/
drwxr-x--- 8 mysql mysql 4096 Aug 18 16:18 ./
drwxr-xr-x 39 root root 4096 Aug 18 16:07 ../
-rw-r----- 1 mysql mysql 56 Aug 18 16:07 auto.cnf
-rw-r----- 1 mysql mysql 507 Aug 18 16:08 binlog.000001
-rw-r----- 1 mysql mysql 181 Aug 18 16:08 binlog.000002
-rw-r----- 1 mysql mysql 835 Aug 18 16:08 binlog.000003
-rw-r----- 1 mysql mysql 530 Aug 18 16:14 binlog.000004
-rw-r----- 1 mysql mysql 64 Aug 18 16:08 binlog.index
-rw------- 1 mysql mysql 1705 Aug 18 16:08 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 18 16:08 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 18 16:08 client-cert.pem
-rw------- 1 mysql mysql 1705 Aug 18 16:08 client-key.pem
drwxr-x--- 2 mysql mysql 4096 Aug 18 16:18 db1/
-rw-r----- 1 mysql mysql 3424 Aug 18 16:14 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 18 16:18 ibdata1
-rw-r----- 1 mysql mysql 12582912 Aug 18 16:15 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Aug 18 16:08 mysql/
-rw-r----- 1 mysql mysql 27262976 Aug 18 16:18 mysql.ibd
-rw-r----- 1 mysql mysql 125 Aug 18 16:07 mysql_upgrade_history
drwxr-x--- 2 mysql mysql 4096 Aug 18 16:08 performance_schema/
-rw------- 1 mysql mysql 1705 Aug 18 16:08 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Aug 18 16:08 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 18 16:08 server-cert.pem
-rw------- 1 mysql mysql 1705 Aug 18 16:08 server-key.pem
drwxr-x--- 2 mysql mysql 4096 Aug 18 16:08 sys/
-rw-r----- 1 mysql mysql 16777216 Aug 18 16:20 undo_001
-rw-r----- 1 mysql mysql 16777216 Aug 18 16:20 undo_002
# 完全备份
# 停止 MySQL 服务
systemctl stop mysql
# 创建备份文件目录
mkdir -p /data/backup
# 完全备份数据文件和二进制文件
tar -zcf /data/backup/mysql_datadir.tar.gz /var/lib/mysql
tar -zcf /data/backup/mysql_binlog.tar.gz /data/mysql/logs
ls /data/backup/
mysql_binlog.tar.gz mysql_datadir.tar.gz
# 启动 MySQL 服务,注意重新服务会刷新二进制文件,后续数据写入新的二进制文件
systemctl start mysql
# 插入新数据
mysql <<EOF
insert into db1.student(name,age,gender) values('u55',25,'M'),('u66',26,'F');
EOF
mysql -e 'select * from db1.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u33 | 13 | M |
| 4 | u44 | 24 | F |
| 5 | u55 | 25 | M |
| 6 | u66 | 26 | F |
+----+------+-----+--------+
ll /data/mysql/logs/
-rw-r----- 1 mysql mysql 1488 Aug 18 16:21 binlog.000001
-rw-r----- 1 mysql mysql 491 Aug 18 16:27 binlog.000002
-rw-r----- 1 mysql mysql 181 Aug 18 16:27 binlog.000003
-rw-r----- 1 mysql mysql 158 Aug 18 16:27 binlog.000004
-rw-r----- 1 mysql mysql 124 Aug 18 16:27 binlog.index
# 还原
# 在 节点二 还原数据
# 停止 MySQL 服务
systemctl stop mysql
# 删除还原数据库主机上的无用数据
rm -rf /var/lib/mysql/*
rm -rf /data/mysql/logs/*
# 创建备份文件目录
mkdir -p /data/backup
scp 10.0.0.221:/data/backup/* /data/backup/
cd /data/backup/
tar xf mysql_datadir.tar.gz
tar xf mysql_binlog.tar.gz
ls
data mysql_binlog.tar.gz mysql_datadir.tar.gz var
mv var/lib/mysql/* /var/lib/mysql/
mv data/mysql/logs/* /data/mysql/logs/
chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /data/mysql/
systemctl start mysql
mysql -e 'select * from db1.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u33 | 13 | M |
| 4 | u44 | 24 | F |
+----+------+-----+--------+
ll /data/mysql/logs/
-rw-r----- 1 mysql mysql 1488 Aug 18 16:21 binlog.000001
-rw-r----- 1 mysql mysql 158 Aug 18 16:34 binlog.000002
-rw-r----- 1 mysql mysql 62 Aug 18 16:34 binlog.index
scp 10.0.0.221:/data/mysql/logs/* /data/backup/
ls /data/backup/
binlog.000001 binlog.000002 binlog.000003 binlog.000004 binlog.index data mysql_binlog.tar.gz mysql_datadir.tar.gz var
# 使用 mysqlbinlog 分析数据发现 新数据 从 pos 值 158 开始
mysqlbinlog -v binlog.000002
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260818 16:23:50 server id 1 end_log_pos 127 CRC32 0x43343a7b Start: binlog v 4, server v 8.4.11 created 260818 16:23:50 at startup
ROLLBACK/*!*/;
BINLOG '
lhaEag8BAAAAewAAAH8AAAAAAAQAOC40LjExAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACWFoRqEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAF7OjRD
'/*!*/;
# at 127
#260818 16:23:50 server id 1 end_log_pos 158 CRC32 0x05eddbaf Previous-GTIDs
# [empty]
# at 158
#260818 16:25:01 server id 1 end_log_pos 237 CRC32 0x25032256 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yesoriginal_committed_timestamp=1787041501538845 immediate_commit_timestamp=1787041501538845 transaction_length=310
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1787041501538845 (2026-08-18 16:25:01.538845 CST)
# immediate_commit_timestamp=1787041501538845 (2026-08-18 16:25:01.538845 CST)
/*!80001 SET @@session.original_commit_timestamp=1787041501538845*//*!*/;
/*!80014 SET @@session.original_server_version=80411*//*!*/;
/*!80014 SET @@session.immediate_server_version=80411*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 237
#260818 16:25:01 server id 1 end_log_pos 308 CRC32 0x90eaaf99 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1787041501/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 308
#260818 16:25:01 server id 1 end_log_pos 372 CRC32 0x628788a5 Table_map: `db1`.`student` mapped to number 83
# has_generated_invisible_primary_key=0
# at 372
#260818 16:25:01 server id 1 end_log_pos 437 CRC32 0xfc9db564 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
3RaEahMBAAAAQAAAAHQBAAAAAFMAAAAAAAEAA2RiMQAHc3R1ZGVudAAEAw8D/gT8A/cBAAEBAAID
/P8ApYiHYg==
3RaEah4BAAAAQQAAALUBAAAAAFMAAAAAAAEAAgAE/wAFAAAAAwB1NTUZAAAAAQAGAAAAAwB1NjYa
AAAAAmS1nfw=
'/*!*/;
### INSERT INTO `db1`.`student`
### SET
### @1=5
### @2='u55'
### @3=25
### @4=1
### INSERT INTO `db1`.`student`
### SET
### @1=6
### @2='u66'
### @3=26
### @4=2
# at 437
#260818 16:25:01 server id 1 end_log_pos 468 CRC32 0x8f7dcb0e Xid = 4
COMMIT/*!*/;
# at 468
#260818 16:27:13 server id 1 end_log_pos 491 CRC32 0xa57e8d94 Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# 从 pos 值 158 开始导入数据到 MySQL
mysqlbinlog --start-position=158 binlog.000002 | mysql
# 分析 二进制文件,无新数据插入,跳过还原
mysqlbinlog -v binlog.000003
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260818 16:27:15 server id 1 end_log_pos 127 CRC32 0x664622da Start: binlog v 4, server v 8.4.11 created 260818 16:27:15 at startup
ROLLBACK/*!*/;
BINLOG '
YxeEag8BAAAAewAAAH8AAAAAAAQAOC40LjExAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABjF4RqEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAHaIkZm
'/*!*/;
# at 127
#260818 16:27:15 server id 1 end_log_pos 158 CRC32 0x33350bbd Previous-GTIDs
# [empty]
# at 158
#260818 16:27:54 server id 1 end_log_pos 181 CRC32 0xf5f014e3 Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# 分析 二进制文件,无新数据插入,跳过还原
mysqlbinlog -v binlog.000004
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260818 16:27:56 server id 1 end_log_pos 127 CRC32 0x113e9c9f Start: binlog v 4, server v 8.4.11 created 260818 16:27:56 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
jBeEag8BAAAAewAAAH8AAAABAAQAOC40LjExAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACMF4RqEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAGfnD4R
'/*!*/;
# at 127
#260818 16:27:56 server id 1 end_log_pos 158 CRC32 0xa31e3665 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# 确认数据完全恢复
mysql -e 'select * from db1.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u33 | 13 | M |
| 4 | u44 | 24 | F |
| 5 | u55 | 25 | M |
| 6 | u66 | 26 | F |
+----+------+-----+--------+
13.3 mysqldump 逻辑备份案例
13.3.1测试环境准备
# 在 节点一 准备测试数据
mysql <<EOF
drop database if exists db1; create database db1;
drop database if exists db2; create database db2;
use db1;
CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
age int NOT NULL,
gender enum('M','F') NOT NULL,
PRIMARY KEY (id)
);
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F'),
('u33',33,'M'),('u44',44,'F');
use db2;
create table student select * from db1.student;
create table student2 select * from db1.student;
create table student3 select * from db1.student;
insert into student(name,age,gender) values("db2-user1",55,'M');
insert into student2(name,age,gender) values("db2-user2",55,'M');
insert into student3(name,age,gender) values("db2-user3",55,'M');
EOF
# 验证数据
mysql -e "show databases like 'db%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| Database (db%) |
+----------------+
| db1 |
| db2 |
+----------------+
13.3.2 mysqldump 选项
基础连接选项
-u # 指定用户名
-p # 指定密码
-P # 指定端口,默认3306
-S # 指定 socket 文件地址
库级别控制选项
-A # 备份所有业务数据库,包含创建数据库语句,排除系统库
-B db # 导出指定数据库,不使用 -B 仅导出数据库中的表和数据
--ignore-table=db.table # 排除指定表
对象导出选项
-E # 备份事件
-R # 备份存储过程和自定义函数
--triggers # 备份触发器.默认开启,使用 --skip-triggers 关闭
数据导出控制选项
-d # 只备份表结构,不导出数据
-t # 仅导出数据,不导出建表语句
-c # insert 语句带字段名导出
--hex-blob # 二进制字段十六进制导出,防止乱码
一致性快照与 binlog 位点,重点
--single-transaction # InnoDB 依靠 MVCC 开启事务快照,开始备份前,先执行 START TRANSACTION 指令开启事务,备份全程不加全局读锁，业务正常读写,实现热备
--source-data=1 # 备份数据前加一条不注释语句记录备份时使用的二进制日志文件和文件中的位置,主从复制使用,GTID 模式下不需要
--source-data=2 # 注释语句,单机备份推荐使用,GTID 模式下不需要
GTID 模式选项
--set‑gtid‑purged=ON # 导出的 SQL 末尾会生成 SET @@GLOBAL.gtid_purged='xxx',从库导入后自动记录已执行 GTID;
其他选项
-F # 备份前刷新二进制日志文件
--default-character-set=utf8mb4 # #指定字符集,MySQL8 默认 utf8mb4
--add-drop-table # 建表前执行 DROP TABLE IF EXISTS,默认开启
# 完全备份示例
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --source-data=1 --default-character-set=utf8mb4 --hex-blob > /data/fullbak_`date +%F_%T`.sql
13.3.3 备份示例
alias mysqldump='mysqldump -uroot -pDengtest@123'
# 完全备份
mysqldump -A > all.sql
ll -h
-rw-r--r-- 1 root root 1.2M Aug 18 17:03 all.sql
# 包含创建数据库语句
grep '^CREATE DATABASE' all.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
# 压缩完全备份
mysqldump -A | gzip > all_1.sql.gz
# 比较备份文件大小
ll -h
-rw-r--r-- 1 root root 1.2M Aug 18 17:03 all.sql
-rw-r--r-- 1 root root 256K Aug 18 17:06 all_1.sql.gz
# 备份指定数据库
mysqldump -B db2 > db2.sql
# 包含创建数据库语句
grep '^CREATE' db2.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE TABLE `student` (
CREATE TABLE `student2` (
CREATE TABLE `student3` (
# 备份数据库中的表和数据
mysqldump db2 > db2_without_db.sql
# 不包含创建数据库语句
grep '^CREATE' db2_without_db.sql
CREATE TABLE `student` (
CREATE TABLE `student2` (
CREATE TABLE `student3` (
# 备份数据表
mysqldump db2 student2 > db2_student2.sql
# 包含指定表建表语句
grep '^CREATE' db2_student2.sql
CREATE TABLE `student2` (
13.3.4 使用备份文件还原
mysql
# 创建存储过程
DROP PROCEDURE IF EXISTS batch_insert_student;
DELIMITER //
CREATE PROCEDURE batch_insert_student(IN insert_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE random_name VARCHAR(255);
DECLARE random_age INT;
DECLARE random_gender ENUM('M','F');
SET autocommit = 0; -- 关闭自动提交事务
WHILE i <= insert_count DO
-- 多次拼接MD5值，把name拉长到245字符左右
SET random_name = CONCAT('STUDENT_',i,'_',
MD5(RAND()),MD5(RAND()),MD5(RAND()),MD5(RAND()),MD5(RAND()),MD5(RAND()));
SET random_name = LEFT(random_name,245); -- 控制长度245，不超过255上限
SET random_age = FLOOR(18 + RAND() * 43);
SET random_gender = IF(RAND()>0.5,'M','F');
INSERT INTO student(name,age,gender)
VALUES(random_name,random_age,random_gender);
IF i % 1000 = 0 THEN
COMMIT; -- 累计1000行才提交一次磁盘，写入速度提升几十倍
END IF;
SET i = i + 1;
END WHILE;
COMMIT;
SET autocommit = 1; -- 恢复自动提交
END //
DELIMITER ;
# 清空历史数据
TRUNCATE TABLE db1.student;
# 临时调整刷盘策略
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
# 生成 100 万条数据,执行2次
CALL batch_insert_student(1000000);
CALL batch_insert_student(1000000);
# 查看数据总条数
SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 2000000 |
+----------+
# 查看前 10 条数据,确认 name 字段长度
SELECT id, LENGTH(name), name, age, gender FROM db1.student LIMIT 10;
+----+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+--------+
| id | LENGTH(name) | name | age | gender |
+----+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+--------+
| 1 | 202 | STUDENT_1_da8ae3d42df04ee810050f4ea0c05679cecea25bafd0edeb8d31dcb4f1c16afbbe7a5ec6a3dc4d703b098f59ad72dcd30008473816ed55761bb8cc5c361acc5210922b33bd6db273e3e88dc5f099fcc3e6aa6a39d19627386ac1cf8858ba4b61 | 49 | M |
# 改回默认刷盘策略
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
# 使用 -B 备份指定数据库
mysqldump -uroot -pDengtest@123 -S /var/run/mysqld/mysqld.sock \
--single-transaction --source-data=2 \
-E -R --triggers --hex-blob --default-character-set=utf8mb4 \
-B db1 db2 > /data/backup/db1_db2_full_$(date +%F).sql
ll -h
-rw-r--r-- 1 root root 432M Aug 18 19:16 db1_db2_full_2026-08-18.sql
# 查看 binlog 位点
grep "CHANGE.*TO" /data/backup/db1_db2_full_$(date +%F).sql
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000001', SOURCE_LOG_POS=636153713;
# 不使用 -B 备份指定数据库,不包含数据库相关语句
mysqldump -uroot -pDengtest@123 -S /var/run/mysqld/mysqld.sock \
--single-transaction --source-data=2 \
-E -R --triggers --hex-blob --default-character-set=utf8mb4 \
db1 > /data/backup/no_b_db1_full_$(date +%F).sql
# 使用 -B 备份文件恢复
# 删除数据库模拟故障
mysql
drop database db1;
drop database db2;
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# 使用 -B 备份文件恢复
mysql < db1_db2_full_2026-08-18.sql
# 数据库恢复
show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# 使用 未使用 -B 备份文件恢复
# 删除数据库模拟故障
mysql
drop database db1;
show databases;
+--------------------+
| Database |
+--------------------+
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# 使用 未使用 -B 备份文件恢复,直接导入失败,需要提前创建数据库并指定导入的数据库
mysql < no_b_db1_full_2026-08-18.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1046 (3D000) at line 28: No database selected
# 创建数据库
create database db1;
# 指定导入数据库
mysql db1 < no_b_db1_full_2026-08-18.sql
# 数据导入成功
show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
+---------------+
# 使用 source 导入 未使用 -B 备份文件恢复
use db1;
drop table student;
# 临时关闭 binlog,避免恢复的数据再次写入 binlog 生冗余日志(生产可选)
set sql_log_bin = 0;
# 使用 source 导入 未使用 -B 备份文件恢复
source /data/backup/no_b_db1_full_2026-08-18.sql;
# 导入完成重新打开 binlog
set sql_log_bin = 1;
# 验证
select count(*) from db1.student;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
13.3.5 定期备份脚本
# 准备测试数据
mysql <<EOF
drop database if exists db1; create database db1;
drop database if exists db2; create database db2;
use db1;
CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
age int NOT NULL,
gender enum('M','F') NOT NULL,
PRIMARY KEY (id)
);
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F'),
('u33',33,'M'),('u44',44,'F');
use db2;
create table student select * from db1.student;
create table student2 select * from db1.student;
create table student3 select * from db1.student;
insert into student(name,age,gender) values("db2-user1",55,'M');
insert into student2(name,age,gender) values("db2-user2",55,'M');
insert into student3(name,age,gender) values("db2-user3",55,'M');
EOF
# 验证
mysql -e 'select * from db1.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u33 | 33 | M |
| 4 | u44 | 44 | F |
+----+------+-----+--------+
# 业务数据库备份脚本
cat > mysql_dump_backup.sh << 'EOF'
#!/bin/bash
# *************************************
# * 功能: Mysql 8.4 版本数据备份脚本
# * 作者:
# * 联系:
# * 版本: 2026-08-18
# *************************************
MY_USER="root"
MY_PASS="Dengtest@123"
MY_SOCKET="/var/run/mysqld/mysqld.sock"
BACKUP_DIR="/data/backup"
DATE=$(date +%F)
RETENTION_DAYS=7
IGNORE_DB="Database|information_schema|performance_schema|sys|mysql"
# 创建目录，清空当日错误日志
[ ! -d ${BACKUP_DIR}/${DATE} ] && mkdir -p ${BACKUP_DIR}/${DATE}
> ${BACKUP_DIR}/backup_error.log
# 获取业务库列表
DB_LIST=$(mysql -u${MY_USER} -p${MY_PASS} -S ${MY_SOCKET} -e "show databases;" 2>/dev/null | grep -Ewv "${IGNORE_DB}")
# 循环逐个数据库备份
for db in ${DB_LIST}
do
SQL_FILE=${BACKUP_DIR}/${DATE}/${db}_${DATE}.sql
mysqldump -u${MY_USER} -p${MY_PASS} -S ${MY_SOCKET} \
--single-transaction --source-data=2 -E -R --triggers --hex-blob -B ${db} \
> ${SQL_FILE} 2>>${BACKUP_DIR}/backup_error.log
if [ $? -eq 0 ];then
echo "${db}_${DATE}.sql 备份完成" >> ${BACKUP_DIR}/backup_run.log
else
echo "${db}_${DATE}.sql 备份执行失败" >> ${BACKUP_DIR}/backup_run.log
fi
done
# 清理7天前备份目录
find ${BACKUP_DIR} -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \;
# 安全权限设置
chmod 700 ${BACKUP_DIR}
chmod 600 ${BACKUP_DIR}/${DATE}/*.sql
# 开启异地备份按需启用
# rsync -avz --delete ${BACKUP_DIR}/${DATE} root@10.0.0.16:/data/backup/mysql/
echo "${DATE} 数据库备份脚本执行完毕"
EOF
rm /data/backup/*
bash mysql_dump_backup.sh
tree /data/backup/
/data/backup/
├── 2026-08-18
│ ├── db1_2026-08-18.sql
│ └── db2_2026-08-18.sql
├── backup_error.log
└── backup_run.log
# 备份数据校验脚本
cat > mysql_dump_check.sh << 'EOF'
#!/bin/bash
# *************************************
# * 功能: Mysql 8.4 版本数据备份校验脚本
# * 作者:
# * 联系:
# * 版本: 2026-08-18
# *************************************
MY_USER="root"
MY_PASS="Dengtest@123"
MY_SOCKET="/var/run/mysqld/mysqld.sock"
BACKUP_DIR="/data/backup"
DATE=$(date +%F)
WEEK_DAY=$(date +%w)
TEMP_DB="temp_check_db"
SAMPLE_TABLES="student"
FAIL_FLAG=0
# 判断备份目录是否存在
if [ ! -d "${BACKUP_DIR}/${DATE}" ];then
echo "错误：${BACKUP_DIR}/${DATE} 备份目录不存在"
exit 1
fi
# 遍历备份目录里面的sql文件，只校验备份成功的库，不再执行show databases
SQL_FILES=$(ls ${BACKUP_DIR}/${DATE}/*.sql 2>/dev/null)
for full_sql in ${SQL_FILES}
do
# 提取数据库名称：db1_2026‑07‑12.sql → db1
db=$(basename ${full_sql} _${DATE}.sql)
SQL_FILE=${full_sql}
echo "> 开始校验数据库：${db}"
#第一层：判断文件非空
if [ ! -s "${SQL_FILE}" ];then
echo "${db}_${DATE}.sql 文件为空，校验失败" >> ${BACKUP_DIR}/backup_run.log
FAIL_FLAG=1
continue
fi
#第二层：SQL语法校验：导入临时库，验证sql文件是否可以正常执行
mysql -u${MY_USER} -p${MY_PASS} -S ${MY_SOCKET} -e "DROP DATABASE IF EXISTS ${TEMP_DB}; CREATE DATABASE ${TEMP_DB}" 2>/dev/null
mysql -u${MY_USER} -p${MY_PASS} -S ${MY_SOCKET} ${TEMP_DB} < ${SQL_FILE} 2>>${BACKUP_DIR}/backup_error.log
if [ $? -ne 0 ];then
echo "${db}:备份文件语法异常，校验失败" >> ${BACKUP_DIR}/backup_run.log
FAIL_FLAG=1
mysql -u${MY_USER} -p${MY_PASS} -S ${MY_SOCKET} -e "DROP DATABASE IF EXISTS ${TEMP_DB}" 2>/dev/null
continue
fi
#第三层：元数据对比：原库表数量和备份里面表数量对比
ORIGIN_TABLE_COUNT=$(mysql -u${MY_USER} -p${MY_PASS} -S ${MY_SOCKET} -N -e \
"select count(table_name) from information_schema.tables where table_schema='${db}';" 2>/dev/null)
BACKUP_TABLE_COUNT=$(grep -i "CREATE TABLE" "${SQL_FILE}" | wc -l)
if [ "${ORIGIN_TABLE_COUNT}" -ne "${BACKUP_TABLE_COUNT}" ];then
echo "${db}:原库表数${ORIGIN_TABLE_COUNT},备份表数${BACKUP_TABLE_COUNT}，数量不一致" >> ${BACKUP_DIR}/backup_run.log
FAIL_FLAG=1
fi
#第四层：导入校验逻辑
if [ "${WEEK_DAY}" -eq 0 ];then
echo "${db}_${DATE}.sql 周日‑全量导入校验成功" >> ${BACKUP_DIR}/backup_run.log
else
#工作日只校验指定表，这里已经全部导入，仅做抽样判断逻辑展示
for t in ${SAMPLE_TABLES}
do
if grep -q "CREATE TABLE \`${t}\`" ${SQL_FILE};then
echo "${db}:核心表 ${t} 校验正常" >> ${BACKUP_DIR}/backup_run.log
else
echo "${db}:核心表 ${t} 在备份文件缺失" >> ${BACKUP_DIR}/backup_run.log
FAIL_FLAG=1
fi
done
echo "${db}_${DATE}.sql 日常‑抽样校验成功" >> ${BACKUP_DIR}/backup_run.log
fi
#销毁临时库
mysql -u${MY_USER} -p${MY_PASS} -S ${MY_SOCKET} -e "DROP DATABASE IF EXISTS ${TEMP_DB}" 2>/dev/null
done
#终端输出告警信息
if [ ${FAIL_FLAG} -eq 1 ];then
echo -e "\n====================备份校验异常警告===================="
if [ ${WEEK_DAY} -eq 0 ];then
echo "执行模式：周日全量校验模式"
else
echo "执行模式：日常抽样校验模式"
fi
echo "日期：${DATE}"
echo "部分数据库备份校验失败！"
echo "日志查看：${BACKUP_DIR}/backup_run.log、${BACKUP_DIR}/backup_error.log"
echo "========================================================"
else
if [ ${WEEK_DAY} -eq 0 ];then
echo "周日全量校验：所有数据库备份校验全部正常"
else
echo "日常抽样校验：所有数据库备份校验全部正常"
fi
fi
EOF
# 将备份文件传输到测试服务器执行脚本校验
bash mysql_dump_check.sh
> 开始校验数据库：db1
> 开始校验数据库：db2
日常抽样校验：所有数据库备份校验全部正常
13.4 XtraBackup 物理热备份
13.4.1 相关概念
官方网站: https://www.percona.com/
备份类型区分:
- mysqldump : 逻辑备份,导出 SQL 语句,数据量超过 100G 时速度很慢,备份和恢复耗时长
- XtraBackup : 物理备份,直接复制磁盘上的 ibd、ibdata1、redo‑log、undo‑log 物理文件,速度快,大数据场景效率极高
使用时注意版本匹配
- XtraBackup8.4 适配 MySQL8.4 及以后的版本
- XtraBackup8.0 适配 MySQL8.0 及以后的版本
- XtraBackup2.4 适配 MySQL5.7 及以前的版本
- XtraBackup 大版本必须和 MySQL 服务版本匹配
对于 InnoDB 存储引擎,全程不加全局读锁,不会锁表,DML 语句正常执行,业务不受影响
13.4.2 安装 XtraBackup
# 离线安装方法
先提前下载 MySQL 对应版本的 XtraBackup deb 包
wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.4/Percona-XtraBackup-8.4.0-6/binary/debian/noble/x86_64/percona-xtrabackup-84_8.4.0-6-1.noble_amd64.deb
# 安装依赖包
apt install -y libmysqlclient21 libssl3 libcurl4 libev4t64 libgcrypt20 zlib1g rsync lz4 libcurl4-openssl-dev zstd
# 将下载的 deb 包传入服务安装
dpkg -i percona-xtrabackup-84_8.4.0-6-1.noble_amd64.deb
# 若出现缺失依赖,自动安装依赖
apt install -y -f
# 验证安装
xtrabackup --version
2026-08-18T20:42:59.989962+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql --log_bin=/data/mysql/logs/binlog
xtrabackup version 8.4.0-6 based on MySQL server 8.4.0 Linux (x86_64) (revision id: 46a895ed)
13.4.3 创建专用用户及授权
-- 创建仅允许本机登录的备份专用账号 bkpuser
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'Dengtest@123';
-- 授予核心必备权限
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
-- 授予性能模式对应视图查询权限
GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO 'bkpuser'@'localhost';
-- 可选授权:用于维护PERCONA_SCHEMA备份历史表(生产环境推荐开启)
GRANT CREATE,ALTER,INSERT,SELECT ON PERCONA_SCHEMA.xtrabackup_history TO 'bkpuser'@'localhost';
GRANT CREATE TABLESPACE ON *.* TO 'bkpuser'@'localhost';
-- 刷新权限使配置立即生效
FLUSH PRIVILEGES;
-- 查看备份账号的授权
SHOW GRANTS FOR 'bkpuser'@'localhost';
+-----------------------------------------------------------------------------------------------------------+
| Grants for bkpuser@localhost |
+-----------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO `bkpuser`@`localhost` |
| GRANT BACKUP_ADMIN ON *.* TO `bkpuser`@`localhost` |
| GRANT SELECT, INSERT, CREATE, ALTER ON `PERCONA_SCHEMA`.`xtrabackup_history` TO `bkpuser`@`localhost` |
| GRANT SELECT ON `performance_schema`.`keyring_component_status` TO `bkpuser`@`localhost` |
| GRANT SELECT ON `performance_schema`.`log_status` TO `bkpuser`@`localhost` |
| GRANT SELECT ON `performance_schema`.`replication_group_members` TO `bkpuser`@`localhost` |
+-----------------------------------------------------------------------------------------------------------+
# 验证账号是否可用
xtrabackup --user=bkpuser --password='Dengtest@123' -S /var/run/mysqld/mysqld.sock --backup --target-dir=/data/backup/xtra_test
# 命令执行未报错,目录中生成备份文件,账号可用
ls /data/backup/xtra_test/
backup-my.cnf db1 ibdata1 performance_schema undo_002 xtrabackup_info
binlog.000004 db2 mysql sys xtrabackup_binlog_info xtrabackup_logfile
binlog.index ib_buffer_pool mysql.ibd undo_001 xtrabackup_checkpoints xtrabackup_tablespaces
13.4.4 全量、增量备份
# 清空备份文件目录
rm -rf /data/backup/*
命令格式:
# 执行物理备份
xtrabackup [--defaults-file=FILE] [OPTIONS] --backup
# 恢复时执行让备份数据达到一致性状态
xtrabackup [--defaults-file=FILE] [OPTIONS] --prepare
# 创建全量备份目录
mkdir -p /backup/base
# 全量备份
xtrabackup --user=bkpuser --password='Dengtest@123' -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/base
# 恢复时执行
xtrabackup --prepare --target-dir=/backup/base
# 全量备份
xtrabackup --user=bkpuser --password='Dengtest@123' -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/base
# 基于完全备份创建增量备份
xtrabackup --user=bkpuser --password='Dengtest@123' -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
# 使用增量备份恢复时,全量备份 prepare 时加上 --apply‑log‑only, 合并增量备份时,最后一次 prepare 去掉 --apply‑log‑only ; 全量先只回放，不回滚；中间增量继续只回放；最后一份增量合并完，才做完整回滚
# 指定数据库和数据表
xtrabackup --user=bkpuser --password='Dengtest@123' -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/db1_stu --databases='db1' --tables='stu'
# xtrabackup 只能完成单表备份,而不能完成单表恢复
13.4.5 全量备份案例
# 准备测试环境
# 在节点一执行
mysql <<EOF
drop database if exists db1; create database db1;
drop database if exists db2; create database db2;
use db1;
CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
age int NOT NULL,
gender enum('M','F') NOT NULL,
PRIMARY KEY (id)
);
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F'),
('u33',33,'M'),('u44',44,'F');
use db2;
create table student select * from db1.student;
create table student2 select * from db1.student;
create table student3 select * from db1.student;
insert into student(name,age,gender) values("db2-user1",55,'M');
insert into student2(name,age,gender) values("db2-user2",55,'M');
insert into student3(name,age,gender) values("db2-user3",55,'M');
EOF
# 创建全量备份目录
mkdir -p /backup/base
# 创建全量备份
xtrabackup --user=bkpuser --password=Dengtest@123 -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/base
# 查看备份文件
ll /backup/base/
-rw-r----- 1 root root 447 Aug 18 21:19 backup-my.cnf
-rw-r----- 1 root root 158 Aug 18 21:19 binlog.000005
-rw-r----- 1 root root 31 Aug 18 21:19 binlog.index
drwxr-x--- 2 root root 4096 Aug 18 21:19 db1/
drwxr-x--- 2 root root 4096 Aug 18 21:19 db2/
-rw-r----- 1 root root 19717 Aug 18 21:19 ib_buffer_pool
-rw-r----- 1 root root 12582912 Aug 18 21:19 ibdata1
drwxr-x--- 2 root root 4096 Aug 18 21:19 mysql/
-rw-r----- 1 root root 29360128 Aug 18 21:19 mysql.ibd
drwxr-x--- 2 root root 4096 Aug 18 21:19 performance_schema/
drwxr-x--- 2 root root 4096 Aug 18 21:19 sys/
-rw-r----- 1 root root 16777216 Aug 18 21:19 undo_001
-rw-r----- 1 root root 16777216 Aug 18 21:19 undo_002
-rw-r----- 1 root root 18 Aug 18 21:19 xtrabackup_binlog_info # 记录备份时刻binlog日志文件与position位置,用于精细化时间点恢复
-rw-r----- 1 root root 140 Aug 18 21:19 xtrabackup_checkpoints # 检查点文件,标识备份类型为 full-backuped 全量备份,记录 from_lsn、to_lsn、last_lsn, 是增量备份的唯一基准
-rw-r----- 1 root root 597 Aug 18 21:19 xtrabackup_info # 全局备份日志,记录备份工具版本、MySQL 版本、备份起止时间、锁表时长、压缩加密状态、binlog位点、首尾 LSN 等全量信息
-rw-r----- 1 root root 2560 Aug 18 21:19 xtrabackup_logfile
-rw-r----- 1 root root 39 Aug 18 21:19 xtrabackup_tablespaces
# 还原数据库
# 在节点二执行
# 创建备份数据目录
mkdir -p /data/backup
# 复制远程备份文件到当前主机
scp -r 10.0.0.221:/backup/* /data/backup/
# 事务修复前文件大小
du -sh /data/backup/
73M /data/backup/
# 恢复前执行 --prepare 对备份集做事务修复,让裸数据变成「MySQL可启动的一致性数据」
xtrabackup --prepare --target-dir=/data/backup/base
# 事务修复后文件大小
du -sh /data/backup/
117M /data/backup/
# 停止 MySQL 服务
systemctl stop mysql.service
# 清空数据目录和 binlog 目录
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# 还原数据
xtrabackup --copy-back --target-dir=/data/backup/base --datadir=/var/lib/mysql
# 更改目录属主属组
chown -R mysql:mysql /var/lib/mysql /data/mysql/logs
# 启动 MySQL 服务
systemctl start mysql.service
# 验证
mysql -e "show databases;select * from db1.student;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u33 | 33 | M |
| 4 | u44 | 44 | F |
+----+------+-----+--------+
13.4.6 增量备份案例
# 在节点一基于已做全量备份执行
# 插入数据
mysql -e "insert into db1.student(name,age,gender)values('u77',77,'M');"
# 基于全量备份进行增量备份
xtrabackup --user=bkpuser --password=Dengtest@123 -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
# 插入数据
mysql -e "insert into db1.student(name,age,gender)values('u88',88,'F');"
# 基于上次增量备份进行新的增量备份
xtrabackup --user=bkpuser --password=Dengtest@123 -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
# 查看备份文件大小
du -sh /backup/*
73M /backup/base
2.0M /backup/inc1
2.1M /backup/inc2
# 增量备份还原
# 在节点二操作
rm -rf /data/backup/*
# 复制远程备份文件到当前主机
scp -r 10.0.0.240:/backup/* /data/backup/
# 事务修复前文件大小
du -sh /data/backup/*
73M /data/backup/base
2.0M /data/backup/inc1
2.1M /data/backup/inc2
# 预处理全量备份, -apply-log-only 仅重做,不回滚
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
# 合并第一次增量备份, -apply-log-only 仅重做,不回滚
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1
# 合并最后一次增量备份,做事务修复,让裸数据变成「MySQL可启动的一致性数据」
xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2
# 事务修复后文件大小
du -sh /data/backup/*
93M /data/backup/base
10M /data/backup/inc1
35M /data/backup/inc2
# 停止 MySQL 服务
systemctl stop mysql.service
# 清空数据目录和 binlog 目录
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# 还原数据
xtrabackup --copy-back --target-dir=/data/backup/base --datadir=/var/lib/mysql
# 更改目录属主属组
chown -R mysql:mysql /var/lib/mysql /data/mysql/logs
# 启动 MySQL 服务
systemctl start mysql.service
# 验证
mysql -e "show databases;select * from db1.student;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1 | u11 | 11 | M |
| 2 | u22 | 22 | F |
| 3 | u33 | 33 | M |
| 4 | u44 | 44 | F |
| 5 | u77 | 77 | M |
| 6 | u88 | 88 | F |
+----+------+-----+--------+
13.5 Binlog 数据恢复
13.5.1 误删除数据恢复
# 在 节点一 操作
# 对数据库进行全量备份
rm -rf /backup/*
xtrabackup --user=bkpuser --password=Dengtest@123 -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/base
# 查看备份结束对应的 binlog 位置
cat /backup/base/xtrabackup_binlog_info
binlog.000012 158
# 在节点二校验备份文件可用性
rm -rf /data/backup/*
scp -r 10.0.0.240:/backup/* /data/backup/
xtrabackup --prepare --target-dir=/data/backup/base
# 查看备份结束对应的 binlog 位置
cat /data/backup/base/xtrabackup_binlog_info
binlog.000012 158
# 停止 MySQL 服务
systemctl stop mysql.service
# 清空数据目录和 binlog 目录
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# 还原数据
xtrabackup --copy-back --target-dir=/data/backup/base --datadir=/var/lib/mysql
# 更改目录属主属组
chown -R mysql:mysql /var/lib/mysql /data/mysql/logs
# 启动 MySQL 服务
systemctl start mysql.service
# 验证
mysql -e "select count(*) from db1.student;"
+----------+
| count(*) |
+----------+
| 6 |
+----------+
# 查看binlog文件清单
mysql -e "show binary logs;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 181 | No |
| binlog.000002 | 181 | No |
| binlog.000003 | 181 | No |
| binlog.000004 | 181 | No |
| binlog.000005 | 181 | No |
| binlog.000006 | 2338 | No |
| binlog.000007 | 4247 | No |
| binlog.000008 | 497 | No |
| binlog.000009 | 497 | No |
| binlog.000010 | 181 | No |
| binlog.000011 | 158 | No |
+---------------+-----------+-----------+
# 查看二进制日志保留周期
mysql -e "show variables like '%expire%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------+--------+
| Variable_name | Value |
+--------------------------------+--------+
| binlog_expire_logs_auto_purge | ON |
| binlog_expire_logs_seconds | 604800 | # 查看 MySQL 自动清理过期 binlog 日志周期,604800 / 86400 = 7 天
| disconnect_on_expired_password | ON | # 查看 MySQL 自动清理过期 binlog 日志状态
+--------------------------------+--------+
# 查看二进制日志开启状态
mysql -e "show variables like 'log_bin';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
# 在 节点一 操作
# 插入数据
mysql <<EOF
use db1;
CREATE TABLE ruoyi_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
phone VARCHAR(20)
);
INSERT INTO ruoyi_user(username,phone) VALUES
('admin','13800138000'),
('test','13900139000'),
('zhangsan','13700137000');
select * from ruoyi_user;
EOF
# 查看二进制信息
mysqlbinlog /data/mysql/logs/binlog.000012
# at 158
#260821 15:53:49 server id 1 end_log_pos 237 CRC32 0x01600e4a Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1787298829240421 immediate_commit_timestamp=1787298829240421 transaction_length=266
# original_commit_timestamp=1787298829240421 (2026-08-21 15:53:49.240421 CST)
# immediate_commit_timestamp=1787298829240421 (2026-08-21 15:53:49.240421 CST)
/*!80001 SET @@session.original_commit_timestamp=1787298829240421*//*!*/;
/*!80014 SET @@session.original_server_version=80411*//*!*/;
/*!80014 SET @@session.immediate_server_version=80411*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 237
#260821 15:53:49 server id 1 end_log_pos 424 CRC32 0x08b121f2 Query thread_id=13 exec_time=0 error_code=0 Xid = 50
use `db1`/*!*/;
SET TIMESTAMP=1787298829/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE ruoyi_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
phone VARCHAR(20)
)
/*!*/;
# at 424
#260821 15:53:49 server id 1 end_log_pos 503 CRC32 0x624e6a90 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes original_committed_timestamp=1787298829246897 immediate_commit_timestamp=1787298829246897 transaction_length=356
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1787298829246897 (2026-08-21 15:53:49.246897 CST)
# immediate_commit_timestamp=1787298829246897 (2026-08-21 15:53:49.246897 CST)
/*!80001 SET @@session.original_commit_timestamp=1787298829246897*//*!*/;
/*!80014 SET @@session.original_server_version=80411*//*!*/;
/*!80014 SET @@session.immediate_server_version=80411*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 503
#260821 15:53:49 server id 1 end_log_pos 577 CRC32 0x006a99bc Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1787298829/*!*/;
BEGIN
/*!*/;
# at 577
#260821 15:53:49 server id 1 end_log_pos 643 CRC32 0x193de33a Table_map: `db1`.`ruoyi_user` mapped to number 95
# has_generated_invisible_primary_key=0
# at 643
#260821 15:53:49 server id 1 end_log_pos 749 CRC32 0x66d244e3 Write_rows: table id 95 flags: STMT_END_F
BINLOG '
DQSIahMBAAAAQgAAAIMCAAAAAF8AAAAAAAEAA2RiMQAKcnVveWlfdXNlcgADAw8PBMgAUAAGAQEA
AgP8/wA64z0Z
DQSIah4BAAAAagAAAO0CAAAAAF8AAAAAAAEAAgAD/wABAAAABWFkbWluCzEzODAwMTM4MDAwAAIA
AAAEdGVzdAsxMzkwMDEzOTAwMAADAAAACHpoYW5nc2FuCzEzNzAwMTM3MDAw40TSZg==
'/*!*/;
# at 749
#260821 15:53:49 server id 1 end_log_pos 780 CRC32 0xfd478718 Xid = 51
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
13.5.1.1 基于 Binlog 时间点 恢复
由于 MySQL 在微秒级执行任务,导致多个 SQL语句 时间点(记录时间只到秒级) 相同
使用 时间点 恢复不够精准,只能到秒级,会恢复 1秒 内所有 SQL语句
# 基于 时间点 查看二进制日志
mysqlbinlog --base64-output=decode-rows -v \
--start-datetime="2026-08-21 15:53:49" \
--stop-datetime="2026-08-21 15:53:49" \
/data/mysql/logs/binlog.000012
# 由于 MySQL 在微秒级执行任务,导致多个 SQL语句 时间点(记录时间只到秒级) 相同,所有使用 时间点 查看时无法过滤出 SQL 语句.时间需要 +1秒
mysqlbinlog --base64-output=decode-rows -v \
--start-datetime="2026-08-21 15:53:49" \
--stop-datetime="2026-08-21 15:53:50" \
/data/mysql/logs/binlog.000012
# 恢复命令, -d db1 解析指定数据库的 SQL 语句,生成环境 多数据库 时必须使用
mysqlbinlog --base64-output=decode-rows -v -d db1 \
--start-datetime="2026-08-21 15:53:49" \
--stop-datetime="2026-08-21 15:53:50" \
/data/mysql/logs/binlog.000012 | mysql
13.5.1.2 基于 位点 精准恢复
生产环境推荐使用
根据 xtrabackup 备份结束对应的 binlog 位点继续恢复
# 基于 位点 在 节点二 精确恢复数据
scp 10.0.0.240:/data/mysql/logs/binlog.000012 /data/backup/
# 查看二进制日志选择起止位点
mysqlbinlog /data/backup/binlog.000012
# 将选择日志内容导出到临时文件
mysqlbinlog --base64-output=decode-rows -v -d db1 \
--start-position=158 --stop-position=780 \
/data/backup/binlog.000012 > /tmp/pos_recover.sql
# 查看文件内容,确认 SQL语句 是否有误
cat /tmp/pos_recover.sql
# 导入 SQL语句
mysqlbinlog --start-position=158 --stop-position=780 /data/backup/binlog.000012 | mysql
# 验证
mysql -e "select * from db1.ruoyi_user;"
+----+----------+-------------+
| id | username | phone |
+----+----------+-------------+
| 1 | admin | 13800138000 |
| 2 | test | 13900139000 |
| 3 | zhangsan | 13700137000 |
+----+----------+-------------+
13.5.1.3 一条完整事务在二进制文件中包含内容
# at 1111
#260821 18:35:50 server id 1 end_log_pos 1190 CRC32 0xdcded61e Anonymous_GTID last_committed=2 sequence_number=4 rbr_only=yes original_committed_timestamp=1787308550068305 immediate_commit_timestamp=1787308550068305 transaction_length=341
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1787308550068305 (2026-08-21 18:35:50.068305 CST)
# immediate_commit_timestamp=1787308550068305 (2026-08-21 18:35:50.068305 CST)
/*!80001 SET @@session.original_commit_timestamp=1787308550068305*//*!*/;
/*!80014 SET @@session.original_server_version=80411*//*!*/;
/*!80014 SET @@session.immediate_server_version=80411*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1190
#260821 18:35:50 server id 1 end_log_pos 1273 CRC32 0xc4452c17 Query thread_id=18 exec_time=0 error_code=0
SET TIMESTAMP=1787308550/*!*/;
BEGIN
/*!*/;
# at 1273
#260821 18:35:50 server id 1 end_log_pos 1339 CRC32 0x3f06e429 Table_map: `db1`.`ruoyi_user` mapped to number 153
# has_generated_invisible_primary_key=0
# at 1339
#260821 18:35:50 server id 1 end_log_pos 1421 CRC32 0xd12fd2d1 Update_rows: table id 153 flags: STMT_END_F
BINLOG '
BiqIahMBAAAAQgAAADsFAAAAAJkAAAAAAAEAA2RiMQAKcnVveWlfdXNlcgADAw8PBMgAUAAGAQEA
AgP8/wAp5AY/
BiqIah8BAAAAUgAAAI0FAAAAAJkAAAAAAAEAAgAD//8AAQAAAAVhZG1pbgsxMzgwMDEzODAwMAAB
AAAABWFkbWluCzEzODAwMDAwMDAw0dIv0Q==
'/*!*/;
### UPDATE `db1`.`ruoyi_user`
### WHERE
### @1=1
### @2='admin'
### @3='13800138000'
### SET
### @1=1
### @2='admin'
### @3='13800000000'
# at 1421
#260821 18:35:50 server id 1 end_log_pos 1452 CRC32 0x9718ab2a Xid = 133
COMMIT/*!*/;
GTIDBEGIN: 标记事务开始Table_map: 记录库名和表名Write_rows/Update_rows/Delete_rows: 核心数据,存储真实变更行数据（ROW 模式核心）Xid+COMMIT: InnoDB 内部事务 ID,标记事务提交完成，整条事务结束
13.6 综合案例
13.6.1 环境准备
# 重置二进制日志文件
reset binary logs and gtids;
show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 158 | No |
+---------------+-----------+-----------+
13.6.2 全量备份
# 对数据库进行全量备份
rm -rf /backup/base/*
xtrabackup --user=bkpuser --password=Dengtest@123 -S /var/run/mysqld/mysqld.sock --backup --target-dir=/backup/base
# 查看备份结束对应的 binlog 位置
cat /backup/base/xtrabackup_binlog_info
binlog.000002 158
13.6.3 插入新数据
# 插入数据
mysql <<EOF
use db1;
CREATE TABLE ruoyi_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
phone VARCHAR(20)
);
INSERT INTO ruoyi_user(username,phone) VALUES
('admin','13800138000'),
('test','13900139000'),
('zhangsan','13700137000');
select * from ruoyi_user;
EOF
# 继续插入数据
mysql -e "use db1;INSERT INTO ruoyi_user(username,phone) VALUES ('lisi','13600136000'),('wangwu','13500135000');"
# 更新数据
mysql -e "use db1;UPDATE ruoyi_user SET phone='13800000000' WHERE username='admin';"
# 删除数据,模拟误操作
mysql -e "use db1; delete from ruoyi_user where id in (1,2);"
# 继续插入数据
mysql -e "use db1;INSERT INTO ruoyi_user(username,phone) VALUES ('zhaoliu','13400134000'),('qianqi','13300133000');"
13.6.4 分析二进制日志
mysqlbinlog -v /data/mysql/logs/binlog.000002
...
# at 1452
#260821 18:36:31 server id 1 end_log_pos 1531 CRC32 0xa46a9962 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes original_committed_timestamp=1787308591784183 immediate_commit_timestamp=1787308591784183 transaction_length=330
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1787308591784183 (2026-08-21 18:36:31.784183 CST)
# immediate_commit_timestamp=1787308591784183 (2026-08-21 18:36:31.784183 CST)
/*!80001 SET @@session.original_commit_timestamp=1787308591784183*//*!*/;
/*!80014 SET @@session.original_server_version=80411*//*!*/;
/*!80014 SET @@session.immediate_server_version=80411*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1531
#260821 18:36:31 server id 1 end_log_pos 1605 CRC32 0x858b6f7c Query thread_id=19 exec_time=0 error_code=0
SET TIMESTAMP=1787308591/*!*/;
BEGIN
/*!*/;
# at 1605
#260821 18:36:31 server id 1 end_log_pos 1671 CRC32 0x4d89259c Table_map: `db1`.`ruoyi_user` mapped to number 153
# has_generated_invisible_primary_key=0
# at 1671
#260821 18:36:31 server id 1 end_log_pos 1751 CRC32 0xa5f3f2e5 Delete_rows: table id 153 flags: STMT_END_F
BINLOG '
LyqIahMBAAAAQgAAAIcGAAAAAJkAAAAAAAEAA2RiMQAKcnVveWlfdXNlcgADAw8PBMgAUAAGAQEA
AgP8/wCcJYlN
LyqIaiABAAAAUAAAANcGAAAAAJkAAAAAAAEAAgAD/wABAAAABWFkbWluCzEzODAwMDAwMDAwAAIA
AAAEdGVzdAsxMzkwMDEzOTAwMOXy86U=
'/*!*/;
### DELETE FROM `db1`.`ruoyi_user`
### WHERE
### @1=1
### @2='admin'
### @3='13800000000'
### DELETE FROM `db1`.`ruoyi_user`
### WHERE
### @1=2
### @2='test'
### @3='13900139000'
# at 1751
#260821 18:36:31 server id 1 end_log_pos 1782 CRC32 0xda835fb1 Xid = 139
COMMIT/*!*/;
...
# 分析发现误删除语句位点是 1452 到 1782
13.6.5 恢复数据测试
# 在节点二操作
rm -rf /data/backup
scp -r 10.0.0.221:/backup/* /data/backup/
xtrabackup --prepare --target-dir=/data/backup/base
# 查看备份结束对应的 binlog 位置
cat /data/backup/base/xtrabackup_binlog_info
binlog.000002 158
# 停止 MySQL 服务
systemctl stop mysql.service
# 清空数据目录和 binlog 目录
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# 还原数据
xtrabackup --copy-back --target-dir=/data/backup/base --datadir=/var/lib/mysql
# 更改目录属主属组
chown -R mysql:mysql /var/lib/mysql /data/mysql/logs
# 启动 MySQL 服务
systemctl start mysql.service
# 验证
mysql -e "select count(*) from db1.student;"
# 拷贝二进制文件到测试节点
scp -r 10.0.0.221:/data/mysql/logs/binlog.000002 /data/backup/
# 再次确认误删除位点是 1452 到 1782
mysqlbinlog -v /data/backup/binlog.000002
# 使用需要保留的位点恢复数据,分两段回放
mysqlbinlog --start-position=158 --stop-position=1452 /data/backup/binlog.000002 | mysql
mysqlbinlog --start-position=1782 --stop-position=2139 /data/backup/binlog.000002 | mysql
# 验证
mysql -e "select * from db1.ruoyi_user;"
+----+----------+-------------+
| id | username | phone |
+----+----------+-------------+
| 1 | admin | 13800000000 |
| 2 | test | 13900139000 |
| 3 | zhangsan | 13700137000 |
| 4 | lisi | 13600136000 |
| 5 | wangwu | 13500135000 |
| 6 | zhaoliu | 13400134000 |
| 7 | qianqi | 13300133000 |
+----+----------+-------------+
13.6.6 恢复数据到业务节点
# 测试节点操作
# 导出数据表
mysqldump -uroot -pDengtest@123 db1 ruoyi_user > db1_ruoyi_user.sql
# 拷贝到业务节点
scp db1_ruoyi_user.sql 10.0.0.221:
# 业务节点操作
mysql
use db1;
# 临时关闭二进制日志功能,不记录接下来的操作
set sql_log_bin=0;
# 删除发生误删除的数据表
drop table ruoyi_user;
# 导入测试节点拷贝的完整数据表
source /root/db1_ruoyi_user.sql;
# 验证
select * from ruoyi_user;
+----+----------+-------------+
| id | username | phone |
+----+----------+-------------+
| 1 | admin | 13800000000 |
| 2 | test | 13900139000 |
| 3 | zhangsan | 13700137000 |
| 4 | lisi | 13600136000 |
| 5 | wangwu | 13500135000 |
| 6 | zhaoliu | 13400134000 |
| 7 | qianqi | 13300133000 |
+----+----------+-------------+
# 开启二进制日志功能
set sql_log_bin=1;
十四、主从复制
14.1 相关概念
MySQL主从复制架构中,分为主服务器(Master)和从服务器(Slave) 2 种角色,主服务器复制数据写入,从服务器复制查询服务
基于二进制日志实现单向复制
主从架构的三个线程和2个日志
| 线程 | 节点 | 说明 |
|---|---|---|
| Binlog Dump | Master | 实时监控本机的 binlog 文件,将变更推送给从库;为每个连接上来的从库创建 dump 线程 |
| I/O | Slave | 连接主库 dump 线程,接收 binlog 日志,写入 Relay Log (中继日志) |
| SQL | Slave | 读取本地 Relay Log, 回放变更到从库执行,保证最终数据和主库一致 |
| 日志 | 节点 | 说明 |
|---|---|---|
| binlog | Master | 从节点的数据源 |
| Relay Log | Slave | 中继日志,从主节点同步过来的数据暂存于此,防止从节点发生意外导致数据同步失败 |
14.2 主从相关配置
14.2.1 主节点配置
# 开启二进制日志并指定文件路径和日志前缀
log_bin = /data/mysql/logs/binlog
# 二进制日志使用行模式,精确记录
binlog_format=ROW
# 二进制文件自动保存的秒数,7天
binlog_expire_logs_seconds = 604800
# 设置当前实例唯一 ID,集群内不可重复,建议将IP最后一段作为server-id
server-id=221
-- 创建用户
CREATE USER repluser@'10.0.0.%' IDENTIFIED BY 'Dengtest@123';
-- 授予 REPLICATION SLAVE 权限
GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%';
-- 刷新权限
FLUSH PRIVILEGES;
# 查看二进制日志文件信息
show binary log status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 881 | | | |
+---------------+----------+--------------+------------------+-------------------+
14.2.2 从节点配置
# 开启二进制日志并指定文件路径和日志前缀
log_bin = /data/mysql/logs/binlog
# 二进制日志使用行模式,精确记录
binlog_format=ROW;
# 二进制文件自动保存的秒数,30天
binlog_expire_logs_seconds = 2592000
# 设置当前实例唯一 ID,集群内不可重复,建议将 IP 最后一段作为 server-id
server-id=222
# 设置从库只读
read_only=ON
# 管理员只读,生产环境添加
super_read_only=ON
# 中继日志路径,默认值 hostname-relay-bin
relay_log=
# 中继日志索引路径,默认值 hostname-relay-bin.index
relay_log_index=
-- 配置同步,8.4版本
CHANGE REPLICATION SOURCE TO
GET_SOURCE_PUBLIC_KEY=0, -- 默认 0, 主从之间使用 SSL/TLS 加密连接（安全通道下不需要 RSA 公钥交换密码）
SOURCE_SSL=1, -- 启用 SSL, 8.4 版本默认启用了 caching_sha2_password 认证插件
SOURCE_HOST='10.0.0.221', -- 指定 master 节点
SOURCE_USER='repluser', -- 连接用户
SOURCE_PASSWORD='Dengtest@123', -- 连接密码
SOURCE_LOG_FILE='binlog.000003', -- 从哪个二进制文件开始复制
SOURCE_LOG_POS=881, -- 指定同步开始的位置
SOURCE_DELAY = interval; -- 可指定延迟复制实现防止误操作,单位秒，这里可以用作延时同步，一般用于备份
-- 管理命令
-- 启动复制线程
START REPLICA;
START REPLICA IO_THREAD;
START REPLICA SQL_THREAD;
-- 停止复制线程
STOP REPLICA;
-- 清除主从配置
RESET REPLICA ALL;
-- 查看从库状态
SHOW REPLICA STATUS\G
-- 主节点查看所有从节点信息
SHOW REPLICAS;
-- 确认线程信息
SHOW processlist\G
14.2.3 延迟复制
主要作用是防止误操作,用于备份恢复数据
延迟复制在出现事故时的操作步骤
-
立刻停止回放进程 :
STOP REPLICA SQL_THREAD; -
保持 IO 线程运行,继续接收 binlog 日志
-
精确回放到事故那笔事务前一个GTID
START REPLICA SQL_THREAD UNTIL SQL_BEFORE_GTIDS='事故事务GTID' -
此时从库数据就是事故操作前一刻的数据,导出需要的数据恢复到主库
-- 开启延迟复制,指定延迟时间,单位 秒
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = interval;
14.3 零数据主从搭建案例
14.3.1 环境准备
# 在主从节点操作
systemctl stop mysql.service
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# MySQL 初始化
mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql
systemctl start mysql.service
\mysql -e "alter user root@'localhost' identified WITH caching_sha2_password by 'Dengtest@123';flush privileges;"
echo "alias mysql='mysql -uroot -pDengtest@123 2>/dev/null'" > /etc/profile.d/mysqld.sh
source /etc/profile.d/mysqld.sh
14.3.2 主节点配置
# 主节点服务端配置
vim /etc/mysql/mysql.conf.d/mysqld.cnf
log_timestamps = SYSTEM
binlog_format=ROW
log_bin = /data/mysql/logs/binlog
binlog_expire_logs_seconds = 2592000
server-id=247
systemctl restart mysql.service
-- 创建用户
CREATE USER repluser@'10.0.0.%' IDENTIFIED BY 'Dengtest@123';
-- 授予 REPLICATION SLAVE 权限
GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 清理二进制日志
RESET BINARY LOGS AND GTIDS;SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 158 | No |
+---------------+-----------+-----------+
14.3.3 从节点配置
# 从节点服务端配置
vim /etc/mysql/mysql.conf.d/mysqld.cnf
log_bin = /data/mysql/logs/binlog
server-id=222
read_only=ON
systemctl restart mysql.service
-- 配置同步,8.4版本
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.221', -- 指定master节点
SOURCE_USER='repluser', -- 连接用户
SOURCE_PASSWORD='Dengtest@123', -- 连接密码
SOURCE_LOG_FILE='binlog.000001', -- 从哪个二进制文件开始复制
SOURCE_LOG_POS=158, -- 指定同步开始的位置
SOURCE_SSL=1; -- 启用 SSL
-- 查看从库状态
SHOW REPLICA STATUS\G
-- 启动从库
START REPLICA;
-- 再次查看从库状态
SHOW REPLICA STATUS\G
Replica_IO_State: Waiting for source to send event -- 等待主库数据发送
Source_Log_File: binlog.000002 -- 主节点二进制日志文件
Read_Source_Log_Pos: 158 -- 主节点二进制日志大小
Relay_Log_File: Ubuntu-222-relay-bin.000003 -- 中继日志
Replica_IO_Running: Yes -- 线程状态转为 Yes ,表示线程启动成功
Replica_SQL_Running: Yes -- 线程状态转为 Yes ,表示线程启动成功
Seconds_Behind_Source: 0 -- 主从节点数据时间差,0表示己经完全同步
-- 确认线程信息
show processlist\G
*************************** 3. row ***************************
Id: 9
User: system user
Host: connecting host
db: NULL
Command: Connect
Time: 502
State: Waiting for source to send event -- IO 线程
Info: NULL
*************************** 4. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Query
Time: 502
State: Replica has read all relay log; waiting for more updates -- SQL 线程
Info: NULL
11.3.4 主节点查看状态
-- 确认线程信息
show processlist\G
*************************** 2. row ***************************
Id: 8
User: repluser
Host: 10.0.0.222:33042
db: NULL
Command: Binlog Dump -- dump 线程
Time: 644
State: Source has sent all binlog to replica; waiting for more updates
Info: NULL
-- 主节点查看所有从节点信息
SHOW REPLICAS;
+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 222 | | 3306 | 221 | 75ca82c5-9d66-11f1-9bef-000c29f1d7f5 |
+-----------+------+------+-----------+--------------------------------------+
11.3.5 数据同步测试
-- 主节点操作
create database db1;
use db1;
CREATE TABLE `student` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into student (name,age,gender)values('user1',10,'M'),('user2',20,'F'),('user3',30,'M');
-- 从节点验证
SHOW REPLICA STATUS\G
Read_Source_Log_Pos: 1071 -- 主节点二进制日志位点
select * from db1.student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | user1 | 10 | M |
| 2 | user2 | 20 | F |
| 3 | user3 | 30 | M |
+----+-------+------+--------+
14.4 有数据主从搭建案例
14.4.1 环境准备
# 从节点重置
systemctl stop mysql.service
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# MySQL 初始化
mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql
systemctl start mysql.service
\mysql -e "alter user root@'localhost' identified WITH caching_sha2_password by 'Dengtest@123';flush privileges;"
echo "alias mysql='mysql -uroot -pDengtest@123 2>/dev/null'" > /etc/profile.d/mysqld.sh
source /etc/profile.d/mysqld.sh
14.4.2 主节点备份数据
# 完全备份
mysqldump -uroot -pDengtest@123 -A -F --single-transaction --source-data=1 > all.sql
# 拷贝到从节点
scp all.sql 10.0.0.222:
# 查看当前二进制日志信息
mysql -e "SHOW BINARY LOG STATUS;"
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 158 | | | |
+---------------+----------+--------------+------------------+-------------------+
14.4.3 从节点配置
# 添加主从同步配置
vim all.sql
CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=158; # 修改此行
# 补全为以下格式
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.221',
SOURCE_USER='repluser',
SOURCE_PASSWORD='Dengtest@123',
SOURCE_LOG_FILE='binlog.000003',
SOURCE_LOG_POS=158,
SOURCE_SSL=1;
mysql
# 临时关闭二进制日志功能,不记录接下来的操作
set sql_log_bin=0;
# 导入主节点拷贝的数据
source /root/all.sql;
# 开启二进制日志功能
set @@sql_log_bin=1;
-- 确认主从同步配置
SHOW REPLICA STATUS\G
-- 启动从库
START REPLICA;
-- 再次查看从库状态
SHOW REPLICA STATUS\G
# 验证
select * from db1.student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | user1 | 10 | M |
| 2 | user2 | 20 | F |
| 3 | user3 | 30 | M |
+----+-------+------+--------+
14.4.4 数据同步测试
-- 主节点插入数据
insert into student (name,age,gender)values('user4',40,'M');
-- 从节点验证
select * from db1.student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | user1 | 10 | M |
| 2 | user2 | 20 | F |
| 3 | user3 | 30 | M |
| 4 | user4 | 40 | M |
+----+-------+------+--------+
14.5 一主多从
使用脚本添加从节点
# 将以下2个脚本传入从节点服务器
ls
install_mysql84_ubuntu24.sh slave_join_mysql_cluster.sh
# 输入主节点 IP 开始初始化从节点
bash slave_join_mysql_cluster.sh
默认主节点为：10.0.0.13，回车使用默认值
请输入当前Mysql集群的主节点：10.0.0.221
# 验证
mysql -uroot -pDengtest@123 -e 'show replica status\G'
-- 主节点插入数据
insert into student (name,age,gender)values('user5',50,'M');
-- 从节点验证
mysql -uroot -pDengtest@123
select * from db1.student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | user1 | 10 | M |
| 2 | user2 | 20 | F |
| 3 | user3 | 30 | M |
| 4 | user4 | 40 | M |
| 5 | user5 | 50 | M |
+----+-------+------+--------+
-- 主节点查看所有从节点信息
SHOW REPLICAS;
+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 223 | | 3306 | 221 | 889e8601-9d75-11f1-9e81-000c298c5f83 |
| 222 | | 3306 | 221 | 3385534f-9d6d-11f1-99c1-000c29f1d7f5 |
+-----------+------+------+-----------+--------------------------------------+
14.6 半同步
同步方式:
- 异步复制 : 写人数据发送到主服务器,主服务器写入二进制文件后即给客户端返回成功,同时将数据同步到从服务器,默认
- 同步复制 : 写人数据发送到主服务器,主服务器写入二进制文件后,再将数据同步到从服务器,所有从服务器返回同步成功结果给主服务器,主服务器再给客户端返回成功
- 半同步复制 :写人数据发送到主服务器,主服务器写入二进制文件后,再将数据同步到从服务器,只要有一台从服务器写入中继日志成功就返回成功结果给主服务器,主服务器就给客户端返回成功,需要设置从服务同步超时时间,超时检查本地写入成功也返回成功
-- 配置半同步复制
-- 安装插件
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
-- 主节点临时开启半同步复制
set global rpl_semi_sync_source_enabled=1;
-- 从节点临时开启半同步复制,需要重启 IO 线程
set global rpl_semi_sync_source_enabled=1;
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;
# 配置文件永久生效
[mysqld]
plugin_load = "rpl_semi_sync_source=semisync_source.so"
rpl_semi_sync_source_enabled
14.7 复制过滤器
让从节点仅复制指定的数据库，或指定数据库的指定表
可以在 主节点配置(不推荐), 也可以在从节点配置(推荐)
14.8 GTID复制,推荐
GTID (Global Transaction Identifier,全局事务标识符)
GTID 会为每个事务分配唯一的事务 ID
GTID 由 server_uuid(数据库服务器) : transaction_id(事务的序列号) 组成
在主从复制中可以简化复制配置和故障转移过程
基于位点构建主从,当主节点故障需要提升新主时,由于各节点 binlog 文件编号不一致,需要手动打开各节点 binlog 日志确认哪个节点同步内容更新,相当繁琐,因此更推荐使用 GTID 复制
14.8.1 环境准备
# 所有节点执行
stop replica;
reset replica all;
# 配置文件保留以下配置项
vim /etc/mysql/mysql.conf.d/mysqld.cnf
server-id=222
read-only
log_bin=/data/mysql/logs/binlog
# 在主从节点操作
systemctl stop mysql.service
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# MySQL 初始化
mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql
systemctl start mysql.service
\mysql -e "alter user root@'localhost' identified WITH caching_sha2_password by 'Dengtest@123';flush privileges;"
echo "alias mysql='mysql -uroot -pDengtest@123 2>/dev/null'" > /etc/profile.d/mysqld.sh
source /etc/profile.d/mysqld.sh
-- 主节点创建用户
CREATE USER repluser@'10.0.0.%' IDENTIFIED BY 'Dengtest@123';
-- 授予 REPLICATION SLAVE 权限
GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%';
-- 刷新权限
FLUSH PRIVILEGES;
14.8.2 配置文件
生产环境并不是一次性将 gtid_mode 改为 ON,需要逐步修改,修改步骤如下 :
- 从节点临时修改为
gtid_mode=OFF_PERMISSIVE,此时从节点可以同时接收 GTID 和 binlog 匿名事务,以 binlog 匿名事务为主 - 主节点临时修改为
gtid_mode=OFF_PERMISSIVE,此时主节点生成 binlog 匿名事务 - 主节点临时修改为
gtid_mode=ON_PERMISSIVE,此时主节点生成 GTID 事务 - 从节点临时修改为
gtid_mode=ON_PERMISSIVE,此时从节点可以同时接收 GTID 和 binlog 匿名事务,以 GTID 事务为主,此时需要确认所有从库复制正常,链路中不再有 binlog 匿名事务后继续执行 - 主节点临时修改为
gtid_mode=ON - 从节点执行
CHANGE REPLICATION SOURCE TO语句创建基于 GTID 的主从复制配置 - 从节点临时修改为
gtid_mode=ON - 主从节点将
gtid_mode=ON和enforce_gtid_consistency=ON写入配置文件
以上步骤是为了避免 匿名事务和GTID事务混合 传输导致主从复制中断,这种情况一旦发生,基本需要考虑重做复制集群
-- 临时修改 gtid_mode 和 enforce_gtid_consistency
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = ON;
-- 持久修改 gtid_mode 和 enforce_gtid_consistency
SET PERSIST enforce_gtid_consistency = ON;
SET PERSIST gtid_mode = ON;
# 添加 GTID 相关配置项
vim /etc/mysql/mysql.conf.d/mysqld.cnf
# GTID 模式设置为 ON ,仅允许 GTID 事务,不允许传统 binlog 事务
gtid_mode=ON
# 禁止执行"无法被 GTID 正确标识"的语句,保证 GTID 安全的参数
enforce_gtid_consistency=ON
systemctl restart mysql.service
# 主节点重置二进制日志
reset binary logs and gtids;show binary log status;
14.8.3 设置主从
-- 从节点执行
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.221',
SOURCE_USER='repluser',
SOURCE_PASSWORD='Dengtest@123',
SOURCE_PORT=3306,
SOURCE_SSL=1,
SOURCE_AUTO_POSITION=1;
-- 启动从节点
START REPLICA;
-- 查看从节点状态
SHOW REPLICA STATUS\G
Retrieved_Gtid_Set: 3caf7a61-9dcd-11f1-a986-000c29659254:1-3 -- 从主库拉取
Executed_Gtid_Set: 3caf7a61-9dcd-11f1-a986-000c29659254:1-3 -- 本地已执行
-- 主节点查看二进制日志信息
show binary log status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 880 | | | 3caf7a61-9dcd-11f1-a986-000c29659254:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
14.9 主从复制管理
14.9.1 监控和维护
-- 查看从节点状态
show replica status\G
Replica_IO_Running: Yes -- IO 线程状态必须为 Yes
Replica_SQL_Running: Yes -- SQL 线程状态必须为 Yes
Seconds_Behind_Source: 0 -- 从节点是否落后于主节点,重点关注此行值是否为 0
脚本判断复制状态
vim mysql_cluster_status_check.sh
#!/bin/bash
# *****************************************************************
# 功能: mysql 状态检查
# 作者:
# 日期: 2026-08-22
# 联系:
# *****************************************************************
# 定制两个待检测的核心变量
io_status=$(mysql -uroot -pDengtest@123 -e "show replica status\G" 2>/dev/null | awk -F": " '/IO_Running/{print $2}')
sql_status=$(mysql -uroot -pDengtest@123 -e "show replica status\G" 2>/dev/null | awk -F": " '/SQL_Running:/{print $2}')
gtidr_status=$(mysql -uroot -pDengtest@123 -e "show replica status\G" 2>/dev/null | awk -F": " '/Retrieved_Gtid_Set/{print $2}')
gtide_status=$(mysql -uroot -pDengtest@123 -e "show replica status\G" 2>/dev/null | awk -F": " '/Executed_Gtid_Set:/{print $2}')
# 检查变量
if [[ $io_status == "Yes" && $sql_status == "Yes" ]]
then
echo "cluster is health"
cluster_status='ok'
else
echo "cluster is not health"
fi
if [[ "$gtidr_status" == "$gtide_status" ]]
then
echo "数据同步成功"
else
[ $cluster_status == "ok" ] && echo "数据同步存在延迟"
fi
bash mysql_cluster_status_check.sh
cluster is health
数据同步成功
14.9.2 主从数据不一致处理
14.9.2.1 标准处理方式
查看报错关键字,如果错误不影响最终主从数据一致性,可停止主从复制,然后忽略错误事件,或手动执行相关 SQL 语句,再开启主从复制
-- 查看报错关键字
SHOW REPLICA STATUS\G
Last_SQL_Error: 1007
-- 停止主从复制
stop replica;
-- 非 GTID 模式跳过 1 个错误事件
set global sql_slave_skip_counter=1;
-- 开启主从复制
start replica;
-- 检查状态
SHOW REPLICA STATUS\G
-- GTID 模式跳过故障
-- 停止主从复制
stop replica;
-- 设置要跳过的故障GTID
SET SESSION GTID_NEXT='3caf7a61-9dcd-11f1-a986-000c29659254:1-3';
BEGIN;
COMMIT;
-- 恢复自动 GTID 模式
SET SESSION GTID_NEXT='AUTOMATIC'
-- 开启主从复制
start replica;
-- 检查状态
SHOW REPLICA STATUS\G
14.9.2.2 非标准处理方式
重置主从关系,重建复制集群
14.9.3 常见问题代码
-- 查看报错关键字
SHOW REPLICA STATUS\G
Last_SQL_Error: 1007 -- 问题代码
十五、RuoYi-Vue 综合案例
15.1 环境部署
15.1.1 JAVA
# 安装 JDK17
apt update && apt install -y openjdk-17-jdk
# 配置全局 JAVA_HOME
echo "export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64" >> ~/.bashrc
source ~/.bashrc
# 验证
java -version
openjdk version "17.0.19" 2026-04-21
echo $JAVA_HOME
/usr/lib/jvm/java-17-openjdk-amd64
15.1.2 Maven
# 安装 Maven
apt install -y maven
# 配置镜像加速
mkdir -p ~/.m2
cat > ~/.m2/settings.xml << 'EOF'
<?xml version="1.0" encoding="UTF-8"?>
<settings>
<mirrors>
<mirror>
<id>aliyun</id>
<mirrorOf>central</mirrorOf>
<url>https://maven.aliyun.com/repository/public</url>
</mirror>
</mirrors>
</settings>
EOF
# 验证
mvn -v
Apache Maven 3.8.7
15.1.3 nodejs
curl -fsSL https://deb.nodesource.com/setup_20.x | sudo -E bash -
apt install -y nodejs
# 镜像加速
npm config set registry https://registry.npmmirror.com
# 验证
node -v
v20.20.2
npm -v
10.8.2
npm config get registry
https://registry.npmmirror.com
15.1.4 Nginx
apt install -y nginx
# 删除默认网站配置
rm -rf /etc/nginx/sites-enabled/default
15.1.5 Redis
apt install -y redis-server
# 配置外网访问
sed -i 's/bind 127.0.0.1/bind 10.0.0.221/' /etc/redis/redis.conf
sed -i '/^requirepass /d' /etc/redis/redis.conf
sed -i '/# requirepass/a\requirepass "Redis@2026"' /etc/redis/redis.conf
sed -i 's/^protected-mode .*/protected-mode yes/' /etc/redis/redis.conf
systemctl restart redis-server
# 验证
redis-cli -a 'Redis@2026' -h 10.0.0.221 ping
PONG
15.2 部署 RuoYi 后端
读写分离
写下发到主节点
读下发到2台从节点
15.2.1 数据库初始化
# 创建数据库、ruoyi用户并授权
cat > ruoyi.sql << 'EOF'
CREATE DATABASE ry_vue DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'ruoyi'@'%' IDENTIFIED WITH mysql_native_password BY 'Ruoyi@123';
GRANT ALL ON ry_vue.* TO 'ruoyi'@'%';
FLUSH PRIVILEGES;
EOF
mysql < ruoyi.sql
# 验证
mysql -uruoyi -p'Ruoyi@123' -h10.0.0.221 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| ry_vue |
+--------------------+
15.2.2 RuoYi 源码获取
apt install -y git
# 拉取开源仓库代码
git clone https://gitee.com/y_project/RuoYi-Vue.git
cd RuoYi-Vue
# 查看目录结构
ls
LICENSE bin pom.xml ruoyi-common ruoyi-generator ruoyi-system ry.sh
README.md doc ruoyi-admin ruoyi-framework ruoyi-quartz ry.bat sql
15.2.3 数据库导入初始化 SQL 脚本
cd RuoYi-Vue/sql/
mysql -uruoyi -p'Ruoyi@123' -h10.0.0.221 ry_vue < ry_20260417.sql
mysql -uruoyi -p'Ruoyi@123' -h10.0.0.221 ry_vue < quartz.sql
# 验证
mysql -uruoyi -p'Ruoyi@123' -h10.0.0.221 -e "show tables from ry_vue;"
+--------------------------+
| Tables_in_ry_vue |
+--------------------------+
| QRTZ_BLOB_TRIGGERS |
| QRTZ_CALENDARS |
| QRTZ_CRON_TRIGGERS |
| QRTZ_FIRED_TRIGGERS |
| QRTZ_JOB_DETAILS |
| QRTZ_LOCKS |
| QRTZ_PAUSED_TRIGGER_GRPS |
| QRTZ_SCHEDULER_STATE |
| QRTZ_SIMPLE_TRIGGERS |
| QRTZ_SIMPROP_TRIGGERS |
| QRTZ_TRIGGERS |
| gen_table |
| gen_table_column |
| sys_config |
| sys_dept |
| sys_dict_data |
| sys_dict_type |
| sys_job |
| sys_job_log |
| sys_logininfor |
| sys_menu |
| sys_notice |
| sys_notice_read |
| sys_oper_log |
| sys_post |
| sys_role |
| sys_role_dept |
| sys_role_menu |
| sys_user |
| sys_user_post |
| sys_user_role |
+--------------------------+
mysql -uruoyi -p'Ruoyi@123' -h10.0.0.221 -e "show tables from ry_vue;"|wc -l
32
# 在从节点执行,验证数据同步
mysql -uruoyi -p'Ruoyi@123' -h10.0.0.221 -e "show tables from ry_vue;"|wc -l
32
15.2.4 RuoYi 后端配置数据库
cd ..
vim ruoyi-admin/src/main/resources/application-druid.yml
master:
# 修改数据库地址,数据库名称,useSSL=false
url: jdbc:mysql://10.0.0.221:3306/ry_vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8
username: ruoyi
password: Ruoyi@123
15.2.5 RuoYi 后端配置 Redis
vim ruoyi-admin/src/main/resources/application.yml
# 文件路径 示例（ Windows配置D:/ruoyi/uploadPath，Linux配置 /home/ruoyi/uploadPath）
profile: /data/ruoyi/uploadPath # 调整为linux环境下的配置
...
data:
# redis 配置
redis:
# 地址
host: 10.0.0.221
# 端口，默认为6379
port: 6379
# 数据库索引
database: 0
# 密码
password: Redis@2026
# 连接超时时间
timeout: 10s
15.2.6 RuoYi 后端打包构建
mvn clean package -Dmaven.test.skip=true
# 查看打包的 jar 包
ls ruoyi-admin/target/ruoyi-admin.jar
ruoyi-admin/target/ruoyi-admin.jar
# 手动运行测试是否报错
java -jar ruoyi-admin/target/ruoyi-admin.jar
(♥◠‿◠)ﾉﾞ 若依启动成功 ლ(´ڡ`ლ)ﾞ
15.2.7 RuoYi 后端服务
mkdir -p /data/ruoyi/server
cp ruoyi-admin/target/ruoyi-admin.jar /data/ruoyi/server
# 创建服务文件
cat > /etc/systemd/system/ruoyi-admin.service << 'EOF'
[Unit]
Description=RuoYi-Vue Backend Admin Service
After=network.target mysql.service redis-server.service
[Service]
User=root
WorkingDirectory=/data/ruoyi/server
# JDK17启动参数，内存可根据服务器配置调整
ExecStart=/usr/lib/jvm/java-17-openjdk-amd64/bin/java -Xms256m -Xmx512m -jar ruoyi-admin.jar
SuccessExitStatus=143
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable --now ruoyi-admin.service
systemctl status ruoyi-admin.service
15.3 部署 RuoYi 前端
15.3.1 前端打包
# 查看 RuoYi 项目地址
git remote -v
origin https://gitee.com/y_project/RuoYi-Vue.git (fetch)
origin https://gitee.com/y_project/RuoYi-Vue.git (push)
# 切换到 tag 标签 v3.9.2
git checkout v3.9.2
# 查看目录结构,出现 ruoyi-ui 目录
ls
LICENSE bin pom.xml ruoyi-common ruoyi-generator ruoyi-system ry.bat sql
README.md doc ruoyi-admin ruoyi-framework ruoyi-quartz ruoyi-ui ry.sh
# 进入前端目录
cd ~/RuoYi-Vue/ruoyi-ui
# 安装前端依赖
npm install
# 打包生成 dist 静态资源文件夹
npm run build:prod
# 查看生成的静态资源
ls dist/
favicon.ico html index.html index.html.gz robots.txt static styles
# 将静态资源拷贝到指定目录
mkdir /data/ruoyi/web
cp -r dist /data/ruoyi/web/
15.3.2 配置 Nginx 反向代理
vim /etc/nginx/conf.d/ruoyi.conf
server {
listen 80;
server_name ruoyi.deng.org;
# 前端静态资源根目录
root /data/ruoyi/web/dist;
index index.html;
# 解决Vue History模式刷新页面404问题
location / {
try_files $uri $uri/ /index.html;
}
# 接口请求转发至后端8080端口,适配前端 /prod-api 接口前缀
location ^~ /prod-api/ {
proxy_pass http://127.0.0.1:8080/;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
}
# 本地文件上传访问路由
location /profile/ {
proxy_pass http://127.0.0.1:8080/profile/;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
# 静态资源缓存优化
location ~* \.(js|css|png|jpg|jpeg|gif|ico|svg)$ {
expires 1d;
add_header Cache-Control "public";
}
}
# 校验语法错误
nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
# 重载 Nginx 配置
systemctl reload nginx.service
15.3.3 验证
Windows 添加 hosts 解析记录: 10.0.0.221 ruoyi.deng.org
浏览器访问 ruoyi.deng.org
# 查看 Druid 监控页相关信息
cat ~/RuoYi-Vue/ruoyi-admin/src/main/resources/application-druid.yml
url-pattern: /druid/*
# 控制台管理用户名和密码
login-username: ruoyi
login-password: 123456
浏览器访问 ruoyi.deng.org:8080/druid
15.4 故障切换
15.4.1 模拟故障
# 停止主节点 MySQL 服务模拟主节点故障
systemctl stop mysql.service
15.4.2 选择提升从节点
-- 查看从节点状态
show replica status\G
Source_Log_File: binlog.000002 -- 当前正在执行的中继日志
Relay_Source_Log_File: binlog.000002 -- 对应的主节点二进制日志文件
Last_IO_Errno: 2003
Last_IO_Error: Error reconnecting to source 'repluser@10.0.0.221:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Can't connect to MySQL server on '10.0.0.221:3306' (111)
Retrieved_Gtid_Set: 3caf7a61-9dcd-11f1-a986-000c29659254:1-320
Executed_Gtid_Set: 3caf7a61-9dcd-11f1-a986-000c29659254:1-320 -- Retrieved_Gtid_Set = Executed_Gtid_Set 表示接收的二进制事务全部执行完毕
需要同时满足 Source_Log_File = Relay_Source_Log_File 和 Retrieved_Gtid_Set = Executed_Gtid_Set ,才能将从节点提升为主节点
15.4.3 执行提升操作
-- 选定的从节点执行
stop replica;
reset replica all;
-- 查看从节点是否配置主从复制专用用户
select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| repluser | 10.0.0.% |
+------------------+-----------+
vim /etc/mysql/mysql.conf.d/mysqld.cnf
read_only=ON # 删除此行
systemctl restart mysql.service
15.4.4 配置其余从节点指向新主节点
-- 其他从节点更改主从配置
stop replica;
reset replica all;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.222',
SOURCE_USER='repluser',
SOURCE_PASSWORD='Dengtest@123',
SOURCE_PORT=3306,
SOURCE_SSL=1,
SOURCE_AUTO_POSITION=1;
start replica;
show replica status\G
15.4.5 重新构建打包应用
cd ~/RuoYi-Vue
vim ruoyi-admin/src/main/resources/application-druid.yml
url: jdbc:mysql://10.0.0.222:3306/ry_vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8 # 更改数据库地址
mvn clean package -Dmaven.test.skip=true
# 手动运行测试是否报错
systemctl stop ruoyi-admin.service
java -jar ruoyi-admin/target/ruoyi-admin.jar
(♥◠‿◠)ﾉﾞ 若依启动成功 ლ(´ڡ`ლ)ﾞ
cp ruoyi-admin/target/ruoyi-admin.jar /data/ruoyi/server
systemctl start ruoyi-admin.service
\
15.4.6 应用验证
浏览器访问 ruoyi.deng.org:8080/druid 验证,数据源指向新的服务器
15.4.7 旧主节点降级为从节点
# 旧主节点启动 MySQL 服务
systemctl start mysql
-- 清空二进制日志和 GTID 记录
reset binary logs and gtids;
-- 配置主从复制
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.222',
SOURCE_USER='repluser',
SOURCE_PASSWORD='Dengtest@123',
SOURCE_PORT=3306,
SOURCE_SSL=1,
SOURCE_AUTO_POSITION=1;
start replica;
show replica status\G
十六、读写分离、中间件和高可用
中间件是部署在应用程序和MySQL数据库中间的独立服务程序,主要用于实现读写分离,分库分表
常见中间件
| 中间件/方案 | 特点 | 适用场景 |
|---|---|---|
| ShardingSphere-JDBC | 轻量级,无中间件节点,性能优,适配微服务,更新迭代活跃 | 新项目,微服务架构,高并发 |
| MyCat | 适配老旧项目 | 老项目使用,低并发场景 |
| ProxySQL | 专注读写分离,负载均衡,分库分表能力弱 | 仅需读写分离场景 |
16.1 ProxySQL 安装和初始化
官方网站: https://proxysql.com/documentation/installing-proxysql
16.1.1 ProxySQL 安装
离线包下载 : https://github.com/sysown/proxysql/releases#release-v3.0.10
wget -nv -O /etc/apt/trusted.gpg.d/proxysql-3.0.x-keyring.gpg \
'https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/repo_pub_key.gpg'
# 安装传入的离线包
apt install -y ./proxysql_3.0.10-ubuntu24_amd64.deb
# 相关文件
dpkg -L proxysql
/etc/logrotate.d/proxysql # 日志轮转配置，实现日志分割
/etc/proxysql.cnf # 主配置文件
/lib/systemd/system/proxysql-initial.service # 初始化服务，首次启动加载默认配置
/lib/systemd/system/proxysql.service # 服务
/usr/bin/proxysql # 可执行二进制程序
/usr/share/doc/proxysql # 文档
/usr/share/proxysql/tools # 配套工具脚本目录
/usr/share/proxysql/tools/proxysql_galera_checker.sh # 集群状态检查脚本
/usr/share/proxysql/tools/proxysql_galera_writer.pl # 集群写入测试脚本
# 启动服务
systemctl enable --now proxysql
systemctl status proxysql
# 启动后生成相关文件
ll /var/lib/proxysql/
-rw-rw---- 1 proxysql proxysql 1082 Aug 24 18:23 proxysql-ca.pem
-rw-rw---- 1 proxysql proxysql 1086 Aug 24 18:23 proxysql-cert.pem
-rw-rw---- 1 proxysql proxysql 1704 Aug 24 18:23 proxysql-key.pem
-rw-r----- 1 proxysql proxysql 360448 Aug 24 18:23 proxysql.db # 持久化规则配置文件,持久化磁盘库
-rw-rw---- 1 proxysql proxysql 9913 Aug 24 18:23 proxysql.log
-rw-r--r-- 1 proxysql proxysql 6 Aug 24 18:23 proxysql.pid
-rw-r----- 1 proxysql proxysql 245760 Aug 24 18:23 proxysql_stats.db # 历史统计磁盘库
# 监听端口
ss -ntlp | grep sql
LISTEN 0 128 0.0.0.0:6032 0.0.0.0:* users:(("proxysql",pid=26834,fd=71)) # 管理端口
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=26834,fd=51)) # 业务代理端口,业务应用连接端口
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=26834,fd=50))
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=26834,fd=49))
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=26834,fd=48))
LISTEN 0 128 0.0.0.0:6132 0.0.0.0:* users:(("proxysql",pid=26834,fd=72))
LISTEN 0 1024 0.0.0.0:6133 0.0.0.0:* users:(("proxysql",pid=26834,fd=55))
LISTEN 0 1024 0.0.0.0:6133 0.0.0.0:* users:(("proxysql",pid=26834,fd=54))
LISTEN 0 1024 0.0.0.0:6133 0.0.0.0:* users:(("proxysql",pid=26834,fd=53))
LISTEN 0 1024 0.0.0.0:6133 0.0.0.0:* users:(("proxysql",pid=26834,fd=52))
16.1.2 登录 ProxySQL
# 查看主配置文件默认 管理员用户密码和管理端口
cat /etc/proxysql.cnf
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"
# 安装 MySQL 客户端程序
apt install -y mysql-client
# 登录管理员控制界面
mysql -uadmin -padmin -h10.0.0.224 -P6032
ERROR 1040 (42000): User 'admin' can only connect locally # 默认的 admin 用户仅允许本地登录
# 正确登录方式
mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module) # ProxySQL 版本信息
16.1.3 默认数据库说明
show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | | -- 内存配置库，最重要
| 2 | disk | /var/lib/proxysql/proxysql.db | -- 持久化磁盘库
| 3 | stats | | -- 实时统计库，内存
| 4 | monitor | | -- 监控库，内存
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | -- 历史统计磁盘库
+-----+---------------+-------------------------------------+
-- 查看 内存配置库 中的表
show tables from main;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| global_variables | -- 核心表,磁盘层面的全局参数
| mysql_query_rules | -- 核心表,读写分离路由规则
| mysql_servers | -- 核心表,存放后端 MySQL 主从节点
| runtime_global_variables | -- 核心表,正在生效的运行时参数
+----------------------------------------------------+
-- 内存配置库 的 工作流程
执行 INSERT、UPDATE 修改的是 main 库
-- 把 main 库配置加载进 runtime 运行内存
LOAD xxx TO RUNTIME
-- 将 main 库配置写入 disk 库
CALL PROXYSQL_SAVE_MEMORY_TO_DISK()
16.1.4 修改 admin 账号密码
systemctl stop proxysql
vim /etc/proxysql.cnf
admin_variables=
{
admin_credentials="admin:Dengtest@123" # admin:后即为管理员账号密码
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
}
# 删除旧的 持久化磁盘库 文件
rm -f /var/lib/proxysql/proxysql.db
systemctl start proxysql
# 使用新的密码登录
mysql -uadmin -pDengtest@123 -h127.0.0.1 -P6032
16.1.5 修改默认的 server 版本
生成环境修改, 避免应用检测 MySQL 版本不符合需求
# 查看后端 MySQL 服务版本
mysql -V
mysql Ver 8.4.11 for Linux on x86_64 (MySQL Community Server - GPL)
# 修改 server 版本
vim /etc/proxysql.cnf
mysql_variables=
{
server_version="8.4.11" # 修改为后端 MySQL 对应版本
# 删除旧的 持久化磁盘库 文件,重启服务
rm -f /var/lib/proxysql/proxysql.db
systemctl restart proxysql
# 验证
mysql -uadmin -pDengtest@123 -h127.0.0.1 -P6032
Server version: 8.4.11 (ProxySQL Admin Module)
16.2 ProxySQL 配置
16.2.1 初始化环境
# 从节点执行
systemctl stop mysql.service
rm -rf /var/lib/mysql/* /data/mysql/logs/*
# MySQL 初始化
mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql
systemctl start mysql.service
\mysql -e "alter user root@'localhost' identified WITH caching_sha2_password by 'Dengtest@123';flush privileges;"
echo "alias mysql='mysql -uroot -pDengtest@123 2>/dev/null'" > /etc/profile.d/mysqld.sh
source /etc/profile.d/mysqld.sh
-- 主节点创建用户
CREATE USER repluser@'10.0.0.%' IDENTIFIED BY 'Dengtest@123';
-- 授予 REPLICATION SLAVE 权限
GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%';
-- 刷新权限
FLUSH PRIVILEGES;
# 主节点做全量备份
mysqldump -uroot -pDengtest@123 -A --set-gtid-purged=ON --single-transaction --triggers --routines --events > full_back.sql
# 从节点将全量备份拷贝到本机还原
scp 10.0.0.222:/root/full_back.sql .
mysql < full_back.sql
-- 从节点加入集群
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.222',
SOURCE_USER='repluser',
SOURCE_PASSWORD='Dengtest@123',
SOURCE_PORT=3306,
SOURCE_SSL=1,
SOURCE_AUTO_POSITION=1;
START replica;
show replica status\G
-- 验证
-- 主节点确认复制集群
show replicas;
+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 223 | | 3306 | 222 | 13110372-9fad-11f1-8191-000c298c5f83 |
| 221 | | 3306 | 222 | fdd1cd29-9fad-11f1-ba1a-000c29659254 |
+-----------+------+------+-----------+--------------------------------------+
-- 主库创建数据库
create database nohao;
-- 从库验证,主从复制正常
show databases;
+--------------------+
| Database |
+--------------------+
| nohao |
16.2.2 注册主从节点至 mysql_servers 表
注册相关参数说明:
- hostgroup_id : 1 为写组(INSERT、UPDATE、DELETE), 2 为读组(SELECT)
- hostname : 主机 IP
- port : 端口
- weight : 权重
- max_connections : 最大连接数
- max_replication_lag : 监控从库复制延迟,单位秒,超过指定时间自动把该节点踢出读组
- status : 状态
-- 登录 ProxySQL
mysql -uadmin -pDengtest@123 -h127.0.0.1 -P6032
-- 切换到内存配置库
use main;
-- 查看主机节点表表结构以确认可以使用的字段
show create table mysql_servers;
-- 插入主节点到写组
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,status) VALUES (1,'10.0.0.222',3306,100,1000,0,'ONLINE');
-- 插入 2 台从节点到读组
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,status) VALUES (2,'10.0.0.221',3306,100,1000,0,'ONLINE'),(2,'10.0.0.223',3306,100,1000,0,'ONLINE');
-- 查看添加的节点列表
select * from mysql_servers;
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 10.0.0.222 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 10.0.0.221 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 10.0.0.223 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
-- 保存配置
-- 载入配置到内存
load mysql servers to runtime;
-- 写入磁盘以持久化
save mysql servers to disk;
16.2.3 配置数据库监控账户
-- 主库创建用户
create user proxyer@'10.0.0.%' identified by 'Dengtest@123';
-- 对用户授权
grant REPLICATION CLIENT on *.* to proxyer@'10.0.0.%';
-- 刷新权限
flush privileges;
-- ProxySQL 配置连接 MySQL 的用户名和密码
set mysql-monitor_username='proxyer';
set mysql-monitor_password='Dengtest@123';
-- 保存配置
-- 载入配置到内存
load mysql variables to runtime;
-- 写入磁盘以持久化
save mysql variables to disk;
-- 验证
show variables like "mysql-monitor_username";
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| mysql-monitor_username | proxyer |
+------------------------+---------+
show variables like "mysql-monitor_password";
+------------------------+--------------+
| Variable_name | Value |
+------------------------+--------------+
| mysql-monitor_password | Dengtest@123 |
+------------------------+--------------+
-- 查询连接日志,connect_error 为空(NULL)表示连接成功
select * from mysql_server_connect_log;
+------------+------+------------------+-------------------------+---------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+------------+------+------------------+-------------------------+---------------------------------------------------------------------+
| 10.0.0.221 | 3306 | 1787575295040319 | 0 | Access denied for user 'monitor'@'10.0.0.224' (using password: YES) |
| 10.0.0.222 | 3306 | 1787575295603309 | 0 | Access denied for user 'monitor'@'10.0.0.224' (using password: YES) |
| 10.0.0.223 | 3306 | 1787575296165870 | 0 | Access denied for user 'monitor'@'10.0.0.224' (using password: YES) |
| 10.0.0.221 | 3306 | 1787575336268073 | 1142 | NULL |
| 10.0.0.222 | 3306 | 1787575337040671 | 20820 | NULL |
| 10.0.0.223 | 3306 | 1787575337813385 | 1412 | NULL |
+------------+------+------------------+-------------------------+---------------------------------------------------------------------+
-- 查看 ping 日志
select * from mysql_server_ping_log;
16.2.4 配置业务读写账户
-- 主库创建用户
create user ruoyi@'10.0.0.%' identified by 'Dengtest@123';
-- 对用户授权
grant ALL PRIVILEGES ON ruoyi_vue.* to ruoyi@'10.0.0.%';
-- 刷新权限
flush privileges;
USE main;
-- ProxySQL 配置 业务账号
INSERT INTO mysql_users(username,password,active,max_connections) VALUES ('ruoyi','Dengtest@123',1,1000);
-- 保存配置
load mysql users to runtime;
save mysql users to disk;
16.2.5 配置默认分组
default_hostgroup=1 默认组设置为写组,当读写规则匹配失败时交给写组处理,避免请求无法执行
USE main;
-- ProxySQL 更新 ruoyi 账户默认分组为写组
UPDATE mysql_users SET default_hostgroup=1 WHERE username='ruoyi';
-- 验证
SELECT username,password,default_hostgroup,active FROM mysql_users;
+----------+--------------+-------------------+--------+
| username | password | default_hostgroup | active |
+----------+--------------+-------------------+--------+
| ruoyi | Dengtest@123 | 1 | 1 |
+----------+--------------+-------------------+--------+
-- 保存配置
load mysql users to runtime;
save mysql users to disk;
-- 连接 ProxySQL 业务端口测试
mysql -uruoyi -pDengtest@123 -h10.0.0.224 -P6033 -e 'select @@server_id,@@read_only,user();'
+-------------+-------------+------------------+
| @@server_id | @@read_only | user() |
+-------------+-------------+------------------+
| 222 | 0 | ruoyi@10.0.0.224 |
+-------------+-------------+------------------+
16.2.6 ProxySQL 配置读写分离规则
读写分离规则相关参数解析:
- rule_id : 数值越小优先级越高
- active=1 : 启用规则
- match_digest : 路由匹配逻辑
- destination_hostgroup : 对应主机加入的分组(1 写组,2 读组)
- apply=1 : 匹配成功后终止后续规则判断
USE main;
-- 查看规则配置表可用字段
show create table mysql_query_rules;
-- 配置 SQL 路由规则
-- SELECT ... FOR UPDATE 是排他锁，必须走主库
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (0,1,'^SELECT.*FOR UPDATE$',1,1);
-- 查询元数据路由到读组,测试规则,生产环境不需要
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SHOW.*',2,1);
-- SELECT 语句路由到读组,INSERT|UPDATE|DELETE|CREATE|ALTER|DROP 语句路由到写组
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'^SELECT.*',2,1),(3,1,'^(INSERT|UPDATE|DELETE|CREATE|ALTER|DROP)',1,1);
-- 保存配置
load mysql QUERY RULES to runtime;
save mysql QUERY RULES to disk;
-- 验证
SELECT rule_id,active,match_digest,destination_hostgroup,apply FROM runtime_mysql_query_rules;
+---------+--------+-------------------------------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+-------------------------------------------+-----------------------+-------+
| 0 | 1 | ^SELECT.*FOR UPDATE$ | 1 | 1 |
| 1 | 1 | ^SHOW.* | 2 | 1 |
| 2 | 1 | ^SELECT.* | 2 | 1 |
| 3 | 1 | ^(INSERT|UPDATE|DELETE|CREATE|ALTER|DROP) | 1 | 1 |
+---------+--------+-------------------------------------------+-----------------------+-------+
16.2.7 读写测试
# MySQL 节点开启通用日志并追踪查看日志
mysql -e "set global general_log=on;"
tail -f /var/lib/mysql/ubuntu-221.log
mysql -e "set global general_log=on;"
tail -f /var/lib/mysql/ubuntu-222.log
mysql -e "set global general_log=on;"
tail -f /var/lib/mysql/ubuntu-223.log
# 读请求测试
# 向 ProxySQL 发起查询请求
mysql -uruoyi -pDengtest@123 -h10.0.0.224 -P6033 -e "SELECT @@server_id;"
+-------------+
| @@server_id |
+-------------+
| 221 |
+-------------+
# 从节点 221 查看日志
2026-08-24T21:48:34.957542+08:00 208 Query SELECT @@server_id
# 再次发起查询请求,发现轮询路由到读组另一台主机
mysql -uruoyi -pDengtest@123 -h10.0.0.224 -P6033 -e "SELECT @@server_id;"
+-------------+
| @@server_id |
+-------------+
| 223 |
+-------------+
# 从节点 223 查看日志
2026-08-24T21:50:02.136601+08:00 194 Query SELECT @@server_id
# 写请求测试
# 登录到代理服务端
mysql -uruoyi -pDengtest@123 -h10.0.0.224 -P6033
-- 插入测试数据
INSERT INTO ry_vue.sys_user (user_name, nick_name, email) VALUES ('test01','测试用户','test@163.com');
-- 更新测试数据
UPDATE ry_vue.sys_user SET nick_name = '更新后的名称' WHERE user_name='test01';
-- 删除测试数据
DELETE FROM ry_vue.sys_user WHERE user_name='test01';
# 主节点 222 查看日志
2026-08-24T21:57:21.206648+08:00 2812 Query -- 插入测试数据
2026-08-24T21:57:21.208331+08:00 2812 Query INSERT INTO ry_vue.sys_user (user_name, nick_name, email) VALUES ('test01','测试用户','test@163.com')
2026-08-24T21:57:21.226718+08:00 2812 Query -- 更新测试数据
2026-08-24T21:57:21.227634+08:00 2812 Query UPDATE ry_vue.sys_user SET nick_name = '更新后的名称' WHERE user_name='test01'
2026-08-24T21:57:21.230044+08:00 2812 Query -- 删除测试数据
2026-08-24T21:57:22.259483+08:00 2812 Query DELETE FROM ry_vue.sys_user WHERE user_name='test01'
-- ProxySQL 查看监控信息
USE stats;
-- 查看指定规则路由记录
SELECT digest_text,hostgroup FROM stats_mysql_query_digest WHERE digest_text LIKE 'INSERT%' OR digest_text LIKE 'UPDATE%' OR digest_text LIKE 'DELETE%';
+------------------------------------------------------------------------+-----------+
| digest_text | hostgroup |
+------------------------------------------------------------------------+-----------+
| DELETE FROM ry_vue.sys_user WHERE user_name=? | 1 |
| UPDATE ry_vue.sys_user SET nick_name = ? WHERE user_name=? | 1 |
| INSERT INTO ry_vue.sys_user (user_name,nick_name,email) VALUES (?,?,?) | 1 |
+------------------------------------------------------------------------+-----------+
16.3 RuoYi 对接 ProxySQL
16.3.1 环境说明
- 若依-Vue 后端部署:
10.0.0.221 - ProxySQL 代理:
10.0.0.224 - 后端 MySQL 集群:
10.0.0.222(主)、10.0.0.221(从)、10.0.0.223(从) - ProxySQL 业务账户:
ruoyi/Dengtest@123
16.3.2 配置数据库指向 ProxySQL
cd RuoYi-Vue/
vim ruoyi-admin/src/main/resources/application-druid.yml
master:
url: jdbc:mysql://10.0.0.224:6033/ry_vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8 # 修改数据库地址和端口指向 ProxySQL
username: ruoyi # 修改为 ProxySQL 业务读写账户
password: Dengtest@123 # 修改为 ProxySQL 业务读写账户密码
16.3.3 重新打包
mvn clean package -Dmaven.test.skip=true
ls ruoyi-admin/target/ruoyi-admin.jar
ruoyi-admin/target/ruoyi-admin.jar
16.3.4 测试运行
java -jar ruoyi-admin/target/ruoyi-admin.jar
(♥◠‿◠)ﾉﾞ 若依启动成功 ლ(´ڡ`ლ)ﾞ
16.3.5 重启 若依-Vue 后端
cp ruoyi-admin/target/ruoyi-admin.jar /data/ruoyi/server/
systemctl restart ruoyi-admin.service
16.3.6 验证
浏览器访问 http://ruoyi.deng.org
浏览器访问 http://ruoyi.deng.org:8080/druid/login.html
-- 查看语句路由记录
-- 查看读请求
SELECT digest_text,hostgroup,count_star FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%';
-- 查看写请求
SELECT digest_text,hostgroup FROM stats_mysql_query_digest WHERE digest_text LIKE 'INSERT%' OR digest_text LIKE 'UPDATE%' OR digest_text LIKE 'DELETE%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| digest_text | hostgroup |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| insert into sys_oper_log(title,business_type,method,request_method,operator_type,oper_name,dept_name,oper_url,oper_ip,oper_location,oper_param,json_result,status,error_msg,cost_time,oper_time) values (?,?,?,...,null,?,sysdate()) | 1 |
| UPDATE ry_vue.sys_user SET nick_name = ? WHERE user_name=? | 1 |
| insert into sys_user_post(user_id,post_id) values (?,?) | 1 |
| DELETE FROM ry_vue.sys_user WHERE user_name=? | 1 |
| INSERT INTO ry_vue.sys_user (user_name,nick_name,email) VALUES (?,?,?) | 1 |
| update sys_user set login_ip = ?,login_date = ? where user_id = ? | 1 |
| insert into sys_user_role(user_id,role_id) values (?,?) | 1 |
| insert into sys_logininfor (user_name,status,ipaddr,login_location,browser,os,msg,login_time) values (?,?,?,...,sysdate()) | 1 |
| insert into sys_user(dept_id,user_name,nick_name,phonenumber,sex,password,status,create_by,create_time)values(?,?,?,...,sysdate()) | 1 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
16.4 ProxySQL 监控
16.4.1 代理流量监控表
SELECT
hostgroup,
CASE hostgroup
WHEN 1 THEN '主库(写分组)'
WHEN 2 THEN '从库(读分组)'
END AS group_type,
SUM(count_star) AS total_req,
ROUND( CAST(SUM(sum_time) AS FLOAT)/1000, 2 ) AS total_ms,
ROUND( CAST(SUM(sum_time) AS FLOAT) / SUM(count_star) / 1000, 2 ) AS avg_ms
FROM stats_mysql_query_digest
WHERE schemaname = 'ry_vue'
GROUP BY hostgroup;
+-----------+-------------------+-----------+----------+--------+
| hostgroup | group_type | total_req | total_ms | avg_ms |
+-----------+-------------------+-----------+----------+--------+
| 1 | 主库(写分组) | 41 | 38.37 | 0.94 |
| 2 | 从库(读分组) | 73 | 66.03 | 0.9 |
+-----------+-------------------+-----------+----------+--------+
16.4.2 区分主库写入流量、从库查询流量占比
WITH group_stat AS (
SELECT
hostgroup,
SUM(count_star) AS req_total
FROM stats_mysql_query_digest
WHERE schemaname = 'ry_vue'
GROUP BY hostgroup
)
SELECT
CASE hostgroup
WHEN 1 THEN '主库(写流量)'
WHEN 2 THEN '从库(读流量)'
END AS traffic_type,
req_total,
ROUND( CAST(req_total AS FLOAT) / (SELECT SUM(req_total) FROM group_stat) * 100 ,2) AS traffic_percent
FROM group_stat;
+-------------------+-----------+-----------------+
| traffic_type | req_total | traffic_percent |
+-------------------+-----------+-----------------+
| 主库(写流量) | 41 | 35.96 |
| 从库(读流量) | 73 | 64.04 |
+-------------------+-----------+-----------------+
16.4.3 捕获慢 SQL
-- 筛选平均耗时 > 1ms 的 慢SQL,展示路由分组、执行次数、返回行数
SELECT
hostgroup, -- SQL 路由到哪个分组：1主库、2从库
count_star, -- 这条 SQL 累计执行多少次
ROUND(sum_time/count_star/1000,2) avg_ms, -- 计算单次平均耗时，单位毫秒，保留2位小数
sum_rows_sent, -- 这条 SQL 总共返回给前端多少行数据
digest_text -- 脱敏后的原始 SQL 语句（参数全部替换成?）
FROM stats_mysql_query_digest
WHERE schemaname = 'ry_vue' -- 只看业务库 ry_vue，过滤系统内置库信息 _schema
ORDER BY avg_ms DESC; -- 按平均耗时从大到小排序，最慢的 SQL 排在最上面
16.4.4 开启 SQL 日志
-- 查看相关属性信息
select * from global_variables where variable_name like "mysql-eventslog%";
+-----------------------------------------+----------------+
| variable_name | variable_value |
+-----------------------------------------+----------------+
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-eventslog_buffer_history_size | 0 |
| mysql-eventslog_table_memory_size | 10000 |
| mysql-eventslog_buffer_max_query_length | 32768 |
| mysql-eventslog_default_log | 0 |
| mysql-eventslog_format | 1 |
| mysql-eventslog_stmt_parameters | 0 |
| mysql-eventslog_flush_timeout | 1000 |
| mysql-eventslog_flush_size | 4096 |
| mysql-eventslog_rate_limit | 1 |
+-----------------------------------------+----------------+
-- 开启全量事件日志
UPDATE global_variables SET variable_value='1' WHERE variable_name='mysql-eventslog_default_log';
-- 配置日志文件路径
UPDATE global_variables SET variable_value='/var/lib/proxysql/mysql_events.log' WHERE variable_name='mysql-eventslog_filename';
-- 保存配置
load mysql VARIABLES to runtime;
save mysql VARIABLES to disk;
-- 故障排查完毕将日志级别调回默认值
UPDATE global_variables SET variable_value='0' WHERE variable_name='mysql-eventslog_default_log';
load mysql VARIABLES to runtime;
save mysql VARIABLES to disk;
16.5 MySQL 高可用
常见解决方案
| 高可用方案 | 适用场景 | 备注 |
|---|---|---|
| MHA | MySQL 5.7 | 一次性故障切换 |
| MGR(官方) | MySQL 8.0 + | 内置自动故障切换 |
| Galera Cluster(PXC)、NDB Cluster | 强一致性,低并发写入 | Galera 大事务阻塞集群,NDB运维成本极高 |
十七、SQL 语句进阶
17.1 多表查询
17.1.1 多表查询语句类型
- 子查询 : 基于查询结果再次查询
- 联合查询
- 交叉查询 : 笛卡尔集
- 等值内连接
- 不等值内连接
- 自然连接
- 左外连接
- 右外连接
- 完全外连接
- 自连接 : 本表和本表进行连接查询
17.1.2 子查询
SQL 语句嵌套 select 语句
-- 创建测试环境
create database testdb;
use testdb;
-- 创建表
CREATE TABLE `stu` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` char(30) DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
`age` tinyint unsigned DEFAULT '18',
`is_del` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `student2` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
`age` tinyint unsigned DEFAULT '18',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56;
CREATE TABLE `user1` (
`id` int NOT NULL AUTO_INCREMENT,
`Host` char(255) NOT NULL DEFAULT '',
`User` char(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5;
CREATE TABLE `teacher` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
);
-- 插入数据
INSERT student2 (name, age) values ('zhangsan',14);
INSERT INTO stu (name,age)VALUES('test1',20),('test2',21),('test3',27);
INSERT INTO stu (name,mobile,age) values('user1',13812345678,27),('user2',11212345678,27);
INSERT INTO stu (name,age) VALUES ('user3',27),('zhangsan',27),('lisi',27),('wangwu',27);
UPDATE stu SET is_del='0' where id in (1,3,5,7);
INSERT INTO teacher(name,age)values('zhang',30),('wang',40);
-- 验证
select * from teacher;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | zhang | 30 | M |
| 2 | wang | 40 | M |
+----+-------+------+--------+
select * from stu;
+----+----------+-------------+------+--------+
| id | name | mobile | age | is_del |
+----+----------+-------------+------+--------+
| 1 | test1 | NULL | 20 | 0 |
| 2 | test2 | NULL | 21 | 1 |
| 3 | test3 | NULL | 27 | 0 |
| 4 | user1 | 13812345678 | 27 | 1 |
| 5 | user2 | 11212345678 | 27 | 0 |
| 6 | user3 | NULL | 27 | 1 |
| 7 | zhangsan | NULL | 27 | 0 |
| 8 | lisi | NULL | 27 | 1 |
| 9 | wangwu | NULL | 27 | 1 |
+----+----------+-------------+------+--------+
-- 查询年龄比老师小13岁的
select name,age from stu where age<(select avg(age)-13 from teacher);
+-------+------+
| name | age |
+-------+------+
| test1 | 20 |
| test2 | 21 |
+-------+------+
-- 更新 id 为 5 的学生的年龄为老师表的最大年龄
update stu set age=(select max(age) from teacher) where id=5;
-- 更新 id 为 2 的学生的年龄为老师表的最小年龄
update stu set age=(select min(age) from teacher) where id=2;
select * from stu where id in (2,5);
+----+-------+-------------+------+--------+
| id | name | mobile | age | is_del |
+----+-------+-------------+------+--------+
| 2 | test2 | NULL | 30 | 1 |
| 5 | user2 | 11212345678 | 40 | 0 |
+----+-------+-------------+------+--------+
-- 基于查询结果的值匹配
select name,age from stu where age in (select age from teacher);
+-------+------+
| name | age |
+-------+------+
| test2 | 30 |
| user2 | 40 |
+-------+------+
17.1.3 联合查询
使用UNION或UNION ALL时，必须确保每个SELECT语句的列数和列的数据类型都匹配
联合查询只能联合每个表都有的字段
联合查询时如果两个表字段数量不一致,不能查询
字段名不同但类型一致字段可以合并
union 默认具有去重能力
union all 表示不使用去重能力
select id,name from stu union select id,name from teacher;
+----+----------+
| id | name |
+----+----------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | user1 |
| 5 | user2 |
| 6 | user3 |
| 7 | zhangsan |
| 8 | lisi |
| 9 | wangwu |
| 1 | zhang |
| 2 | wang |
+----+----------+
select is_del,name from stu union select id,name from teacher;
+--------+----------+
| is_del | name |
+--------+----------+
| 0 | test1 |
| 1 | test2 |
| 0 | test3 |
| 1 | user1 |
| 0 | user2 |
| 1 | user3 |
| 0 | zhangsan |
| 1 | lisi |
| 1 | wangwu |
| 1 | zhang |
| 2 | wang |
+--------+----------+
select is_del,name,age from stu union select id,name,"test-data" from teacher;
+--------+----------+-----------+
| is_del | name | age |
+--------+----------+-----------+
| 0 | test1 | 20 |
| 1 | test2 | 30 |
| 0 | test3 | 27 |
| 1 | user1 | 27 |
| 0 | user2 | 40 |
| 1 | user3 | 27 |
| 0 | zhangsan | 27 |
| 1 | lisi | 27 |
| 1 | wangwu | 27 |
| 1 | zhang | test-data |
| 2 | wang | test-data |
+--------+----------+-----------+
select * from teacher union select * from teacher;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | zhang | 30 | M |
| 2 | wang | 40 | M |
+----+-------+------+--------+
select * from teacher union all select * from teacher;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | zhang | 30 | M |
| 2 | wang | 40 | M |
| 1 | zhang | 30 | M |
| 2 | wang | 40 | M |
+----+-------+------+--------+
17.1.4 交叉连接
返回两个或多个表中所有可能的行组合
select * from stu cross join teacher;
+----+----------+-------------+------+--------+----+-------+------+--------+
| id | name | mobile | age | is_del | id | name | age | gender |
+----+----------+-------------+------+--------+----+-------+------+--------+
| 1 | test1 | NULL | 20 | 0 | 2 | wang | 40 | M |
| 1 | test1 | NULL | 20 | 0 | 1 | zhang | 30 | M |
| 2 | test2 | NULL | 30 | 1 | 2 | wang | 40 | M |
| 2 | test2 | NULL | 30 | 1 | 1 | zhang | 30 | M |
| 3 | test3 | NULL | 27 | 0 | 2 | wang | 40 | M |
| 3 | test3 | NULL | 27 | 0 | 1 | zhang | 30 | M |
| 4 | user1 | 13812345678 | 27 | 1 | 2 | wang | 40 | M |
| 4 | user1 | 13812345678 | 27 | 1 | 1 | zhang | 30 | M |
| 5 | user2 | 11212345678 | 40 | 0 | 2 | wang | 40 | M |
| 5 | user2 | 11212345678 | 40 | 0 | 1 | zhang | 30 | M |
| 6 | user3 | NULL | 27 | 1 | 2 | wang | 40 | M |
| 6 | user3 | NULL | 27 | 1 | 1 | zhang | 30 | M |
| 7 | zhangsan | NULL | 27 | 0 | 2 | wang | 40 | M |
| 7 | zhangsan | NULL | 27 | 0 | 1 | zhang | 30 | M |
| 8 | lisi | NULL | 27 | 1 | 2 | wang | 40 | M |
| 8 | lisi | NULL | 27 | 1 | 1 | zhang | 30 | M |
| 9 | wangwu | NULL | 27 | 1 | 2 | wang | 40 | M |
| 9 | wangwu | NULL | 27 | 1 | 1 | zhang | 30 | M |
+----+----------+-------------+------+--------+----+-------+------+--------+
select * from stu,teacher;
+----+----------+-------------+------+--------+----+-------+------+--------+
| id | name | mobile | age | is_del | id | name | age | gender |
+----+----------+-------------+------+--------+----+-------+------+--------+
| 1 | test1 | NULL | 20 | 0 | 2 | wang | 40 | M |
| 1 | test1 | NULL | 20 | 0 | 1 | zhang | 30 | M |
| 2 | test2 | NULL | 30 | 1 | 2 | wang | 40 | M |
| 2 | test2 | NULL | 30 | 1 | 1 | zhang | 30 | M |
| 3 | test3 | NULL | 27 | 0 | 2 | wang | 40 | M |
| 3 | test3 | NULL | 27 | 0 | 1 | zhang | 30 | M |
| 4 | user1 | 13812345678 | 27 | 1 | 2 | wang | 40 | M |
| 4 | user1 | 13812345678 | 27 | 1 | 1 | zhang | 30 | M |
| 5 | user2 | 11212345678 | 40 | 0 | 2 | wang | 40 | M |
| 5 | user2 | 11212345678 | 40 | 0 | 1 | zhang | 30 | M |
| 6 | user3 | NULL | 27 | 1 | 2 | wang | 40 | M |
| 6 | user3 | NULL | 27 | 1 | 1 | zhang | 30 | M |
| 7 | zhangsan | NULL | 27 | 0 | 2 | wang | 40 | M |
| 7 | zhangsan | NULL | 27 | 0 | 1 | zhang | 30 | M |
| 8 | lisi | NULL | 27 | 1 | 2 | wang | 40 | M |
| 8 | lisi | NULL | 27 | 1 | 1 | zhang | 30 | M |
| 9 | wangwu | NULL | 27 | 1 | 2 | wang | 40 | M |
| 9 | wangwu | NULL | 27 | 1 | 1 | zhang | 30 | M |
+----+----------+-------------+------+--------+----+-------+------+--------+
17.1.5 内连接 [ * ]
-- 将教师表的字段追加到 stu表的 age 字段后面，同时将新增字段的默认值设为0
ALTER TABLE stu ADD teacher_id int(11) default 0 after age;
-- 更改范围学生的指定字段值
update stu set teacher_id=1 where id in (1,3,8,9);
update stu set teacher_id=2 where id in (2,5,4);
-- 将 stu表的teacher_id字段值 和 teacher的id值进行比较判断，返回满足条件的结果
select * from stu inner join teacher on stu.teacher_id=teacher.id;
+----+--------+-------------+------+------------+--------+----+-------+------+--------+
| id | name | mobile | age | teacher_id | is_del | id | name | age | gender |
+----+--------+-------------+------+------------+--------+----+-------+------+--------+
| 1 | test1 | NULL | 20 | 1 | 0 | 1 | zhang | 30 | M |
| 2 | test2 | NULL | 21 | 2 | 1 | 2 | wang | 40 | M |
| 3 | test3 | NULL | 27 | 1 | 0 | 1 | zhang | 30 | M |
| 4 | user1 | 13812345678 | 27 | 2 | 1 | 2 | wang | 40 | M |
| 5 | user2 | 11212345678 | 27 | 2 | 0 | 2 | wang | 40 | M |
| 8 | lisi | NULL | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 9 | wangwu | NULL | 27 | 1 | 1 | 1 | zhang | 30 | M |
+----+--------+-------------+------+------------+--------+----+-------+------+--------+
-- 取交集的时候，同时指定输出字段 和 对字段名字改名输出
select stu.id as stu_id,stu.name as stu_name,stu.teacher_id as teacher_id,teacher.name as teacher_name from stu inner join teacher on stu.teacher_id=teacher.id;
+--------+----------+------------+--------------+
| stu_id | stu_name | teacher_id | teacher_name |
+--------+----------+------------+--------------+
| 1 | test1 | 1 | zhang |
| 2 | test2 | 2 | wang |
| 3 | test3 | 1 | zhang |
| 4 | user1 | 2 | wang |
| 5 | user2 | 2 | wang |
| 8 | lisi | 1 | zhang |
| 9 | wangwu | 1 | zhang |
+--------+----------+------------+--------------+
-- 使用 "表1,表2 where" 样式,与上方语句效果一致
select stu.id as stu_id,stu.name as stu_name,stu.teacher_id as teacher_id,teacher.name as teacher_name from stu,teacher where stu.teacher_id=teacher.id;
+--------+----------+------------+--------------+
| stu_id | stu_name | teacher_id | teacher_name |
+--------+----------+------------+--------------+
| 1 | test1 | 1 | zhang |
| 2 | test2 | 2 | wang |
| 3 | test3 | 1 | zhang |
| 4 | user1 | 2 | wang |
| 5 | user2 | 2 | wang |
| 8 | lisi | 1 | zhang |
| 9 | wangwu | 1 | zhang |
+--------+----------+------------+--------------+
-- 使用 where 和 and 实现多条件判断
select stu.id as stu_id,stu.name as stu_name,stu.teacher_id as teacher_id,teacher.name as teacher_name from stu,teacher where stu.teacher_id=teacher.id and stu.id>5;
+--------+----------+------------+--------------+
| stu_id | stu_name | teacher_id | teacher_name |
+--------+----------+------------+--------------+
| 9 | wangwu | 1 | zhang |
| 8 | lisi | 1 | zhang |
+--------+----------+------------+--------------+
17.1.6 自然连接
-- 准备环境
-- 创建role职称表
CREATE TABLE `role` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
);
-- 创建user用户表
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
);
-- 插入数据
insert into role(title) values('cto'),('ceo'),('cfo');
insert into user(name) values('zhao'),('wang'),('li');
-- 验证
select * from role;select * from user;
+----+-------+
| id | title |
+----+-------+
| 1 | cto |
| 2 | ceo |
| 3 | cfo |
+----+-------+
+----+------+
| id | name |
+----+------+
| 1 | zhao |
| 2 | wang |
| 3 | li |
+----+------+
-- 基于id列，实现数据合并
select * from user NATURAL JOIN role;
+----+------+-------+
| id | name | title |
+----+------+-------+
| 1 | zhao | cto |
| 2 | wang | ceo |
| 3 | li | cfo |
+----+------+-------+
-- 使用自然连接的时候，无需使用 on 属性进行字段名称的限制
select user.name,role.title from user NATURAL JOIN role;
+------+-------+
| name | title |
+------+-------+
| zhao | cto |
| wang | ceo |
| li | cfo |
+------+-------+
17.1.7 外连接
17.1.7.1 左外连接
-- 为了方便信息显示，删除 mobile 字段
ALTER TABLE stu DROP COLUMN mobile;
-- 左边都要出现( outer 可以省略)
select * from stu left outer join teacher on stu.teacher_id=teacher.id;
+----+----------+------+------------+--------+------+-------+------+--------+
| id | name | age | teacher_id | is_del | id | name | age | gender |
+----+----------+------+------------+--------+------+-------+------+--------+
| 1 | test1 | 20 | 1 | 0 | 1 | zhang | 30 | M |
| 2 | test2 | 21 | 2 | 1 | 2 | wang | 40 | M |
| 3 | test3 | 27 | 1 | 0 | 1 | zhang | 30 | M |
| 4 | user1 | 27 | 2 | 1 | 2 | wang | 40 | M |
| 5 | user2 | 27 | 2 | 0 | 2 | wang | 40 | M |
| 6 | user3 | 27 | 0 | 1 | NULL | NULL | NULL | NULL |
| 7 | zhangsan | 27 | 0 | 0 | NULL | NULL | NULL | NULL |
| 8 | lisi | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 9 | wangwu | 27 | 1 | 1 | 1 | zhang | 30 | M |
+----+----------+------+------------+--------+------+-------+------+--------+
-- 左外连接同时指定输出字段和字段名
select stu.id tid,stu.name tname,stu.teacher_id tid2,teacher.name tname from stu left join teacher on stu.teacher_id=teacher.id;
+-----+----------+------+-------+
| tid | tname | tid2 | tname |
+-----+----------+------+-------+
| 1 | test1 | 1 | zhang |
| 2 | test2 | 2 | wang |
| 3 | test3 | 1 | zhang |
| 4 | user1 | 2 | wang |
| 5 | user2 | 2 | wang |
| 6 | user3 | 0 | NULL |
| 7 | zhangsan | 0 | NULL |
| 8 | lisi | 1 | zhang |
| 9 | wangwu | 1 | zhang |
+-----+----------+------+-------+
-- 使用 where 过滤
select stu.id tid,stu.name tname,stu.teacher_id tid2,stu.age sage,teacher.name tname from stu left join teacher on stu.teacher_id=teacher.id where teacher.name is not null;
+-----+--------+------+------+-------+
| tid | tname | tid2 | sage | tname |
+-----+--------+------+------+-------+
| 1 | test1 | 1 | 20 | zhang |
| 2 | test2 | 2 | 21 | wang |
| 3 | test3 | 1 | 27 | zhang |
| 4 | user1 | 2 | 27 | wang |
| 5 | user2 | 2 | 27 | wang |
| 8 | lisi | 1 | 27 | zhang |
| 9 | wangwu | 1 | 27 | zhang |
+-----+--------+------+------+-------+
select stu.id tid,stu.name tname,stu.teacher_id tid2,stu.age sage,teacher.name tname from stu left join teacher on stu.teacher_id=teacher.id where teacher.name is null;
+-----+----------+------+------+-------+
| tid | tname | tid2 | sage | tname |
+-----+----------+------+------+-------+
| 6 | user3 | 0 | 27 | NULL |
| 7 | zhangsan | 0 | 27 | NULL |
+-----+----------+------+------+-------+
17.1.7.2 右外连接
-- 左边都要出现( outer 可以省略)
select * from stu right outer join teacher on stu.teacher_id=teacher.id;
+------+--------+------+------------+--------+----+-------+------+--------+
| id | name | age | teacher_id | is_del | id | name | age | gender |
+------+--------+------+------------+--------+----+-------+------+--------+
| 9 | wangwu | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 8 | lisi | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 3 | test3 | 27 | 1 | 0 | 1 | zhang | 30 | M |
| 1 | test1 | 20 | 1 | 0 | 1 | zhang | 30 | M |
| 5 | user2 | 27 | 2 | 0 | 2 | wang | 40 | M |
| 4 | user1 | 27 | 2 | 1 | 2 | wang | 40 | M |
| 2 | test2 | 21 | 2 | 1 | 2 | wang | 40 | M |
+------+--------+------+------------+--------+----+-------+------+--------+
-- teacher 表插入 stu.teacher_id 无对应 id 的数据
insert into teacher(id,name,age)values(14,'zhao',37),(15,'liu',45);
-- 再次使用右外连接,stu 表无对应数据的行输出 NULL
select * from stu right join teacher on stu.teacher_id=teacher.id;
+------+--------+------+------------+--------+----+-------+------+--------+
| id | name | age | teacher_id | is_del | id | name | age | gender |
+------+--------+------+------------+--------+----+-------+------+--------+
| 9 | wangwu | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 8 | lisi | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 3 | test3 | 27 | 1 | 0 | 1 | zhang | 30 | M |
| 1 | test1 | 20 | 1 | 0 | 1 | zhang | 30 | M |
| 5 | user2 | 27 | 2 | 0 | 2 | wang | 40 | M |
| 4 | user1 | 27 | 2 | 1 | 2 | wang | 40 | M |
| 2 | test2 | 21 | 2 | 1 | 2 | wang | 40 | M |
| NULL | NULL | NULL | NULL | NULL | 14 | zhao | 37 | M |
| NULL | NULL | NULL | NULL | NULL | 15 | liu | 45 | M |
+------+--------+------+------------+--------+----+-------+------+--------+
-- 右外连接取非交集
select * from stu right join teacher on stu.teacher_id=teacher.id where teacher_id is not null;
+------+--------+------+------------+--------+----+-------+------+--------+
| id | name | age | teacher_id | is_del | id | name | age | gender |
+------+--------+------+------------+--------+----+-------+------+--------+
| 1 | test1 | 20 | 1 | 0 | 1 | zhang | 30 | M |
| 2 | test2 | 21 | 2 | 1 | 2 | wang | 40 | M |
| 3 | test3 | 27 | 1 | 0 | 1 | zhang | 30 | M |
| 4 | user1 | 27 | 2 | 1 | 2 | wang | 40 | M |
| 5 | user2 | 27 | 2 | 0 | 2 | wang | 40 | M |
| 8 | lisi | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 9 | wangwu | 27 | 1 | 1 | 1 | zhang | 30 | M |
+------+--------+------+------------+--------+----+-------+------+--------+
-- 左外连接取交集
select * from stu left join teacher on stu.teacher_id=teacher.id where teacher_id is not null;
+----+----------+------+------------+--------+------+-------+------+--------+
| id | name | age | teacher_id | is_del | id | name | age | gender |
+----+----------+------+------------+--------+------+-------+------+--------+
| 1 | test1 | 20 | 1 | 0 | 1 | zhang | 30 | M |
| 2 | test2 | 21 | 2 | 1 | 2 | wang | 40 | M |
| 3 | test3 | 27 | 1 | 0 | 1 | zhang | 30 | M |
| 4 | user1 | 27 | 2 | 1 | 2 | wang | 40 | M |
| 5 | user2 | 27 | 2 | 0 | 2 | wang | 40 | M |
| 6 | user3 | 27 | 0 | 1 | NULL | NULL | NULL | NULL |
| 7 | zhangsan | 27 | 0 | 0 | NULL | NULL | NULL | NULL |
| 8 | lisi | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 9 | wangwu | 27 | 1 | 1 | 1 | zhang | 30 | M |
+----+----------+------+------------+--------+------+-------+------+--------+
17.1.7.3 完全外连接
需要同时使用 左外连接 和 右外连接 实现
select * from stu left join teacher on stu.teacher_id=teacher.id union select * from stu right join teacher on stu.teacher_id=teacher.id;
+------+----------+------+------------+--------+------+-------+------+--------+
| id | name | age | teacher_id | is_del | id | name | age | gender |
+------+----------+------+------------+--------+------+-------+------+--------+
| 1 | test1 | 20 | 1 | 0 | 1 | zhang | 30 | M |
| 2 | test2 | 21 | 2 | 1 | 2 | wang | 40 | M |
| 3 | test3 | 27 | 1 | 0 | 1 | zhang | 30 | M |
| 4 | user1 | 27 | 2 | 1 | 2 | wang | 40 | M |
| 5 | user2 | 27 | 2 | 0 | 2 | wang | 40 | M |
| 6 | user3 | 27 | 0 | 1 | NULL | NULL | NULL | NULL |
| 7 | zhangsan | 27 | 0 | 0 | NULL | NULL | NULL | NULL |
| 8 | lisi | 27 | 1 | 1 | 1 | zhang | 30 | M |
| 9 | wangwu | 27 | 1 | 1 | 1 | zhang | 30 | M |
| NULL | NULL | NULL | NULL | NULL | 14 | zhao | 37 | M |
| NULL | NULL | NULL | NULL | NULL | 15 | liu | 45 | M |
+------+----------+------+------------+--------+------+-------+------+--------+
17.1.8 自连接
处理需要比较表中记录之间关系的场景时非常有用
-- 准备测试数据
create table emp (id int,name varchar(10),leaderid int);
insert emp values (1,'liu',null),(2,'zhangsir',1), (3,'wang',2),(4,'zhang',3);
-- 验证
select * from emp;
+------+----------+----------+
| id | name | leaderid |
+------+----------+----------+
| 1 | liu | NULL |
| 2 | zhangsir | 1 |
| 3 | wang | 2 |
| 4 | zhang | 3 |
+------+----------+----------+
-- 查看每个用户的领导
select a.id,a.name,a.leaderid,b.name as leadername from emp a left join emp b on a.leaderid=b.id;
+------+----------+----------+------------+
| id | name | leaderid | leadername |
+------+----------+----------+------------+
| 1 | liu | NULL | NULL |
| 2 | zhangsir | 1 | liu |
| 3 | wang | 2 | zhangsir |
| 4 | zhang | 3 | wang |
+------+----------+----------+------------+
-- 如果值为 NULL 输出指定默认值
select a.id,a.name,ifnull(a.leaderid,"0") as leaderid,ifnull(b.name,"founder") as leadername from emp a left join emp b on a.leaderid=b.id;
+------+----------+----------+------------+
| id | name | leaderid | leadername |
+------+----------+----------+------------+
| 1 | liu | 0 | founder |
| 2 | zhangsir | 1 | liu |
| 3 | wang | 2 | zhangsir |
| 4 | zhang | 3 | wang |
+------+----------+----------+------------+
17.2 多表查询综合案例
-- 准备测试数据
-- 创建学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
-- 创建科目表
CREATE TABLE subjects (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
-- 创建分数表
CREATE TABLE scores (
student_id INT,
subject_id INT,
score INT,
PRIMARY KEY (student_id, subject_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (subject_id) REFERENCES subjects(id)
);
-- 插入数据到students表
INSERT INTO students (id, name) VALUES
(1, 'zhang san'),
(2, 'li si'),
(3, 'wang wu');
-- 插入数据到subjects表
INSERT INTO subjects (id, name) VALUES
(1, 'linux'),
(2, 'golang'),
(3, 'python');
-- 插入数据到scores表
INSERT INTO scores (student_id, subject_id, score) VALUES
(1, 1, 60),
(1, 2, 70),
(1, 3, 80),
(2, 1, 70),
(2, 2, 75),
(2, 3, 65),
(3, 1, 90),
(3, 2, 85),
(3, 3, 65);
-- 验证
select * from students;select * from subjects;select * from scores;
+----+-----------+
| id | name |
+----+-----------+
| 1 | zhang san |
| 2 | li si |
| 3 | wang wu |
+----+-----------+
3 rows in set (0.00 sec)
+----+--------+
| id | name |
+----+--------+
| 1 | linux |
| 2 | golang |
| 3 | python |
+----+--------+
3 rows in set (0.01 sec)
+------------+------------+-------+
| student_id | subject_id | score |
+------------+------------+-------+
| 1 | 1 | 60 |
| 1 | 2 | 70 |
| 1 | 3 | 80 |
| 2 | 1 | 70 |
| 2 | 2 | 75 |
| 2 | 3 | 65 |
| 3 | 1 | 90 |
| 3 | 2 | 85 |
| 3 | 3 | 65 |
+------------+------------+-------+
9 rows in set (0.00 sec)
-- 右外连接: 从三个表（students、scores、subjects）中联合检索信息，以获取学生的姓名、他们参加的科目名称以及对应的分数
select students.id, students.name,subjects.name as subject_name,score from students left join scores on students.id=scores.student_id left join subjects on subjects.id=scores.subject_id;
+----+-----------+--------------+-------+
| id | name | subject_name | score |
+----+-----------+--------------+-------+
| 1 | zhang san | linux | 60 |
| 1 | zhang san | golang | 70 |
| 1 | zhang san | python | 80 |
| 2 | li si | linux | 70 |
| 2 | li si | golang | 75 |
| 2 | li si | python | 65 |
| 3 | wang wu | linux | 90 |
| 3 | wang wu | golang | 85 |
| 3 | wang wu | python | 65 |
+----+-----------+--------------+-------+
-- 内连接: 从三个表（ students 、 scores 、 subjects ）中联合检索信息，以获取学生的ID、姓名、分数以及对应的科目名称
select students.id,students.name,scores.score,subjects.name from students inner join scores on students.id=scores.student_id inner join subjects on scores.subject_id=subjects.id;
+----+-----------+-------+--------+
| id | name | score | name |
+----+-----------+-------+--------+
| 1 | zhang san | 60 | linux |
| 1 | zhang san | 70 | golang |
| 1 | zhang san | 80 | python |
| 2 | li si | 70 | linux |
| 2 | li si | 75 | golang |
| 2 | li si | 65 | python |
| 3 | wang wu | 90 | linux |
| 3 | wang wu | 85 | golang |
| 3 | wang wu | 65 | python |
+----+-----------+-------+--------+
17.3 SELECT 语句的处理顺序
- 加载数据源 : FROM → ON → JOIN
- 过滤加载的数据 : WHERE → GROUP BY → HAVING
- 显示内容 : SELECT
- 对显示的内容进行排序 : ORDER BY
- 分页展示 : LIMIT
扫一扫
2703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
