MySQL 数据库日常运维实战——备份、优化与故障处理

作为 IDC 运维人员，数据库运维是重中之重。MySQL 作为最流行的开源关系型数据库，每天都要面对备份、优化、故障恢复等任务。

<h2>一、数据库备份策略</h2>

<h3>1. mysqldump 逻辑备份</h3>

<pre><code># 备份单个数据库
mysqldump -u root -p database_name > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 压缩备份（推荐）
mysqldump -u root -p database_name | gzip > backup_$(date +%Y%m%d).sql.gz
</code></pre>

<h3>2. XtraBackup 物理备份（适合大库）</h3>

<pre><code># 全量备份
xtrabackup --backup --target-dir=/backup/base/

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1/ \
--incremental-basedir=/backup/base/
</code></pre>

<h3>3. 自动备份脚本</h3>

<pre><code>#!/bin/bash
DB_USER="root"
DB_PASS="your_password"
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

mysqldump -u$DB_USER -p$DB_PASS \
--all-databases --single-transaction \
--routines --triggers --events | gzip \
> $BACKUP_DIR/full_$DATE.sql.gz

# 删除 7 天前的旧备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
</code></pre>

配合 crontab：<code>0 3 * * * /usr/local/bin/mysql_backup.sh</code>

<h3>1. 慢查询日志</h3>

<pre><code>SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# 分析慢查询
mysqldumpslow /var/log/mysql/slow.log
</code></pre>

<h3>2. my.cnf 优化（4GB 内存服务器参考）</h3>

<pre><code>[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
max_connections = 500
tmp_table_size = 64M
</code></pre>

<h2>三、常见故障处理</h2>

<h3>1. Too many connections</h3>

<pre><code># 紧急处理
mysql -u root -p -e "SHOW FULL PROCESSLIST\G" | grep Sleep

# 临时增加连接数
mysql -u root -p -e "SET GLOBAL max_connections = 1000;"

# 看看到底有多少连接
netstat -anp | grep 3306 | wc -l
</code></pre>

<h3>2. 磁盘空间满（binlog 是杀手）</h3>

<pre><code># 查看 binlog 占用
ls -lh /var/lib/mysql/mysql-bin.*

# 清理 7 天前的 binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

# my.cnf 限制
expire_logs_days = 7
max_binlog_size = 100M
</code></pre>

<h3>3. 表损坏修复</h3>

<pre><code>CHECK TABLE table_name;
REPAIR TABLE table_name;

# 命令行批量修复
mysqlcheck -u root -p --auto-repair --all-databases
</code></pre>

<h2>四、总结</h2>
数据库运维核心三件事：<strong>备份要自动化、查询要监控、空间要预警</strong>。做好这三样，99% 的数据库问题都能在酿成大祸之前被发现并解决。

顺带一提：我们自研了一款免费的[七亿批量远程管理工具](https://blog.7ehl.com/index.php/archives/3/)，Windows 端，支持 RDP 九宫格、SSH 终端、SFTP/FTP，一台电脑批量管多台服务器。

评论