MySQL 管理 - SQL 数据库

01. 用户与权限管理

MySQL 的权限系统分成两层：用户能连上（登录权限）、用户能干啥（操作权限）。root 是超级管理员，什么都能干，但也最危险——生产环境别直接用 root 操作。建用户的正确姿势：先 CREATE USER 创建账号，再 GRANT 撒权限，最后 FLUSH PRIVILEGES 刷新让改动生效。删用户就 DROP USER，改密码用 ALTER USER。权限粒度很细：可以精确到某张表的某几列、某个库的所有表、甚至某个存储过程。原则是最小权限——只给用户干活必需的权限。

sql

-- 创建只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT ON mydb.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

-- 创建应用用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppPass456!';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost';

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 回收权限
REVOKE DELETE ON mydb.* FROM 'app_user'@'localhost';

别用 GRANT 自动创建用户（老版本可以），MySQL 8+ 不支持了。必须先 CREATE USER 再 GRANT。

02. 备份与恢复——mysqldump 与 XtraBackup

备份是 DBA 的生命线。两种备份思路：逻辑备份（mysqldump）和物理备份（XtraBackup）。 mysqldump 把数据和结构导成 SQL 语句，人可读，可以只备份某些表甚至某些行。缺点是慢，大库备份和恢复都慢。适合小库和部分数据迁移。 XtraBackup 是物理备份——直接复制数据文件，速度快、不锁表。支持增量备份。生产环境的大库备份标配。备份策略一般是：每天全量加每小时增量，保留最近 N 天。定期做恢复演练——光备份不验证等于没备份。

bash

# mysqldump 备份
mysqldump -u root -p --single-transaction mydb > mydb_backup.sql

# 只备份结构
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# 恢复
mysql -u root -p mydb < mydb_backup.sql

# XtraBackup 全量备份
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

mysqldump 加 --single-transaction 参数能保证 InnoDB 表在备份期间一致性，不锁表。生产环境必加。

03. 主从复制——读写分离的基础

主从复制是 MySQL 高可用的基石——一台主库负责写，多台从库负责读。主库把变更记到二进制日志（binlog），从库拉取日志并在本地重放，最终两边数据一致。三种复制格式：STATEMENT（记录 SQL 语句，省空间但可能不准）、ROW（记录每行怎么变的，准但日志大）、MIXED（混合）。生产环境推荐 ROW 格式。复制延迟是常见问题——从库追不上主库的写入速度。可能是网络、从库性能、大事务等原因。延迟高时去从库读可能拿到旧数据。

sql

# 主库配置 my.cnf
# server-id=1
# log-bin=mysql-bin
# binlog_format=ROW

# 在从库上执行
CHANGE MASTER TO
  MASTER_HOST='10.0.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;
START SLAVE;
SHOW SLAVE STATUS\G

SHOW SLAVE STATUS 里的 Seconds_Behind_Master 是关键指标——0 说明完美同步，数值大说明有延迟。

04. 慢查询分析与优化流程

生产环境最怕突然变慢，排查慢查询有固定套路：第一步，开慢查询日志——设置 long_query_time（比如超过 1 秒算慢），打开 slow_query_log。第二步，用 mysqldumpslow 或 pt-query-digest 分析日志——哪个 SQL 跑得最慢、哪类查询最频繁。第三步，对慢查询用 EXPLAIN 分析——看 type 列是不是 ALL、key 列有没有走索引、rows 列预估扫多少行。第四步，对症下药——加索引、改 SQL、分表、加缓存、升级硬件。改之前拿 EXPLAIN 对比一下前后的差异。

bash

# 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

# 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log

# EXPLAIN 分析具体 SQL
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

pt-query-digest 是 Percona Toolkit 里的神器，比 mysqldumpslow 强大得多，生产环境必备。

05. InnoDB 参数调优基础

MySQL 默认配置偏保守，生产环境必须调。InnoDB 几个核心参数： innodb_buffer_pool_size——InnoDB 的内存缓存区，存数据和索引。值越大缓存命中率越高，读写越快。一般设成物理内存的 50% 到 70%。 innodb_log_file_size——redo log 文件大小，影响写入性能。太小的日志写满了就要刷盘，写入会卡。建议设成 buffer pool 的 25% 左右。 innodb_flush_log_at_trx_commit——控制 redo log 刷盘策略。默认 1（最安全但最慢），2（每秒刷一次，宕机可能丢 1 秒数据），0（最快但最不安全）。 innodb_io_capacity——告诉 InnoDB 磁盘 IO 能力，影响后台刷脏页的速度。SSD 可以设高一些。

sql

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS\G

-- 查看 buffer pool 使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - reads/requests，应该大于 99%

改 innodb_buffer_pool_size 之前确认物理内存够用。改大了导致系统用 swap 的话，性能反而崩盘。

知识测验

第 1/5 题正确 0

mysqldump 的 --single-transaction 参数干嘛的？

PostgreSQL 入门