LNMP环境中MySQL数据库的备份与恢复指南

一、MySQL备份方法

1. 使用mysqldump工具（推荐）

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

# 备份所有数据库
mysqldump -u用户名 -p密码 --all-databases > 全量备份.sql

# 示例（备份名为mydb的数据库）
mysqldump -uroot -p123456 mydb > /backup/mydb_$(date +%Y%m%d).sql

2. 直接复制数据文件（适用于MyISAM存储引擎）

# 停止MySQL服务
service mysql stop

# 复制数据目录
cp -R /usr/local/mysql/data /backup/mysql_data_backup

# 启动MySQL服务
service mysql start

3. 使用mysqlhotcopy（仅限MyISAM）

mysqlhotcopy -u用户名 -p密码 数据库名 /backup/path/

4. 自动化备份脚本

#!/bin/bash
# 定义变量
DB_USER="root"
DB_PASS="yourpassword"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)

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

# 获取所有数据库列表
DATABASES=$(mysql -u$DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)")

# 循环备份每个数据库
for db in $DATABASES
do
  mysqldump -u$DB_USER -p$DB_PASS --databases $db > $BACKUP_DIR/$DATE/$db.sql
done

# 压缩备份文件
tar -zcvf $BACKUP_DIR/mysql_backup_$DATE.tar.gz $BACKUP_DIR/$DATE

# 删除临时目录
rm -rf $BACKUP_DIR/$DATE

# 删除7天前的备份
find $BACKUP_DIR -name "mysql_backup_*.tar.gz" -type f -mtime +7 -exec rm -f {} \;

二、MySQL恢复方法

1. 使用mysql命令恢复

# 恢复单个数据库
mysql -u用户名 -p密码 数据库名 < 备份文件.sql

# 恢复所有数据库
mysql -u用户名 -p密码 < 全量备份.sql

# 示例
mysql -uroot -p123456 mydb < /backup/mydb_20230101.sql

2. 恢复直接复制的数据文件

# 停止MySQL服务
service mysql stop

# 恢复数据目录
cp -R /backup/mysql_data_backup /usr/local/mysql/data

# 启动MySQL服务
service mysql start

3. 恢复部分表

# 从完整备份中提取特定表的SQL语句
sed -n '/^-- Table structure for table `表名`/,/^-- Table structure for table/p' 备份文件.sql > 表名.sql

# 然后导入该表
mysql -u用户名 -p密码 数据库名 < 表名.sql

三、备份策略建议

1. 定期全量备份：每周一次完整备份
2. 每日增量备份：使用binlog进行增量备份
3. 异地备份：将备份文件复制到其他服务器或云存储
4. 备份验证：定期测试备份文件的可恢复性

四、binlog增量备份与恢复

1. 启用binlog

在my.cnf配置文件中添加：

[mysqld]
log-bin=mysql-bin
binlog-format=ROW

2. 查看binlog文件

mysql -uroot -p -e "SHOW BINARY LOGS;"

3. 使用binlog恢复

# 恢复到指定时间点
mysqlbinlog --start-datetime="2023-01-01 00:00:00" --stop-datetime="2023-01-01 23:59:59" mysql-bin.000001 | mysql -u用户名 -p密码

# 恢复到指定位置
mysqlbinlog --start-position=107 --stop-position=1000 mysql-bin.000001 | mysql -u用户名 -p密码

五、注意事项

1. 备份时确保有足够的磁盘空间
2. 大型数据库备份可能会影响性能，建议在低峰期进行
3. 恢复前最好先备份当前数据，以防意外
4. 定期检查备份文件的完整性
5. 重要数据建议采用多种备份方式并存

通过以上方法，您可以在LNMP环境中有效地备份和恢复MySQL数据库，确保数据安全。