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

一、备份类型和工具

1.根据服务是否在线

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

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

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

常用的备份策略

完全 + 增量
完全 + 差异

3.根据备份的结果

• 逻辑备份
  • 将数据库执行的写操作转换成SQL语句，保存到文件中
  • .sql
  • 备份工具
    • mysqldump
• 物理备份
  • 直接拷贝数据文件
  • 开源备份工具
    • xtrabackup
  • 官网：https://www.percona.com/

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={1|2}
  • 以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

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