MySQL数据库的备份与恢复

MySQL的备份与恢复

概述
备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改．查询历史数据．审计等。
1.数据备份的重要性

在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性及数据
的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。

通常情况下造成数据丢失的原因有如下几种
程序错误。
人为操作错误。
运算错误。
磁盘故障。
灾难（如火灾．地震）和盗窃
2．数据库备份类型

1）从物理与逻辑的角度

​ 数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据
文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。
物理备份又可以分为冷备份（脱机备份)、热备份〔联机备份）和温备份。
冷备份:在数据库关闭状态下进行备份操作。

热备份:在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件.
温备份:数据库锁定表格〔不可写入但可读）的状态下进行备份操作。
逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构(CREATE
DATABASE，CREATE TABLE语句）和内容(INSERT语句或分隔文本文件〉的信息。这种类型的备份
适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据。

2）从数据库的备份策略角度

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

3．常见的备份方法

MySQL_数据库的备份可以采用很多种方式，如直接打包数据库文件〈物理冷备份).专用备份工
具(mysqldump)、二进制日志增量备份．第三方工具备份等。

1）物理冷备份

​ 物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一
般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简
单的。通常通过直接打包数据库文件夹（本章中的数据库文件夹位于/usr/local/mysql/data）来实现
备份。

2）专用备份工具mysqldump 或 mysqlhotcopy

​ mysqldump程序和mysqlhotcopy都可以做备份。mysqldump是客户端常用逻辑备份程序，能够产
生一组被执行以再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据
库，对其进行备份或传输到远程SOL服务器，mmysqldump更为通用，因为它可以备份各种表。

mysqlhotcopy仅适用于某些存储引擎。
mysqlhotcopy是由Tim Bunce最初编写和贡献的Perl脚本。mysqlhotcopy仅用于备份MyISAM和
ARCHVE表。它只能运行在UNX或Linux 上。因为使用范围很小，因此本文中不做详细介绍，如果同
学们有兴趣可以在课下研究。

3）通过启用二进制日志进行增量备份

MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制.
对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份（包含自上次完全备份
或增量备份以来发生的数据修改)，需要刷新二进制日志。

4）通过第三方工具备份

Percona XtraBackup是一个免费的MSQL热备份软件，支持在线热备份lInnodb和XtraDB，也可以
支持MySOL表备份，不过MyISAM表的备份要在表锁的情况进行。本节对于Percona XtraBackupr的叙
述是基于2.4版本的。
Percona XtrBackup有三个主要的工具: xtrabackup,innobackupex、xbstream，其中;
xtrabackup:是一个编译了的二进制文件，只能备份Innodb/Xtradb数据文件。
innodbackupex，是一个封装了xtrabackup的Perl脚本，除了可以备份Innodb/Xtradb之外，还
可以备份MySIAM。
xbstream，是一个新组件，能够允许将文件格式转成xbstream格式或从xbstream格式转到
文件格式。
xtrabackup工具可以单独使用，但推荐使用innobackupex来进行备份，这是因为innobackupex本身
就已经包含了xtrabackup的所有功能。
xtrabackup是基于Innodb的灾难恢复功能进行设计的，备份工具复制’hmodb的数据文件，但是，
由于不锁表，这样复制出来的数据将不一致。Innodb维护了一个重做日志，包含lnnodb数据的所有改
动情况。在xtrabackup备份lhnodb 的数据同时， xtrabackup还有另外一个线程用来监控重做日志，一
但日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。

实验
在数据库表中，分三次录入学生考试成绩
首次录入成绩后，做该表的完全备份．后两次成绩的录入之后，分别做增量备份。
模拟数据丢失，并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩.

安装mysql ： https://blog.csdn.net/weixin_46298214/article/details/108885414

1、在数据库中录入数据

1、打开二进制日志，恢复时使用

​ [root@centos01 ~]# vim /etc/my.cnf

2、更新二进制日志

​ [root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-log

3、进入数据库创建库和表然后插入数据

​ 创建库

​ mysql> create database benet;

​ 插入数据

​ mysql> insert into benet.student values (‘张三’,‘一班’,‘20170822’,110,105,92,235);

​ mysql> insert into benet.student values (‘李四’,‘一班’,‘20170820’,95,115,110,260);

​ mysql> insert into benet.student values (‘王五’,‘一班’,‘20170823’,117,95,99,253);

​ mysql> insert into benet.student values (‘赵六’,‘一班’,‘20170824’,90,100,92,232);

4、完全备份表数据

​ [root@centos01 ~]# mysqldump -uroot -ppwd@123 benet student > benet_student_$(date +%F).sql

5、插入二班数据

​ mysql> insert into benet.student values (‘李宁’,‘二班’,‘20170825’,110,115,98,240);

​ mysql> insert into benet.student values (‘陈铭’,‘二班’,‘20170826’,109,117,95,243);

​ mysql> insert into benet.student values (‘付杰’,‘二班’,‘20170827’,98,90,100,203);

​ mysql> insert into benet.student values (‘郭尚’,‘二班’,‘20170828’,100,95,110,253);

2、模拟数据丢失 并恢复数据

​ mysql> delete from benet.student where 姓名=‘张三’;

​ 以此类推

1、基于位置恢复一班所有人数据

2，基于时间恢复一班所有人数据

​ 恢复二班陈铭和付杰成绩

1、基于位置恢复
[root@centos01 ~]# mysqlbinlog --start-position=‘452’ --stop-position=‘1362’ /usr/local/mysql/data/mysql-bin.000004 |
mysql -uroot -ppwd@123

2、基于时间恢复
[root@centos01 ~]# mysqlbinlog --start-datetime=‘2020-10-05 14:47:34’ --stop-datetime=‘2020-10-05 14:48:36’ /usr/local
/mysql/data/mysql-bin.000004 | mysql -uroot -ppwd@123