数据库备份与恢复和日志管理

一、数据库备份的概述

1、数据库备份的目的：备灾。在生产环境中，数据的安全性非常重要

2、造成数据丢失的原因：程序出错、人为的问题、磁盘故障、自然灾害。

二、备份的分类

从物理和逻辑的角度

1、物理备份：

对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

冷备份（脱机备份）：是在关闭数据库的时候进行的

热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

2、逻辑备份：

对数据库逻辑组件（如：表等数据库对象）的备份

从数据库的备份策略角度

完全备份、差异备份、增量备份

完全备份：（全量备份）每次对数据库进行完整的备份。

差异备份：备份自从上次完全备份之后被修改过的文件。

增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份。

三、常见的备份方法

1、物理冷备 （完全备份）：备份时数据库处于关闭状态，直接打包数据库文件，备份速度快，恢复时也是最简单的，通过直接打包数据库文件夹(/usr/loc.al/mysql/data)来实现备份。

2、通过启用二进制日志进行增量备份：进行增量备份，需要刷新二进制日志。

3、第三方工具备份：免费的MySQL热备份软件Percona XtraBackup。四、物理冷备与恢复
优点：备份与恢复操作简单方便
缺点：数据存在大量的重复，占用大量的备份空间，备份与恢复时间长

1、物理冷备份与恢复

关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可

在安装创建数据的的时候在vim /etc/my.cnf修改数据库路径，数据文件都保存在/usr/local/mysql/date目录下

systemctl stop mysqld #关闭数据库
yum -y install xz  #下载工具，xz是一个高效率的压缩工具
 
#压缩备份
cd /usr/local/mysql/data
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
systemctl start mysqld
 
#模拟故障，删除数据库
drop database xy102;
 
#解压恢复
tar jxvf /opt/mysql_all_2024-07-19.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
mv usr/local/mysql/data/* ./

2、mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份可以备份库，也可以备份库里面的表 ,将指定的库、表导出为SQL脚本（.sql结尾），使用命令mysq|导入备份的数据。

操作步骤：

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库

mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库

mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库

mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表

mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

恢复：

方法一：

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sql

mysql> drop database test;

mysql> create database test2; ###建立空库

[root@server1 ~]# mysql -u root -p test2 < test-2024-07-19.sql

方法二：

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sql

mysql> drop database test;

mysql> create database test2;

mysql> use test2;

mysql> source /root/test-2024-07-19.sql;

3、增量备份

1、使用mysqldump进行完全备份存在的问题

备份数据中有重复数据

备份时间与恢复时间过长

2、MySQL增量备份是自上一次备份后增加/变化的文件或者内容

3、特点

没有重复数据，备份量不大，时间短

恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

4、MySQL没有提供直接的增量备份方法

可通过MySQL提供的二进制日志间接实现增量备份

5、MySQL二进制日志对备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作

二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件

只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

4、增量恢复

1、一般恢复

将所有备份的二进制日志内容全部恢复

2、断点恢复

1、基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

[root@server1 ~]# mkdir -p /opt/bak_sql

[root@server1 ~]# mysqldump -uroot -p test2 > /opt/bak_sql/test2-$(date +%F).sql; ###完整备份
[root@server1 ~]# vi /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/data/mysql_bin ###开启增量备份

[root@server1 ~]# systemctl restart mysqld



[root@server1 ~]# mysqladmin -uroot -p flush-logs ###在日志进行打一个断点，让后续的命令存储到另外一个日志中去，产生新的日志文件

[root@server1 ~]# cd /usr/local/mysql/data/

[root@server1 data]# ll ###查询增量备份结果

查看日志：mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.00001

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容是否正确



[root@server1 ~]# mysqladmin -u root -p flush-logs; ###在日志进行打一个断点，让后续的命令存储到另外一个日志中去，产生新的日志文件

[root@server1 ~]# cd /usr/local/mysql/data/

[root@server1 data]# ll ###查询增量备份结果

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容是否正确



mysql> use test2;

mysql> drop table aa; ###先删掉坏的那张表

[root@server1 ~]# mysql -u root -p test2 < /opt/bak_sql/test2-2024-07-19.sql ###还原完全备份的数据库



[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容



[root@server1 ~]# mysqlbinlog --no-defaults --stop-datetime='时间点' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###停止错误的时间



[root@server1 ~]# mysqlbinlog --no-defaults --start-datetime='时间点' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###开始正确的时间




从开始位置开始恢复

mgsqlbinlog --no-defaults --start-position='8224' mysql-bin.00001（文件名）| mysql -u roo -p 

从结束位置恢复

mgsqlbinlog --no-defaults --stop-position='9011' mysql-bin.00001（文件名）| mysql -u roo -p 

以什么位置为开始以什么位置为结束

mgsqlbinlog --no-defaults --start-position='8224'  --stop-position='9011' mysql-bin.00001（文件名）| mysql -u roo -p 

3、对/etc/my.cnf配置文件进行解读

general_log=on 开启通用查询日志

general_log_file=/usr/local/mysql/data/mysql_general.log 查询日志的保存位置

log-erro=/usr/local/mysql/data/mysql_error.log 错误日志的保存位置，错误日志默认开启

slow_query_log=ON 开启慢查询日志，执行记录朝超过一定时间的日志

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log 设定慢查询日志的位置

long_query_time=5 默认的慢查询时间是5秒的记录都会被保存