MySQL备份数据库，mysqldump定时备份

前言

数据库不备份，出事就完蛋。本文讲用mysqldump备份MySQL，再配crontab定时自动备份。

一、mysqldump基本用法

备份单个数据库

mysqldump -u root -p mydb > /backup/mydb.sql

输入密码后，就把mydb库备份到mydb.sql了。

备份多个数据库

mysqldump -u root -p --databases db1 db2 > /backup/mydbs.sql

备份所有数据库

mysqldump -u root -p --all-databases > /backup/all.sql

备份单个表

mysqldump -u root -p mydb users > /backup/users.sql

二、常用参数

参数	说明
--single-transaction	InnoDB一致性备份，不锁表
--routines	备份存储过程和函数
--triggers	备份触发器
--events	备份事件调度器
--master-data=2	记录binlog位置，用于主从
--default-character-set=utf8mb4	指定字符集

推荐完整命令

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  mydb > /backup/mydb_$(date +%Y%m%d).sql

三、压缩备份

备份完太大，直接压缩：

mysqldump -u root -p mydb | gzip > /backup/mydb.sql.gz

恢复的时候：

gunzip < /backup/mydb.sql.gz | mysql -u root -p mydb

四、定时备份脚本

创建备份脚本：

vi /usr/local/bin/mysql_backup.sh

内容：

#!/bin/bash

# 备份目录
BACKUP_DIR=/backup/mysql

# 数据库配置
DB_USER=root
DB_PASS=你的密码
DB_NAME=mydb

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

# 备份文件名（带日期）
DATE=$(date +%Y%m%d_%H%M%S)
FILE=$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 执行备份
mysqldump -u$DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --default-character-set=utf8mb4 \
  $DB_NAME | gzip > $FILE

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "备份完成：$FILE"

加执行权限：

chmod +x /usr/local/bin/mysql_backup.sh

五、配置crontab定时任务

编辑crontab：

crontab -e

加上：

# 每天凌晨2点备份
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

六、恢复备份

恢复普通备份

mysql -u root -p mydb < /backup/mydb.sql

恢复压缩备份

gunzip < /backup/mydb.sql.gz | mysql -u root -p mydb

常见坑

坑1：密码写在命令行里不安全

mysqldump -u root -p123456 mydb > xxx.sql

这样密码会出现在进程列表里。

安全做法： 用配置文件存密码。

# /root/.my.cnf
[mysqldump]
user=root
password=你的密码

权限设600：

chmod 600 /root/.my.cnf

以后备份就不用输密码了：

mysqldump mydb > xxx.sql

坑2：备份时锁表

大库备份的时候，业务卡住了。

原因： 没加 --single-transaction。

解决： InnoDB库一定要加这个参数。

坑3：备份文件是空的

备份完一看，文件0字节。

原因： 密码错了，或者数据库名错了。

解决： 先手动执行一次，看报错。

坑4：备份没包含存储过程

备份后恢复，发现存储过程没了。

原因： mysqldump默认不备份存储过程。

解决： 加 --routines 参数。

总结

三步搞定自动备份：

1. 写备份脚本
2. 配crontab定时执行
3. 定期清理旧备份

记住： 备份了不算数，能恢复才算数。

遇到问题加QQ23979811 协助处理