MySQL 数据库部署与性能优化-小程博客

适用版本：MySQL 8.0+（覆盖 5.7 主要差异说明）

适用系统：CentOS 7 / Rocky Linux 9 / Ubuntu 20.04+

目标读者：DBA / 后端开发 / DevOps / 运维工程师

一、MySQL 架构概览

1.1 核心组件

┌─────────────────────────────────────────────────────────────────┐
│                         连接层                                    │
│  连接池 │ 认证 │ 线程管理 │ 连接限制 │ SSL/TLS                     │
├─────────────────────────────────────────────────────────────────┤
│                         服务层                                    │
│  SQL 接口 │ 解析器 │ 优化器 │ 缓存（MySQL 8.0 已移除）             │
├─────────────────────────────────────────────────────────────────┤
│                         存储引擎层                                │
│   InnoDB  │  MyISAM  │  Memory  │  Archive                      │
│   (默认/推荐)                                                     │
├─────────────────────────────────────────────────────────────────┤
│                         文件层                                   │
│  表空间 │ Redo Log │ Undo Log │ Binlog │ Error Log │ Slow Log   │
└─────────────────────────────────────────────────────────────────┘

1.2 存储引擎对比

特性	InnoDB	MyISAM	Memory
事务支持	✅ ACID	❌ 不支持	❌ 不支持
行级锁	✅	❌ 表级锁	❌ 表级锁
外键约束	✅	❌	❌
全文索引	✅（5.6+）	✅	❌
MVCC	✅	❌	❌
崩溃恢复	✅ 自动	❌ 需修复	❌ 数据丢失
适用场景	OLTP/生产环境	只读/日志	临时表/缓存
单表大小	64TB+	256TB	受 RAM 限制

💡 强烈建议：生产环境一律使用 InnoDB 引擎，MySQL 8.0 已将 InnoDB 设为默认引擎。

1.3 硬件推荐配置

环境	CPU	内存	磁盘	说明
开发/测试	2核	4GB	100GB SSD	单机部署
小型生产	4核	16GB	500GB SSD	单机 + 主从
中型生产	8核	64GB	1TB NVMe SSD	主从 + 读写分离
大型生产	16核+	128GB+	多盘 RAID10 NVMe SSD	多节点集群

二、安装 MySQL

2.1 YUM 方式安装（CentOS / Rocky Linux）

# 1. 添加 MySQL 官方 YUM 源
sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

# 如果 Rocky Linux 9，使用
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-4.noarch.rpm

# 2. 安装 MySQL Server
sudo yum install -y mysql-community-server

# 3. 启动并设置开机自启
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 4. 查看临时密码（MySQL 8.0 首次安装会生成临时密码）
sudo grep 'temporary password' /var/log/mysqld.log

# 输出示例：[Note] A temporary password is generated for root@localhost: abc123XYZ!@#

2.2 APT 方式安装（Ubuntu / Debian）

# 1. 下载 DEB 包并安装
cd /tmp
curl -LO https://dev.mysql.com/get/mysql-apt-config_0.8.25-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.25-1_all.deb
# 在交互界面中选择 MySQL 版本（建议 8.0）

# 2. 更新包列表并安装
sudo apt update
sudo apt install -y mysql-community-server

# 3. 启动并设置开机自启
sudo systemctl start mysql
sudo systemctl enable mysql

# 4. 安全初始化（设置 root 密码，移除匿名用户等）
sudo mysql_secure_installation

2.3 二进制方式安装（通用，所有 Linux 发行版）

# 1. 下载二进制包
cd /tmp
curl -LO https://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz

# 2. 解压到 /usr/local
sudo tar -xf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /usr/local/
sudo ln -s /usr/local/mysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql

# 3. 创建 MySQL 用户和数据目录
sudo groupadd mysql
sudo useradd -r -g mysql -s /bin/false mysql
sudo mkdir -p /data/mysql
sudo chown -R mysql:mysql /data/mysql

# 4. 初始化 MySQL
cd /usr/local/mysql
sudo bin/mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
# 注意：--initialize-insecure 不生成临时密码（生产环境用 --initialize 并记录密码）

# 5. 配置 systemd 服务
sudo cp support-files/mysql.server /etc/init.d/mysql
sudo systemctl daemon-reload
sudo systemctl start mysql
sudo systemctl enable mysql

2.4 Docker 快速部署（仅限开发/测试）

# 启动 MySQL 容器
docker run -d \
  --name mysql8 \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=MySecurePass123! \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=myapp \
  -e MYSQL_PASSWORD=myapp_pass123 \
  -v /data/mysql8:/var/lib/mysql \
  -v /data/mysql8/conf:/etc/mysql/conf.d \
  --restart unless-stopped \
  mysql:8.0

# 验证
docker exec -it mysql8 mysql -uroot -p

三、基础配置

3.1 主配置文件结构

# MySQL 配置文件查找顺序（后面的覆盖前面的）
# /etc/my.cnf
# /etc/mysql/my.cnf
# /usr/local/mysql/etc/my.cnf
# ~/.my.cnf

# 查看生效配置
mysql -uroot -p -e "SHOW VARIABLES LIKE '% Variables';"

# 查看所有配置
mysql -uroot -p -e "SHOW GLOBAL VARIABLES;"

3.2 生产环境优化配置

sudo vi /etc/my.cnf.d/mysql-server.cnf
# 如果文件不存在，创建 /etc/my.cnf.d/custom.cnf

/etc/my.cnf.d/custom.cnf

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

[mysql]
default-character-set = utf8mb4 
no-auto-rehash 
prompt = "\\u@\\h [\\d]> " 
auto-rehash

[mysqld]
# ── 基础设置 ──────────────────────────────────────────────
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /data/mysql
pid-file = /var/run/mysqld/mysqld.pid
user = mysql
# MySQL 8.0 默认大小写不敏感（Linux 下建议显式设置）
lower_case_table_names = 1

# ── 字符集 ───────────────────────────────────────────────
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# ── InnoDB 引擎 ──────────────────────────────────────────
default_storage_engine = InnoDB
innodb_buffer_pool_size = 12G           # 建议为系统总内存的 60-70%
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1      # 1=强一致（推荐）/ 2=折中 / 0=最快
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON              # 每个表单独一个表空间文件
innodb_io_capacity = 4000               # SSD 建议 4000-8000
innodb_io_capacity_max = 16000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_autoinc_lock_mode = 2            # 交错锁模式（高并发推荐）

# ── 连接管理 ─────────────────────────────────────────────
max_connections = 2000
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600
connect_timeout = 10
max_allowed_packet = 64M

# ── 缓存设置 ─────────────────────────────────────────────
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
key_buffer_size = 32M                   # MyISAM 索引缓存（InnoDB 不使用）

# ── 日志配置 ─────────────────────────────────────────────
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2                     # 超过 2 秒的查询记入慢日志
log_queries_not_using_indexes = 1       # 记录未使用索引的查询
log_error = /var/log/mysql/error.log
log_error_verbosity = 2                 # 2=Errors and warnings only

# ── Binlog（主从复制必备）───────────────────────────────
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW                     # ROW 格式（推荐，更安全）
binlog_row_image = FULL
max_binlog_size = 1G
binlog_cache_size = 4M
binlog_expire_logs_seconds = 604800     # Binlog 保留 7 天
sync_binlog = 1                         # 每事务同步 binlog（强一致）/ 设为 N 可提高性能
gtid_mode = ON                          # GTID 模式（MySQL 8.0 推荐）
enforce_gtid_consistency = ON

# ── 性能探针（Prometheus 监控）─────────────────────────
performance_schema = ON
performance_schema_instrument = '%=ON'

# 创建日志目录并授权
sudo mkdir -p /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql

# 重启 MySQL 生效配置
sudo systemctl restart mysqld

# 验证配置
mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'max_connections';"

3.3 首次安全配置

# MySQL 8.0 必须使用 ALTER USER 修改密码
mysql -uroot -p'临时密码'

# 修改 root 密码（MySQL 8.0 密码策略要求：大小写+数字+特殊字符，最少 8 位）
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourSecureP@ss123!';
mysql> FLUSH PRIVILEGES;

# 创建应用数据库和用户
mysql> CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> CREATE USER 'myapp'@'%' IDENTIFIED BY 'MyAppSecureP@ss456!';
mysql> CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'MyAppSecureP@ss456!';
mysql> GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp'@'%';
mysql> GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp'@'localhost';
mysql> FLUSH PRIVILEGES;

# 查看用户权限
mysql> SHOW GRANTS FOR 'myapp'@'%';

# 移除匿名用户（安全加固）
mysql> DELETE FROM mysql.user WHERE User='';
mysql> FLUSH PRIVILEGES;

四、主从复制配置

4.1 主从复制原理

┌─────────────────┐                          ┌─────────────────┐
│   Master        │                          │   Slave         │
│                 │   Binlog (DML events)    │                 │
│   Writer ───────┼──────────────────────────┼→ Relay Log      │
│   INSERT/UPDATE │                          │                 │
│   DELETE        │   Replication Thread     │ SQL Thread ──── │
│                 │                          │ Apply Events   │
│                 │   ──────────────────    │                 │
│                 │   IO Thread ──────────→ │ Read/Query     │
└─────────────────┘                          └─────────────────┘

GTID 模式（MySQL 8.0 推荐）：
Master 每次事务提交都会生成一个全局唯一的事务 ID（GTID）
Slave 记录已执行 GTID，自动跳过已复制的事务，复制更可靠

4.2 主库配置（Master）

sudo vi /etc/my.cnf.d/custom.cnf

[mysqld]
# ── 主库配置 ────────────────────────────────────────────
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
gtid_mode = ON
enforce_gtid_consistency = ON

# 复制相关（可选，但推荐配置）
binlog_checksum = NONE
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_purge = ON

# 重启主库
sudo systemctl restart mysqld

# 创建复制专用账户
mysql -uroot -p -e "
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplSecureP@ss789!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
"

# 查看主库状态（记录 File 和 Position）
mysql -uroot -p -e "SHOW MASTER STATUS\G"

输出示例：

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 155
     Binlog_Default_DB: 
         Convert_GTID_Set: 
              GTID_Next: auto:1

4.3 从库配置（Slave）

sudo vi /etc/my.cnf.d/custom.cnf

[mysqld]
# ── 从库配置 ────────────────────────────────────────────
server-id = 2                       # 必须是唯一值！
log_bin = /var/log/mysql/mysql-bin
relay_log = /var/log/mysql/mysql-relay-bin
read_only = ON                      # 从库只读（但 root 仍可写）
super_read_only = ON                # 彻底禁止写入（包括 root）
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON              # 从库也记录 binlog（用于级联复制）
slave_parallel_workers = 8          # 并行复制线程数
slave_parallel_type = LOGICAL_CLOCK  # 基于逻辑时钟的并行复制

# 重启从库
sudo systemctl restart mysqld

# 配置主从复制（使用 GTID）
mysql -uroot -p -e "
STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='ReplSecureP@ss789!',
    MASTER_AUTO_POSITION=1;         # GTID 模式使用自动定位

START SLAVE;
"

# 查看从库状态
mysql -uroot -p -e "SHOW SLAVE STATUS\G"

关键状态指标：

Slave_IO_State: Waiting for source to send event
Slave_IO_Running: Yes              ← 必须为 Yes
Slave_SQL_Running: Yes            ← 必须为 Yes
Last_Error:                       ← 必须为空
Seconds_Behind_Master: 0          ← 从库延迟，0 表示无延迟
Executed_Gtid_Set:                ← GTID 执行记录
Auto_Position: 1

4.4 半同步复制（Semi-Sync Replication）

标准异步复制主库提交后立即返回，不等待从库确认。半同步复制确保至少一个从库收到并写入 relay log 后才返回。

# 主库安装半同步插件
mysql -uroot -p -e "
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 3000;  -- 3 秒超时
SHOW VARIABLES LIKE 'rpl_semi_sync%';
"

# 从库安装半同步插件
mysql -uroot -p -e "
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
STOP SLAVE;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
START SLAVE;
SHOW VARIABLES LIKE 'rpl_semi_sync%';
"

4.5 主主复制（双主）

# 节点 A 配置
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
auto_increment_offset = 1
auto_increment_increment = 2         # 两节点用不同步长，避免主键冲突

# 节点 B 配置
[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
auto_increment_offset = 2
auto_increment_increment = 2

五、性能优化

5.1 慢查询分析与优化

5.1.1 开启慢查询日志

# 查看慢查询配置
mysql -uroot -p -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'long_query_time';"

# 临时设置（立即生效，重启失效）
mysql -uroot -p -e "SET GLOBAL slow_query_log = 'ON';"
mysql -uroot -p -e "SET GLOBAL long_query_time = 1;"
mysql -uroot -p -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';"

5.1.2 使用 mysqldumpslow 分析慢日志

# 查看最慢的 10 条查询
sudo mysqldumpslow -t 10 /var/log/mysql/slow.log

# 查看最常见的查询（按次数）
sudo mysqldumpslow -t 10 -s c /var/log/mysql/slow.log

# 查看平均耗时最长的查询
sudo mysqldumpslow -t 10 -s at /var/log/mysql/slow.log

5.1.3 使用 EXPLAIN 分析查询

-- 基本分析
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 完整分析（MySQL 8.0+）
EXPLAIN ANALYZE SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
ORDER BY u.created_at DESC
LIMIT 20;

EXPLAIN 输出解读：

字段	说明	优化目标
`type`	连接类型	最好是 `const`/`eq_ref`，避免 `ALL`（全表扫描）
`key`	使用的索引	不为空
`rows`	扫描行数	越少越好
`Extra`	附加信息	避免 `Using filesort` / `Using temporary`

# 优化目标参考
type 优先级（从优到劣）：
const > eq_ref > ref > range > index > ALL

Extra 警告信号：
⚠️ Using filesort    → 需要额外排序，缺索引
⚠️ Using temporary    → 使用临时表，缺索引
⚠️ Using index        → 覆盖索引，性能好 ✅

5.2 索引优化

5.2.1 索引创建原则

-- ✅ 原则 1：WHERE 条件字段建索引
WHERE user_id = 100              →  CREATE INDEX idx_user_id ON orders(user_id)

-- ✅ 原则 2：JOIN 连接的字段建索引
ON orders.user_id = users.id     →  两边字段都建索引

-- ✅ 原则 3：ORDER BY 字段建索引（如查询有 WHERE 条件，可联合索引）
ORDER BY created_at DESC          →  CREATE INDEX idx_created_at ON orders(created_at)

-- ✅ 原则 4：区分度高的字段（Cardinality 越高越好）
区分度高：user_id, email, phone_number
区分度低：status (只有 active/inactive), gender (M/F)

-- ❌ 避免在区分度低的字段上建索引

5.2.2 联合索引设计

-- 高频查询：WHERE status = 'active' AND created_at > '2024-01-01'
-- 最优联合索引：(status, created_at)
-- 字段顺序：等值查询字段优先，范围查询字段放最后

CREATE INDEX idx_status_created ON orders(status, created_at);

-- 高频查询：WHERE user_id = 100 ORDER BY created_at DESC
-- 最优联合索引：(user_id, created_at)
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

5.2.3 查看索引使用情况

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

-- 分析索引使用情况（MySQL 8.0+）
SELECT
    object_schema,
    object_name,
    index_name,
    cardinality,
    statics_enabled,
    statics_update
FROM performance_schema.table_statistics
WHERE object_schema = 'myapp_db';

-- 查看未使用索引
SELECT
    object_schema,
    object_name,
    index_name,
    cardinality
FROM mysql.innodb_index_stats
WHERE stat_modified > DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND index_name != 'PRIMARY';

5.3 SQL 优化技巧

5.3.1 分页优化

-- ❌ 低效：OFFSET 越大越慢（MySQL 需要扫描前 10000 行再返回 10 行）
SELECT * FROM orders ORDER BY id LIMIT 10000, 10;

-- ✅ 优化 1：基于上一页最后一条 ID
SELECT * FROM orders
WHERE id > 10000
ORDER BY id
LIMIT 10;

-- ✅ 优化 2：使用子查询定位起始位置
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 10000, 1
)
ORDER BY id
LIMIT 10;

5.3.2 COUNT 优化

-- ❌ 低效：全表 COUNT
SELECT COUNT(*) FROM orders;  -- 每次都扫描全表

-- ✅ 优化 1：使用索引覆盖
SELECT COUNT(*) FROM orders USE INDEX (idx_status);
-- 前提：idx_status 在 status 字段上

-- ✅ 优化 2：业务缓存（高并发场景用 Redis 缓存计数值）
-- SELECT COUNT(*) FROM orders → Redis GET order:count

5.3.3 批量插入优化

-- ❌ 低效：逐条插入（N 次网络往返）
INSERT INTO orders (user_id, amount) VALUES (1, 100);
INSERT INTO orders (user_id, amount) VALUES (2, 200);
INSERT INTO orders (user_id, amount) VALUES (3, 300);

-- ✅ 优化：批量插入（1 次网络往返）
INSERT INTO orders (user_id, amount) VALUES
    (1, 100),
    (2, 200),
    (3, 300);

-- ✅ 优化 2：事务批量提交
START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
INSERT INTO orders (user_id, amount) VALUES (2, 200);
-- ... 更多 INSERT
COMMIT;

5.4 InnoDB 优化

5.4.1 Buffer Pool 优化

Buffer Pool 是 InnoDB 的核心内存区域，用于缓存表数据和索引。

-- 查看 Buffer Pool 状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

参数	说明	推荐值
`innodb_buffer_pool_size`	缓存池大小	总内存的 60-70%
`innodb_buffer_pool_instances`	缓存池分区数	CPU 核心数（最大 16）
`innodb_buffer_pool_load_at_startup`	启动时预热	ON
`innodb_old_blocks_pct`	旧区域占比	默认 37%
`innodb_page_cleaners`	页面清理线程数	CPU 核心数

# /etc/my.cnf.d/custom.cnf
innodb_buffer_pool_size = 12G           # 生产环境推荐 12G+
innodb_buffer_pool_instances = 8         # 8 个分区实例
innodb_buffer_pool_load_at_startup = ON
innodb_page_cleaners = 4

-- 动态调整 Buffer Pool（MySQL 8.0+）
SET GLOBAL innodb_buffer_pool_size = 16 * 1024 * 1024 * 1024;  -- 16GB

-- 查看当前大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 预热（重启后立即加载热点数据）
-- 方式 1：设置启动时加载
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;

-- 方式 2：手动预热（MySQL 8.0+）
SELECT SLEEP(5);
CALL innodb_buffer_pool_load_now();

5.4.2 Redo Log 优化

# /etc/my.cnf.d/custom.cnf
innodb_log_file_size = 2G               # 建议 1-4G
innodb_log_files_in_group = 3          # 建议 2-4 个
innodb_log_buffer_size = 64M            # 默认 16M，高并发可增大

Redo Log 大小计算：

观察 Innodb_os_log_written 估算日志写入量
计算公式：innodb_log_file_size × innodb_log_files_in_group > 1 小时的日志量

-- 查看当前 Redo Log 写入速率
SHOW STATUS LIKE 'Innodb_os_log_written';

-- 查看未刷新到磁盘的日志量
SHOW STATUS LIKE 'Innodb_log_unflushed%';

六、备份与恢复

6.1 全量备份（mysqldump）

# 创建备份目录
sudo mkdir -p /backup/mysql
sudo chmod 750 /backup/mysql

# 全量备份（单库）
mysqldump -uroot -p'MySecureP@ss123!' \
    --single-transaction \             # 使用快照备份（InnoDB 推荐）
    --routines \                       # 备份存储过程和函数
    --triggers \                       # 备份触发器
    --events \                         # 备份事件调度器
    --master-data=2 \                  # 记录备份时的 Binlog 位置
    --flush-logs \                     # 刷新日志
    myapp_db > /backup/mysql/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# 全量备份（所有数据库）
mysqldump -uroot -p'MySecureP@ss123!' \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --master-data=2 \
    --flush-logs > /backup/mysql/full_backup_$(date +%Y%m%d).sql

# 查看备份文件
ls -lh /backup/mysql/

6.2 增量备份（Binlog）

# 查找当前 Binlog 文件和位置
mysql -uroot -p'MySecureP@ss123!' -e "SHOW MASTER STATUS;" | awk 'NR==2 {print $1, $2}'

# 备份 Binlog（增量备份）
sudo mysqlbinlog --read-binary-log \
    --host=localhost \
    --to-last-log \
    --result-file=/backup/mysql/incrementals/ \
    mysql-bin.000001

# 配合 cron 实现每日增量备份
sudo crontab -e

# 每天凌晨 2 点增量备份
0 2 * * * mysqladmin -uroot -p'MySecureP@ss123!' flush-logs

# 每天凌晨 2:30 备份 Binlog
30 2 * * * mysqlbinlog --read-binary-log --host=localhost \
    --result-file=/backup/mysql/incrementals/ mysql-bin.0000*

# 每周日凌晨 3 点全量备份
0 3 * * 0 mysqldump -uroot -p'MySecureP@ss123!' \
    --single-transaction --all-databases > /backup/mysql/full_$(date +%Y%m%d).sql

6.3 物理备份（XtraBackup）

XtraBackup 是 Percona 提供的在线热备份工具，支持不锁表的物理备份，性能远超 mysqldump。

# 安装 Percona XtraBackup（CentOS/Rocky）
sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install -y percona-xtrabackup-80

# Ubuntu
sudo apt install -y percona-xtrabackup-80

# 全量物理备份
sudo innobackupex \
    --user=root \
    --password='MySecureP@ss123!' \
    --parallel=4 \
    /backup/mysql/physical/

# 查看备份
ls /backup/mysql/physical/

6.4 数据恢复

6.4.1 恢复全量备份

# 恢复（MySQL 服务需要先停止）
sudo systemctl stop mysqld

# 清理旧数据目录（谨慎！）
sudo rm -rf /data/mysql/*

# 解压备份到数据目录
sudo innobackupex --apply-log /backup/mysql/physical/2024-05-16_02-00-00/
sudo innobackupex --copy-back /backup/mysql/physical/2024-05-16_02-00-00/

# 修复权限并启动
sudo chown -R mysql:mysql /data/mysql
sudo systemctl start mysqld

6.4.2 恢复单个数据库（mysqldump）

# 恢复单库
mysql -uroot -p'MySecureP@ss123!' myapp_db < /backup/mysql/myapp_db_20240516.sql

# 恢复所有数据库
mysql -uroot -p'MySecureP@ss123!' < /backup/mysql/full_backup_20240516.sql

# 从全量备份中恢复单个数据库（不覆盖其他库）
mysql -uroot -p'MySecureP@ss123!' -e "
CREATE DATABASE IF NOT EXISTS myapp_db;
" && mysql -uroot -p'MySecureP@ss123!' myapp_db < /backup/mysql/myapp_db_20240516.sql

6.4.3 基于 Binlog 恢复（时间点恢复 PITR）

# 场景：误删数据，需要恢复到 2024-05-16 10:30:00 之前的数据

# Step 1：先恢复全量备份
mysql -uroot -p'MySecureP@ss123!' myapp_db < /backup/mysql/myapp_db_20240515.sql

# Step 2：找到 Binlog 中的误操作位置
mysqlbinlog \
    --start-datetime="2024-05-15 00:00:00" \
    --stop-datetime="2024-05-16 10:30:00" \
    /var/log/mysql/mysql-bin.000001 | grep -i "DROP TABLE\|DELETE FROM" | head -20

# Step 3：恢复到指定时间点（跳过误操作）
mysqlbinlog \
    --stop-datetime="2024-05-16 10:29:00" \
    /var/log/mysql/mysql-bin.000001 | mysql -uroot -p'MySecureP@ss123!'

# Step 4：验证数据
mysql -uroot -p'MySecureP@ss123!' -e "SELECT COUNT(*) FROM myapp_db.users;"

七、安全加固

7.1 网络访问控制

-- 查看当前用户允许的登录主机
SELECT user, host FROM mysql.user;

-- 限制只能从特定 IP 登录
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'AppSecureP@ss!';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'app'@'192.168.1.%';

-- 限制只能从本地登录（Web 服务器）
CREATE USER 'app'@'localhost' IDENTIFIED BY 'AppLocalP@ss!';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'app'@'localhost';

-- 删除不安全的账户
DROP USER 'root'@'%';           -- 只允许本地登录 root
DROP USER 'app'@'%';            -- 删除允许任意主机登录的账户
FLUSH PRIVILEGES;

7.2 密码策略与复杂度

-- MySQL 8.0 密码验证插件（默认已启用）
-- 设置密码复杂度要求
SET GLOBAL validate_password.policy = 'STRONG';    -- STRONG 要求：大写+小写+数字+特殊字符
SET GLOBAL validate_password.length = 12;         -- 最小 12 位

-- 查看当前密码策略
SHOW VARIABLES LIKE 'validate_password%';

7.3 权限最小化原则

-- ❌ 错误：授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';

-- ✅ 正确：按需授予最小权限
-- 应用账户：只需增删改查
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app'@'%';

-- 备份账户：需要读所有表
GRANT SELECT, LOCK TABLES, RELOAD ON *.* TO 'backup'@'localhost';

-- DDL 管理员账户：需要创建/修改表结构
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON myapp_db.* TO 'dba'@'localhost';

-- 只读账户：仅查询
GRANT SELECT ON myapp_db.* TO 'readonly'@'%';

7.4 审计日志配置

# MySQL Enterprise Edition 提供审计日志插件
# MySQL Community 可使用 MariaDB Audit Plugin 或 Percona Audit Log Plugin

# Percona Audit Log Plugin（推荐）
sudo yum install -y percona-server-rocksdb-80  # 或 percona-server

# /etc/my.cnf.d/audit.cnf
[mysqld]
plugin-load-add = audit_log.so
audit_log = FILE
audit_log_file = /var/log/mysql/audit.log
audit_log_policy = LOG_ALL                   # 记录所有操作
audit_log_rotate_on_size = 100M
audit_log_max_size = 1G
audit_log_exclude_accounts = mysql.session@localhost  # 排除系统账户

7.5 加密连接（SSL/TLS）

# MySQL 8.0 默认使用 TLS 1.2+
# 检查是否启用了加密
mysql -uroot -p -e "SHOW VARIABLES LIKE '%ssl%';"

# 配置强制使用加密连接
sudo vi /etc/my.cnf.d/custom.cnf

[mysqld]
require_secure_transport = ON                # 强制 SSL 连接
ssl_ca = /etc/mysql/certs/ca.pem
ssl_cert = /etc/mysql/certs/server-cert.pem
ssl_key = /etc/mysql/certs/server-key.pem

# 应用连接时使用 SSL
mysql -uroot -p --ssl-mode=REQUIRED

# Java JDBC 连接字符串
jdbc:mysql://host:3306/db?useSSL=true&requireSSL=true

八、高可用方案

8.1 MHA（MySQL High Availability Manager）

┌──────────────┐         ┌──────────────┐         ┌──────────────┐
│   Master     │◄────────│     MHA     │────────►│   Slave 1    │
│   (Primary)  │  Binlog │   Manager   │  SSH    │  (Candidate) │
└──────────────┘  Fetch   └──────────────┘         └──────────────┘
       │                                                      │
       │           ┌──────────────┐                          │
       └──────────►│   Slave 2    │◄─────────────────────────┘
                   │   (Data)     │         Relay Log
                   └──────────────┘

8.2 MySQL Router + InnoDB Cluster

# MySQL InnoDB Cluster = MySQL Group Replication + MySQL Shell + MySQL Router

# 1. 在每个节点初始化集群
mysqlsh -- dba configure-instance --user=root --password='MySecureP@ss123!'
mysqlsh -- dba create-cluster my_cluster --member-weight=80

# 2. 添加其他节点
mysqlsh -- root@node2:3306
mysqlsh> cluster.addInstance('root@node3:3306')

# 3. 配置 MySQL Router
mysqlrouter --bootstrap root@localhost:3306 \
    --user=mysqlrouter \
    --directory /var/lib/mysqlrouter

# 4. 启动 Router
sudo systemctl restart mysqlrouter
sudo systemctl enable mysqlrouter

# 5. 应用通过 Router 连接（自动负载均衡/故障转移）
mysql -h 127.0.0.1 -P 6446 -u root -p    # 读写端口（Primary）
mysql -h 127.0.0.1 -P 6447 -u root -p    # 只读端口（Secondary）

九、常见问题排错

❌ 问题 1：MySQL 无法启动

# 排查步骤
# 1. 查看错误日志
sudo tail -100 /var/log/mysql/error.log

# 2. 常见原因及解决

# 原因 A：数据目录权限错误
sudo chown -R mysql:mysql /data/mysql
sudo chmod 755 /data/mysql

# 原因 B：端口被占用
sudo ss -tlnp | grep 3306
# 杀掉占用进程或修改 my.cnf 中 port

# 原因 C：Redo Log 文件损坏
# 解决方案：删除 Redo Log 并重启（最后手段！）
sudo mv /var/log/mysql/ib_logfile0 /var/log/mysql/ib_logfile0.bak
sudo mv /var/log/mysql/ib_logfile1 /var/log/mysql/ib_logfile1.bak
sudo systemctl start mysqld

# 原因 D：ibdata1 文件损坏
# 需要从备份恢复

❌ 问题 2：从库复制延迟严重

-- 查看从库状态
SHOW SLAVE STATUS\G

-- 常见原因 1：主从网络延迟
-- 解决：检查网络或升级带宽

-- 常见原因 2：从库负载过高
-- 解决：增加 slave_parallel_workers
SET GLOBAL slave_parallel_workers = 16;

-- 常见原因 3：主库写入过快，从库来不及重放
-- 解决：调整 innodb_flush_log_at_trx_commit 和 sync_binlog
SET GLOBAL sync_binlog = 100;     -- 每 100 个事务同步一次
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- 常见原因 4：缺少索引导致查询慢
SHOW SLAVE STATUS\G
-- 查看 Last_SQL_Error
-- 在主库添加索引后，从库会自动应用

❌ 问题 3：连接数过多导致 “Too many connections”

-- 查看当前连接数
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

-- 临时解决：杀掉空闲连接
SHOW PROCESSLIST;
KILL <process_id>;

-- 永久解决：调整 max_connections
SET GLOBAL max_connections = 3000;

# 查看最大使用连接数（评估是否需要扩容）
mysql -uroot -p -e "
SELECT MAX_CONNECTIONS - (SELECT @@max_connections) AS '可用余量',
       MAX_USED_CONNECTIONS AS '历史最大使用'
FROM (SELECT MAX(1) AS MAX_CONNECTIONS) vars
CROSS JOIN performance_schema.global_status
WHERE VARIABLE_NAME = 'Max_used_connections';
"

❌ 问题 4：查询突然变慢

# 1. 查看当前慢查询
mysql -uroot -p -e "SHOW PROCESSLIST;"

# 2. 分析锁等待
SELECT
    r.trx_id,
    r.trx_state,
    r.trx_mysql_thread_id,
    r.trx_started,
    p.user,
    p.host,
    p.command,
    a.sql_text
FROM information_schema.INNODB_TRX r
LEFT JOIN information_schema.PROCESSLIST p ON r.trx_mysql_thread_id = p.id
LEFT JOIN performance_schema.threads t ON p.id = t.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current a ON t.THREAD_ID = a.THREAD_ID;

# 3. 查看表锁
SHOW OPEN TABLES WHERE In_use > 0;

# 4. 常见原因：
#    A. 缺少索引 → 添加索引
#    B. 表碎片 → OPTIMIZE TABLE mytable
#    C. 统计信息过时 → ANALYZE TABLE mytable
#    D. 锁争用 → 优化事务/使用更低隔离级别

❌ 问题 5：InnoDB 表空间膨胀

-- 查看表大小
SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    ROUND(data_length / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引大小(MB)',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)'
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- 清理碎片
OPTIMIZE TABLE myapp_db.large_table;

-- 清理 InnoDB undo log（MySQL 8.0+）
SET GLOBAL innodb_undo_log_truncate = ON;

❌ 问题 6：字符集乱码

-- 检查字符集配置
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- 常见原因：客户端与服务端字符集不一致
-- 解决：在 my.cnf 中统一设置

-- 修改现有表的字符集
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 导出时指定字符集
mysqldump -uroot -p --default-character-set=utf8mb4 myapp_db > backup.sql

十、监控与诊断

10.1 关键监控指标

-- 连接与并发
SHOW STATUS LIKE 'Threads_connected';      -- 当前连接数
SHOW STATUS LIKE 'Threads_running';        -- 当前运行查询的线程数
SHOW STATUS LIKE 'Max_used_connections';   -- 历史最大连接数
SHOW STATUS LIKE 'Aborted_connects';       -- 失败连接数

-- 查询性能
SHOW STATUS LIKE 'Slow_queries';           -- 慢查询总数
SHOW STATUS LIKE 'Qcache_hits';            -- 查询缓存命中（MySQL 5.7）
SHOW STATUS LIKE 'Innodb_buffer_pool_hits'; -- Buffer Pool 命中率

-- 事务与锁
SHOW STATUS LIKE 'Innodb_row_lock_waits';  -- 行锁等待次数
SHOW STATUS LIKE 'Innodb_deadlocks';        -- 死锁次数
SHOW ENGINE INNODB STATUS\G                 -- 引擎详细状态（含死锁信息）

-- 复制健康
SHOW SLAVE STATUS\G                        -- 从库状态
SHOW MASTER STATUS\G                        -- 主库状态
SHOW PROCESSLIST;                           -- 所有连接

10.2 Prometheus + MySQL Exporter 监控

# 安装 mysqld_exporter
curl -LO https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/

# 创建监控账户
mysql -uroot -p -e "
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'ExporterP@ss123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
"

# 配置 mysqld_exporter
sudo vi /etc/mysql/exporter.env

DATA_SOURCE_NAME=exporter:ExporterP@ss123!@localhost:3306

# 启动 exporter
sudo nohup /usr/local/bin/mysqld_exporter \
    --config.my-cnf=/etc/mysql/exporter.env \
    --web.listen-address=:9104 \
    > /var/log/mysql/exporter.log 2>&1 &

# 验证
curl http://localhost:9104/metrics | head -20

10.3 pt-query-digest 深度分析

# 安装 Percona Toolkit
sudo yum install -y percona-toolkit

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

# 分析实时查询（每 5 秒采样）
pt-query-digest --processlist h=localhost,u=root,p='MySecureP@ss123!' \
    --watch-time=10s --interval=0.5

# 分析 SHOW GLOBAL STATUS
pt-mysql-summary --user=root --password='MySecureP@ss123!'

附录：生产环境检查清单

✅ 安装配置
    ☐ MySQL 8.0 已安装，版本最新稳定版
    ☐ innodb_buffer_pool_size 已根据内存配置（60-70% 总内存）
    ☐ innodb_log_file_size 已配置（建议 1-4GB）
    ☐ 数据目录与日志目录分离到不同磁盘

✅ 账户安全
    ☐ root 账户仅允许 localhost 登录
    ☐ 应用使用独立账户，密码满足复杂度要求
    ☐ 匿名账户已删除
    ☐ 密码过期策略已设置（90天）

✅ 备份恢复
    ☐ 每日全量备份已配置（mysqldump 或 XtraBackup）
    ☐ Binlog 增量备份已配置
    ☐ 备份保留策略已设置（建议保留 7-30 天）
    ☐ 备份恢复已测试演练

✅ 主从复制
    ☐ GTID 模式已启用
    ☐ 从库配置 super_read_only = ON
    ☐ 复制账户已创建，权限正确
    ☐ 定期检查 SLAVE STATUS，无延迟/错误

✅ 性能优化
    ☐ 慢查询日志已开启（long_query_time = 2）
    ☐ 关键表已建立合理索引
    ☐ 定期执行 ANALYZE TABLE 更新统计信息

✅ 监控告警
    ☐ MySQL Exporter + Prometheus 监控已部署
    ☐ 关键告警已配置（连接数/复制延迟/Binlog 空间）
    ☐ Grafana 仪表盘已导入

文章版权归作者所有，未经允许请勿转载。

THE END

数据库专题运维专题 IT技术运维技术
# 运维 # MySQL # 数据库 # 性能优化 # 主从复制

MySQL 数据库部署与性能优化