跳到主要内容

MySQL 运维

主从复制​

主库配置 /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW           # 推荐 ROW 格式
gtid_mode = ON                # 启用 GTID
enforce_gtid_consistency = ON
sync_binlog = 1               # 每次事务刷盘
主库创建复制账号
CREATE USER 'repl'@'10.0.%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';
从库配置复制
CHANGE MASTER TO
    MASTER_HOST='10.0.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='strong_password',
    MASTER_AUTO_POSITION=1;    -- GTID 模式

START SLAVE;
SHOW SLAVE STATUS\G            -- 检查 Seconds_Behind_Master

备份与恢复​

方式工具特点
逻辑备份mysqldump可读 SQL，慢，适合小库
物理备份xtrabackup热备，快速，适合大库
Binlog 备份mysqlbinlog增量备份，PITR
xtrabackup 全量备份 + 恢复
# 全量备份
xtrabackup --backup --target-dir=/backup/full \
    --user=root --password=xxx

# 准备恢复（应用 redo log）
xtrabackup --prepare --target-dir=/backup/full

# 恢复（停止 MySQL 后）
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
PITR 恢复（时间点恢复）
# 先恢复全量备份，再回放 binlog 到指定时间点
mysqlbinlog --stop-datetime="2024-01-15 10:30:00" \
    /var/log/mysql/mysql-bin.000042 | mysql -u root -p

慢查询优化​

开启慢查询日志
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1            # 超过 1 秒记录
log_queries_not_using_indexes = ON
# 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# pt-query-digest 更详细的分析
pt-query-digest /var/log/mysql/slow.log --limit=20

关键参数调优​

参数推荐值说明
innodb_buffer_pool_size物理内存 60-80%InnoDB 缓冲池，最重要的参数
innodb_log_file_size1-2GBredo log 大小
max_connections按需，200-1000最大连接数
innodb_flush_log_at_trx_commit1（安全）或 2（性能）事务日志刷盘策略
sync_binlog1binlog 刷盘策略
innodb_io_capacity按磁盘性能，SSD 可设 2000+IO 吞吐量

常见面试问题​

Q1: 主从复制延迟怎么解决？​

答案：

  1. 并行复制：slave_parallel_workers = 8，slave_parallel_type = LOGICAL_CLOCK 开启多线程回放
  2. 优化大事务：拆分批量 UPDATE/DELETE，避免单事务过大
  3. 升级硬件：从库用 SSD，提升 IO 能力
  4. 监控：Seconds_Behind_Master + Prometheus 告警

Q2: innodb_flush_log_at_trx_commit 各值的区别？​

答案：

值行为数据安全性能
1每次事务提交刷盘最安全最慢
2每次事务写入 OS 缓存，每秒刷盘掉电丢 1 秒较快
0每秒写入 + 刷盘掉电丢 1 秒最快

生产环境一般设为 1（金融等高可靠场景）或 2（普通业务，性能优先）。

相关链接​