MySQL定时备份（全量备份+增量备份）

说明

产品上线后，数据非常非常重要，万一哪天数据被误删，那么就gg了，准备跑路吧。
所以要对线上的数据库定时做全量备份。

增量备份的优点是没有重复数据，备份量不大，时间短。但缺点也很明显，需要建立在上次完全备份及完全备份之后所有的增量才能恢复。

MySQL没有提供直接的增量备份方法，但是可以通过mysql二进制日志间接实现增量备份。二进制日志对备份的意义如下：

• 二进制日志保存了所有更新或者可能更新数据的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到所设大小或者收到flush logs 命令后重新创建新的日志文件
• 只需定时执行flush logs 方法重新创建新的日志，生成二进制文件序列，并及时把这些文件保存到一个安全的地方，即完成了一个时间段的增量备份。

一、MySQL全量备份

mysqldump -u root -p --all-databases -q -E --flush-logs --single-transaction > test.sql

• 参数 --lock-all-tables

对于InnoDB将替换为 --single-transaction。
该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 --quick 选项。

• 参数 --flush-logs，结束当前log_bin日志，生成并使用新日志文件
• 参数 --quick或-q，该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中
• 参数 --events, -E，导出事件
• 参数 --master-data=2，该选项将会在输出SQL中记录下完全备份后新日志文件的名称，用于日后恢复时参考，例如输出的备份SQL文件中含有：CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002', MASTER_LOG_POS=106;
• 参数 test，该处的test表示数据库test，如果想要将所有的数据库备份，可以换成参数 --all-databases,-A
• 参数 --databases 指定多个数据库
• 参数 --ignore-table，忽略某个数据表，如 --ignore-table test.user 忽略数据库test里的user表
• 参数 --flush-privileges，在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
• 参数 --delete-master-logs，在master备份后删除日志. 这个参数将自动激活--master-data。
• 更多mysqldump 参数，请参考网址

全量备份指定库的shell脚本

#!/bin/bash
# **********************************************************
# * Author        : Wangc
# * Email         : xxxxx@163.com
# * Create time   : 2022-02-11 10:47
# * Filename      : mysql_fullybak.sh
# * Release       : v1.5
# * Description   : MySQL全量备份，本脚本备份指定库
# **********************************************************
# 用户名、密码、数据库
user="root"
password="root"
dbName="datadb"
# 备份开始时间
beginTime=`date +"%Y年%m月%d日 %H:%M:%S"`
# 备份结束时间
endTime=`date +"%Y年%m月%d日 %H:%M:%S"`
# 全量备份目录，提前手动创建这个目录
bakDir=/data/mysql/backup
# 日志文件
logFile=/data/mysql/backup/fullybak.log
# 备份工具
mysqlDump=/usr/local/mysql/bin/mysqldump
# 备份时间
nowDate=`date +"%Y%m%d_%H%M%S"`
dumpFile="alldb_${nowDate}.sql"
gzDumpFile="alldb_${nowDate}.sql.tgz"

cd $bakDir
echo 全量备份开始:$beginTime >> $logFile
# 全量备份（--databases对指定数据库备份，--all-databases对所有数据库备份）
$mysqlDump -u${user} -p${password} --databases ${dbName} -E -q --flush-privileges --single-transaction > $dumpFile

# 判断备份是否成功
if [ $? -eq 0 ]; then
  # 将备份SQL打包
  /bin/tar -zvcf $gzDumpFile $dumpFile
  /bin/rm $dumpFile
  echo 全量备份结束:$endTime $gzDumpFile ! SUCCESS ! >> $logFile
  exit 0;
else
  echo 全量备份结束:$endTime $gzDumpFile ! ERROR ! >> $logFile
  exit 1;    
fi

#删除60天前的备份
find $bakDir -name "*.sql.tgz" -type f -mtime +60 -exec rm -rf {} \; > /dev/null 2>&1
exit 0;

这里全量备份只备份了一个数据库，因为如果所有数据库都备份的话，文件太大了。这里的取舍我也不是很清楚，毕竟自己还在学习阶段，没有实际的操作经验。

全量备份全库的shell脚本

#!/bin/bash
# **********************************************************
# * Author        : Wangc
# * Email         : xxxxx@163.com
# * Create time   : 2022-02-11 10:47
# * Filename      : mysql_fullybak.sh
# * Release       : v1.5
# * Description   : MySQL全量备份，本脚本备份所有库
# **********************************************************
# 用户名、密码
user="root"
password="root"
# 备份开始时间
beginTime=`date +"%Y年%m月%d日 %H:%M:%S"`
# 备份结束时间
endTime=`date +"%Y年%m月%d日 %H:%M:%S"`
# 全量备份目录，提前手动创建这个目录
bakDir=/data/mysql/backup
# 日志文件
logFile=/data/mysql/backup/fullybak.log
# 备份工具
mysqlDump=/usr/local/mysql/bin/mysqldump
# 备份时间
nowDate=`date +"%Y%m%d_%H%M%S"`
dumpFile="alldb_${nowDate}.sql"
gzDumpFile="alldb_${nowDate}.sql.tgz"

cd $bakDir
echo 全量备份开始:$beginTime >> $logFile
# 全量备份（--databases对指定数据库备份，--all-databases对所有数据库备份）
$mysqlDump -u${user} -p${password} -A -E -q --flush-privileges --single-transaction > $dumpFile

# 判断备份是否成功
if [ $? -eq 0 ]; then
  # 将备份SQL打包
  /bin/tar -zvcf $gzDumpFile $dumpFile
  /bin/rm $dumpFile
  echo 全量备份结束:$endTime $gzDumpFile ! SUCCESS ! >> $logFile
  exit 0;
else
  echo 全量备份结束:$endTime $gzDumpFile ! ERROR ! >> $logFile
  exit 1;    
fi

#删除60天前的备份
find $bakDir -name "*.sql.tgz" -type f -mtime +60 -exec rm -rf {} \; > /dev/null 2>&1
exit 0;

这里全量备份了所有数据库。

备份时间安排
每天凌晨2点进行一次全量备份，保存在本地/data/mysql/backup。
定期检查备份的可用性和完整性，确保备份可以成功恢复数据。

注意，这只是一个示例计划，具体的备份时间安排应根据你的实际需求和数据库负载情况进行调整。

定时备份

执行命令 crontab -e，添加如下配置

# 在每天的第2小时（凌晨2点）执行脚本。
# `-x`选项用于在执行命令时显示详细的调试信息。
# `>/dev/null 2>&1`：将脚本的输出重定向到空设备（`/dev/null`），这意味着不保存脚本的输出结果。
0 2 * * * /bin/bash -x /data/mysql/backup/mysql_fullybak.sh >/dev/null 2>&1

就是通过设置 crontab 文件中的这五个*来确定任务的执行时间的， user-name 是执行任务的用户，command to be executed 是要执行的命令或者脚本任务，我们具体来看下这五个*所代表的含义。

其中，星号代表通配符，表示可以匹配任何值。每个星号代表不同的时间单位：

第一个星号表示分钟（0-59）

第二个星号表示小时（0-23）

第三个星号表示一个月中的某一天（1-31）

第四个星号表示月份（1-12或者用缩写，如1表示一月，2表示二月）

第五个星号表示星期几（0-7或者用缩写，0和7都表示星期日，1表示星期一，以此类推）

其实，除了*这个字符之外还有其他的特殊字符，用来满足不同的定时需求。

秒与分的范围是0-59，时范围是0-23，日期是1-31，月份是1-12，星期是0-6

其实它还支持月份与星期的英文缩写（其实如果是我自己写的话，估计星期范围就是1-7了，(*Φ皿Φ*)），月份 FEB,JAN,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC ，星期 TUE,WED,THU,FRI,SAT,SUN,MON

最后还支持两个通配符*?，一般推荐只用*表示任意就好了。

英文逗号,表示并列，比如1,2 * * * * * 每分钟1、2秒触发

英文减号-表示范围，比如13-17 * * * * * 每分钟13-17秒内反复触发（下次触发必须等上次触发处理完才会触发）

英文左斜杠/表示间隔，比如*/12 * * * * * 每12秒触发一次

也可以一起用，0 0 1-6/2 * * * 1、3、5小时整触发一次

二、MySQL增量备份

MySQL增量备份是一种备份策略，用于仅备份发生更改的数据和日志，以减少备份时间和存储空间的消耗。

在MySQL增量备份中，首先进行完全备份（Full Backup），将数据库的所有数据和日志备份到一个初始状态的快照中。然后，在每次备份之后，只备份自上次备份以来发生变化的数据和日志。

常见的MySQL增量备份方法有两种：

1. 二进制日志备份（Binary Log Backup）：MySQL服务器会记录所有对数据库的修改操作，并将这些操作以二进制日志的形式存储起来。为了进行增量备份，可以定期地备份这些二进制日志文件，并将其应用到最新的完全备份之上，从而还原数据库到最新状态。
2. 基于事务日志的备份（InnoDB Transaction Log Backup）：对于使用InnoDB存储引擎的MySQL数据库，可以备份事务日志（Transaction Log）。事务日志包含了对数据库表的插入、更新和删除等操作，可以通过备份事务日志并重放这些操作来实现增量备份的效果。

使用增量备份可以有效减少备份所需的时间和存储空间，特别是在大型数据库或高活跃性数据库中，当每天仅有少量的更改时，增量备份比完全备份更加高效和经济。但需要注意的是，在恢复数据时，需要依赖完整备份和增量备份的组合来还原数据库到特定时间点的状态。

1. 检查log_bin是否开启

进入mysql命令行，执行 show variables like '%log_bin%';

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.01 sec)

如上所示，log_bin 未开启；如果log_bin开启，则跳过第2步，直接进入第3步。

2. 开启 log_bin，并重启MySQL

• 编辑 mysql 的配置文件 vim /etc/my.cnf，在 mysqld 下面添加下面2条配置

[mysqld]
log_bin=/var/lib/mysql/mysql_bin
server_id=152

Tip1: 一定要加 server_id，否则会报错。至于server_id的值，随便设就可以。
Tip2: log_bin 中间可以下划线_相连，也可以-减号相连。同理server_id也一样。

• 重启mysql

service mysqld restart

• 再次在mysql命令行中执行 show variables like '%log_bin%';

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)

3. 备份

• 进入mysql命令行，执行 show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      430 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

当前正在记录日志的文件名是 mysql-bin.000003

• 比如当前数据库test的bk_user只有2条记录

mysql> select * from test.bk_user;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | 小明 | 男   |   25 |
|  2 | 小红 | 女   |   21 |
+----+------+------+------+
2 rows in set (0.00 sec)

• 插入一条新的记录

mysql> insert into test.bk_user(name, sex, age) values('小强', '男', 24);
Query OK, 1 row affected (0.02 sec)
mysql> select * from test.bk_user;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 小明 | 男  |  25 |
|  2 | 小红 | 女  |  21 |
|  5 | 小强 | 男  |  24 |
+----+------+-----+-----+
3 rows in set (0.03 sec)

• 执行命令mysqladmin -uroot -p密码 flush-logs，生成并使用新的日志文件

再次查看当前使用的日志文件，已经变为 mysql-bin.000004 了。
mysql-bin.000003 则记录着刚才执行的 insert 语句的日志。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

到这里，其实已经完成了增量备份了。

三、MySQL数据恢复策略

恢复过程亦会写入日志文件，如果数据量很大，建议先关闭binlog日志功能
1、场景：假设早上9点的时候，数据库被攻击，drop了整个数据库！
2、恢复思路：
利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件中增量的那部分。
用mysqlbinlog命令将上述的binlog文件导出为sql文件，并剔除其中的drop语句。
通过全备文件和增量binlog文件导出的sql文件，就可以恢复到完整的数据。

恢复全量备份

（1）首先，进入备份文件目录，解压最新的全量备份文件，执行：

解压最新的全量备份文件# 进入备份文件目录
cd /data/mysql/backup
# 解压最新的全量备份文件
tar -zxvf xxxx.sql.tgz

（2）查看全备之后新增的binlog文件，执行：

# 查找binlog文件位置
grep CHANGE xxxx.sql

由图可知，这是全备时刻的binlog文件位置，即mysql-bin.000003的154行，因此在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中。

(3)恢复全备数据，执行：

mysql -uroot -p < xxxx.sql

如：#mysql -uroot -p < alldb_20231227_144955.sql
(7)恢复增量数据，执行（syseco为数据库名称）：

mysql -uroot -p syseco < 00Xbin.sql

如：#mysql -uroot -p syseco < 004bin.sql
自此，已经完成所有工作，让我们查看一下运行一周后产生的文件：

恢复增量备份

• 首先假装误删数据库记录

mysql> delete from test.bk_user where id=4;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from test.bk_user;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | 小明 | 男   |   25 |
|  2 | 小红 | 女   |   21 |
+----+------+------+------+
2 rows in set (0.00 sec)

• 从备份的日志文件mysql-bin.000003中恢复数据

[root@centos56 ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p test
Enter password: 
ERROR 1032 (HY000) at line 36: Can't find record in 'bk_user'
 

如果你也遇到这个问题的话，不妨修改 /etc/my.cnf 配置试试。
我在server_id那一行下添加了 slave_skip_errors=1032 ，然后就执行成功了，不再报错。

mysql> select * from test.bk_user;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | 小明 | 男   |   25 |
|  2 | 小红 | 女   |   21 |
|  5 | 小强 | 男   |   24 |
+----+------+------+------+
3 rows in set (0.00 sec)

四、MySQL备份错误记录

错误一、mysqldump: [Warning] Using a password on the command line interface can be insecure.

这个问题应该是在MySQL5.6+版本的时候出现意思是说：在命令行界面上使用密码可能是不安全的，不能直接把密码写在脚本中。那我们怎么使用密码才能安全呢？我查了好多解决方案，基本都是修改mysql配置文件my.cnf，将账号密码信息放到配置文件中。如下：

解决办法：

vim /etc/my.cnf

# 客户端设置,即客户端默认的连接参数
[mysqldump]
# 程序与mysqlserver处于同一台机器,发起本地连接时可用
socket = /opt/software/mysql/mysql.sock
# 数据库字符集
default_character_set = utf8mb4
# IP地址
host = localhost
# 账号,必须是user才行，username不生效
user = root
# 密码
password = '123456'

注意：

• 修改my.cnf文件后必须重启数据库 service mysqld restart 否则信息不生效
• mysql.scok所在位置可以使用脚本：netstat -ln | grep mysql 查看
  
  再次进行备份，该异常警告就会消失了。

错误二、mysqldump: Got error: 1049: Unknown database 'data' when selecting the database

这个问题意思是说：选择数据库时未知的数据库'data'，说明数据库名称不对或者这个库不存在。

解决办法：

查看mysqldump备份的库名称是否准确。