Mysql数据的备份与恢复

一.备份概述

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

1.数据备份的重要性

         在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。通常情况下造成数据丢失的原因有如下几种：

1. 程序错误。
2.  人为操作错误
3. 运算错误。
4. 磁盘故障。
5. 灾难（如火灾、地震）和盗窃。

2.数据库备份类型

（1）从物理与逻辑的角度分类

        数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、口志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。

        物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。

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

        逻辑备份是对数据库逻辑组件的备份（如表等数据库对象），表示为逻辑数据库结构和内容的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据。

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

从数据库的各份策略角度，数据库的各份可分为完全备份、差异各份和增量备份。

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

3.常见的备份方法

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

（1）物理冷备份

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

（2）专用备份工具mysqldump或mysqihotcopy

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

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

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

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

（4）通过第三方工具备份

        PerconaXtraBackup是一个免费的MySQL热备份软件，支持在线热备份Innodb和XtraDB，也可以支持MySQL表备份，不过MyISAM表的各份要在表锁的情况下进行。

二.数据库完全备份操作

1.物理冷备份与恢复

（1）先关闭mysqld服务

[root@localhost ~]# systemctl stop mysqld

（2）创建一个存储目录

[root@localhost ~]# mkdir /backup

 （3）使用tar创建备份文件

[root@localhost local]# tar zcvf /backup/mysql_$(date +%F).tar.gz mysql/

（4）恢复数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /backup/
[root@localhost backup]# tar zxvf mysql_2023-06-08.tar.gz[root@localhost backup]# cd /usr/local/
[root@localhost local]# mv mysql/ mysql.bak[root@localhost local]# cd /backup/
[root@localhost backup]# mv mysql /usr/local/
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'

2.mysqldump备份与恢复

        通过mysqldump命令可以将指定的库，表或全部的库导出为SQL脚本，便于该命令在不同版本的MySQL服务器上使用。例如，当需要升级MySQL服务器时，可以先试用mysqldump命令将原有库信息导出，然后直接在升级后的MySQL服务器中导入即可。

（1）备份数据库

        使用mysqldump命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合Shell的“>重定向输出操作。

模式一：备份指定库中的部分表

mysqldump 【选项】 库名 【表名1】 【表名2】…>/备份文件路径/文件名

模式二：备份一个或多个完整的库

mysqldump 【选项】 --database 库名1 库名2…>/备份文件路径/文件名

 模式三：备份MySQL服务器中所有的库

mysqldump 【选项】 --all-databases>/备份路径/备份文件名

备注：常用的选项包括“-u”，“-p”，分别用于指定数据库的用户和密码，当导出的数据量较大时，可以添加“--opt”选项以优化执行速度

（2）恢复数据库

        使用mysqldump命令导出的SQL备份脚本，在需要恢复时可以通过mysql命令对其进行导入操作，格式：

mysql 【选项】【库名】 【表名】</备份路径/备份文件名

 三.mysql增量备份与恢复

         使用mysqldump进行完全备份，备份的数据中有重复数据，份时间与恢复时间过长而增量备份就是自上一次备份之后增加或改变的内容

1.特点：

        与完全备份不同，增量备份没有重复数据，备份量不大，时间短：但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志（binanylogs）间接实现增量备份。 

2.意义

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

3.增量备份

        要进行MySQL的增量备份，首先要开启二进制日志功能。开启MySQL的二进制日志功能的实现方法有很多种，最常用的是在MySQL配置文件的mysqld项加入“log-bin=/文件路径/文件名”前，如log-bin=/usr/local/mysql/mysql-bin，然后重启MySQL服务就可以在指定路径下查看二进制日志文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如mysql-bin.000001

 （1）修改MySQL主配置文件my.cnf

在[mysqld]模块中添加log-bin=mysql-binserver-id=1binlog——format=MIXED[root@localhost ~]# systemctl restart mysqld

（2）生成日志内容

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mysqladmin -u root -p flush-logs

（3）查看新的日志文件

mysqlbinlog --no-defaults mysql-bin.000002
注意：如果sql语句被加密了，可以使用以下指令
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

4.MySQL增量恢复

         常用的增量恢复的方法有三种：一般恢复、基子位置的恢复、基于时间点的恢复般恢复：将所有备份的二进制日志内容全部恢复，命令格式如下所示。

1.一般恢复：

将所有备份的二进制日志内容全部恢复，命令格式如下：

mysqlbinlog  --no-defaults 增量备份文件 | mysql -u 用户 -p 密码

2.基于位置恢复：

数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以使恢复更加精确。 

（1）恢复数据到指定位置

mysqlbinlog --stop-position=操作id 二进制日志 | mysql -u 用户名 -p 密码

 （2）从指定的位置开始恢复

mysqlbinlog --start-opsition=操作id 二进制日志 | mysql -u 用户名 -p 密码

3.基于时间点 ：

跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分为三种情况

（1）从日志开头截止到某个时间点

mysqlbinlog --no-defaults --stop-datetime=时间 二进制日志 | mysql -u 用户 -p 密码

（2）从某个时间点到日志结尾的恢复

mysqlbinlog --no-defaults --start-detatime=时间 二进制日志 | grep -u 用户名 -p 密码

（3）从某个时间点到某个时间点的恢复

mysqlbinlog --no-defaults --start-datetime=时间 --stop-datetime=时间 二进制日志 | mysql -u 用户名 -p 密码