MySQL5.7生产运维手册

最新推荐文章于 2026-06-28 16:52:34 发布

原创最新推荐文章于 2026-06-28 16:52:34 发布 · 110 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#运维 #mysql #linux

话题

#AI编程·六月创作之星博客挑战赛

MYSQL 专栏收录该内容

17 篇文章

订阅专栏

MySQL 5.7 生产运维手册

面向生产环境的 MySQL 5.7 安装、配置、SQL、备份恢复、主从复制、性能调优与故障排障指南
适用场景：业务 OLTP、读写分离、分库分表前置、日常 DBA 运维
版本说明：本文针对 MySQL 5.7.x（5.7 无 CTE/窗口函数，见附录）

架构与核心原理
安装部署与目录规划
核心配置参数
用户权限与安全
SQL 基础与数据类型
SQL 组合查询与实战
索引与执行计划
事务、锁与死锁
备份与恢复
主从复制与高可用
- 10.6 通过 xtrabackup 备份恢复搭建主从
- 10.7 MHA 高可用启停
性能监控与慢查询
性能调优
日常运维命令
故障排查手册
故障处理 SOP
日常巡检清单
值班速查
在线 DDL 与 gh-ost

1. 架构与核心原理

1.1 MySQL 逻辑架构

层次	职责	运维关注
连接层	认证、连接管理、线程分配	`max_connections`、连接数监控
SQL 层	解析、优化、缓存、权限检查	慢 SQL、执行计划
存储引擎层	数据读写、事务、锁	InnoDB 状态、表空间
文件层	数据文件、日志	磁盘、IO、备份

1.2 InnoDB 核心机制

Buffer Pool（缓冲池）

将热点数据页和索引页缓存在内存，减少磁盘 IO
默认占物理内存 50%～70%（需给 OS 留余量）
脏页异步刷盘，由 innodb_flush_log_at_trx_commit 和 checkpoint 控制

redo log（重做日志）

物理日志，记录「在某个数据页上做了什么修改」
循环写，固定大小（如 2GB×2 组）
作用：崩溃恢复（Crash Recovery），保证已提交事务持久化
写入顺序：事务修改 → 写 redo log（prepare）→ 写 binlog → redo commit

undo log（回滚日志）

记录数据修改前的旧值
作用：事务回滚、MVCC 多版本读（一致性非锁定读）
长事务会导致 undo 堆积，拖慢 purge

binlog（二进制日志）

逻辑日志，记录 SQL 或行变更（ROW/STATEMENT/MIXED）
用于：主从复制、点对点恢复
由 Server 层产生，与存储引擎无关

MVCC（多版本并发控制）

每行有隐藏列：DB_TRX_ID（事务 ID）、回滚指针
READ COMMITTED：每次读生成新 Read View
REPEATABLE READ（InnoDB 默认）：事务内第一次读生成 Read View，后续复用
幻读：RR 下 InnoDB 通过 Next-Key Lock（记录锁 + 间隙锁）缓解

1.3 一条 UPDATE 的完整路径

1.4 表空间与文件

文件/目录	说明
`ibdata1`	系统表空间（5.7 默认共享；`innodb_file_per_table=ON` 时每表独立 `.ibd`）
`*.ibd`	独立表空间数据+索引
`ib_logfile*`	redo log 文件
`undo_*`	undo 表空间（5.7.8+ 可独立）
`mysql-bin.*`	binlog
`*.frm`	表结构定义（5.7）
`slow.log` / `error.log`	慢查询、错误日志

2. 安装部署与目录规划

2.1 生产目录规划

/data/mysql/
├── data/           # datadir，数据文件
├── logs/
│   ├── error.log
│   ├── slow.log
│   └── mysql-bin.*
├── tmp/            # tmpdir
└── backup/         # 本地备份暂存
/etc/my.cnf         # 主配置

2.2 安装（YUM / 二进制）

# CentOS 7 示例
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql-community-server-5.7.44

# 初始化（若空数据目录）
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/data
# 或安全模式（生成临时 root 密码）
mysqld --initialize --user=mysql --datadir=/data/mysql/data

systemctl enable mysqld
systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log   # 首次登录改密

2.3 初始化后必做

-- 修改 root 密码（5.7 密码策略较严）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourStr0ng#Pass';
-- 或降低策略（仅测试环境）
SET GLOBAL validate_password_policy=LOW;

-- 删除匿名用户、禁止 root 远程（按需）
DELETE FROM mysql.user WHERE User='';
DROP USER IF EXISTS 'root'@'%';   -- 生产建议单独建运维账号
FLUSH PRIVILEGES;

2.4 systemd 管理

systemctl status mysqld
systemctl start|stop|restart mysqld
# 改配置后
mysqladmin -uroot -p variables | grep -i config   # 确认生效项
# 动态参数 SET GLOBAL；静态参数需重启

3. 核心配置参数

3.1 my.cnf 生产模板（精简）

[mysqld]
user                    = mysql
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock
pid-file                = /var/run/mysqld/mysqld.pid
datadir                 = /data/mysql/data
tmpdir                  = /data/mysql/tmp
log-error               = /data/mysql/logs/error.log
slow_query_log          = 1
slow_query_log_file     = /data/mysql/logs/slow.log
long_query_time         = 1
log_queries_not_using_indexes = 0

# 字符集
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci

# 连接
max_connections         = 2000
max_connect_errors      = 100000
wait_timeout            = 600
interactive_timeout     = 600
back_log                = 512

# InnoDB
default_storage_engine  = InnoDB
innodb_buffer_pool_size = 16G          # 物理内存 50%~70%
innodb_buffer_pool_instances = 8       # 每实例至少 1GB
innodb_log_file_size    = 2G
innodb_log_buffer_size  = 64M
innodb_flush_log_at_trx_commit = 1     # 1=最安全；2=性能折中
innodb_flush_method     = O_DIRECT
innodb_file_per_table   = 1
innodb_io_capacity      = 2000         # SSD 可调高
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_lock_wait_timeout = 50

# binlog
server-id               = 1
log_bin                 = /data/mysql/logs/mysql-bin
binlog_format           = ROW          # 主从/恢复推荐 ROW
expire_logs_days        = 7
sync_binlog             = 1            # 1=最安全
max_binlog_size         = 1G
gtid_mode               = ON           # 主从建议开启
enforce_gtid_consistency = ON

# 复制（从库）
# read_only = 1
# super_read_only = 1
# relay_log_recovery = 1

# 安全
local_infile            = 0
symbolic-links          = 0
sql_mode                = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[client]
default-character-set   = utf8mb4
socket                  = /var/lib/mysql/mysql.sock

3.2 参数速查

参数	含义	生产建议
`innodb_buffer_pool_size`	缓冲池	内存主力，最重要
`innodb_flush_log_at_trx_commit`	redo 刷盘策略	金融=1；可接受丢 1s=2
`sync_binlog`	binlog 刷盘	主库=1
`max_connections`	最大连接	配合连接池，避免过大
`thread_cache_size`	线程缓存	减少建连开销
`table_open_cache`	表缓存	表多时调大
`innodb_lock_wait_timeout`	等锁超时	默认 50s，OLTP 可降

4. 用户权限与安全

4.1 账号规范

-- 按最小权限：应用只读写业务库
CREATE USER 'app_rw'@'10.%.%.%' IDENTIFIED BY '复杂密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON biz_db.* TO 'app_rw'@'10.%.%.%';

-- 只读从库 / 报表
CREATE USER 'app_ro'@'10.%.%.%' IDENTIFIED BY '复杂密码';
GRANT SELECT ON biz_db.* TO 'app_ro'@'10.%.%.%';

-- 运维备份账号
CREATE USER 'backup'@'localhost' IDENTIFIED BY '复杂密码';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

FLUSH PRIVILEGES;

4.2 权限查看与回收

SHOW GRANTS FOR 'app_rw'@'10.%.%.%';
REVOKE DELETE ON biz_db.* FROM 'app_rw'@'10.%.%.%';
DROP USER 'app_rw'@'10.%.%.%';

4.3 安全清单

禁止 root 远程、禁止空密码
应用账号不用 SUPER/FILE/PROCESS
local_infile=0
定期审计 mysql.user
敏感列加密 / 脱敏（应用层或 TDE 企业版）
3306 仅内网、安全组白名单

5. SQL 基础与数据类型

5.1 DDL（库表管理）

-- 建库
CREATE DATABASE biz_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 建表（InnoDB + 主键 + 注释）
CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  order_no    VARCHAR(32)     NOT NULL COMMENT '订单号',
  user_id     BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  amount      DECIMAL(12,2)   NOT NULL DEFAULT 0.00 COMMENT '金额',
  status      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待付 1已付 2关闭',
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status (user_id, status),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 改表
ALTER TABLE orders ADD COLUMN remark VARCHAR(255) DEFAULT NULL AFTER status;
ALTER TABLE orders MODIFY amount DECIMAL(14,2) NOT NULL;
-- 在线 DDL（5.7，大表谨慎）
ALTER TABLE orders ALGORITHM=INPLACE, LOCK=NONE, ADD INDEX idx_remark (remark(64));

-- 删表
DROP TABLE IF EXISTS orders_bak;

5.2 DML

INSERT INTO orders (order_no, user_id, amount, status)
VALUES ('O20250624001', 1001, 99.90, 0);

INSERT INTO orders (order_no, user_id, amount, status) VALUES
('O20250624002', 1002, 199.00, 0),
('O20250624003', 1003, 49.90, 1);

UPDATE orders SET status = 1, updated_at = NOW() WHERE order_no = 'O20250624001' AND status = 0;

DELETE FROM orders WHERE status = 2 AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 1000;

5.3 DQL 基础

SELECT id, order_no, amount FROM orders WHERE user_id = 1001 AND status = 1;
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total FROM orders GROUP BY status;
SELECT COUNT(DISTINCT user_id) FROM orders WHERE created_at >= '2025-06-01';

5.4 常用数据类型选型

类型	适用	注意
`BIGINT UNSIGNED`	主键、ID	不用 INT 做主键防耗尽
`DECIMAL(M,D)`	金额	不用 FLOAT/DOUBLE
`VARCHAR(N)`	变长字符串	N 按实际；索引过长用前缀
`DATETIME`	时间	不用字符串存时间
`TINYINT`	状态枚举	配合注释或字典表
`JSON`	半结构化（5.7+）	大字段慎索引
`TEXT/BLOB`	长文本	独立拆表或对象存储

5.5 事务控制

START TRANSACTION;   -- 或 BEGIN
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1001 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1002;
COMMIT;              -- 或 ROLLBACK;

-- 查看隔离级别
SELECT @@transaction_isolation;   -- 5.7.20+ 或 @@tx_isolation
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

6. SQL 组合查询与实战

6.1 多表 JOIN

-- INNER JOIN：两表匹配行
SELECT o.order_no, o.amount, u.username, u.mobile
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at >= '2025-06-01';

-- LEFT JOIN：保留左表全部
SELECT u.id, u.username, COUNT(o.id) AS order_cnt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 1
GROUP BY u.id, u.username;

-- 多表关联
SELECT o.order_no, oi.sku_name, oi.qty, oi.price
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN users u ON o.user_id = u.id
WHERE o.created_at BETWEEN '2025-06-01' AND '2025-06-30';

运维注意：JOIN 字段需同类型、有索引；避免大表无条件下笛卡尔积。

6.2 子查询

-- WHERE IN
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE level = 'VIP');

-- EXISTS（常比 IN 高效）
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 1
);

-- 标量子查询
SELECT order_no, amount,
  (SELECT username FROM users u WHERE u.id = o.user_id) AS username
FROM orders o
WHERE o.status = 1;

-- FROM 子查询（派生表，5.7 无 CTE，用派生表代替）
SELECT t.status, t.cnt FROM (
  SELECT status, COUNT(*) AS cnt FROM orders GROUP BY status
) t WHERE t.cnt > 100;

6.3 UNION / UNION ALL

-- UNION 去重；UNION ALL 不去重（更快）
SELECT order_no, amount, created_at FROM orders_202506
UNION ALL
SELECT order_no, amount, created_at FROM orders_202505
ORDER BY created_at DESC
LIMIT 100;

6.4 GROUP BY 与 HAVING

-- 按用户统计订单
SELECT user_id,
       COUNT(*) AS order_cnt,
       SUM(amount) AS total_amount,
       MAX(created_at) AS last_order_time
FROM orders
WHERE created_at >= '2025-06-01'
GROUP BY user_id
HAVING order_cnt >= 5 AND total_amount > 1000
ORDER BY total_amount DESC
LIMIT 50;

6.5 常用聚合组合

-- 按日统计
SELECT DATE(created_at) AS dt,
       COUNT(*) AS orders,
       SUM(amount) AS gmv
FROM orders
WHERE created_at >= '2025-06-01'
GROUP BY DATE(created_at)
ORDER BY dt;

-- CASE 分段统计
SELECT
  SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS closed
FROM orders
WHERE created_at >= CURDATE();

6.6 运维实用 SQL 组合

-- 查表大小 Top 10
SELECT table_schema, table_name,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY data_length + index_length DESC
LIMIT 10;

-- 查重复数据
SELECT order_no, COUNT(*) AS c FROM orders GROUP BY order_no HAVING c > 1;

-- 查无主键表
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
  ON t.table_schema = s.table_schema AND t.table_name = s.table_name AND s.index_name = 'PRIMARY'
WHERE t.table_schema = 'biz_db' AND t.table_type = 'BASE TABLE' AND s.index_name IS NULL;

-- 查未使用索引（需开启记录）
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'biz_db';

6.7 分页优化

-- 低效：深分页 OFFSET 大时全表扫描
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 推荐：延迟关联 / 游标
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

-- 或记住上次最大 id
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

7. 索引与执行计划

7.1 索引类型

类型	说明
主键索引	聚簇索引，叶子存整行
唯一索引	列值唯一
普通索引	加速查询
联合索引	最左前缀原则

7.2 最左前缀

联合索引 (user_id, status, created_at) 可命中：

WHERE user_id = ?
WHERE user_id = ? AND status = ?
WHERE user_id = ? AND status = ? AND created_at > ?

不可单独命中 status 或 created_at（无 user_id 时）。

7.3 EXPLAIN 解读

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1\G

列	关注点
`type`	性能：system > const > eq_ref > ref > range > index > ALL（全表扫描差）
`key`	实际使用索引
`rows`	预估扫描行数，越小越好
`Extra`	`Using filesort`/`Using temporary` 需优化
`filtered`	5.7 过滤百分比

-- 查看索引使用情况
SHOW INDEX FROM orders;
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;   -- 碎片整理，低峰执行

8. 事务、锁与死锁

8.1 锁类型

锁	说明
共享锁 S	`SELECT ... LOCK IN SHARE MODE`
排他锁 X	`UPDATE/DELETE/INSERT`、`SELECT ... FOR UPDATE`
意向锁	表级，与行锁协调
间隙锁	RR 下防幻读
临键锁	记录锁 + 间隙锁

8.2 死锁与锁阻塞排查

-- 查看最近一次死锁
SHOW ENGINE INNODB STATUS\G
-- 关注 LATEST DETECTED DEADLOCK 段

-- 当前锁等待（5.7）
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM sys.innodb_lock_waits;

-- 进行中的事务
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started DESC;
-- Kill 事务：KILL <trx_mysql_thread_id>

查看阻塞者并生成 KILL 语句（生产常用）：

SELECT CURRENT_TIMESTAMP,
   pw.user AS pw_user, pw.db AS pw_db,
   SUBSTRING_INDEX(pw.host,':',1) AS pw_host,
   pw.COMMAND AS pw_command, pw.state AS pw_state,
   pw.INFO AS pw_sqltext, pw.id AS pw_processid, pw.TIME AS pw_time,
   r.trx_wait_started AS waiting_started,
   TIMEDIFF(NOW(), r.trx_wait_started) AS waiting_age,
   rl.lock_table AS waiting_locked_table,
   r.trx_mysql_thread_id AS waiting_pid,
   r.trx_query AS waiting_query,
   b.trx_mysql_thread_id AS blocking_pid,
   b.trx_query AS blocking_query,
   CONCAT('KILL ', b.trx_mysql_thread_id, ';') AS kill_sql,
   pb.user AS pb_user, pb.INFO AS pb_sqltext
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
JOIN information_schema.processlist pw ON pw.id = r.trx_mysql_thread_id
JOIN information_schema.processlist pb ON pb.id = b.trx_mysql_thread_id
ORDER BY r.trx_id;

-- 长事务
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- 活跃非 Sleep 会话（排除复制）
SELECT CONCAT('KILL ', id, ';') AS kill_sql, id, user, host, db, state, command, time, LEFT(info, 50)
FROM information_schema.processlist
WHERE command <> 'Sleep' AND command <> 'Binlog Dump GTID'
ORDER BY time DESC;

8.3 死锁处理原则

InnoDB 自动回滚代价小的事务，应用需有重试
固定顺序访问多表，减少交叉锁
缩短事务、避免大事务中交互等待
高并发热点行：拆分、队列、乐观锁

9. 备份与恢复

9.1 备份策略

类型	工具	场景
逻辑备份	`mysqldump`	小库、跨版本、结构导出
物理热备	`xtrabackup`	大库、生产不停机
binlog	连续归档	点对点恢复
快照	LVM/云盘	配合冷备/从库

3-2-1 原则：3 份副本、2 种介质、1 份异地。

9.2 mysqldump

# 全库（InnoDB 一致性，--single-transaction）
mysqldump -uroot -p --single-transaction --master-data=2 \
  --routines --triggers --events \
  --all-databases | gzip > /data/mysql/backup/all_$(date +%F).sql.gz

# 单库
mysqldump -uroot -p --single-transaction biz_db | gzip > biz_db_$(date +%F).sql.gz

# 仅结构
mysqldump -uroot -p -d biz_db > biz_db_schema.sql

生产环境单库备份（含 GTID 集群）：

port=3306
dbname=visualdata
sock=/export/servers/data/my${port}/run/mysqld.sock
mysqldump=/export/servers/app/mysql-5.7.22/bin/mysqldump
ts=$(date +%Y%m%d_%H%M%S)

${mysqldump} -uroot -S ${sock} -p \
  --default-character-set=utf8 \
  --opt --hex-blob --skip-tz-utc \
  --add-drop-database=FALSE --add-drop-table=FALSE \
  --single-transaction \
  --set-gtid-purged=OFF \
  --log-error=${dbname}.full.sql.${ts}.err \
  ${dbname} > ${dbname}.full.sql.${ts} 2>${dbname}.full.sql.${ts}.log

单表备份：

dbname=smartops
tablename=your_table
${mysqldump} -uroot -S ${sock} -p \
  --default-character-set=utf8 --opt --hex-blob --skip-tz-utc \
  --add-drop-database=FALSE --add-drop-table=FALSE \
  --single-transaction --set-gtid-purged=OFF \
  ${dbname} --tables ${tablename} > ${dbname}.${tablename}.sql.${ts}

参数	说明
`--default-character-set=utf8`	备份字符集
`--opt`	常用优化组合（quick、extended-insert 等）
`--hex-blob`	BLOB 十六进制导出，防损坏
`--skip-tz-utc`	不写 `SET TIME_ZONE='+00:00'`
`--add-drop-database/table=FALSE`	不生成 DROP 语句
`--single-transaction`	InnoDB 一致性快照
`--set-gtid-purged=OFF`	GTID 环境避免污染目标
`-R`	备份存储过程/函数（需 SUPER 权限）

9.3 xtrabackup 热备

MySQL 5.7 使用 Percona XtraBackup 2.4.x（与 8.0 的 8.x 工具链不通用）。

# 全量（innobackupex 2.4 语法）
innobackupex --defaults-file=/export/servers/data/my3306/my.cnf \
  --user=dbbackup --password='xxx' \
  --backup --lock-ddl-per-table --slave-info \
  --parallel=6 \
  --socket=/export/servers/data/my3306/run/mysqld.sock \
  /export/backup

# 或使用 xtrabackup 命令
xtrabackup --defaults-file=/export/servers/data/my3306/my.cnf \
  --user=dbbackup --password='xxx' \
  --backup --lock-ddl-per-table --slave-info \
  --socket=/export/servers/data/my3306/run/mysqld.sock \
  --target-dir=/export/backupmysql/backup_3306/data/$(date +%y%m%d_%H_%M)

# 流式打包备份（适合大库传输）
innobackupex --defaults-file=/export/servers/data/my3306/my.cnf \
  --user=dbbackup --password='xxx' --slave-info \
  /tmp --stream=tar | gzip > /export/backup/full_$(date +%Y%m%d).tar.gz

# 生产定时脚本常用写法
innobackupex --defaults-file=${mycnfdir} --user=$username --password=$passwd \
  --lock-ddl-per-table --slave-info ${backup_path} \
  --stream=tar | gzip > ${backup_path}/full_$(date +%Y%m%d)bak.tar.gz

参数	说明
`--lock-ddl-per-table`	备份期间对单表 DDL 加锁，保证结构一致
`--slave-info`	生成主从位点/GTID 信息（`xtrabackup_slave_info`）
`--parallel=N`	并行拷贝数据文件，大库加速
`--slave-info` + GTID	从 `xtrabackup_info` 读取 `gtid_purged`

备份成功标志：日志末尾出现 completed OK!，并生成：

文件	含义
`xtrabackup_info`	备份元信息、binlog 位点、GTID 范围
`xtrabackup_binlog_info`	binlog 文件名与 position
`xtrabackup_checkpoints`	LSN checkpoint
`backup-my.cnf`	备份时配置快照

# 增量基于 LSN
innobackupex --user=backup --password='xxx' --incremental /backup/inc \
  --incremental-basedir=/backup/full

# 准备（apply log）
innobackupex --apply-log /export/backup/2022-01-05_15-01-34/

9.4 恢复

# 逻辑恢复
gunzip < all_2025-06-24.sql.gz | mysql -uroot -p

# 单库
mysql -uroot -p biz_db < biz_db_2025-06-24.sql

# 基于 binlog 点对点（误删后）
mysqlbinlog --start-datetime='2025-06-24 10:00:00' \
  --stop-datetime='2025-06-24 10:05:00' \
  /data/mysql/logs/mysql-bin.000123 | mysql -uroot -p

9.5 备份巡检

# 验证备份可恢复（定期演练）
# 检查备份文件大小、时间
ls -lh /data/mysql/backup/
# binlog 是否堆积
SHOW BINARY LOGS;

10. 主从复制与高可用

10.1 复制原理

10.2 主库配置

server-id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

CREATE USER 'repl'@'10.%.%.%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.%.%.%';

10.3 从库配置

server-id = 2
relay_log = relay-bin
read_only = 1
super_read_only = 1
gtid_mode = ON
enforce_gtid_consistency = ON

CHANGE MASTER TO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_AUTO_POSITION=1;   -- GTID
START SLAVE;
SHOW SLAVE STATUS\G

关键字段：

字段	正常值
`Slave_IO_Running`	Yes
`Slave_SQL_Running`	Yes
`Seconds_Behind_Master`	0 或很小
`Last_IO_Error` / `Last_SQL_Error`	空

10.4 主从故障切换（手工）

-- 1. 确认从库追上
SHOW SLAVE STATUS\G

-- 2. 停应用写主库

-- 3. 从库停复制并提升
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = 0;

-- 4. 应用改连新主库
-- 5. 旧主修复后作为从库挂回

10.5 读写分离注意

从库有延迟：Seconds_Behind_Master > 0 时读可能落后
写后读一致：同连接读主库，或等待 GTID、缓存版本号
中间件：MyCAT、ProxySQL、ShardingSphere

10.6 通过 xtrabackup 备份恢复搭建主从（生产实战）

来源：MySQL通过备份恢复的方式搭建主从.pdf。适用于新建从库或主从不同步需重建场景，比 mysqldump 更快，适合大库。

流程总览

第一步：主库获取备份

mkdir -p /export/backup

innobackupex --defaults-file=/export/servers/data/my3306/my.cnf \
  --user=dbbackup --password='xxx' \
  --backup --lock-ddl-per-table --parallel=6 \
  --socket=/export/servers/data/my3306/run/mysqld.sock \
  --slave-info \
  /export/backup
# 成功标志：completed OK!

备份目录示例：/export/backup/2022-01-05_15-01-34/，内含业务库目录、ibdata1、xtrabackup_info 等。

记录 binlog/GTID 信息（备份结束时日志会打印，也可事后查看）：

cat /export/backup/2022-01-05_15-01-34/xtrabackup_info
# binlog_pos = filename 'mysql-bin.000027', position '9080176',
# GTID of the last change '6d22d516-baf5-11eb-91ad-00155d4b3c01:1-26214'

第二步：传输至从库

# 大文件可先打包
tar czf backup.tar.gz -C /export/backup 2022-01-05_15-01-34
scp backup.tar.gz slave-host:/export/backup/

第三步：从库恢复准备

从库安装同版本 MySQL + XtraBackup 2.4
新实例：用 init_mysql.sh 初始化空实例（如 3307 端口），修改 server-id 与主库不同
重建从库：若实例已存在仅复制不同步，跳过初始化，直接停库清数据

/etc/init.d/mysql3307.server stop

cd /export/servers/data/my3307
rm -rf ./binlog/* ./data/* ./ibdata/* ./iblog/*

# 新目录结构（若新建）
mkdir -p my3307/{binlog,data,ibdata,iblog,log,run,tmp} -pv
# 拷贝主库 my.cnf 到 my3307，修改 server-id
touch /export/servers/data/my3307/log/mysqld-err.log

第四步：apply-log（应用 redo）

cd /export/backup/2022-01-05_15-01-34
innobackupex --apply-log ./
# 成功标志：completed OK!

第五步：copy-back（还原到数据目录）

# 方式一：拷贝（保留备份目录）
innobackupex --defaults-file=/export/servers/data/my3307/my.cnf \
  --copy-back /export/backup/2022-01-05_15-01-34

# 方式二：移动（节省磁盘）
xtrabackup --defaults-file=/export/servers/data/my3307/my.cnf \
  --move-back --target-dir=/export/backup/2022-01-05_15-01-34

chown -R mysql:myinstall /export/servers/data/my3307
/etc/init.d/mysql3307.server start

第六步：搭建 GTID 主从

mysql -uroot -p -S /export/servers/data/my3307/run/mysqld.sock

-- 1. 从 xtrabackup_info 获取 GTID 范围
-- cat /export/servers/data/my3307/data/xtrabackup_info

RESET MASTER;
SET GLOBAL gtid_purged='6d22d516-baf5-11eb-91ad-00155d4b3c01:1-26214';

CHANGE MASTER TO
  MASTER_HOST='172.18.80.107',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='repl',
  MASTER_AUTO_POSITION=1;

START SLAVE;
SHOW SLAVE STATUS\G

验证通过标准：

字段	期望值
`Slave_IO_Running`	Yes
`Slave_SQL_Running`	Yes
`Seconds_Behind_Master`	0
`Auto_Position`	1
`Last_IO_Error` / `Last_SQL_Error`	空

注意事项

gtid_purged 必须与 xtrabackup_info 中 完全一致
从库 server-id 必须唯一；read_only=1、super_read_only=1
若 apply-log 提示 already prepared，说明已执行过，可直接 copy-back
主库备份期间尽量避开大 DDL；已用 --lock-ddl-per-table 降低风险

10.7 MHA 高可用启停

来源：数据库日常维护操作手册.docx。适用于 MHA + VIP 架构。

环境示例

角色	IP	说明
主库	10.7.87.136	server-id=223306
从库	10.7.87.137
MHA Manager	10.7.87.34
VIP	10.7.87.138	应用连接地址
网关	10.7.87.254	arping 用

关闭顺序

关闭 MHA → 主库摘 VIP → 停主库 → 停从库

# 1. MHA 节点：停高可用
cd /service
systemctl stop daemontools.service
# 仅停单个 MHA 实例：
# svc -d /service/masterha_chengxu/

# 2. 主节点：摘 VIP + 停 MySQL
ifconfig bond0:0 down
/etc/init.d/mysql3306 stop
ps -ef | grep mysqld    # 确认已关闭

# 3. 从节点：停 MySQL
/etc/init.d/mysql3306 stop
ps -ef | grep mysqld

启动顺序

启主库（改读写）→ 启从库（START SLAVE）→ 挂 VIP → 启 MHA

# 1. 主节点
/etc/init.d/mysql3306 start
mysql -uroot -p -S /export/servers/data/my3306/run/mysqld.sock

mysql> SHOW SLAVE STATUS\G
-- 主库应 Empty set（无复制配置）

mysql> SHOW VARIABLES LIKE '%read_only%';
-- 若 read_only=ON，改为可写：
SET GLOBAL read_only=OFF;
SET GLOBAL super_read_only=OFF;

# 2. 从节点
/etc/init.d/mysql3306 start
mysql -uroot -p -S /export/servers/data/my3306/run/mysqld.sock

mysql> SHOW SLAVE STATUS\G
-- 若 IO/SQL 为 No：
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
-- 确认 Slave_IO_Running=Yes, Slave_SQL_Running=Yes

# 3. 主节点挂 VIP
/sbin/ifconfig bond0:0 10.7.87.138 netmask 255.255.255.0
/sbin/arping -c 3 -s 10.7.87.138 -I bond0 10.7.87.254

# 4. 启动 MHA
systemctl start daemontools.service

11. 性能监控与慢查询

11.1 关键状态指标

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Select_scan';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

-- 缓存命中率（参考）
-- (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

11.2 慢查询

slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 1
log_slow_admin_statements = 1

# pt-query-digest 分析
pt-query-digest /data/mysql/logs/slow.log > slow_report.txt

11.3 Performance Schema / sys

-- 当前最耗时的 SQL（需开启 performance_schema）
SELECT * FROM sys.statement_analysis LIMIT 10;

-- 全表扫描最多的表
SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 10;

-- 等待事件
SELECT * FROM sys.waits_global_by_latency LIMIT 10;

11.4 告警建议

指标	阈值（参考）
`Threads_running`	持续 > CPU 核数 × 2
连接数	> max_connections × 80%
复制延迟	> 60s
磁盘使用率	> 85%
慢查询增长率	突增
死锁	频繁出现

12. 性能调优

12.1 连接层

应用使用连接池，避免频繁建连
max_connections 不是越大越好
查 Aborted_connects、Threads_cached

12.2 InnoDB

-- Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 脏页比例
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

大表分区/归档，控制单表体量
合理索引，定期 ANALYZE TABLE
避免 SELECT *、大事务、热点行竞争

12.3 硬件与 OS

项	建议
磁盘	SSD/NVMe；数据与 binlog 分盘更佳
文件系统	XFS/ext4；`noatime`
内存	给 Buffer Pool；避免 swap（`swappiness=1`）
网络	主从同机房低延迟

12.4 参数调优流程

1. 定位瓶颈（CPU/IO/锁/慢 SQL）
2. 看监控 + EXPLAIN + SHOW PROFILE
3. 先 SQL/索引，后参数
4. 一次改一项，对比验证
5. 低峰变更，保留回滚

13. 日常运维命令

本节整合 数据库日常维护操作手册.docx 中 MySQL 生产操作指令。

13.1 服务与连接

# systemd 或自定义脚本
/etc/init.d/mysql3306 start|stop|status
systemctl start|stop mysqld

mysqladmin -uroot -p ping
mysqladmin -uroot -p status
mysqladmin -uroot -p processlist

# 登录（多实例用 socket）
/export/servers/app/mysql-5.7.22/bin/mysql -uroot -p \
  -S /export/servers/data/my3306/run/mysqld.sock

-- 连接数相关
SHOW VARIABLES LIKE '%conn%';
-- max_connections / max_user_connections / connect_timeout

-- 正在执行的 SQL
SELECT * FROM information_schema.processlist WHERE info IS NOT NULL;

13.2 库表维护

-- 查看库字符集后建库
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'bank_data';

CREATE DATABASE bank_data CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 表结构与注释
SHOW CREATE TABLE orders\G;

-- 修改字段长度（保留原 NOT NULL / DEFAULT / COMMENT）
ALTER TABLE orders MODIFY COLUMN channel_name VARCHAR(500) NOT NULL DEFAULT '' COMMENT '通道名称';

-- 根据表名反查库名
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_NAME = '已知表名';

13.3 容量与表空间

-- 库大小（GB）
SELECT SUM(data_length)/1024/1024/1024
FROM information_schema.tables
WHERE table_schema = UPPER('foreigntrade_db');

-- 单表大小（GB）
SELECT SUM(data_length)/1024/1024/1024
FROM information_schema.tables
WHERE table_schema = UPPER('foreigntrade_db') AND table_name = 'for_access_manifest';

-- 友好显示 MB
SELECT CONCAT(SUM(data_length)/1024/1024, ' MB') AS size
FROM information_schema.tables
WHERE table_schema = UPPER('foreigntrade_db') AND table_name = 'for_access_manifest';

-- 表行数估算
SELECT table_rows FROM information_schema.tables
WHERE table_schema = 'biz_db' AND table_name = 'orders';

-- 精确行数
SELECT COUNT(*) FROM orders;

13.4 进程、事务与会话

SHOW FULL PROCESSLIST;

KILL QUERY 12345;   -- 仅终止当前语句
KILL 12345;         -- 断开连接

-- 会话历史（Performance Schema）
SELECT b.processlist_id, c.db, c.user, a.sql_text, c.command, c.time, c.state
FROM performance_schema.events_statements_history a
JOIN performance_schema.threads b USING(thread_id)
JOIN information_schema.processlist c ON b.processlist_id = c.id
WHERE c.id = 50658
ORDER BY a.EVENT_ID;

-- 线程内存占用
SELECT mt.thread_id,
  SUM(mt.current_count_used) AS current_count_used,
  CAST(ROUND(SUM(mt.current_number_of_bytes_used)/1024/1024) AS SIGNED) AS current_allocated_mb,
  t.processlist_user, t.processlist_db, t.processlist_info, t.processlist_time
FROM performance_schema.memory_summary_by_thread_by_event_name mt
INNER JOIN performance_schema.threads t ON mt.thread_id = t.thread_id
WHERE t.processlist_db IS NOT NULL AND t.name = 'thread/sql/one_connection'
GROUP BY mt.thread_id
ORDER BY current_allocated_mb DESC;

13.5 binlog 管理

SHOW MASTER LOGS;
SHOW BINARY LOGS;

-- 按时间清理
PURGE BINARY LOGS BEFORE '2025-06-20 17:00:00';

-- 按文件清理（删除 mysql-bin.000006 之前）
PURGE BINARY LOGS TO 'mysql-bin.000006';

# 解析 binlog（ROW 模式建议 DECODE-ROWS）
mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000008 > mysql-bin.000008.log

# 按时间范围导出用于恢复
mysqlbinlog --start-datetime='2025-06-24 10:00:00' \
  --stop-datetime='2025-06-24 10:05:00' mysql-bin.000200 > recover.sql

13.6 索引审计

-- 未使用的索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'cityos' AND object_name IN ('table1')
ORDER BY object_name;

-- 冗余索引
SELECT table_schema, table_name, redundant_index_name, redundant_index_columns,
       dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema != 'performance_schema';

13.7 数据导出

-- 导出 CSV（目录需有写权限，常用 /tmp）
SELECT column1, column2 INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM your_table WHERE your_conditions;

13.8 日志查看

# 错误日志
tail -200 /export/servers/data/my3306/log/mysqld-err.log
# 或 more /export/server/data/my3306/log/mysqld-err.log

# 慢日志分析 Top SQL
pt-query-digest --type slowlog \
  --since "2025-06-24 17:01:00" --until "2025-06-24 17:05:00" \
  slow.log > slow_report.txt

-- general log（仅排障临时开启，生产慎开）
SHOW VARIABLES WHERE Variable_name IN ('general_log','general_log_file');
SET GLOBAL general_log = ON;
-- 排查完毕关闭
SET GLOBAL general_log = OFF;

13.9 主从状态

SHOW SLAVE STATUS\G
-- 关注 Slave_IO_Running / Slave_SQL_Running / Seconds_Behind_Master

13.10 GTID 跳过单事务（慎用）

仅当确认可跳过某条冲突 GTID 时使用，需 DBA 审批。

SET @@SESSION.GTID_NEXT = 'ece7b132-33c5-11e8-bfd6-e8611f23c126:256649553';
BEGIN; COMMIT;
SET @@SESSION.GTID_NEXT = 'AUTOMATIC';

13.11 空间清理

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY);
ALTER TABLE orders DROP PARTITION p202401;   -- 若已分区

13.12 版本与升级

SELECT VERSION();
SHOW VARIABLES LIKE 'version%';

5.7 升级路径：5.7 → 8.0 需 mysql_upgrade、检查兼容性；小版本低峰滚动重启从库再主库。

14. 故障排查手册

14.1 无法连接

现象	排查	处理
`Can't connect`	进程、端口、防火墙	`systemctl start mysqld`；开放 3306
`Too many connections`	`Threads_connected`	杀空闲连接；调大 max_connections；修连接泄漏
`Access denied`	用户/host/密码	`SHOW GRANTS`；重置密码
`Host is blocked`	`max_connect_errors`	`FLUSH HOSTS`；修网络抖动

ss -lntp | grep 3306
tail -100 /data/mysql/logs/error.log

14.2 磁盘满

df -h
du -sh /data/mysql/*

-- 查大表
SELECT table_schema, table_name,
  ROUND((data_length+index_length)/1024/1024/1024,2) AS gb
FROM information_schema.tables
ORDER BY data_length+index_length DESC LIMIT 20;

-- 应急：清理 binlog、慢日志、临时表、归档历史数据
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 1 DAY);

14.3 主从复制异常

SHOW SLAVE STATUS\G

错误	原因	处理
IO 线程 No	网络、账号、server-id	查 `Last_IO_Error`；`CHANGE MASTER`
SQL 线程 No	数据不一致、DDL 冲突	`STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE;`（慎用）
延迟大	从库性能、大事务、单线程回放	并行复制 `slave_parallel_workers`；优化慢 SQL
GTID 冲突	误操作双写	GTID 跳过或 rebuild 从库（见 10.6）

重建从库（常用）：见 10.6 通过 xtrabackup 备份恢复搭建主从。

14.4 锁等待 / 库卡死

-- 谁阻塞谁
SELECT w.requesting_trx_id, w.blocking_trx_id,
       pl1.info AS requesting_sql, pl2.info AS blocking_sql
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx t1 ON w.requesting_trx_id = t1.trx_id
JOIN information_schema.innodb_trx t2 ON w.blocking_trx_id = t2.trx_id
JOIN information_schema.processlist pl1 ON t1.trx_mysql_thread_id = pl1.id
JOIN information_schema.processlist pl2 ON t2.trx_mysql_thread_id = pl2.id;

-- 杀阻塞源
KILL <blocking_thread_id>;

14.5 慢查询突增

1. pt-query-digest 看 Top SQL
2. EXPLAIN 是否走索引
3. 是否统计信息过期 → ANALYZE TABLE
4. 是否缓存失效 / 业务放量
5. 是否锁等待导致堆积

14.6 数据误删恢复

1. 立即停止写入（只读、摘流量）
2. 确认误操作时间点
3. 全备 + binlog 恢复到新实例验证
4. 回灌或切换

mysqlbinlog --start-datetime='2025-06-24 14:00:00' \
  --stop-datetime='2025-06-24 14:10:00' \
  mysql-bin.000200 > recover.sql
# 手工编辑 recover.sql 去掉误操作语句后导入

14.7 表损坏

CHECK TABLE orders;
-- InnoDB 多数情况可恢复
-- 严重：xtrabackup 恢复或 innodb_force_recovery（最后手段，可能丢数据）

# my.cnf 应急（1→6 逐级尝试，仅恢复导出用）
innodb_force_recovery = 1

15. 故障处理 SOP

SOP-1：连接数打满

1. mysqladmin processlist 看 Sleep 与 Running 比例
2. 应用是否连接泄漏（未 close）
3. 临时 KILL 长 Sleep（需审批）
4. 必要时调 max_connections（治标）
5. 复盘连接池配置

SOP-2：主从延迟过高

1. SHOW SLAVE STATUS → Seconds_Behind_Master
2. 从库 SHOW PROCESSLIST 是否在跑大 SQL
3. 主库是否有大事务 / DDL
4. 开启 parallel workers；优化慢 SQL
5. 延迟持续 > 阈值 → 读流量切主或降级

SOP-3：死锁频发

1. SHOW ENGINE INNODB STATUS 取死锁日志
2. 对齐应用 SQL 加锁顺序
3. 缩短事务、索引优化
4. 应用侧重试机制

SOP-4：数据库 CPU 100%

1. SHOW PROCESSLIST 找耗时 SQL
2. EXPLAIN + 索引
3. 是否缓存击穿 / 全表扫描
4. 限流、读写分离

SOP-5：紧急只读（保护数据）

SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1;   -- 5.7 super 权限也禁止写
-- 恢复
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

16. 日常巡检清单

每日

实例存活：mysqladmin ping
连接数 / Threads_running
复制状态：Slave_IO/SQL_Running、Seconds_Behind_Master
磁盘空间 < 80%
错误日志无新 Fatal
慢查询量无异常突增
备份任务成功

每周

大表增长趋势
binlog 占用与保留策略
索引冗余 / 未使用索引
长事务、死锁统计
备份恢复演练（抽样）

每月

账号权限审计
参数回顾与容量规划
小版本安全补丁评估
information_schema 碎片整理计划

17. 值班速查

告警	第一步	关键命令
连接失败	进程/端口/错误日志	`systemctl status mysqld`、`tail error.log`
连接数高	processlist	`SHOW PROCESSLIST`、`KILL`
复制中断	slave status	`SHOW SLAVE STATUS\G`
复制延迟	从库负载	`Seconds_Behind_Master`、慢 SQL
磁盘满	大表/binlog	`PURGE BINARY LOGS`、归档
CPU 高	活跃 SQL	`PROCESSLIST`、`EXPLAIN`
死锁	innodb status	`SHOW ENGINE INNODB STATUS\G`
数据误操作	停写	全备 + `mysqlbinlog` 恢复

禁止未经审批的操作：

生产 DROP DATABASE / TRUNCATE 大表
随意 sql_slave_skip_counter
随意调高 innodb_force_recovery
无备份情况下执行不可逆 DDL

18. 在线 DDL 与 gh-ost

来源：数据库日常维护操作手册.docx。大表结构变更优先用 gh-ost，避免原生 ALTER TABLE 长时间锁表。

18.1 原生在线 DDL（5.7）

ALTER TABLE orders ALGORITHM=INPLACE, LOCK=NONE, ADD INDEX idx_remark (remark(64));

仍可能因元数据锁、重建表等导致抖动，GB 级表建议 gh-ost。

18.2 gh-ost 示例

gh-ost \
  --user="root" --password="******" \
  --host=10.241.241.224 --port=3306 \
  --database="order" --table="orderinfo" \
  --alter="ADD KEY ind_account (trans_account)" \
  --allow-on-master \
  --initially-drop-old-table \
  --initially-drop-ghost-table \
  --allow-nullable-unique-key \
  --max-load=Threads_connected=1500 \
  --critical-load=Threads_connected=4000 \
  --chunk-size=200000 \
  --max-lag-millis=1500 \
  --initially-drop-socket-file \
  --verbose \
  --execute

参数	说明
`--allow-on-master`	允许在主库执行（无独立从库时）
`--max-load` / `--critical-load`	负载过高时暂停迁移
`--chunk-size`	每批拷贝行数
`--max-lag-millis`	主从延迟超阈值则暂停
`--execute`	不加则仅 dry-run

18.3 变更前检查清单

已在预发验证 DDL
磁盘空间 ≥ 表大小 2 倍
低峰窗口 + 回滚方案（gh-ost 可 panic 中断）
主从延迟正常

附录：MySQL 5.7 与 8.0 差异速查

特性	5.7	8.0
窗口函数	不支持	支持
CTE（WITH）	不支持	支持
默认字符集	latin1（建议改 utf8mb4）	utf8mb4
认证插件	mysql_native_password	caching_sha2_password
查询缓存	有（8.0 移除）	无
不可见索引	不支持	支持
直方图	不支持	支持

附录：常用 information_schema 查询

-- 当前所有连接
SELECT id, user, host, db, command, time, state, LEFT(info,100) AS sql_text
FROM information_schema.processlist ORDER BY time DESC;

-- 表字段信息
SELECT column_name, column_type, is_nullable, column_key, column_comment
FROM information_schema.columns
WHERE table_schema = 'biz_db' AND table_name = 'orders';

-- 外键
SELECT * FROM information_schema.key_column_usage
WHERE table_schema = 'biz_db' AND referenced_table_name IS NOT NULL;