数据库备份与恢复

数据库备份类型

从物理逻辑的角度分类

• 冷备份：在数据库关闭状态下进行备份操作。
• 热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件。
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构

从数据库的备份策略角度分类

• 完全备份：每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢   复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时   间也很长。
• 差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从   上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的   一次差异备份。
• 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，   占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

专用的备份工具

• mysqldump 程序
  • mysqldump是客户端常用逻辑备份程序，能产生一组被执行以后在现原始数据库对象定义和表数据的SQL语句。它可以存储一个或多个MySQL数据库对其进行备份或传输到远程SQL服务器。mysqldump更为通用，因为它可以备份各种表。
• mysqlhotcopy 都可以做备份。
  • mysqlhotcopy仅适用于某些存储引擎，mysqlhotcopy是由Tim Bunce 最初编写和贡献的 Perl 脚本。mysqlhotcopy 仅用于备份 MyISAM和 ARCHIVE 表。它只能运行在UNIX 或 Linux 上。
• xtrabackup：是一个编译了的二进制文件，只能备份Innodb/Xtradb 数据文件。

• innodbackupex ： 是一个封装了 xtrabackup 的 Perl 脚本， 除了可以备份Innodb/Xtradb 之外，还可以备份 MySIAM。xbstream：是一个新组件，能够允许将文件格式转成 xbstream 格式或从 xbstream格式转到文件格式。
• xbstream：是一个新组件，能够允许将文件格式转成 xbstream 格式或从 xbstream格式转到文件格式。

mysqldump程序

软件包为mysql-community-client

//可以使用yum安装
yum install mysql-community-client
//如果显示没有
wget https://dev.mysql.com/get/mysql80-community-release-el8-3.noarch.rpm
rpm -Uvh /tmp/mysql80-community-release-el8-3.noarch.rpm

完整备份

//备份test数据库为backup.sql
mysqldump -u root -p test > backup.sql
//备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > backup.sql
//备份特定表
mysqldump -u root -p test kc01 kc02 > backup.sql

数据恢复

mysql -uroot -p < backup.sql

常见的备份方法

物理冷备份

需要数据库处于关闭状态下，能够较好的保证数据的完整性。速度快，恢复最简单。

直接打包数据库文件夹

#关闭数据库
systemctl stop mysqld
#创建备份存放目录
mkdir /backup
#打包数据库文件
tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

 模拟故障

#创建并转移mysql的数据文件
mkdir bak
mv /usr/local/mysql/data bak/

 恢复文件数据

cd /backup
#创建临时存放目录，并把文件解压至该目录
mkdir /restore
tar zxf /backup/mysql_all-2024-04-12.tar.gz -C /restore
#移动文件至mysql的数据存放目录
mv /restore/usr/local/mysql/data/ /usr/local/mysql/
systemctl restart mysqld

增量备份

MySQL二进制日志

二进制日志保存了所有更新数据库的操作。二进制日志在启动 MySQL 服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到flush logs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时间段的增量备份。

使用 max_binlog_size 配置项可以设置二进制日志文件的最大值，如果二进制文件的大小超过了 max_binlog_size，它就会自动创建新的二进制文件。

开启MySQL二进制日志功能

#也可以vi进入文件添加，在[mysqld]标签下面即可
cat >> /etc/my.cnf << EOF
#开启二进制日志
log-bin=/usr/local/mysql/data/mysql-bin
binlog_format = MIXED
server-id=1
EOF


systemctl restart mysqld

mysqladmin -uroot -p flush-logs
//刷新日志
//以免备份文件丢失，可以把备份文件复制一份
cp /usr/local/mysql/data/mysql-bin.000001 /backup/

 

查询二进制日志文件

cd /usr/local/mysql/data
mysqlbinlog mysql-bin.000001

增量恢复的三种方法

• 一般恢复

//进入存放二进制日志的目录
cd /usr/local/mysql/data
mysqlbinlog mysql-bin.000001 | mysql -uroot -p

• 基于位置恢复

通过查询二进制文件来查看每条数据的偏移量

--start-position=''            //恢复数据开始的时间
--stop-position=''            //恢复数据结束的时间
mysqlbinlog --start-position='750' --stop-position='1071' mysql-bin.000001 | mysql -uroot -p

• 基于时间点恢复

查看二进制文件记录恢复时间

//把数据库删除
mysqlbinlog  --stop-datetime='24-04-13 9:38:09' mysql-bin.000001 | mysql -uroot -p
//恢复开始的时间
--start-datetime
//恢复的时间
--stop-datetime
//没有开始的时间就会变为从头开始