Mysql 备份与恢复

一、数据库备份概述：

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

1.1 数据备份的重要性：

在企业中数据的价值至关重要．数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。通常情况下造成数据丢失的原因有如下几种:
程序错误、人为操作错误、运算错误、磁盘故障、灾难和盗窃。

1.2 数据库备份类型：

从物理与逻辑的角度
数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件.日志文件等〉的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。
物理备份又可以分为冷备份（脱机备份)．热备份（联机备份）和温备份。

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

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

温备份:数据库锁定表格（不可写入但可读)的状态下进行备份操作。
从数据库的备份策略角度

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

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

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

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

1.3 常见的备份方法：

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

1）物理冷备份
物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务--般都允许中断，物理冷备份的特点就是速度快．恢复时也是最为简单的。

mysqldtump程序和mysqlhotcopy都可以做备份。mysqldump 是客户端常用逻辑备份程序，能够产
生一组被执行以再现原始数据库对象定义和表数据的SQL.语句。它可以转储一个到多个MySQL数据库，对其进行备份或传输到远程SQL 服务器。mysqldump更为通用，因为它可以备份各种表mysqlhotcopy仅适用于某些存储引擎。
mysqIhotcopy是由Tim Bunce 最初编写和贡献的Perl脚本。mmysqlhotcopy仅用于备份MyISAM和ARCHIVE 表。它只能运行在UNIX或 Linux 上。
通过启用二进制日志进行增量备份
MySQL支持增量备份．进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制.对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份〈包含自上次完全备份或增量备份以来发生的数据修改)．需要刷新二进制曰志。

二、数据库完全备份操作：

2.1 物理冷备份与恢复:

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

mysql> create database chenji;   ##创建库##

mysql> create table chenji.yiban (姓名 nvarchar(20) not null,班级 nvarchar(20) not null,学号 int  not null,语文 int not null,数学 int not null,英语 int not null,理综 int not null);
##创建表##

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

[root@xiao ~]# systemctl stop mysqld.service
[root@xiao ~]# mkdir /backup
[root@xiao ~]# tar zcvf /backup/mysql_all-$(data +%F).tar.gz /usr/local/mysql/data/
[root@xiao ~]# ls -l /backup/
总用量 744
-rw-r--r-- 1 root root 759810 12月 15 00:18 mysql_all-.tar.gz

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

[root@xiao ~]# mkdir /bak
[root@xiao ~]# mv /usr/local/mysql/data/ /bak/

执行下面操作可以从备份文件恢复数据

[root@xiao ~]# mkdir /restore
[root@xiao ~]# tar zxvf /backup/mysql_all-.tar.gz -C /restore/
[root@xiao ~]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/
[root@xiao ~]# systemctl start mysqld

2.2mysqldump备份与恢复：
备份数据库的部分表：

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

备份一个或多个完整的库：

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

备份所有库：

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

将mysql库中的user 表导出为mysql-user , sql，将整个auth库导出为auth,sql文件．所有操作均以root 用户身份验证。

若需要备份整个MySQL服务器中的所有库，应使用格式3。当导出的数据量较大的时候，可以添加“——opt”选项以优化执行速度。例如，执行以下操作将创建备份文件all-data ,sql，其中包括MySQL服务器中的所有库。

查看备份文件：

恢复数据库：

若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名。例如，执行以下操作可以从备份文件auth . sql恢复auth 库。

?三、MySQL的增量备份与恢复：

1．增量备份的特点
与完全备份不同，增量备份没有重复数据．备份量不大，时间短;但其恢复麻烦．需要上次完全备份及完全备份之后所有的增量备份才能恢复．而且要对所有增量备份进行逐个反推恢复。MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志(binary logs)间接实现增量备份。
2. MySQL二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据库的操作。二进制日志在启动MySQL服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到 flush logs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些曰志保存到安全的存储位置，即可完成一个时间段的增量备份。使用max_binlog_size配置项可以设置二进制日志文件的最大值．如果二进制文件的大小超过了max_binlog_size，它就会自动创建新的二进制文件。要进行MySQL的增量备份.首先要开启二进制日志功能，开启MSQL的二进制日志功能的实现方法有很多种.最常用的是在MySQL配置文件的mysqld项下加入“log-bin=/文件路径/文件名”前缀，如 log-bin=/usr / local/mysql/mysql--bin，然后重启MySQL服务，就可以在指定路径下查看二进制日志文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如mysql-bin.00001,

3.1 myaql增量恢复：

常用的增量恢复的方法有三种:--般恢复．基于位置的恢复．基于时间点的恢复

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

mysq1binlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -P

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

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

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

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

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

?四、实验：（增量备份与恢复）

实验描述：

在数据库表中.分三次录入学生考试成绩．具体的考试成绩信息分别参见表4--2~表4-4。

创建表：

再次录入成绩：

mysql> insert into chenji.yiban values ('李宁','二班',20170824,92,98,105,235);
Query OK, 1 row affected (0.01 sec)

mysql> insert into chenji.yiban values ('陈铭','二班',20170826,111,107,96,204);
Query OK, 1 row affected (0.01 sec)

第三次录入成绩：

mysql> insert into chenji.yiban values ('付杰','二班',20170828,115,118,116,268);
Query OK, 1 row affected (0.01 sec)

mysql> insert into chenji.yiban values ('郭尚','二班',20170830,111,99,80,259);
Query OK, 1 row affected (0.00 sec)

?

首次录入成绩后，做该表的完全备份，后两次成绩的录入之后，分别做增量备份。

首次成绩录入完整备份

录入第二次成绩增量备份：

?

第三次录入成绩增量备份：?

模拟数据丢失．并使用增量备份分别基于位置和时间点恢复-一班所有学生成绩与二班陈铭


?