# 语法：mysql -h 主机 -P 端口 -u 用户名 -p
# -p 后面不加密码，回车后交互式输入（更安全，密码不会留在历史记录）
mysql -h 10.0.1.100 -P 3306 -u root -p

# 快速登录本地 Socket（比 TCP 更快）
mysql -u root -p -S /tmp/mysql.sock

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql>

-- 查看当前所有活跃连接（排查连接池爆满时必用）
SHOW FULL PROCESSLIST;

-- 只看非 Sleep 的活跃查询
SELECT id, user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep';

+----+------+-------------+------+---------+------+-----------+------------------+
| id | user | host        | db   | command | time | state     | info             |
+----+------+-------------+------+---------+------+-----------+------------------+
| 10 | root | 10.0.1.1:22 | test | Query   |    0 | executing | SELECT * FROM t  |
| 11 | app  | 10.0.1.2:33 | test | Sleep   |  120 |           | NULL             |
+----+------+-------------+------+---------+------+-----------+------------------+

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE mydb;

-- 查看当前库所有表
SHOW TABLES;

-- 查看表结构（字段、类型、主键等）
DESC mytable;
-- 或
SHOW CREATE TABLE mytable\G

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

-- 创建数据库（指定字符集，避免乱码）
CREATE DATABASE IF NOT EXISTS mydb
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

-- 创建表
CREATE TABLE IF NOT EXISTS users (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    name     VARCHAR(100) NOT NULL COMMENT '用户名',
    email    VARCHAR(200) NOT NULL COMMENT '邮箱',
    age      TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    status   TINYINT DEFAULT 1 COMMENT '状态 1=正常 0=禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ⚠️ 大表加字段可能导致锁表，生产环境用 pt-online-schema-change

-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改字段类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);

-- 重命名字段
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20);

-- 删除字段（谨慎！）
ALTER TABLE users DROP COLUMN mobile;

-- 添加索引
ALTER TABLE users ADD INDEX idx_name (name);

-- 查看表大小（排查磁盘空间）
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'size_mb'
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY size_mb DESC;

-- 日志表：记录系统操作日志（如 API 请求、错误、任务执行）
CREATE TABLE operation_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trace_id    VARCHAR(32)     NOT NULL COMMENT '链路追踪ID',
    app_name    VARCHAR(50)     NOT NULL COMMENT '应用名（如 order-api）',
    log_level   ENUM('DEBUG','INFO','WARN','ERROR','FATAL') NOT NULL DEFAULT 'INFO',
    message     TEXT            NOT NULL COMMENT '日志内容',
    req_ip      VARCHAR(45)     NOT NULL COMMENT '请求来源IP',
    user_id     INT UNSIGNED    DEFAULT NULL COMMENT '操作用户ID（可能为空：未登录）',
    api_path    VARCHAR(200)    DEFAULT NULL COMMENT '请求路径（如 /api/order/create）',
    status_code SMALLINT        DEFAULT NULL COMMENT 'HTTP状态码（200, 404, 500...）',
    cost_ms     INT UNSIGNED    DEFAULT 0 COMMENT '请求耗时(毫秒)',
    created_at  DATETIME(3)     NOT NULL COMMENT '日志时间（精确到毫秒）',
    INDEX idx_app_level (app_name, log_level),
    INDEX idx_created (created_at),
    INDEX idx_user (user_id),
    INDEX idx_api_status (api_path(100), status_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';

-- 用户表
CREATE TABLE users (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    role       ENUM('admin','ops','dev','viewer') NOT NULL DEFAULT 'dev',
    status     TINYINT DEFAULT 1 COMMENT '1=正常 0=禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 🎯 需求：查某段时间内某应用的 ERROR 日志
SELECT trace_id, created_at, message, api_path, cost_ms, req_ip
FROM operation_logs
WHERE app_name   = 'order-api'
  AND log_level  = 'ERROR'
  AND created_at >= '2026-06-23 09:50:00'
  AND created_at <  '2026-06-23 10:00:00'
ORDER BY created_at DESC;

+----------------------------------+-------------------------+-----------------------------------------+----------------------+--------+-------------+
| trace_id                         | created_at              | message                                 | api_path             | cost_ms| req_ip      |
+----------------------------------+-------------------------+-----------------------------------------+----------------------+--------+-------------+
| a1b2c3d4-e5f6-7890-abcd-ef123456 | 2026-06-23 09:59:58.123 | [Timeout] Redis read timeout after 3s   | /api/order/create    |   3210 | 10.0.1.50   |
| a1b2c3d4-e5f6-7890-abcd-ef789012 | 2026-06-23 09:55:32.456 | [DBError] Deadlock found when trying...  | /api/order/pay       |   5432 | 10.0.1.88   |
+----------------------------------+-------------------------+-----------------------------------------+----------------------+--------+-------------+

-- 🎯 需求：每分钟跑一次的巡检脚本，只查最近 5 分钟的 ERROR
SELECT COUNT(*) AS error_count, api_path
FROM operation_logs
WHERE app_name   = 'order-api'
  AND log_level  = 'ERROR'
  AND created_at >= NOW() - INTERVAL 5 MINUTE
GROUP BY api_path
ORDER BY error_count DESC;

-- 🎯 需求：你拿到一个 trace_id（比如从告警里），想看这个请求从头到尾发生了什么
SELECT created_at, log_level, api_path, message, cost_ms
FROM operation_logs
WHERE trace_id = 'a1b2c3d4-e5f6-7890-abcd-ef123456'
ORDER BY created_at ASC;

-- 🎯 需求：按小时统计某应用过去 24 小时的请求量 + 错误量 + 平均耗时
SELECT
    DATE_FORMAT(created_at, '%Y-%m-%d %H:00') AS time_bucket,
    COUNT(*)                                   AS total_reqs,
    SUM(IF(status_code >= 500, 1, 0))          AS error_5xx,
    ROUND(AVG(cost_ms), 1)                     AS avg_cost_ms,
    ROUND(MAX(cost_ms), 0)                     AS max_cost_ms,
    CONCAT(ROUND(SUM(IF(status_code >= 500, 1, 0)) * 100.0 / COUNT(*), 2), '%') AS error_rate
FROM operation_logs
WHERE app_name   = 'order-api'
  AND created_at >= NOW() - INTERVAL 24 HOUR
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:00')
ORDER BY time_bucket ASC;

+------------------+-----------+----------+-------------+------------+------------+
| time_bucket      | total_reqs| error_5xx| avg_cost_ms | max_cost_ms| error_rate |
+------------------+-----------+----------+-------------+------------+------------+
| 2026-06-22 09:00 |      3520 |       12 |         45.2 |       2100 | 0.34%      |
| 2026-06-22 10:00 |      4210 |      380 |        320.5 |       8910 | 9.03%      | ← 拐点！
| 2026-06-22 11:00 |      1080 |      420 |        980.0 |      15000 | 38.89%     | ← 问题扩大
+------------------+-----------+----------+-------------+------------+------------+

-- 🎯 需求：这周每天的错误率 vs 上周同一天
SELECT
    DATE(created_at) AS day,
    COUNT(*) AS total,
    SUM(IF(log_level = 'ERROR', 1, 0)) AS errors,
    ROUND(AVG(cost_ms), 0) AS avg_latency
FROM operation_logs
WHERE app_name = 'order-api'
  AND created_at >= CURDATE() - INTERVAL 14 DAY
GROUP BY DATE(created_at)
ORDER BY day DESC;

-- 🎯 需求：搜索日志内容中包含某个关键词的记录（如 "Redis timeout"）
SELECT trace_id, created_at, log_level, message, api_path, cost_ms
FROM operation_logs
WHERE app_name = 'order-api'
  AND created_at >= '2026-06-23 00:00:00'
  AND message LIKE '%Redis timeout%'
ORDER BY created_at DESC
LIMIT 50;

-- ✅ 如果非要搜，缩小时间范围 + 加其他索引条件
SELECT trace_id, message, api_path
FROM operation_logs
WHERE app_name = 'order-api'
  AND created_at >= '2026-06-23 09:50:00'
  AND created_at <  '2026-06-23 10:00:00'
  AND (message LIKE '%Redis%' OR message LIKE '%timeout%')
  AND log_level IN ('ERROR', 'WARN')
ORDER BY created_at DESC;

-- 🎯 需求：找昨天哪个接口最慢（排障第一步：确定"受害"接口）
SELECT api_path,
       COUNT(*)                    AS call_count,
       ROUND(AVG(cost_ms), 0)     AS avg_ms,
       ROUND(MAX(cost_ms), 0)     AS max_ms,
       ROUND(percentile_approx(cost_ms, 0.95), 0) AS p95_ms  -- 注意：MySQL 没有内置百分位函数，这是思路
FROM operation_logs
WHERE app_name = 'order-api'
  AND created_at >= CURDATE() - INTERVAL 1 DAY
GROUP BY api_path
ORDER BY avg_ms DESC
LIMIT 10;

-- 🎯 需求：查看某个用户最近的错误日志 + 用户信息
SELECT
    l.created_at,
    l.log_level,
    l.api_path,
    l.message,
    u.name     AS user_name,
    u.role     AS user_role,
    u.status   AS user_status
FROM operation_logs l
JOIN users u ON l.user_id = u.id
WHERE l.user_id = 12345
  AND l.log_level IN ('ERROR', 'WARN')
  AND l.created_at >= '2026-06-01'
ORDER BY l.created_at DESC
LIMIT 20;

-- 🎯 需求：某个 IP 在半小时内发了大量请求，怀疑刷接口，查他做了什么
SELECT l.created_at, l.api_path, l.status_code, l.message, u.name, u.role
FROM operation_logs l
LEFT JOIN users u ON l.user_id = u.id
WHERE l.req_ip = '10.0.1.50'
  AND l.created_at >= '2026-06-23 09:30:00'
  AND l.created_at <  '2026-06-23 10:00:00'
ORDER BY l.created_at ASC;

-- 🎯 需求：找出错误率超过 5% 的接口，按错误次数排序
SELECT api_path, total, errors,
       ROUND(errors * 100.0 / total, 2) AS error_rate
FROM (
    SELECT api_path,
           COUNT(*) AS total,
           SUM(IF(status_code >= 500, 1, 0)) AS errors
    FROM operation_logs
    WHERE app_name = 'order-api'
      AND created_at >= CURDATE() - INTERVAL 1 DAY
    GROUP BY api_path
) AS stats
WHERE errors * 100.0 / total > 5
ORDER BY errors DESC;

+---------------------+-------+--------+------------+
| api_path            | total | errors | error_rate |
+---------------------+-------+--------+------------+
| /api/order/create   | 35210 |   2850 |      8.09% |
| /api/payment/pay    | 18200 |   1200 |      6.59% |
+---------------------+-------+--------+------------+

-- 🎯 同上需求，用 CTE 改写（可读性更好）
WITH api_stats AS (
    SELECT api_path,
           COUNT(*) AS total,
           SUM(IF(status_code >= 500, 1, 0)) AS errors
    FROM operation_logs
    WHERE app_name = 'order-api'
      AND created_at >= CURDATE() - INTERVAL 1 DAY
    GROUP BY api_path
)
SELECT api_path, total, errors,
       ROUND(errors * 100.0 / total, 2) AS error_rate
FROM api_stats
WHERE errors * 100.0 / total > 5
ORDER BY errors DESC;

WITH api_stats AS (
    SELECT api_path,
           COUNT(*) AS total,
           SUM(IF(status_code >= 500, 1, 0)) AS errors,
           ROUND(AVG(cost_ms), 0) AS avg_ms
    FROM operation_logs
    WHERE app_name = 'order-api'
      AND created_at >= CURDATE() - INTERVAL 1 DAY
    GROUP BY api_path
)
SELECT * FROM api_stats
WHERE errors * 100.0 / total > 5
   OR avg_ms > 1000
ORDER BY errors DESC, avg_ms DESC;

-- 🎯 需求：按自定义严重等级分组统计
SELECT
    CASE
        WHEN log_level IN ('FATAL', 'ERROR') THEN '严重'
        WHEN log_level = 'WARN'              THEN '警告'
        ELSE                                      '正常'
    END AS severity,
    COUNT(*) AS cnt
FROM operation_logs
WHERE app_name = 'order-api'
  AND created_at >= NOW() - INTERVAL 7 DAY
GROUP BY severity
ORDER BY cnt DESC;

+----------+-------+
| severity | cnt   |
+----------+-------+
| 正常     | 285000|
| 警告     |   3200|
| 严重     |    480|
+----------+-------+

-- 🎯 需求：统计 API 响应分类（2xx / 4xx / 5xx / 其他）
SELECT
    CASE
        WHEN status_code >= 200 AND status_code < 300 THEN '2xx 成功'
        WHEN status_code >= 400 AND status_code < 500 THEN '4xx 客户端错误'
        WHEN status_code >= 500 THEN '5xx 服务端错误'
        ELSE '其他'
    END AS status_category,
    COUNT(*) AS cnt,
    CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1), '%') AS pct
FROM operation_logs
WHERE app_name = 'order-api'
  AND created_at >= CURDATE()
GROUP BY status_category
ORDER BY cnt DESC;

-- 🎯 需求：找出昨天每个小时错误最多的 TOP 3 接口
WITH hourly_errors AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m-%d %H:00') AS hour_bucket,
        api_path,
        COUNT(*) AS error_count,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_FORMAT(created_at, '%Y-%m-%d %H:00')
            ORDER BY COUNT(*) DESC
        ) AS rn
    FROM operation_logs
    WHERE app_name = 'order-api'
      AND log_level IN ('ERROR', 'FATAL')
      AND created_at >= CURDATE() - INTERVAL 1 DAY
      AND created_at <  CURDATE()
    GROUP BY hour_bucket, api_path
)
SELECT hour_bucket, api_path, error_count
FROM hourly_errors
WHERE rn <= 3
ORDER BY hour_bucket DESC, rn ASC;

+------------------+---------------------+-------------+
| hour_bucket      | api_path            | error_count |
+------------------+---------------------+-------------+
| 2026-06-22 09:00 | /api/order/create   |          45 |
| 2026-06-22 09:00 | /api/order/pay      |          30 |
| 2026-06-22 09:00 | /api/user/login     |           8 |
| 2026-06-22 08:00 | /api/order/create   |          12 |
| 2026-06-22 08:00 | /api/order/refund   |           5 |
+------------------+---------------------+-------------+

WITH daily_stats AS (
    SELECT
        DATE(created_at) AS day,
        api_path,
        ROUND(AVG(cost_ms), 1) AS avg_ms,
        ROUND(AVG(cost_ms), 1) - LAG(ROUND(AVG(cost_ms), 1), 1) OVER (
            PARTITION BY api_path ORDER BY DATE(created_at)
        ) AS diff_from_yesterday
    FROM operation_logs
    WHERE app_name = 'order-api'
      AND created_at >= CURDATE() - INTERVAL 7 DAY
    GROUP BY day, api_path
)
SELECT day, api_path, avg_ms, diff_from_yesterday
FROM daily_stats
WHERE ABS(diff_from_yesterday) > 100  -- 只看耗时波动超过 100ms 的
ORDER BY day DESC, ABS(diff_from_yesterday) DESC;

-- ❌ 错误写法：OFFSET 越大越慢（OFFSET 100000 也要扫描 100000 行）
SELECT * FROM operation_logs WHERE log_level = 'ERROR' LIMIT 100 OFFSET 100000;

-- ✅ 方案一：游标分页（基于上次的最后 ID）
-- 第 1 页
SELECT * FROM operation_logs
WHERE log_level = 'ERROR'
ORDER BY id
LIMIT 100;

-- 第 2 页（把第 1 页最后一条的 id 拿过来）
SELECT * FROM operation_logs
WHERE log_level = 'ERROR'
  AND id > 100100
ORDER BY id
LIMIT 100;

-- ✅ 方案二：基于时间范围分批（适合日志导出）
-- 每次拿 1 天的数据，避免大翻页
SELECT * FROM operation_logs
WHERE app_name = 'order-api'
  AND created_at >= '2026-06-01'
  AND created_at <  '2026-06-02'
ORDER BY id ASC;

-- 🎯 基础：单条插入（场景：偶尔手动补一条日志）
INSERT INTO operation_logs (trace_id, app_name, log_level, message, req_ip, api_path, cost_ms, created_at)
VALUES ('manual-001', 'order-api', 'INFO', '手动回放请求确认修复', '127.0.0.1', '/api/check', 0, NOW());

-- 🎯 批量插入（场景：数据导入，一次插 500 条，速度是逐条的 50 倍）
INSERT INTO operation_logs (trace_id, app_name, log_level, message, req_ip, api_path, cost_ms, created_at)
VALUES
    ('batch-001', 'order-api', 'ERROR', 'DB connection timeout', '10.0.1.1', '/api/order', 3210, NOW()),
    ('batch-002', 'order-api', 'WARN',  'Slow query detected',  '10.0.1.2', '/api/user', 2100, NOW()),
    ('batch-003', 'order-api', 'INFO',  'Request completed',    '10.0.1.3', '/api/ok',     45, NOW());
-- 批量控制在 500~1000 条/次，超过可能撑爆 binlog

-- 🎯 不存在则插入，存在则忽略（场景：幂等导入，避免重复）
INSERT IGNORE INTO operation_logs (id, trace_id, app_name, log_level, message, req_ip, api_path, cost_ms, created_at)
VALUES (100001, 'dup-001', 'order-api', 'INFO', 'replayed', '10.0.1.1', '/api/ok', 0, NOW());

-- 🎯 基础更新：修正一条错误标记的日志
-- 🚨 安全准则：先 SELECT 确认 WHERE 条件再执行 UPDATE
SELECT id, log_level FROM operation_logs WHERE id = 12345;
-- 确认无误后：
UPDATE operation_logs SET log_level = 'ERROR' WHERE id = 12345;

-- 🎯 批量更新（场景：某个 IP 是监控探针，调大批日志级别改成 DEBUG 以免干扰统计）
UPDATE operation_logs
SET log_level = 'DEBUG'
WHERE req_ip = '10.0.1.100'
  AND app_name = 'order-api'
  AND created_at >= '2026-06-01';

-- 🎯 关联更新（场景：禁用一批用户后，标记他们的日志）
-- 把 status=0 的禁用用户，过去 7 天的日志全部标记
UPDATE operation_logs l
JOIN users u ON l.user_id = u.id
SET l.log_level = 'WARN',
    l.message   = CONCAT(l.message, ' [user_disabled]')
WHERE u.status = 0
  AND l.created_at >= NOW() - INTERVAL 7 DAY;

-- 🎯 基础删除：删一条误录的日志
DELETE FROM operation_logs WHERE id = 999999;

-- 🎯 批量删除（场景：定期清理过期日志——最常用的运维操作！）
-- ⚠️ 不要跑 DELETE FROM operation_logs WHERE created_at < '2026-01-01'
-- 一把删几千万行会：① 锁大量行 ② 撑爆 binlog ③ 主从延迟 N 小时
DELETE FROM operation_logs
WHERE created_at < '2026-01-01'
LIMIT 10000;  -- 每批只删 1 万行

-- 写一个循环脚本（Shell 中用）：
-- while true; do
--   mysql -e "DELETE FROM operation_logs WHERE created_at < '2026-01-01' LIMIT 10000;"
--   echo "Deleted 10000 rows..."
--   sleep 1
-- done

-- 建分区表（按月分区）
CREATE TABLE operation_logs_partitioned (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    -- ... 其他字段 ...
    created_at DATETIME(3) NOT NULL,
    PRIMARY KEY (id, created_at)  -- 分区键必须包含在主键中
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
    PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 清理旧分区：秒级完成，不影响业务
ALTER TABLE operation_logs_partitioned DROP PARTITION p202606;

-- 查看表的索引
SHOW INDEX FROM users;

-- 找出没有主键的表（运维巡检用）
SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'InnoDB'
  AND table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
  AND (SELECT COUNT(*) FROM information_schema.statistics
       WHERE table_schema = information_schema.tables.table_schema
         AND table_name = information_schema.tables.table_name
         AND index_name = 'PRIMARY') = 0;

-- 添加普通索引
CREATE INDEX idx_email ON users(email);

-- 添加唯一索引
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- 添加复合索引（最左前缀原则）
-- 场景：经常按 status + created_at 查询
CREATE INDEX idx_status_created ON users(status, created_at);

-- 删除索引
DROP INDEX idx_email ON users;

-- 查看索引使用情况（排查慢查询）
SELECT * FROM sys.schema_unused_indexes;

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- 看 type 列：const > ref > range > index > ALL（ALL 就是全表扫描）

-- 创建用户（密码用强密码）
CREATE USER 'app_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ss123';

-- 查看所有用户
SELECT user, host, account_locked FROM mysql.user;

-- 授予库级权限（最小权限原则）
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'10.0.1.%';

-- 授予只读账号（报表查询专用）
GRANT SELECT ON mydb.* TO 'readonly'@'10.0.1.%';

-- 授予 DBA 权限（仅限个人账号）
GRANT ALL PRIVILEGES ON *.* TO 'dba_zhangsan'@'10.0.1.%' WITH GRANT OPTION;

-- 刷新权限（修改权限后必须执行）
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'10.0.1.%';

-- 撤销权限
REVOKE DELETE ON mydb.* FROM 'app_user'@'10.0.1.%';

-- 删除用户
DROP USER 'old_user'@'10.0.1.%';

# 备份单个库
mysqldump -h 10.0.1.100 -u root -p --single-transaction --routines --triggers --events mydb > mydb_20260623.sql

# 备份多个库
mysqldump -h 10.0.1.100 -u root -p --single-transaction --databases db1 db2 db3 > multi_db.sql

# 备份全部库
mysqldump -h 10.0.1.100 -u root -p --single-transaction --all-databases --routines > all_dbs.sql

# 只导结构（不要数据）
mysqldump -h 10.0.1.100 -u root -p --no-data mydb > mydb_schema.sql

# 恢复单个库
mysql -h 10.0.1.100 -u root -p mydb < mydb_20260623.sql

# 或者在 MySQL 内执行
mysql> source /backup/mydb_20260623.sql;

# 只看备份中的建表语句，不看数据
grep "^CREATE TABLE" mydb_20260623.sql | head -10

# 查看备份文件大小
ls -lh mydb_20260623.sql

-- 开启慢查询日志（临时，当前会话有效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过 2 秒的记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看当前正在执行的查询（排查"卡住"的情况）
SELECT * FROM sys.session
WHERE command != 'Sleep' AND time > 10;

-- 查看最近 10 条慢查询（需要 performance_schema）
SELECT * FROM sys.statement_analysis
WHERE avg_latency > 100000000000  -- 大于 100 秒
ORDER BY avg_latency DESC
LIMIT 10;

-- 1. 找到慢查询的 SQL（从慢查询日志）
-- 2. 用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- 3. 看 type 和 rows 字段
--    type=ALL → 全表扫描，需要加索引
--    rows=100000 → MySQL 扫描了 10 万行，很大

-- 4. 看是否有文件排序
--    Extra: "Using filesort" → 排序字段没索引
--    加复合索引：CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 查看各库磁盘占用
SELECT
    table_schema AS '数据库',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS '数据(MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS '索引(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

-- 查看碎片率（碎片率 > 30% 考虑 OPTIMIZE）
SELECT
    table_name,
    ROUND(data_free / 1024 / 1024, 2) AS '碎片大小(MB)',
    ROUND(data_free * 100 / (data_length + index_length), 2) AS '碎片率(%)'
FROM information_schema.tables
WHERE table_schema = 'mydb' AND data_free > 0
ORDER BY data_free DESC;

-- 查看当前事务（排查事务未提交导致的锁）
SELECT * FROM information_schema.INNODB_TRX\G

-- 查看当前锁等待（排查死锁）
SELECT * FROM sys.innodb_lock_waits;

-- 强制结束长时间未提交的事务（最后手段）
-- KILL 查询线程 ID（从 PROCESSLIST 查到的 id）
KILL 12345;

-- 开启事务
START TRANSACTION;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交（成功）
COMMIT;

-- 或回滚（失败）
ROLLBACK;

-- 查看当前隔离级别（默认 REPEATABLE-READ）
SELECT @@transaction_isolation;

-- 修改隔离级别（当前会话）
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 查看最近一次死锁日志
SHOW ENGINE INNODB STATUS\G

-- 查看当前锁等待信息
SELECT * FROM sys.innodb_lock_waits;

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:  // 事务1：等待 A 锁
*** (2) TRANSACTION:  // 事务2：等待 B 锁，持有 A 锁
*** WE ROLL BACK TRANSACTION (2)  // 自动回滚事务2

# 一键检查 MySQL 健康状态（返回关键指标，适合写入监控脚本）
mysqladmin -h 10.0.1.100 -u root -p ping
mysqladmin -h 10.0.1.100 -u root -p status
mysqladmin -h 10.0.1.100 -u root -p extended-status

# 或者 Shell 脚本方式
mysql -h 10.0.1.100 -u root -p -e "
SELECT 'Uptime' as k, VARIABLE_VALUE as v FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Uptime'
UNION ALL
SELECT 'Connections', VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected'
UNION ALL
SELECT 'Slow_queries', VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Slow_queries';
"

-- 1. 用 root 账号登录
mysql -u root -p

-- 2. 查看当前连接分布
SELECT user, COUNT(*) AS cnt FROM information_schema.PROCESSLIST GROUP BY user;

-- 3. 临时加大连接数
SET GLOBAL max_connections = 500;

-- 4. 杀掉空闲连接（注意：不要 kill 重要业务连接）
-- 杀掉 Sleep 超过 300 秒的连接
SELECT CONCAT('KILL ', id, ';') AS kill_cmd
FROM information_schema.PROCESSLIST
WHERE command = 'Sleep' AND time > 300;
-- 把输出的 KILL 语句复制执行

# 1. 清理慢查询日志 / 错误日志
truncate -s 0 /var/log/mysql/slow.log
truncate -s 0 /var/log/mysql/error.log

# 2. 清理 binlog（确认从库已同步后）
mysql -u root -p -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;"

# 3. 如果还不行，清理大表历史数据
# 见 DELETE 分批删除章节

-- 1. 查看延迟
SHOW SLAVE STATUS\G
-- 看 Seconds_Behind_Master 字段

-- 2. 常见原因：
--    - 从库写入慢 → 升级从库配置或优化慢查询
--    - 大事务 → 避免长事务，大操作分批执行
--    - 从库在做备份 → 错开备份时间

-- 3. 临时跳过错误（不推荐，仅紧急恢复用）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;