MySQL

在资源有限的情况下，很多开发者通常是单机服务器，那么如何做数据备份就成了一个问题。 数据库备份看起来是一件简单的事情：定时导出数据库，将备份文件上传到对象存储，需要时再下载恢复。 但真正放到生产环境中，会遇到不少问题： 数据库正在写入，直接复制数据文件是否会产生不一致？ 数据库备份文件越来越大，如何减少存储空间和上传流量？ 服务器意外关机，错过的定时任务能否自动补执行？ 备份文件存放在 OSS 或 R2 中，如何保证数据安全？ 备份任务显示成功，是否意味着数据库真的可以恢复？ 本文介绍一套基于 Restic + systemd Timer + 对象存储 的自动化数据库备份方案。 数据库以 PostgreSQL、MySQL 和 Redis 为例，远程存储分别使用 Cloudflare R2 和阿里云 OSS。通过数据库原生工具生成备份文件，再交给 Restic 进行加密、去重和快照管理，最后利用 systemd Timer 定时执行。 这套方案适合单机或少量 Linux 服务器上的数据库备份，也可以作为进一步建设企业备份体系的基础。 一、整体架构与技术选型 1.1 整体架构 整个备份流程分为四个部分： 数据库备份：使用数据库原生工具生成一致性备份文件。 本地暂存：将备份文件写入本地临时目录，并进行完整性检查。 Restic 备份：对备份文件进行加密、数据去重和 Snapshot 管理。 对象存储：将 Restic 仓库保存到 Cloudflare R2 或阿里云 OSS。 整体架构如下： systemd Timer | v 数据库备份脚本 | +------------+------------+ | | | v v v PostgreSQL MySQL Redis pg_dump mysqldump BGSAVE | | | +------------+------------+ | v 本地备份目录 | v Restic +--------+--------+ | | v v Cloudflare R2 阿里云 OSS Restic 仓库 Restic 仓库 这里需要特别说明：Restic 不是数据库备份工具，而是负责备份文件的存储和生命周期管理。...

MySQL 中主要的日志类型 MySQL 提供了多种日志类型，每种都有其特定的目的和格式。理解这些日志对于数据库管理、性能调优、数据恢复和故障排查至关重要。 下面我将详细介绍 MySQL 中主要的日志类型及其格式 总结概览 日志类型 主要目的 格式 是否默认开启 错误日志 记录 MySQL 启动、运行、停止时的错误、警告和提示信息。 文本格式 是 二进制日志 记录所有更改数据的语句，用于主从复制和数据恢复。 STATEMENT, ROW, MIXED 否 (8.0默认开启) 通用查询日志 记录所有到达 MySQL 的客户端连接和执行的语句。 文本格式 / CSV 格式 否 慢查询日志 记录执行时间超过指定阈值的查询，用于性能优化。 文本格式 / CSV 格式 否 重做日志 InnoDB 特有的，用于保证事务的持久性和崩溃恢复。 物理格式（二进制） 是 回滚日志 InnoDB 特有的，用于保证事务的原子性和 MVCC。 逻辑格式 是 详细解析 1. 错误日志 这是 DBA 首先需要查看的日志，当数据库出现任何异常时，它通常是排查问题的起点。 内容：启动/关闭信息、错误信息、警告信息、在复制环境中从服务器线程的启动信息等。 格式：纯文本格式，易于阅读。 配置参数： log_error：指定错误日志文件的位置。 示例内容： 2023-10-27T08:00:00.000000Z 0 \[Note\] Server started. 2023-10-27T08:01:23.456789Z 5 \[Warning\] Aborted connection 5 to db: 'test' user: 'root' host: 'localhost' 2....

Mysql窗口函数 一、什么是窗口函数？ 窗口函数是一种在查询结果的"窗口"上执行计算的函数，它不会像常规聚合函数那样将多行合并为一行，而是为每一行返回一个值，同时保持原始行数不变。 基本语法 <窗口函数>(<参数>) OVER ( [PARTITION BY <分区表达式>] [ORDER BY <排序表达式> [ASC | DESC]] [ROWS/RANGE <窗口范围>] ) ‌<窗口函数>‌：可以是聚合函数（如SUM、AVG）或专用函数（如ROW_NUMBER、RANK）。‌‌ ‌OVER()‌：必需子句，定义窗口框架。‌‌ ‌PARTITION BY‌：可选，用于分组数据；若省略，窗口覆盖整个结果集。 ‌ORDER BY‌：可选，指定窗口内行的排序顺序。‌‌ ‌窗口范围‌：可选，默认ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。‌‌ 二、 准备测试数据 -- 创建销售数据表 CREATE TABLE sales ( sale_id INT PRIMARY KEY, salesperson VARCHAR(50), region VARCHAR(50), sale_date DATE, amount DECIMAL(10,2) ); INSERT INTO sales VALUES (1, '张三', '北京', '2023-01-15', 5000), (2, '李四', '上海', '2023-01-16', 8000), (3, '王五', '北京', '2023-01-17', 6000), (4, '张三', '北京', '2023-02-01', 7000), (5, '李四', '上海', '2023-02-02', 9000), (6, '赵六', '广州', '2023-02-03', 4000), (7, '王五', '北京', '2023-02-04', 5500), (8, '张三', '北京', '2023-03-01', 8500); -- 创建员工薪资表 CREATE TABLE employee_salary ( emp_id INT, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10,2), hire_date DATE ); INSERT INTO employee_salary VALUES (1, '张三', '技术部', 15000, '2020-01-15'), (2, '李四', '技术部', 12000, '2021-03-20'), (3, '王五', '销售部', 8000, '2022-06-10'), (4, '赵六', '销售部', 7500, '2022-08-05'), (5, '钱七', '技术部', 13000, '2020-11-30'), (6, '孙八', '人事部', 9000, '2021-09-15'), (7, '周九', '技术部', 16000, '2019-05-20'); 1....

Mysql分库分表和主从复制 MyCat 实现分库分表 MySQL 的分库分表解决方案通常依赖于中间件来实现水平扩展。常见的中间件有以下几种 MyCat ShardingSphere TDDL (Taobao Distributed Data Layer) Cobar Vitess MyCat 是一款开源的数据库中间件，支持 MySQL 数据库的分库分表功能。 使用 MyCat 实现分库分表的过程包括多个步骤，涉及配置 MyCat 和数据库的分片策略，路由规则等。下面通过一个实际案例来详细解释 MyCat 的实现原理、方式和步骤。 案例背景 假设你有一个电商系统，包含一个 orders 表，记录用户的订单信息。随着数据量的增长，单一数据库难以满足性能需求，因此需要进行 分库分表。 实现原理 MyCat 是一种数据库中间件，支持分库分表。它的原理是通过代理 MySQL 连接，将 SQL 请求转发到实际的数据库实例上。MyCat 会根据预定义的路由规则（如分片策略）来决定将查询请求路由到哪个数据库或表。 MyCat 的 分库分表原理如下： 分库：将数据按照某种规则分配到多个数据库实例中。例如，按用户的 ID 来分库。 分表：将一个大表拆分成多个小表，避免单表的数据过大导致查询性能下降。例如，可以按时间范围或数据量来分表。 实现方式 MyCat 的实现方式主要通过 配置分片规则 来实现分库分表。配置内容包括： 数据源配置：定义数据库实例。 分片规则配置：指定哪些字段用于分片，如何分片（按范围、按哈希等）。 路由规则配置：根据 SQL 查询的条件来路由请求到不同的数据库或表。 实现步骤 安装并启动 MyCat 下载 MyCat 安装包并解压。 配置 MyCat 启动脚本，启动 MyCat。 cd MyCat ./bin/start.sh 配置数据源 在 MyCat 的配置文件 conf/context....

MySQL 分库分表：什么时候需要，怎么设计？ MySQL 的单机能力并不是无限的。 当数据规模、并发请求和存储需求持续增长时，即使已经完成索引优化、SQL 优化、缓存、连接池和读写分离，单个 MySQL 实例仍然可能成为系统瓶颈。 这时候才需要考虑一个更重的方案： 分库分表（Sharding）。 分库分表本质上是把原来集中在一个数据库中的数据和请求拆开，让多个数据库实例共同承担存储和访问压力。 但需要注意： 分库分表解决的是容量和并发扩展问题，并不会自动让每一条 SQL 都变快。 而且一旦进入分库分表阶段，原本简单的 SQL、事务、分页、JOIN、统计查询都会变得更加复杂。 因此，分库分表最重要的不是“怎么拆”，而是什么时候拆，以及按照什么规则拆。 1. 为什么需要分库分表 假设有一张订单表： CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(12, 2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_created_at (created_at) ); 随着业务增长，这张表可能达到数亿甚至更大的数据量。 问题可能来自几个方面。 单表数据量过大 表越来越大以后： 索引越来越大； Buffer Pool 缓存命中率下降； 查询和更新涉及更多磁盘 I/O； 索引维护成本增加； ALTER TABLE 等运维操作更加困难； 备份、恢复时间增加。 即使查询本身使用了正确的索引，大表带来的资源压力仍然存在。 单实例并发能力有限 一个 MySQL 实例需要同时承担：...

MySQL 深分页优化：LIMIT 为什么越来越慢 在后台管理、订单查询、文章列表等业务中，分页几乎是最常见的 SQL 场景之一。 最常见的写法是： SELECT id, title, created_at FROM articles ORDER BY id DESC LIMIT 20 OFFSET 1000000; 刚开始数据量比较小时，这种分页方式没有明显问题。但随着数据量增长，翻到后面的页码后，查询速度可能明显下降。 很多人会简单地认为： LIMIT OFFSET 会越来越慢，因为数据库扫描了很多数据。 这个说法没错，但还不够准确。真正需要理解的是：数据库为了找到第 1000001 条记录，通常仍然需要定位并跳过前面的 1000000 条符合条件的记录。 因此，深分页优化的核心并不是简单地给 LIMIT 加索引，而是要改变分页数据的定位方式。 1. 什么是深分页 假设 articles 表有 1000 万条数据： SELECT id, title, created_at FROM articles ORDER BY id DESC LIMIT 20 OFFSET 0; 查询第一页： 跳过 0 条 读取 20 条 查询第 100 页： LIMIT 20 OFFSET 1980; 需要跳过约 1980 条记录。...

MySQL 高并发场景下的性能优化 MySQL 的性能优化并不是简单地给表增加几个索引。 在真实的高并发系统中，数据库性能通常受到多个因素共同影响： 应用请求 ↓ 数据库连接 ↓ SQL 执行 ↓ 索引 ↓ Buffer Pool ↓ 锁 / 事务 ↓ 磁盘 I/O 当并发量继续增加以后，还会遇到： 连接数过高 SQL 执行时间增加 锁竞争 事务堆积 Buffer Pool 压力 磁盘 I/O CPU 使用率 Redo Log 压力 因此，高并发 MySQL 优化应该从整个访问链路出发，而不是只盯着 SQL。 1. 高并发下 MySQL 为什么容易成为瓶颈 假设一个系统每秒有： 5000 QPS 其中： 80% 查询 20% 写入 那么数据库每秒需要处理： 4000 次查询 1000 次写入 如果单条 SQL 平均执行： 10 ms 看起来并不算慢。 但当大量请求同时进入数据库后，数据库真正面对的是： 大量连接 ↓ 大量 SQL ↓ 大量索引访问 ↓ 大量数据页 ↓ 大量事务 ↓ 锁竞争 ↓ 磁盘 / CPU / 内存压力 因此：...

一个 HTTP 请求的并发链路：从 Web Server 到 MySQL 前面的文章分别讨论了并发是什么、进程线程协程如何执行请求、IO 多路复用如何管理大量连接，以及如何用 QPS、并发数和响应时间衡量系统。 但这些概念真正进入后端系统之后，会形成一条完整的链路： Client ↓ Web Server / Nginx ↓ Application ↓ Process / Thread / Coroutine ↓ Redis / RPC / MySQL ↓ Response ↓ Client 真正的高并发问题，通常也不是发生在某一个组件上，而是发生在整个链路的容量不匹配上。 例如： Web Server 可以接收 10000 个连接，应用可以运行 1000 个协程，但 MySQL 连接池只有 50 个连接。 这并不意味着系统只能处理 50 个 HTTP 请求，但意味着同时进行数据库操作的请求最多受到这 50 个连接的约束。 因此，理解高并发，不能只看 Web Server 或应用服务器，而要沿着一次请求完整地看下去。 1. 一个 HTTP 请求到底经过哪些组件？ 假设用户访问： GET /api/orders/10001 一个典型后端系统可能经过： ┌──────────┐ │ Client │ └────┬─────┘ │ HTTP ▼ ┌──────────────┐ │ Nginx / Web │ │ Server │ └──────┬───────┘ │ ▼ ┌──────────────────┐ │ Application │ │ Hyperf / Java....

MySQL Redo Log、Undo Log 与 Binlog 在学习 MySQL InnoDB 时，经常会遇到三个日志： Redo Log Undo Log Binlog 它们名字相似，但解决的问题完全不同。 简单来说： Redo Log → 数据库崩溃后，恢复已经发生的修改 Undo Log → 事务回滚、MVCC Binlog → 记录数据库逻辑变更，用于复制、恢复等 其中最容易混淆的是： 为什么 InnoDB 已经有 Redo Log 了，MySQL 还需要 Binlog？ 原因在于 Redo Log 和 Binlog 所处的层次、记录内容和设计目标并不相同。 理解三者的关系，也是理解 MySQL 事务提交和主从复制的基础。 1. 为什么数据库需要日志 假设执行： UPDATE users SET balance = balance - 100 WHERE id = 1; 最简单的想法是： 修改内存 ↓ 直接把数据写入磁盘 ↓ 完成 但真实数据库不能简单这样做。 原因是磁盘 I/O 成本较高，而且一次事务可能修改很多数据页。...

MySQL InnoDB 存储引擎详解 InnoDB 是 MySQL 8 默认的存储引擎，也是绝大多数业务系统使用的核心存储引擎。 前面介绍索引、事务、锁和死锁时，很多机制实际上都建立在 InnoDB 的内部结构之上。 例如： 为什么 InnoDB 的索引查询通常很快？ 为什么主键设计会影响二级索引大小？ 为什么更新数据不一定立即写入磁盘？ Buffer Pool 到底有什么作用？ Redo Log 为什么能够提高写入性能？ InnoDB 为什么能够支持事务和崩溃恢复？ 要理解这些问题，需要从 InnoDB 的存储结构开始。 1. InnoDB 是什么 MySQL 本身并不负责所有数据存储工作。 可以简单理解为： MySQL Server ↓ SQL 解析 / 优化 / 执行 ↓ Storage Engine ↓ InnoDB ↓ 数据文件 / 日志文件 存储引擎负责真正的数据存储和访问。 早期 MySQL 中存在多种存储引擎，例如： InnoDB MyISAM Memory Archive 现代 OLTP 系统中，InnoDB 是最重要的选择。 InnoDB 提供了关系型业务系统通常需要的核心能力： 事务 行级锁 MVCC 崩溃恢复...