LNMP环境中MySQL数据库备份方法

在LNMP(Linux+Nginx+MySQL+PHP)环境中，MySQL数据库备份是系统维护的重要工作。以下是几种常用的备份方法：

1. 使用mysqldump工具备份

mysqldump是MySQL自带的逻辑备份工具，适合中小型数据库。

基本备份命令

mysqldump -u用户名 -p密码 数据库名 > 备份文件名.sql

常用选项

• --all-databases 备份所有数据库
• --single-transaction 对InnoDB表进行一致性备份(不锁表)
• --routines 包含存储过程和函数
• --events 包含事件
• --triggers 包含触发器
• --skip-lock-tables 不锁表(适用于MyISAM)

完整备份示例

mysqldump -uroot -p --single-transaction --routines --events --triggers --all-databases > full_backup_$(date +%Y%m%d).sql

2. 自动备份脚本

创建自动备份脚本并加入crontab定时任务：

#!/bin/bash
# 备份目录
BACKUP_DIR="/data/backups/mysql"
# MySQL用户名密码
MYSQL_USER="root"
MYSQL_PASSWORD="yourpassword"
# 保留天数
DAYS_TO_KEEP=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份文件名
BACKUP_FILE="$BACKUP_DIR/full_backup_$(date +%Y%m%d_%H%M%S).sql.gz"

# 执行备份
mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --single-transaction --routines --events --triggers --all-databases | gzip > $BACKUP_FILE

# 删除旧备份
find $BACKUP_DIR -name "full_backup_*.sql.gz" -type f -mtime +$DAYS_TO_KEEP -exec rm -f {} \;

添加到crontab每天凌晨执行：

0 3 * * * /path/to/backup_script.sh

3. 二进制日志备份(增量备份)

如果启用了二进制日志(binlog)，可以结合全量备份和binlog实现增量备份：

1. 在my.cnf中启用binlog

[mysqld]
log-bin=mysql-bin

2. 定期执行FLUSH LOGS命令轮转日志
3. 备份binlog文件

4. 使用Percona XtraBackup(物理备份)

对于大型数据库，Percona XtraBackup提供热备份方案：

安装

yum install percona-xtrabackup-24  # CentOS
apt-get install percona-xtrabackup-24  # Ubuntu

全量备份

innobackupex --user=root --password=yourpassword /path/to/backup

增量备份

innobackupex --user=root --password=yourpassword --incremental /path/to/incremental --incremental-basedir=/path/to/full_backup

5. 备份恢复方法

mysqldump备份恢复

mysql -u用户名 -p密码 数据库名 < 备份文件.sql

XtraBackup恢复

innobackupex --apply-log /path/to/backup
innobackupex --copy-back /path/to/backup

最佳实践建议

1. 定期测试备份文件的恢复过程
2. 将备份文件存储在异地或云存储中
3. 对备份文件进行加密(特别是敏感数据)
4. 监控备份作业的执行情况
5. 根据数据重要性设置适当的备份频率
6. 记录备份和恢复的操作流程

以上方法可以根据实际业务需求和数据库规模选择使用或组合使用。