运维笔记：数据库管理

一、数据库概述

1.1 数据库类型与特点

• 关系型数据库（RDBMS）：采用关系模型（二维表格）存储数据，强调数据的一致性和完整性，支持 SQL 查询。

• • 代表产品：MySQL、PostgreSQL、Oracle、SQL Server。

• • 适用场景：需要事务支持（如金融交易）、复杂查询（多表关联）、数据一致性要求高的业务（如订单系统）。

• 非关系型数据库（NoSQL）：不依赖关系模型，数据结构灵活（键值、文档、列族、图形），扩展性好。

• • 代表产品：

• • • 键值型：Redis、Memcached（适合缓存、会话存储）。

• • • 文档型：MongoDB、CouchDB（适合存储非结构化 / 半结构化数据，如日志、用户画像）。

• • • 列族型：HBase、Cassandra（适合大数据量写入和按列查询，如时序数据）。

• • • 图形型：Neo4j（适合存储关系型数据，如社交网络好友关系）。

• • 适用场景：高并发读写（如秒杀活动）、海量数据存储（如物联网日志）、灵活 schema 需求（如电商商品属性）。

1.2 数据库核心概念

• ACID 特性（关系型数据库）：

• • 原子性（Atomicity）：事务要么全部完成，要么全部失败，无中间状态（如转账时 “扣钱” 和 “加钱” 要么都成功，要么都回滚）。

• • 一致性（Consistency）：事务执行前后，数据需满足预设规则（如账户余额不能为负）。

• • 隔离性（Isolation）：多个事务并发执行时，彼此不受干扰，隔离级别包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable），级别越高一致性越好，但性能越低。

• • 持久性（Durability）：事务提交后，数据变更永久保存（如写入磁盘），即使系统崩溃也不丢失。

• CAP 定理（分布式数据库）：

• • 一致性（Consistency）：所有节点同时看到相同的数据。

• • 可用性（Availability）：节点故障时，其他节点仍能响应请求。

• • 分区容错性（Partition Tolerance）：网络分区时，系统仍能运行。

• • 定理：分布式系统只能同时满足其中两项，多数分布式数据库优先保证 AP（可用性 + 分区容错）或 CP（一致性 + 分区容错）。

二、关系型数据库管理

2.1 MySQL 深度管理

• 架构与存储引擎：

• • 架构：客户端连接层（连接器）、核心服务层（查询缓存、分析器、优化器、执行器）、存储引擎层（InnoDB、MyISAM 等）。

• • 存储引擎特性：

• • • InnoDB：支持事务、行级锁、外键，适合读写频繁的 OLTP 场景，MySQL 5.5 + 默认引擎。

• • • MyISAM：不支持事务和行锁，支持全文索引，适合读多写少的场景（如博客文章）。

• • 查看 / 修改存储引擎：SHOW TABLE STATUS LIKE 'table_name';；ALTER TABLE table_name ENGINE=InnoDB;。

• 用户与权限管理：

• • 创建用户：CREATE USER 'user'@'192.168.1.%' IDENTIFIED BY 'password';（允许 192.168.1 网段登录）。

• • 授权：GRANT SELECT, INSERT ON db_name.* TO 'user'@'192.168.1.%';（授予 db_name 库的查询和插入权限）。

• • 回收权限：REVOKE INSERT ON db_name.* FROM 'user'@'192.168.1.%';。

• • 查看权限：SHOW GRANTS FOR 'user'@'192.168.1.%';。

• • 删除用户：DROP USER 'user'@'192.168.1.%';。

• 事务与锁机制：

• • 事务控制：BEGIN;（开始事务）、COMMIT;（提交）、ROLLBACK;（回滚）。

• • 锁类型：

• • • 行锁：InnoDB 通过索引实现行锁，仅锁定匹配的行（如UPDATE users SET age=20 WHERE id=1锁定 id=1 的行），无索引时降级为表锁。

• • • 表锁：MyISAM 默认表锁，操作时锁定整个表，并发性能差。

• • • 间隙锁：InnoDB 在可重复读级别下，锁定索引范围（如WHERE id BETWEEN 1 AND 10锁定 1-10 的间隙），防止幻读。

• • 查看锁状态：SHOW OPEN TABLES WHERE In_use > 0;（表锁）；SELECT * FROM information_schema.innodb_locks;（行锁）。

• 备份与恢复：

• • 物理备份：

• • • xtrabackup（Percona）：支持热备份（不锁表），innobackupex --user=root --password=xxx /backup（全量备份）；innobackupex --user=root --password=xxx --incremental /backup/incr --incremental-basedir=/backup/full（增量备份）。

• • • 恢复：innobackupex --apply-log /backup/full（准备备份）；innobackupex --copy-back /backup/full（恢复到数据目录）。

• • 逻辑备份：

• • • mysqldump：mysqldump -u root -p --databases db1 db2 > backup.sql（备份多个库）；mysqldump -u root -p --single-transaction --master-data=2 db1 > backup.sql（InnoDB 一致性备份，记录 binlog 位置）。

• • • 恢复：mysql -u root -p < backup.sql。

• • binlog 日志恢复：通过 binlog 恢复到指定时间点，mysqlbinlog --start-datetime="2023-10-01 08:00:00" --stop-datetime="2023-10-01 09:00:00" /var/log/mysql/binlog.000001 | mysql -u root -p。

2.2 PostgreSQL 管理

• 特色功能：

• • 高级数据类型：数组（int[]）、JSON/JSONB（适合存储半结构化数据）、地理信息（GIS）、枚举（ENUM）。

• • 强大索引：B-tree、Hash、GiST（空间索引）、GIN（JSONB / 数组索引）、BRIN（时序数据索引）。

• • 事务与 MVCC：通过多版本并发控制（MVCC）实现高并发，读不阻塞写，写不阻塞读。

• 常用操作：

• • 创建数据库：CREATE DATABASE dbname OWNER username;。

• • 创建表：

CREATE TABLE users (
  id SERIAL PRIMARY KEY,  -- 自增主键
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• • 备份与恢复：pg_dump -U username dbname > backup.sql（备份）；psql -U username -d dbname -f backup.sql（恢复）。

• • 查看连接：SELECT * FROM pg_stat_activity;（类似 MySQL 的SHOW PROCESSLIST）。

• • 终止连接：SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'dbname';。

2.3 关系型数据库性能优化

• 索引优化：

• • 适合建索引的场景：WHERE 子句过滤条件（WHERE id=1）、JOIN 关联字段（JOIN orders ON users.id=orders.user_id）、ORDER BY 排序字段（ORDER BY created_at）。

• • 避免过度索引：索引会减慢写入（INSERT/UPDATE/DELETE），因为需维护索引结构，建议单个表索引不超过 5 个。

• • 复合索引顺序：遵循 “最左前缀原则”，如(a, b, c)索引，能优化WHERE a=1、WHERE a=1 AND b=2，但无法优化WHERE b=2。

• • 索引失效场景：使用函数（WHERE SUBSTR(name, 1, 1)='A'）、隐式类型转换（WHERE id='1'，字符串转数字）、NOT IN、!=、LIKE '%abc'（前缀模糊匹配）。

• SQL 语句优化：

• • 避免SELECT *：只查询需要的字段，减少数据传输和内存占用。

• • 优化 JOIN：小表驱动大表（SELECT * FROM small JOIN large ON ...），避免笛卡尔积（未加 ON 条件的 JOIN）。

• • 批量操作：用INSERT INTO users (name) VALUES ('a'), ('b'), ('c')替代多条单条 INSERT，减少网络往返。

• • 分页优化：大 offset 分页（LIMIT 100000, 10）效率低，改用游标或WHERE id > last_id LIMIT 10（基于主键分页）。

• 数据库配置优化：

• • MySQL：innodb_buffer_pool_size（建议设为物理内存的 50%-70%，缓存数据和索引）；innodb_flush_log_at_trx_commit=1（事务 ACID 安全，性能稍低，可设为 2 提升性能但有数据丢失风险）；max_connections（最大连接数，避免连接耗尽）。

• • PostgreSQL：shared_buffers（建议物理内存的 25%）；work_mem（每个排序 / 哈希操作的内存，大查询需调大）；maintenance_work_mem（索引创建 / 真空操作的内存）。

三、非关系型数据库管理

3.1 Redis 深度管理

• 数据结构与应用场景：

• • String（字符串）：缓存（SET user:1 "{}" EX 3600）、计数器（INCR view:100）、分布式锁（SET lock:order NX EX 10）。

• • Hash（哈希）：存储对象属性（HSET user:1 name "Alice" age 20），适合用户信息、商品属性。

• • List（列表）：消息队列（LPUSH queue msg1，RPOP queue）、最新列表（LPUSH news:list "news1", LTRIM news:list 0 99保留最新 100 条）。

• • Set（集合）：去重（SADD tags "java" "python"）、交集 / 并集（SINTER group:1 group:2找共同好友）。

• • Sorted Set（有序集合）：排行榜（ZADD rank:score 100 "user1" 200 "user2"，ZRANGE rank:score 0 9 WITHSCORES取前 10）、延迟队列（ZADD delay:queue 1633065600 "task1"，按时间戳排序）。

• 持久化机制：

• • RDB（Redis Database）：定时快照（如save 3600 1，3600 秒内有 1 次写入则触发），文件小，恢复快，但可能丢失最后一次快照后的数分钟数据。

• • AOF（Append Only File）：记录所有写命令（如SET、HSET），每秒或每次命令同步到磁盘，数据安全性高，但文件大，恢复慢。

• • 推荐配置：开启 AOF+RDB 混合模式（aof-use-rdb-preamble yes），AOF 重写时先做 RDB 快照，再追加增量命令。

• 高可用与集群：

• • 主从复制：1 主 N 从，主节点写入，从节点同步数据并提供读服务，SLAVEOF master_ip master_port配置从节点。

• • 哨兵（Sentinel）：监控主从节点，主节点故障时自动将从节点晋升为主节点，sentinel monitor mymaster 127.0.0.1 6379 2（至少 2 个哨兵同意才触发故障转移）。

• • 集群（Cluster）：分片存储数据（默认 16384 个哈希槽），支持水平扩展（最多 1000 个节点），redis-cli --cluster create ip1:6379 ip2:6379 ...创建集群。

• 性能优化：

• • 内存管理：maxmemory限制最大内存，maxmemory-policy设置内存满时的淘汰策略（如allkeys-lru：淘汰最近最少使用的键）。

• • 避免大键：大 List/Hash 会导致删除 / 序列化阻塞主线程，建议拆分（如user:1:favorites:1、user:1:favorites:2）。

• • 批量操作：用Pipeline减少网络往返（如 Python 的pipe = r.pipeline(); pipe.set('a', 1); pipe.get('a'); pipe.execute()）。

3.2 MongoDB 管理

• 文档模型：数据以 BSON（二进制 JSON）格式存储，支持嵌套文档和数组，示例：

{
  "_id": ObjectId("5f8d0d55b669a2a2d3e2c1a1"),
  "name": "iPhone 13",
  "price": 5999,
  "specs": {
    "storage": "128GB",
    "color": "black"
  },
  "tags": ["phone", "apple"],
  "created_at": ISODate("2023-10-01T08:00:00Z")
}

• 索引与查询：

• • 单字段索引：db.products.createIndex({ "name": 1 })（1 升序，-1 降序）。

• • 复合索引：db.orders.createIndex({ "user_id": 1, "created_at": -1 })。

• • 地理空间索引：db.stores.createIndex({ "location": "2dsphere" })，查询附近商店：db.stores.find({ "location": { $near: { $geometry: { type: "Point", coordinates: [116.4, 39.9] }, $maxDistance: 1000 } })。

• • 聚合查询：db.orders.aggregate([

{ $match: { "status": "paid" } },

{ $group: { _id: "$user_id", total: { $sum: "$amount" } } },

{ $sort: { total: -1 } },

{ $limit: 10 }

])（查询付费用户的总消费 Top10）。

• 高可用与分片：

• • 副本集（Replica Set）：1 主 2 从（默认），主节点故障时自动选举新主，rs.initiate()初始化副本集，rs.add("mongodb2:27017")添加从节点。

• • 分片集群：数据量大时拆分到多个分片，通过 mongos 路由查询，配置服务器存储元数据，sh.enableSharding("dbname")启用分片，sh.shardCollection("dbname.collection", { "user_id": 1 })按 user_id 分片。

3.3 非关系型数据库性能优化

• Redis 优化：

• • 合理设置过期时间：避免内存溢出，EXPIRE key 3600，批量删除用UNLINK（异步删除）替代DEL（同步阻塞）。

• • 选择合适的数据结构：如用 Hash 存储用户属性，比多个 String 节省内存；用 Bitmap 存储签到（SETBIT sign:user1 1 1表示第 1 天签到）。

• • 启用持久化但平衡性能：AOF 设为everysec（每秒同步），避免always（每次命令同步）的性能损耗。

• MongoDB 优化：

• • 索引覆盖查询：db.products.find({ "price": { $lt: 100 } }, { "name": 1, "price": 1, "_id": 0 })，查询字段均在索引中，无需访问文档。

• • 避免全表扫描：确保查询条件有索引支持，用explain("executionStats")分析查询计划，查看executionStats.totalDocsExamined是否等于nReturned（全表扫描时前者远大于后者）。

• • 分片键选择：避免单调递增键（如_id）导致写入集中在单个分片，选择基数高、分布均匀的字段（如user_id）。

四、数据库高可用与灾备

4.1 主从复制与读写分离

• MySQL 主从复制：

• • 原理：主库将数据变更写入 binlog，从库通过 IO 线程读取 binlog 到 relay log，SQL 线程执行 relay log 中的命令，实现数据同步。

• • 配置步骤：

1. 1. 主库：启用 binlog（log_bin=mysql-bin，server-id=1），创建复制用户（GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip' IDENTIFIED BY 'password';），SHOW MASTER STATUS;记录 binlog 文件名和位置。
2. 1. 从库：server-id=2，CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=123;，START SLAVE;，SHOW SLAVE STATUS\G检查Slave_IO_Running和Slave_SQL_Running是否为 Yes。

• • 读写分离：通过中间件（如 MyCat、ProxySQL）或应用层实现，写操作走主库，读操作走从库，分摊主库压力。
• PostgreSQL 流复制：

• • 主库配置：wal_level=replica，max_wal_senders=5（最多 5 个从库连接），创建复制用户（CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'password';）。

• • 从库配置：pg_basebackup -h master_ip -U repl -D /data/pgdata -P（克隆主库数据），创建recovery.conf：standby_mode=on，primary_conninfo='host=master_ip user=repl password=password'，启动从库。

4.2 集群与分布式架构

• MySQL 集群方案：

• • MGR（MySQL Group Replication）：多主模式（任意节点可写）或单主模式，自动故障检测和恢复，支持强一致性，适合中小规模集群。

• • Percona XtraDB Cluster（PXC）：基于 Galera Cluster，同步复制（写操作在所有节点成功才返回），无数据丢失，适合对一致性要求高的场景。

• PostgreSQL 集群：

• • Patroni：结合 etcd/consul 实现自动故障转移，支持主从切换、配置管理，适合云环境。

• • Citus：将 PostgreSQL 扩展为分布式数据库，支持水平扩展，保留 SQL 兼容性，适合 OLAP 和大表查询。

• 分布式数据库：

• • TiDB：兼容 MySQL 协议，支持水平扩展、强一致性，适合混合负载（OLTP+OLAP）。

• • CockroachDB：兼容 PostgreSQL，自动分片和副本，支持全球分布式部署，适合多区域业务。

4.3 灾备策略与实践

• 灾备等级：

• • RPO（恢复点目标）：灾难发生后，允许丢失的数据量（如 15 分钟内的数据）。

• • RTO（恢复时间目标）：灾难发生后，系统恢复的时间（如 4 小时）。

• 灾备方案：

• • 本地备份：每日全量 + 增量备份，存储在本地磁盘或 NAS，应对数据误删。

• • 异地备份：将备份文件复制到异地（如跨城市数据中心），通过scp或对象存储（S3）同步，应对区域性灾难。

• • 热备集群：异地部署从库，实时同步数据（如 MySQL 主从跨区域复制），RPO 接近 0，灾难发生时可快速切换到异地集群。

• 恢复演练：

• • 定期测试：每季度进行恢复演练，验证备份有效性（如将备份恢复到测试环境，检查数据完整性）。

• • 文档化流程：编写详细的恢复步骤（如 “主库宕机后如何将从库提升为主库”），包含命令和责任人，避免操作失误。

五、数据库安全与合规

5.1 数据库访问控制

• 身份认证：

• • 强密码策略：长度≥12 位，包含大小写字母、数字和特殊字符，定期更换（如 90 天）。

• • 多因素认证：数据库管理员登录需额外验证（如短信验证码、硬件 Key），MySQL 企业版、PostgreSQL 可通过插件实现。

• • 限制登录来源：MySQL 通过'user'@'192.168.1.%'限制 IP 段；PostgreSQL 在pg_hba.conf中配置host all all 192.168.1.0/24 md5。

• 权限管理：

• • 最小权限原则：应用账号仅授予SELECT、INSERT等必要权限，禁止DROP、ALTER；管理员账号专人专用，避免共享。

• • 定期审计权限：SELECT user, host, db, command FROM mysql.db;（MySQL）；SELECT grantee, privilege_type FROM information_schema.role_table_grants;（PostgreSQL），回收无用权限。

• • 临时权限：通过工具（如 CyberArk）授予临时权限，操作完成后自动回收，避免权限长期有效。

5.2 数据加密与脱敏

• 传输加密：

• • 启用 SSL/TLS：MySQL 配置ssl-ca=ca.pem，ssl-cert=server.pem，ssl-key=server-key.pem，客户端连接时指定--ssl-mode=REQUIRED；PostgreSQL 配置ssl=on，ssl_cert_file=server.crt。

• • 应用层加密：敏感字段（如手机号）在应用层加密后存储，数据库中仅存密文，避免 DBA 直接查看。

• 存储加密：

• • 透明数据加密（TDE）：MySQL 企业版、SQL Server 支持，加密数据库文件，应用无需修改代码。

• • 字段级加密：对核心字段（如银行卡号）单独加密，MySQL 用AES_ENCRYPT('data', 'key')，PostgreSQL 用pgcrypto扩展。

• 数据脱敏：

• • 生产环境脱敏查询：SELECT id, CONCAT(SUBSTR(name, 1, 1), '***') AS name FROM users;（姓名脱敏）。

• • 测试环境脱敏：通过工具（如 Oracle Data Masking）替换敏感数据，保留格式但隐藏真实值（如手机号13800138000→138****8000）。

5.3 数据库审计与合规

• 审计日志：

• • MySQL：启用审计插件（如 Percona Audit Log），记录登录、DDL（CREATE/DROP）、DML（INSERT/UPDATE）操作，日志包含用户、时间、SQL 语句。

• • PostgreSQL：通过pgAudit扩展，配置shared_preload_libraries = 'pgaudit'，pgaudit.log = 'ddl, write'记录 DDL 和写操作。

• 合规检查：

• • 等保 2.0：数据库需满足 “审计日志留存 180 天”“敏感数据加密”“定期权限审计”。

• • PCI DSS：禁止存储 CVV，卡号需加密，所有访问银行卡数据的操作需记录日志。

• • GDPR：用户有权查询其数据被访问的记录，审计日志需支持按用户 ID 检索。

• 审计工具：

• • 开源工具：Apache Atlas（数据血缘和审计）、ELK Stack（日志集中分析）。

• • 商业工具：IBM Guardium、Oracle Audit Vault，支持实时监控、异常行为检测、合规报告生成。

六、数据库监控与运维工具

6.1 监控指标与工具

• 核心监控指标：

• • 可用性：uptime（运行时间）、status（主从状态）。

• • 性能：QPS（每秒查询数）、TPS（每秒事务数）、慢查询数、连接数（Threads_connected）、锁等待数（Innodb_row_lock_waits）。

• • 资源：CPU 使用率、内存使用率（innodb_buffer_pool_used_pages）、磁盘 I/O（Bytes_sent/Bytes_received）、磁盘空间（data_free）。

• 监控工具：

• • 开源工具：

• • • Prometheus + Exporter：mysqld_exporter（MySQL）、postgres_exporter（PostgreSQL）、redis_exporter（Redis），结合 Grafana 创建仪表盘。

• • • Percona Monitoring and Management（PMM）：专为 MySQL、PostgreSQL 设计，包含预定义仪表盘和性能分析工具。

• • 商业工具：Datadog、New Relic，支持多数据库监控、异常检测、自动告警。

6.2 慢查询分析

• MySQL 慢查询配置：

• • 启用：slow_query_log=1，slow_query_log_file=/var/log/mysql/slow.log，long_query_time=1（记录执行时间≥1 秒的查询），log_queries_not_using_indexes=1（记录未用索引的查询）。

• • 分析工具：mysqldumpslow -s t -t 10 /var/log/mysql/slow.log（按时间排序，取前 10 条）；Percona Toolkit 的pt-query-digest（更详细的分析，含执行计划）。

• PostgreSQL 慢查询配置：

• • 启用：log_min_duration_statement=1000（记录≥1000 毫秒的查询），log_statement='ddl'（记录 DDL）。

• • 分析工具：pgBadger（生成 HTML 报告，含慢查询趋势、最耗时 SQL）。

6.3 运维自动化工具

• 数据库迁移工具：

• • MySQL→PostgreSQL：pgLoader（pgloader mysql://user:pass@host/db postgresql://user:pass@host/db）。

• • 异构迁移：Apache NiFi、Talend，支持多数据源之间的数据同步和转换。

• 自动化运维平台：

• • Ansible：通过 Playbook 批量部署数据库、执行备份、修改配置，示例：

- name: 部署MySQL
  hosts: db_servers
  tasks:
    - name: 安装MySQL
      yum:
        name: mysql-server
        state: present
    - name: 启动服务
      service:
        name: mysqld
        state: started
        enabled: yes

• • 阿里云 DMS、腾讯云 CDB：云数据库管理平台，支持 SQL 窗口、备份恢复、性能诊断、权限管理，适合云环境运维。

七、数据库迁移与升级

7.1 数据库版本升级

• MySQL 升级策略：

• • 小版本升级（如 5.7.30→5.7.40）：直接替换二进制文件，重启服务即可，无需修改数据文件，升级前需备份/etc/my.cnf和数据目录。

• • 大版本升级（如 5.7→8.0）：

1. 全量备份数据（mysqldump或xtrabackup），防止升级失败。
2. 检查不兼容项：使用mysqlupgrade --check-for-server-upgrade检测 deprecated 功能（如查询缓存在 8.0 移除），修改应用代码。
3. 逻辑升级：通过mysqldump导出数据，在 8.0 实例导入（适合中小数据库）。
4. 物理升级：使用mysqlsh的upgrade命令（mysqlsh root@localhost:3306 --upgrade=auto），直接升级数据文件（适合大型数据库）。

• • 验证：升级后检查SHOW GLOBAL STATUS LIKE 'Threads_connected';确认服务正常，运行应用测试用例。
• PostgreSQL 升级：
• • pg_upgrade 工具：支持跨大版本升级（如 11→16），保留数据文件结构，步骤：

1. 安装新版本 PostgreSQL，初始化新集群（initdb -D /data/pg16）。
2. 执行升级：pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/16/bin -d /data/pg11 -D /data/pg16。
3. 升级完成后，检查pg_lsclusters确认新集群运行，删除旧集群文件。

• • 逻辑备份迁移：pg_dumpall导出全库，在新实例导入（psql -f backup.sql postgres），适合数据量小或需要重构表结构的场景。

7.2 异构数据库迁移

• MySQL→PostgreSQL 迁移：

• • 工具选择：pgLoader（自动化程度高）、定制脚本（复杂场景）。

• • 关键差异处理：

• • • 数据类型映射：MySQL 的INT AUTO_INCREMENT→PostgreSQL 的SERIAL或IDENTITY；VARCHAR(255)→VARCHAR(255)；DATETIME→TIMESTAMPTZ（带时区）。

• • • SQL 语法调整：MySQL 的INSERT ... ON DUPLICATE KEY UPDATE→PostgreSQL 的INSERT ... ON CONFLICT ... DO UPDATE；LIMIT/OFFSET语法兼容，但需注意FOR UPDATE锁机制差异。

• • 示例（pgLoader 配置）：

LOAD DATABASE
  FROM mysql://user:pass@mysql-host/dbname
  INTO postgresql://user:pass@pg-host/dbname
  WITH include drop, create tables, create indexes, reset sequences
  SET work_mem to '16MB', maintenance_work_mem to '512MB';

• Oracle→MySQL 迁移：

• • 工具：Oracle SQL Developer（支持迁移向导）、AWS DMS（云环境）。

• • 难点处理：

• • • 存储过程转换：Oracle 的 PL/SQL 与 MySQL 的 Stored Procedure 语法差异大，需重写（如DBMS_OUTPUT→SELECT输出）。

• • • 序列与自增：Oracle 的SEQUENCE→MySQL 的AUTO_INCREMENT，需调整应用获取 ID 的方式。

• • • 分区表：Oracle 的范围分区→MySQL 的PARTITION BY RANGE，语法类似但功能有限制（如不支持自动列表分区）。

7.3 云数据库迁移

• 本地→云数据库迁移：

• • AWS RDS 迁移：

1. 使用 AWS DMS（Database Migration Service）创建 replication instance，配置源（本地 MySQL）和目标（RDS MySQL）端点。
2. 创建迁移任务，选择 “全量 + CDC”（Change Data Capture），确保迁移期间数据同步。
3. 切换应用连接到 RDS，验证数据一致性（checksum对比关键表）。

• • 阿里云 RDS 迁移：
• • • 使用 DTS（数据传输服务），支持同构 / 异构数据库迁移，提供数据校验和增量同步，适合零停机迁移。
• 跨云厂商迁移：

• • 策略：先将数据迁移到中间存储（如 S3 兼容对象存储），再导入目标云数据库，或通过 VPN 建立专线同步。

• • 示例（Azure SQL→阿里云 RDS for SQL Server）：

1. Azure SQL 导出 BACPAC 文件到 Blob 存储。
2. 下载 BACPAC 到本地，通过 SQL Server Management Studio 导入到阿里云 RDS。
3. 启用事务日志同步，确保增量数据一致后切换应用。

八、数据库故障排查与恢复

8.1 常见故障类型与处理

• MySQL 故障：

• • 无法启动：

• • • 检查错误日志（/var/log/mysql/error.log），常见原因：

• • • • 数据目录权限错误（chown -R mysql:mysql /var/lib/mysql）。

• • • • 端口被占用（netstat -tulpn | grep 3306，kill 占用进程）。

• • • • 数据文件损坏：使用innodb_force_recovery=1（配置文件中）启动，导出数据后重建实例。

• • 主从延迟：

• • • 查看延迟：Seconds_Behind_Master（SHOW SLAVE STATUS\G）。

• • • 处理：

• • • • 网络问题：检查主从网络带宽，优化sync_binlog=1（主库）为sync_binlog=100（牺牲部分安全性提升性能）。

• • • • 大事务：拆分长事务（如批量更新改为分批），从库配置slave_parallel_workers=4（并行复制）。

• • 死锁：

• • • 查看死锁日志：SHOW ENGINE INNODB STATUS\G，找到LATEST DETECTED DEADLOCK部分。

• • • 解决：调整事务顺序（如都按主键顺序更新），减少事务持有锁的时间。

• PostgreSQL 故障：

• • 连接数耗尽：

• • • 临时处理：SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND now() - query_start > interval '5 minutes';（终止空闲连接）。

• • • 长期解决：调大max_connections（需同步调整shared_buffers），使用连接池（如 PgBouncer）。

• • WAL 日志满：

• • • 原因：从库延迟导致主库 WAL 无法删除，或磁盘空间不足。

• • • 处理：修复从库同步，删除旧 WAL（pg_archivecleanup），扩展磁盘。

8.2 数据损坏与恢复

• InnoDB 数据页损坏：

1. 启用innodb_force_recovery=6（最高级别，只读），启动 MySQL。
2. 导出受损表：mysqldump dbname tablename > table.sql。
3. 重建表：DROP TABLE tablename;，导入table.sql。
4. 关闭innodb_force_recovery，重启服务。

• PostgreSQL 表损坏：
• • 检查：REINDEX TABLE tablename;（重建索引），若失败则表数据损坏。

• • 恢复：

• • • 从备份恢复：psql -c "DROP TABLE tablename;"，psql -f backup.sql -t tablename。

• • • 利用 MVCC：SELECT * FROM tablename AS OF SYSTEM TIME '2023-10-01 00:00:00';（若启用了时间点恢复）。

• Redis 数据丢失：

• • RDB 文件损坏：删除损坏的dump.rdb，从 AOF 恢复（redis-server --appendonly yes）。

• • 内存溢出被 Kill：配置maxmemory-policy=allkeys-lru，增加内存或启用集群分片。

8.3 性能突发下降排查流程

1. 检查资源瓶颈：

• • CPU：top查看是否有进程 CPU 使用率 100%（如慢查询导致 MySQL 进程占满 CPU）。

• • 内存：free -m确认是否 OOM（内存不足导致 swap 频繁使用），检查数据库缓存命中率（MySQL 的Innodb_buffer_pool_read_hit_ratio应 > 99%）。

• • 磁盘 I/O：iostat -x 5查看%util（磁盘使用率），若接近 100% 可能是大量写入（如日志刷盘）或慢查询导致。

1. 数据库内部检查：

• • 活跃会话：MySQL 的SHOW PROCESSLIST;，PostgreSQL 的SELECT * FROM pg_stat_activity WHERE state != 'idle';，找出阻塞或耗时的 SQL。

• • 锁等待：MySQL 的SELECT * FROM information_schema.innodb_lock_waits;，PostgreSQL 的SELECT * FROM pg_locks WHERE NOT granted;。

• • 临时表与排序：PostgreSQL 的SELECT * FROM pg_stat_activity WHERE temp_files > 0;（临时文件过多可能是work_mem不足）。

1. 定位与解决：

• • 终止阻塞进程：KILL [pid];（MySQL）；SELECT pg_terminate_backend([pid]);（PostgreSQL）。

• • 优化慢查询：添加索引、重写 SQL（如用JOIN替代子查询）。

• • 调整配置：临时调大work_mem（PostgreSQL）、innodb_buffer_pool_size（MySQL），后续进行容量规划。

九、数据库容量规划与扩展

9.1 容量评估方法

• 数据增长预测：

• • 收集历史数据：每周记录数据库大小（du -sh /var/lib/mysql）、表行数（SELECT COUNT(*) FROM table;）。

• • 预测模型：假设每月增长 10%，当前 100GB，1 年后约 314GB（100×(1.1)^12），需预留 30% 冗余（总容量≈418GB）。

• 性能容量评估：

• • 峰值 QPS/TPS：通过监控工具记录每秒查询数，如秒杀活动 QPS 达 5000，需确保数据库能承载（可通过压测验证）。

• • 连接数需求：应用并发用户 × 每个用户连接数，如 1000 用户 ×2 连接 = 2000，max_connections需设为 2000 + 预留（如 2500）。

9.2 水平扩展策略

• 读写分离扩展：

• • 初始：1 主 1 从→随读流量增长增加从库（如 1 主 3 从），通过 ProxySQL 路由读请求到从库，按权重分配（性能好的从库权重高）。

• • 进阶：引入专用读库（如分析查询用从库，配置read_only=1），避免影响业务读。

• 分库分表：

• • 垂直分库：按业务模块拆分（如用户库、订单库），降低单库压力。

• • 水平分表：

• • • 范围分表：按时间（orders_202310、orders_202311）或 ID 范围（users_0-users_9，ID%10 取模）。

• • • 工具：ShardingSphere-JDBC（客户端分表）、MyCat（中间件），透明处理分表逻辑。

• • 示例（ShardingSphere 分表配置）：

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: order_db.t_order_${0..7}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order_inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order_table_inline

• 分布式数据库：

• • 当分库分表复杂度高时，迁移到 TiDB、CockroachDB，自动分片和负载均衡，支持弹性扩缩容（添加节点后自动重平衡数据）。

9.3 垂直扩展与云服务

• 垂直扩展：

• • 升级服务器配置：增加 CPU 核心（如从 8 核→16 核）、内存（如 32GB→128GB）、更换 SSD（提升 I/O），适合中小型数据库。

• • 注意：存在性能瓶颈（单实例难超 128 核 CPU），成本随配置增长非线性上升。

• 云数据库弹性扩展：

• • 阿里云 RDS：支持在线升级配置（CPU / 内存），无需停机，ALTER INSTANCE MODIFY SPECIFICATION InstanceType='rds.mysql.c6.large';。

• • AWS Aurora：自动扩展存储（最大 128TB），读写分离，读副本按需添加，适合突发流量。

十、数据库自动化运维与 DevOps 集成

10.1 数据库即代码（Database as Code）

• 版本控制：

• • 存储对象定义：表结构（CREATE TABLE）、视图、存储过程用 SQL 文件管理，提交到 Git 仓库，如：

sql/
  v1.0/
    create_users_table.sql
    create_orders_table.sql
  v1.1/
    add_user_age_column.sql

• • 迁移工具：Flyway、Liquibase，按版本顺序执行 SQL，记录已执行版本，避免重复执行。

• • • Flyway 示例：mvn flyway:migrate执行新增 SQL，flyway.info查看迁移状态。

• CI/CD 流水线集成：

• • GitLab CI 配置：

stages:
  - lint
  - test
  - migrate

# SQL语法检查
sql_lint:
  stage: lint
  image: sqlfluff/sqlfluff
  script:
    - sqlfluff lint sql/

# 测试环境迁移
test_migrate:
  stage: test
  script:
    - flyway migrate -url=jdbc:mysql://test-db:3306/test -user=root -password=$TEST_DB_PASS

# 生产环境迁移（手动触发）
prod_migrate:
  stage: migrate
  script:
    - flyway migrate -url=jdbc:mysql://prod-db:3306/prod -user=root -password=$PROD_DB_PASS
  when: manual
  only:
    - main

10.2 数据库运维自动化工具

• Percona Toolkit：

• • pt-table-checksum：检查主从数据一致性，pt-table-checksum --host=master --user=root --password=pass。

• • pt-online-schema-change：无锁修改表结构（如添加列），pt-online-schema-change D=db,t=users --alter "ADD COLUMN age INT" --execute。

• pgBackRest：PostgreSQL 备份工具，支持增量备份、并行备份 / 恢复，配置文件定义备份策略：

[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=7

[mydb]
db-path=/data/pgdata

执行全量备份：pgbackrest --stanza=mydb backup --type=full。

• Terraform 管理云数据库：

resource "aws_db_instance" "mysql" {
  allocated_storage    = 100
  storage_type         = "gp3"
  engine               = "mysql"
  engine_version       = "8.0"
  instance_class       = "db.t3.large"
  db_name              = "appdb"
  username             = "admin"
  password             = var.db_password
  backup_retention_period = 7
  multi_az             = true  # 多可用区部署
}

terraform apply自动创建 AWS RDS 实例，实现基础设施即代码。

10.3 数据库监控告警自动化

• Prometheus 告警规则：

groups:
- name: db_alerts
  rules:
  - alert: HighCpuUsage
    expr: avg(rate(mysql_global_status_threads_running[5m])) > 80
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "MySQL CPU使用率过高"
      description: "5分钟内平均运行线程数>80，可能存在性能问题"

• 自动修复脚本：

• • 结合 Alertmanager Webhook，触发修复动作（如终止慢查询）：

# Webhook处理函数
def handle_alert(alert):
    if alert["labels"]["alertname"] == "LongRunningQuery":
        pid = alert["annotations"]["pid"]
        os.system(f"mysql -e 'KILL {pid};'")
        return {"status": "success"}

• 运行手册自动化：

• • 将故障处理步骤编码为 Ansible Playbook，如主从切换：

- name: 从库提升为主库
  hosts: slave_db
  tasks:
    - name: 停止从库复制
      mysql_replication:
        mode: stop
        login_user: root
        login_password: "{{ db_pass }}"

    - name: 重置从库为独立实例
      mysql_replication:
        mode: reset
        login_user: root
        login_password: "{{ db_pass }}"