1 MySQL 与 PostgreSQL 实战要回答什么
数据库实战首先要回答：哪个进程接受连接，数据落在哪个目录，提交成功意味着什么，故障后从哪里恢复，以及刚写入的数据能从哪里读到。MySQL 与 PostgreSQL 都能提供关系表、SQL 和事务，但数据库并没有脱离操作系统独立运行。它们需要 CPU 执行查询，需要内存暂存页面，需要文件系统保存数据和日志，还需要网络连接接收请求。任何环节的等待，都可能表现为客户端的“数据库很慢”。
设想运维平台记录主机维护状态。值班人员把主机 node-a 从 ready 改为 maintenance，随后刷新页面；另有巡检脚本持续读取状态。如果刷新仍显示旧值，要先分辨写事务有没有提交、读取是否去了副本、读取是否仍处于旧事务中。若只看到页面结果就重启数据库，可能中断正在进行的维护操作，却没有消除读旧数据的路径。本课一直使用这类主机状态与巡检记录，讨论真实的进程、文件和网络问题。
在常见 Linux 主机上，MySQL 服务通常涉及 mysqld 进程；PostgreSQL 则有主进程及处理连接、写入日志等工作的子进程。部署包和服务名会有差异，先用进程和监听端口确认实际实例，再查对应服务的配置与日志。下面命令用于只读观察；端口只是常见默认值，不能据此断定数据库身份。
ps -eo pid,ppid,comm,args
ss -lntp 'sport = :3306'
ss -lntp 'sport = :5432'
df -h
df -iss 能帮助确认本机是否监听、绑定哪个地址，但进程详情可能受权限限制。连接失败还要区分名称解析失败、TCP 连接被拒绝、连接超时和认证失败。它们发生在不同位置：认证报错通常说明连接已到达某个数据库服务，却不能证明连到了期望的实例。连接成功以后，应再查询服务器身份和角色，避免把测试库当成生产主库。
目录空间和 inode 都有必要检查。容量看似足够而 inode 耗尽，也可能使日志文件无法创建。数据目录不一定位于根文件系统，容器里看到的路径还可能映射到宿主机挂载卷。应根据实际配置定位目录，再对那个路径执行 df -h /实际目录。不要手工删除看起来很大的数据库文件来释放空间；数据文件、恢复日志和复制日志各有依赖关系。
本课的判断顺序是：确认目标实例和角色，理解提交与复制，再通过可复现实验观察延迟，最后把恢复、连接和一致性要求写进方案。先建立这些边界，就能知道应该继续查 SQL、锁等待、磁盘，还是网络路由，而不是把所有故障归到“数据库性能不好”。
2 存储引擎、WAL 与检查点
MySQL 的存储引擎负责表数据的组织及相关读写行为，本课以常见的 InnoDB 为讨论对象。创建或接管表时，应使用 SHOW TABLE STATUS LIKE 'host_state'; 确认引擎，不能只凭数据库名称推断事务能力。PostgreSQL 的常规表不通过与 MySQL 完全对应的引擎选项来选择事务语义。两者都可以从“内存中修改页面，先记录恢复所需日志，随后逐步把页面写回文件”的方向理解，具体日志格式并不相同。
预写日志的基本约束是：在相关数据页面被持久写回前，先让恢复所需的日志满足持久化要求。PostgreSQL 使用 WAL；InnoDB 使用 redo log 等机制支持崩溃恢复。这里先理解它们帮助数据库重建已提交修改的作用，不展开日志内部结构。MySQL 的 binlog 又承担复制和时间点恢复等用途，不能把 binlog 与 InnoDB redo log 当成同一份文件。
一次更新返回成功时，通常并不要求把所有改动过的数据页面立刻写完。数据库可以先保证所需日志，再让后台工作逐步写回页面。这样能减少提交路径上的工作，但提交的持久性还依赖实际配置以及存储设备对同步写入的承诺。PostgreSQL 的 synchronous_commit、MySQL 的 innodb_flush_log_at_trx_commit 与 sync_binlog 都会影响相关保证，不能仅凭设置名字中的“同步”推断远端副本也已应用事务。
检查点用于推进恢复起点，并协调相关页面的写回。它不是“把尚未提交的事务全部提交”，也不是每执行一次检查点就可以删除所有旧日志。旧日志能否回收，还可能受到复制、归档和备份需求约束。检查点期间磁盘写入增加，可能与查询读取竞争同一存储设备；因此响应变慢不一定意味着查询突然增加了计算量。
-- PostgreSQL：观察当前会话相关设置与实例数据目录
SHOW data_directory;
SHOW synchronous_commit;
SHOW fsync;
-- MySQL：在另一个对应的客户端中执行
SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';这些查询用于记录配置，不要求学员修改参数。生产环境中，降低同步要求可能减少等待，却会改变故障时可能丢失的数据范围；它不应成为遇到慢写入时的默认处理。还要分清数据库进程崩溃、操作系统崩溃和存储设备损坏：仅依靠本机日志无法覆盖所有故障。
观察动画时，把“客户端收到成功”“日志满足本机持久化要求”“数据页面写回”“副本应用完成”看作不同事件。它们之间有约束，但并不总在同一时刻发生。监控若只记录 SQL 耗时，就会遗漏后台写回和日志保留的压力；运维方案应同时关注数据卷空间、日志增长和存储等待。
3 复制、故障切换与读写分离边界
复制把主库发生的修改传到副本。先用异步复制建立直觉：主库提交成功后，复制链路可能还在传输日志，副本也可能已接收但尚未应用。因此“主库写成功”不能自动推出“任意副本立刻读到”。延迟可能来自主库日志产生速度、网络传输、副本磁盘写入、应用速度，也可能受到副本上的长查询影响。
MySQL 常见复制会利用 binlog；PostgreSQL 常见物理流复制传输 WAL。两者都要区分发送、接收和应用位置。接收端保存了日志并不等于查询已经能看到其中的数据。MySQL 半同步复制与 PostgreSQL 同步复制的具体等待目标也不同：确认收到、持久写入和完成应用是不同保证，应查清当前模式及其配置，不能只在架构图上写“同步”就结束讨论。
读写分离也不会自动理解业务语义。适合容忍短暂旧值的容量统计可以读取副本；刚修改维护状态后用于确认是否生效的读取，应走能够满足该一致性要求的路径。最直接的入门方案是让这段操作的读写都去主库，并控制事务边界。若读取仍处在先前建立的事务快照中，即使访问主库，也可能看不到另一事务后来的提交，具体行为取决于隔离级别。
故障切换是另一个边界。主库失联可能只是网络分区，不能据此认定旧主已停止写入。切换前应隔离旧主的写入口，核对候选副本的日志位置及可能丢失范围，再开放新主写入。如果旧主和新主同时接受写入，就可能产生彼此不包含的历史；仅靠之后切回连接地址，无法自动消除这些分歧。
应用连接地址改变后，已经建立的 TCP 连接也不会因此自动迁移。连接池可能继续持有旧主连接，DNS 缓存、代理路由和客户端重试又有各自的更新时间。切换演练必须包含连接重建和角色复核；只确认新主已经启动，不能证明业务已恢复。
另外，连接断开时客户端可能不知道事务是否提交。比如主库已提交，却来不及把成功响应送回。此时直接重试“计数加 1”可能重复执行。维护操作可以携带唯一操作编号，并在库内记录编号与结果，重连后先查询处理状态。它与复制速度无关，却是故障切换后恢复请求时经常遇到的问题。
把目标说具体：可接受丢失最近多少时间的数据，允许中断多久，哪些读取必须看到已确认的修改。对应的 RPO 是可接受的数据损失目标，RTO 是恢复服务的时间目标。主从拓扑只能帮助实现这些目标，不能代替隔离旧主、选择副本和验证连接路径的流程。
4 动手：对照一次主从延迟与读一致性
实验只在独立测试实例进行，前提是已经存在 PostgreSQL 主库与可读副本。准备 PG_PRIMARY、PG_REPLICA 两个连接串，使用受保护的凭据文件或既有认证方式，不把密码放进命令行。用 psql "$PG_PRIMARY" 与 psql "$PG_REPLICA" 分别连接，先在两端执行下面查询，保存实例地址、端口及恢复状态。主库通常返回 false，物理备用库返回 true。
SELECT inet_server_addr(), inet_server_port(), pg_is_in_recovery();在主库创建实验表并提交初始状态。若客户端开启了自动提交，每条语句会独立提交；这里显式书写事务，便于把“更新结束”和“事务提交”区分开。
CREATE TABLE host_state (
host_id text PRIMARY KEY,
status text NOT NULL,
version bigint NOT NULL
);
BEGIN;
INSERT INTO host_state VALUES ('node-a', 'ready', 1);
COMMIT;先确认副本能够查询到版本 1，然后在副本的实验会话执行 SELECT pg_wal_replay_pause();，并用 SELECT pg_is_wal_replay_paused(); 核对状态。这需要适当权限，而且只应暂停独立实验副本。暂停回放后接收仍可能继续，未应用日志会占用空间，因此实验必须短暂进行，并预先准备恢复命令。
在主库执行下面更新。提交后，立即分别在主库和副本查询相同主键；每次查询使用自动提交的新事务，避免把旧快照误认为复制延迟。
BEGIN;
UPDATE host_state
SET status = 'maintenance', version = 2
WHERE host_id = 'node-a';
COMMIT;
SELECT host_id, status, version
FROM host_state WHERE host_id = 'node-a';此时主库应显示版本 2，已暂停回放的副本仍可能显示版本 1。记录结果时同时写下查询来自哪个连接串、事务何时开始、版本号是什么。若观察不到差异，先复核是否真的连到副本、回放是否已暂停，以及查询是否经过代理重新路由。不要通过反复加大负载来“制造”无法解释的延迟。
随后在副本执行 SELECT pg_wal_replay_resume();，确认暂停状态解除，再重复查询直到看到版本 2。即使前面任何步骤失败，也要恢复回放。不要为了清理而直接删除 WAL 文件。实验表若需删除，应在确认恢复后从主库执行，并等待变更传到副本。
可补充查询 pg_last_wal_receive_lsn() 与 pg_last_wal_replay_lsn()，对照接收和回放位置。pg_last_xact_replay_timestamp() 是最近回放事务的时间，空闲时距离当前时间很久并不一定表示存在复制积压。MySQL 对照实验可观察副本状态中的接收、执行状态及错误字段；SHOW REPLICA STATUS 的字段随版本变化，单个秒数不能覆盖复制健康状况。不要把 PostgreSQL 的暂停函数照搬到 MySQL，也不要在生产副本上试停复制。
5 备份恢复与时间点回放取舍
副本与备份解决不同问题。主库上的误删除可能很快复制到副本，因此拥有副本并不等于保留了删除前的数据。备份应提供可恢复的历史基线；时间点恢复则在基线之上重放日志，尽量恢复到误操作发生前。两者都必须实际演练，文件存在、任务退出码为 0，仍不足以证明可以恢复。
逻辑备份把数据库对象和数据导出，便于检查和按对象迁移，但大库导入可能较慢；物理备份保存数据库文件相关内容，恢复速度可能更合适，却更依赖版本、备份方式和配套日志。不能在数据库持续写入时直接复制整个数据目录，就假设获得了一致备份。备份工具需要协调快照或日志，保证文件能够组成可恢复的状态。
# PostgreSQL：连接串沿用受保护的认证方式
pg_dump --dbname="$PG_PRIMARY" --format=custom --file=ops.dump
# MySQL：ops_backup 为事先配置好的登录路径
mysqldump --login-path=ops_backup --single-transaction ops > ops.sql这些命令展示逻辑备份入口，不构成完整灾备流程。pg_dump 面向指定数据库，集群角色等对象需要另行考虑。MySQL 的 --single-transaction 主要面向事务表的一致性导出，备份期间的结构变更及非事务表需要额外安排。shell 重定向可能在工具真正导出前就创建空文件，失败后也可能留下部分输出，因此必须检查退出码和工具错误日志。
时间点恢复需要完整日志链。PostgreSQL 常结合物理基础备份与连续归档 WAL；MySQL 可结合有效备份及其准确对应的 binlog 位置。逻辑导出文件本身不自动包含整条恢复链。若归档漏掉某段日志，恢复就可能停在缺口前；若备份时间早于现存日志的起点，也无法靠“从最新日志开始”补齐过去。
恢复演练应在隔离实例进行，使用独立目录、端口和访问凭据，防止应用误连。先检查备份完整性，再按所选工具的流程恢复和回放，停止在目标事件前，然后核对关键表、操作编号与记录数量。墙上时间适合描述目标，但日志位置、时区和事务提交边界需要一起核实；“恢复到 10:30”必须说明是哪一天、哪个时区以及边界是否包含当时提交的事务。
恢复成功后还要测量从取得备份、拉取日志到完成校验的总耗时。备份在远端、恢复机器磁盘不足、文件属主不正确，都可能使恢复时间远超预期。将恢复记录与备份任务记录一起保存，才能评估 RTO。日志保留越长，越有机会覆盖发现较晚的误操作，但也需要明确容量预算和归档失败告警，不能靠无限保留消除管理责任。
6 常见坑：只扩连接池不看锁等待
假设维护脚本开启事务后更新 node-a，却等待人工确认，迟迟不提交；另一个巡检任务也更新相同行。后者的等待不是因为连接数量不足，而是因为前面的事务仍持有冲突锁。此时扩大连接池，会让更多会话进入同一个等待队列，还增加内存、文件描述符和调度压力。客户端的超时也不代表服务端事务已经结束。
排查先看等待类型，再找阻塞关系。PostgreSQL 可从活动会话中观察事务起点和等待事件，并用阻塞进程列表定位前面的会话。普通账号能看到的信息可能有限，应由有适当权限的运维账号查询。查询文本可能含敏感值，留存时按实际需要脱敏。
SELECT pid, state, xact_start, wait_event_type, wait_event,
pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database();若 blockers 非空，继续核对这些 PID 对应的应用、事务开始时间和操作目的。idle in transaction 表示会话在事务中等待客户端，可能仍持有锁。不要看到事务时间长就直接终止：批处理、备份和维护任务也可能运行较久。需要先判断它是否阻塞关键工作，以及应用能否回滚或重试。
MySQL 可结合 SHOW FULL PROCESSLIST;、SHOW ENGINE INNODB STATUS; 和适用版本中的 performance_schema.data_lock_waits 观察。它们分别提供会话活动、引擎状态和锁等待线索，不能把其中单个字段当成完整结论。终止查询与终止会话的后果不同，回滚也可能耗时；处置后还应验证等待是否消失、业务状态是否完整。
若没有锁等待，就继续看 CPU、存储和网络。vmstat 1 5 可以观察系统整体变化；安装了相应工具时，iostat -xz 1 5 可提供设备读写和等待线索。将它们与数据库指标的时间轴对齐，不能仅凭某个瞬时百分比宣布磁盘故障。客户端连不上而服务端没有对应会话时，则回到地址、端口、访问控制和连接队列等环节。
连接池上限应由数据库能稳定处理的并发、每个事务占用连接的时间及应用实例数量共同约束。每个进程配置 20 个连接，部署 10 个进程就可能形成 200 个连接，还未包含管理和后台任务。把事务缩短、及时结束空闲事务、限制任务并发，往往比单纯加连接更接近问题本身。连接获取超时与 SQL 执行超时应分别记录，才能识别请求到底等在哪里。
7 案例：错误的只读副本承担强一致读
某运维平台把所有 SELECT 都交给只读副本，希望减少主库压力。值班人员将主机设为维护状态后，平台立刻查询状态，决定是否继续发送巡检任务。一次副本延迟期间，更新在主库提交成功，接下来的查询仍读到 ready，于是平台继续调度任务。数据库没有丢掉那次更新，故障发生在路由策略与业务要求之间。
最初日志只记录了“更新成功”和“读到 ready”，团队怀疑事务被回滚。补充证据后发现，更新连接与读取连接来自不同地址；主库版本已经从 7 变为 8，副本还停在 7。副本随后追平到 8，也没有消除已经发出的任务。这说明最终追平不能补救当时依赖旧状态作出的决定。
定位时要记录操作编号、目标主机、事务提交结果、读写端点以及状态版本。数据库端同时检查复制是否报错、是否积压、是否存在阻碍回放的查询。时间戳有助于关联日志，但跨主机时钟偏差可能误导排序；显式版本号和事务位置能提供更直接的证据。确认路径后，再检查主库上的读取是否仍处于旧快照。
修复可以先把维护操作的提交和确认读取固定到主库，并在提交后开启新的读取事务。面向趋势展示的统计仍可读取副本，但必须允许短暂旧值。若业务选择等待副本追到某个提交位置，需要明确数据库版本支持的等待方法、最大等待时间和失败后的处理；等待超时后继续使用旧值，仍然违反了原来的要求。
此外，先读出 ready 再发送任务，本身也存在检查后状态改变的竞态。若“进入维护后不得领取新任务”是硬约束，应把任务领取资格的检查与领取记录放在主库的适当事务中，用条件更新或必要的锁协调并发。仅把查询移回主库，不能让分开的操作自动成为原子动作。已经领取并运行的任务如何停止，也需要业务流程另行定义。
修复后的验证要覆盖副本故意延迟、主库连接失败、确认读取超时等情况。严格依赖维护状态的操作，在无法确认时应进入明确的等待或失败流程，不能静默回落到副本。监控中增加读写端点和版本信息，才能在下次事件中快速区分复制延迟、事务快照与业务竞态。这个案例的教训是把每个读取的承诺写清楚，而不是给整个系统贴上“强一致”标签。
8 把引擎与复制拓扑写进库表方案
库表方案除字段和索引外，还应描述承载它的引擎、事务边界和副本用途。对主机状态表，要说明 MySQL 是否使用 InnoDB，或 PostgreSQL 采用何种常规表与部署方式；对维护动作，要说明哪些语句属于同一个事务，成功后从哪里确认结果。只写“使用关系数据库”无法指导故障时的读写路由。
可以给表按访问要求分类。主机当前状态承担调度决策，需要及时确认已提交修改；巡检历史主要追加，查询通常允许一定延迟；容量趋势按时间窗口汇总，可接受更长的新鲜度间隔。分类的依据是业务读取要求，不是 SQL 是否以 SELECT 开头。同一张表也可能同时服务严格确认与趋势展示，应分别定义接口行为。
拓扑图应标出写入口、读入口、主副角色和复制方向，并说明自动故障切换由哪个组件负责。同步策略要写出等待目标及副本不可用时的行为：等待会影响可用性，放宽等待则可能影响故障时的数据保证。不要把“有副本”直接转换成“无数据损失”。连接池如何清理旧连接、应用如何识别角色变化，同样属于拓扑方案的一部分。
滑块帮助比较副本应用速度下降时的积压和等待。它只计算给定速率下的简化队列，不预测某个数据库版本的真实延迟。实际系统还有事务大小、查询冲突、磁盘抖动和网络波动等因素。读旧值容忍时间改变的是业务接受范围，并不会让副本更快；同步等待范围改变提交路径，也不能替代故障隔离。
容量部分至少列出数据、恢复日志、复制日志、归档和备份的存放位置及保留条件。数据库数据文件和归档若都位于同一块故障磁盘上，就缺少独立恢复来源。空间告警应为排查与扩容留出时间，还应识别日志因副本断连或归档失败而持续保留的情况，不能只观察表数据增长。
方案还应给出可执行的验收：在测试环境提交带版本号的状态变更，分别从各入口读取；暂停副本回放后验证关键读取仍符合约定；恢复副本并确认追平；从备份恢复到指定误操作前，再检查关键记录。每项验收都要有可观察结果和失败处置，避免“数据库高可用”成为无法验证的形容词。
最后写清运维责任。谁有权切换主库，谁负责隔离旧主，谁确认恢复结果，谁处理长期事务和归档积压，都需要可找到的角色。入门阶段不必展开复杂选举或存储内部实现，但必须知道自动化只执行预设规则。若规则没有覆盖网络分区、连接残留和数据落后，自动执行也可能把局部故障扩大。
9 从MySQL/PostgreSQL 实战走向NoSQL 与缓存（S43）
本课建立了几组可以迁移的边界：提交与页面写回不同，日志接收与应用不同，副本与备份不同，连接数与有效并发不同。进入 NoSQL 与缓存之前，先保留这些边界。换一种存储接口，不会使进程、网络、文件系统和故障恢复消失，也不会让刚写入的数据自然出现在所有读取位置。
考虑把主机状态放入缓存。若数据库更新成功后缓存仍保留 ready，页面读到旧状态的表现，与副本滞后有相似之处，但原因和处理路径不同。副本应用日志追赶主库，缓存通常需要失效、刷新或等待过期。不能直接拿数据库复制延迟指标解释缓存新鲜度，也不能以“缓存命中率高”证明维护状态正确。
准备下一课时，可以先整理访问模式：按主机编号查询当前状态、追加巡检事件、按时间扫描历史、汇总容量趋势。不同模式对查询能力、事务和新鲜度的要求不同。选择其他存储时，要说明哪份记录是权威来源、哪些结果可以重建、缓存丢失后如何回源，以及回源流量是否会压垮数据库。此处先提出问题，具体缓存策略留到 S43。
还要区分性能优化与语义改变。减少重复查询可能降低主库负载，却需要处理缓存过期和更新竞争；把历史查询迁往副本，可能保留事务写入方式，却引入读取延迟。每次改动都应明确获得了什么、承担了什么，并保留能观察故障的接口。没有数据来源和版本信息，多个存储叠加后只会让旧值更难定位。
在进入下一课前，尝试口头解释实验中的现象：为什么主库提交后副本仍显示版本 1；为什么恢复回放后副本会追平；为什么扩大连接池无法解除另一个事务持有的锁；为什么误删除后的副本通常不是可直接使用的历史备份。能够把答案连接到进程、日志、事务和读取路径，就已具备继续学习所需的基本判断力。
对于还没有可靠备份和监控的小系统，先把恢复演练、角色识别和连接边界做好，往往比立即增加存储组件更有收益。对于已有明确吞吐瓶颈的系统，则用测量结果选择下一步：是缩短事务、调整查询、分担可延迟读取，还是引入缓存。S43 将继续围绕这些访问需求讨论 NoSQL 与缓存，而不会把它们当成替换关系数据库的统一答案。
放进方案里
在运维平台方案中增加“数据库运行与恢复约定”。主机状态保存在 PostgreSQL 主库，维护动作使用显式事务并写入唯一操作编号和递增版本；提交后的确认读取访问主库并使用新事务。允许延迟的巡检趋势查询访问只读副本，界面展示统计生成时间。涉及能否领取任务的判断与领取记录在主库事务中协调，无法确认状态时进入等待或失败流程。
拓扑列出主库、副本、连接入口和复制方向，记录当前同步策略及其等待目标。故障切换先隔离旧主，再核对候选副本位置和可能损失的数据范围，最后更新入口、重建连接并验证角色。若采用异步复制，不承诺副本立即具备所有已确认写入；切换后的请求重试先根据操作编号查询结果，避免重复执行。
备份约定分别列出基础备份、连续日志归档与异地保存位置，写明保留期限、空间告警及归档失败处理人。恢复演练使用隔离实例，恢复到指定维护误操作前，核对状态版本和操作记录，并记录完整耗时。RPO 与 RTO 由业务确认，演练结果用于判断现有方案是否达标，不能只以备份文件存在作为验收。
观测至少覆盖连接获取等待、活动事务、锁阻塞、复制接收与应用位置、数据卷剩余空间和日志增长。出现超时时，先关联应用操作编号与数据库会话，再区分网络、锁和存储等待。扩大连接池必须同时核算全部应用实例的连接总量，并确认数据库仍有处理余量。
上线验收在测试环境制造短暂副本回放暂停，确认维护动作不会读取副本旧值，恢复后确认追平；再演练连接重建和备份恢复。将证据、操作者与处置结果归档。如果选择 MySQL，则明确 InnoDB 表、binlog 保留及相应复制状态接口，用同样的业务要求重新验证，不照搬 PostgreSQL 命令。
10 小结：下一步NoSQL 与缓存（S43）
MySQL/PostgreSQL 实战的起点，是把每次请求放回具体的进程、事务、日志、网络和文件系统中解释。提交成功、复制追平和恢复可用各有证据；慢请求也需要先区分连接等待、锁等待与存储等待。
自测：副本已收到日志却尚未应用时，能否确认维护状态？主库连接断开后，如何避免重复执行未知结果的操作？备份任务成功后，还需要怎样证明恢复能力？下一步进入 NoSQL 与缓存（S43），继续用状态来源、新鲜度和故障后可恢复性评价新的存储路径。