MySQL 备份与恢复

MySQL 备份与恢复

数据备份的重要性

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

4.1.2 数据库备份类型

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

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

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

>冷备份：在数据库关闭状态下进行备份操作。

>热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件。

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

逻辑备份是对数据库逻辑组件(如表等数据库对象)的备份，表示为逻辑数据库结构 (CREATE DATABASE,CREATE TABLE语句)和内容( INSERT 语句或分隔文本文件)的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器 体系结构上重新创建数据。

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

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

完全备份：每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时 间也很长。

差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次 完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

常见的备份方法

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

1.物理冷备份

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

2. 专用备份工具mysqldump 或 mysqlhotcopy

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

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

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

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

数据库完全备份操作

物理冷备份与恢复

物理冷备份一般用 tar 命令直接打包数据库文件夹，而在进行备份之前需要使用 “systemctl stop mysqld”命令关闭mysqld 服务。

1 . 备 份 数 据 库

创建一个/backup目录作为备份数据存储路径，使用tar 创建备份文件。整个数据库文 件夹备份属于完全备份。

[root@localhost~]#systemctlstop mysqld [root@localhost ~]#mkdir /backup

[root@localhost~]#tar zcf /backup/mysql_all-$(date +%F).tar.gz/usr/local/my sql/datal

2.     恢复数据库

执行下面操作将数据库文件/usr/local/mysql/data/    转移至bak  目录下，模拟故障。

 mysqldump备份与恢复

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

1. 备份数据库

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

mysqldump [选项]库名[表名1][表名2]….>1备份路径/备份文件名

2:备份一个或多个完整的库(包括其中所有的表)。

mysqldump [选项]-databases 库名1[库名2].>1备份路径/备份文件名

3 : 备 份MySQL 服务器中所有的库。

mysqldump[选项]--all-databases  >1备份路径/备份文件名

若需要备份整个 MySQL  服务器中的所有库，应使用格式3。当导出的数据量较大的时候，可以添加“--opt”选项以优化执行速度。

[root@localhost ~]#mysqldump -u root -p--opt --all-databases>all-data.sql Enter password:

3. 恢复数据库

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

mysql   [选项][库名][表名]

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

[root@localhost~] #mysql  -u  root  -p  testEnter  password:

若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名。

  MySQL增 量 备 份 与 恢 复

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

MySQL增量备份概述

1.增量备份的特点

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

2.MySQL 二进制日志对备份的意义

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

要进行MySQL增量备份，首先要开启二进制日志功能。开启MySQL的二进制日志 功能的实现方法有很多种，最常用的是在MySQL 配置文件的mysqld 项下加入“og-bin=1  文件路径/文件名”前缀，如log-bin=/usr/local/mysql/mysql-bin,    然后重启MySQL 服务就可以在指定路径下查看二进制日志文件了。

MySQL增量恢复

在维护数据库时，因为各种各样的原因可能会导致数据丢失，如：人为的SQL  语句破 坏数据库、在进行下一次全备份之前发生系统故障导致数据库数据丢失、在数据库主从架构 中主库的数据发生故障等。当出现以上场景时可以使用增量恢复来恢复数据。

常用的增量恢复的方法有三种： 一般恢复、基于位置的恢复、基于时间点的恢复。 

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

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

基于位置的恢复：数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有 正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。

格式1:恢复数据到指定位置。

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

格式2:从指定的位置开始恢复数据。

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

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

格式1:从日志开头截止到某个时间点的恢复。

mysqlbinlog [-no-defaults]--stop-datetime='年-月-日小时：分钟：秒"二进制日志  |mysql -u 用户名-p 密码

格式2:从某个时间点到日志结尾的恢复。

mysqlbinlog [-no-defaults]--start-datetime=年-月-日小时：分钟：秒二进制日志  |mysql -u用户名-p 密码

格式3:从某个时间点到某个时间点的恢复。

mysqlbinlog [-no-defaults]--start-datetime='年-月-日小时：分钟：秒 --stop-datetime='年-月-日小时：分钟：秒”二进制日志|mysql -u 用户名-p 密码