【数据库系列教程】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=
- 以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