【数据库系列教程】MySQL备份与恢复

一、备份类型和工具

1.根据服务是否在线

  • 热备份
    • 在备份的过程,可以继续执行数据的读写操作
    • 服务持续运行的状态
  • 温备份
    • 在备份的过程,只允许读操作、不允许写操作
    • 锁表
  • 冷备份
    • 停止数据库服务、备份数据
    • 影响业务运行

2.根据备份的数据量大小

  • 完全备份
    • 每次备份,备份所有数据
  • 增量备份
    • 仅备份上一次完全备份后变化的数据
    • 仅备份上一次增量备份后变化的数据
  • 差异备份
    • 仅备份上一次完全备份后变化的数据

常用的备份策略

完全 + 增量
完全 + 差异

3.根据备份的结果

  • 逻辑备份
    • 将数据库执行的写操作转换成SQL语句,保存到文件中
    • .sql
    • 备份工具
      • mysqldump
  • 物理备份

4.需要备份的文件

  • 数据
  • 二进制日志
  • 配置文件

二、mysqldump工具(完全备份) 逻辑备份

1.常用选项

  • -u #用户名
  • -p #密码
  • –all-databases #备份所有数据库
[root@localhost ~]# mysqldump -uroot -predhat --all-databases > /tmp/a.sql

–databases <数据库名称> <数据库名称> #备份指定数据库

[root@localhost ~]# mysqldump -uroot -predhat --databases jiaowu > /tmp/b.sql

库名 表名 表名 #备份单个表

[root@localhost ~]# mysqldump -uroot -predhat jiaowu tutors > /tmp/c.sql

完全备份

root@localhost ~]# mysqldump -uroot -predhat --lock-all-tables --master-data=2 --all-databases > /mysql/backup/data_$(date +%F_%T).sql
  • –lock-all-tables 锁表
  • –flush-logs 执行二进制日志滚动
  • –master-data=
    • 以CHANGE MASTER TO记录当前正在使用的二进制日志、最后一个事件的Position

2.模拟4天操作 完全备份+增量备份

A.备份二进制日志命令

mysqlbinlog --start-position=起始位置 二进制日志文件路径 > 导出的文件路径

B.准备备份文件的存储点

[root@localhost ~]# df -hT | grep backup
/dev/sdd                ext4       20G   45M   19G   1% /mysql/backup

C.第一天 完全备份

[root@localhost ~]# mysqldump -uroot -predhat --lock-all-tables --master-data=2 --all-databases > /mysql/backup/data_$(date +%F_%T).sql

D.第二天 模拟写操作 增量备份

mysql> delete from jiaowu.tutors where age > 60;
查看第一天完整备份记录的最后一个Position ID
cat /mysql/backup/data_$(date +%F_%T).sql | head -n 30

-- CHANGE MASTER TO MASTER_LOG_FILE='master.000003', MASTER_LOG_POS=12644;	#注意后面这个ID
开始第二天的增量备份
[root@localhost ~]# mysqlbinlog --start-position=12644  /mysql/log/master.000003 > /mysql/backup/data_$(date +%F_%T).sql

E.模拟第三天的写操作 增量备份

mysql> insert into jiaowu.tutors(Tname) values("user01"),("user02");
查看第二天的备份,最后一个Position ID
cat /mysql/backup/data_$(date +%F_%T).sql	#查看第二天备份的时间

#230404 20:16:49 server id 10  end_log_pos 12998 CRC32 0xeb32c267 	Xid = 575	#注意end_log_pos后面的ID,就是我们第三天开始备份的ID
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
开始备份第三天的增量备份
[root@localhost ~]# mysqlbinlog --start-position=12998 /mysql/log/master.000003 > /mysql/backup/data_$(date +%F_%T).sql

F.模拟第四天的写操作

mysql> insert into jiaowu.tutors(Tname) values("user03"),("user04");

G.突发事件

第四天在我们没有备份之前,数据库坏掉了!!!

模拟数据库坏掉并且数据全部丢失
# systemctl stop mysqld

# rm -rf /var/lib/mysql/* 

H.恢复数据

a.恢复数据库运行
# systemctl start mysqld
b.修改root密码
[root@localhost ~]# grep -i password /var/log/mysqld.log	#获取临时密码
[root@localhost ~]# mysql -uroot -p临时密码
c.临时关闭二进制日志(重要)

我们接下来恢复数据的命令(不关闭会导致重复操作)和修改root密码的操作,防止被二进制日志记录下来

mysql> set sql_log_bin=0;

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)
修改root密码
mysql>set password for 'root'@'localhost' = PASSWORD("WWW.1.com");
d.恢复第一天的完全备份
第一种命令格式
mysql> source 备份文件名称;
第二种命令格式
mysql -uroot -p < 备份文件名称
第一种演示(任选其一即可)
mysql> source /mysql/backup/data_$(date +%F_%T).sql	#注意日期格式每个人都不一样
第二种演示(任选其一即可)
mysql -uroot -p < /mysql/backup/data_$(date +%F_%T).sql
e.恢复第二天的增量备份
mysql> source /mysql/backup/data_$(date +%F_%T).sql	#注意日期格式每个人都不一样
f.恢复第三天的增量备份
mysql> source /mysql/backup/data_$(date +%F_%T).sql	#注意日期格式每个人都不一样
mysql> exit	#退出后自动重新启用二进制日志
g.恢复第四天的数据

由于我们并没有备份第四天的数据,所以我们需要借助二进制日志恢复第四天数据

查看第三天的end_log_pos的ID

需要查看我们第三天备份文件,最后记录的ID

cat /mysql/backup/data_$(date +%F_%T).sql	#查看第三天备份的时间

#230404 20:16:49 server id 10  end_log_pos 13277 CRC32 0xeb32c267 	Xid = 575	#注意end_log_pos后面的ID
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
通过二进制日志恢复第四天的数据
[root@localhost ~]# mysqlbinlog --start-position=13277 /mysql/log/master.000003 | mysql -uroot -p

输入密码后,即可完成第四天的数据恢复

3.完全备份+差异备份

完全备份步骤同上

[root@localhost ~]# mysqldump -uroot -predhat --lock-all-tables --master-data=2 --all-databases > /mysql/backup/data_$(date +%F_%T).sql
查看完全备份的Position ID
cat /mysql/backup/data_$(date +%F_%T).sql | head -n 30

-- CHANGE MASTER TO MASTER_LOG_FILE='master.000003', MASTER_LOG_POS=12644;	#注意后面这个ID

差异备份,每次的Position ID永远都是第一次完全备份的最后一个Position ID

[root@localhost ~]# mysqlbinlog --start-position=12644  /mysql/log/master.000003 > /mysql/backup/data_$(date +%F_%T).sql

最后恢复,只需要恢复第一天的完全备份和最后一天的差异备份即可!