小阿轩yx-MySQL备份与恢复

小阿轩yx-MySQL备份与恢复

• 企业对信息系统依赖性越来预高，数据库担任着重要角色
• 如果发生意外宕机或数据丢失，损失是非常严重的
• 数据库管理员必须针对具体的业务要求制定详细的数据库备份与灾难恢复策略

对数据库要求高的行业

• 银行
• 证券
• 电信等

MySQL 数据库备份概述

备份

主要目的

• 灾难恢复

还可以

• 测试应用
• 回滚数据修改
• 查询历史数据
• 审计等

数据备份的重要性

• 数据保障了企业业务的正常运行
• 数据的安全性及数据的可靠性是运维的重中之重

造成数据丢失的原因有

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 灾难（如火灾、地震）
• 盗窃

数据库备份类型

从物理与逻辑的角度分类

可以分为

• 物理备份
• 逻辑备份
• 冷备份（脱机备份）
• 热备份（联机备份）
• 温备份

物理备份

• 对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
• 适用于出现问题时需要快速恢复的大型重要数据库

冷备份

• 在数据库关闭状态下进行备份操作

热备份

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

温备份

• 数据库锁定表格（不可写入但可读）的状态下进行备份

逻辑备份

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

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

分为

• 完全备份
• 差异备份
• 增量备份

完全备份

• 每次对数据进行完整的备份
• 对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础

缺点

• 数据存在大量重复
• 会占用大量磁盘空间
• 备份时间长

差异备份

• 自从上次完全备份之后被修改过的所有文件
• 备份时间节点从上次完整备份起
• 备份量会越来越大

恢复

• 只需恢复上次完全备份与最近的一次差异备份

增量备份

只有上次完全备份或增量备份后被修改的才会被备份

以上次完整备份或上次增量备份时间为点，仅备份这之间的变化

优势

• 数据量小
• 占用空间小
• 备份速度快

恢复

• 需要从上次完整备份开始到最后一次增量备份之间的所有增量一次恢复

（注：中间某次备份数据损坏，将导致数据丢失）

常见的备份方法

可采用多种方式

• 直接打包数据库文件（物理冷备份）
• 专用备份工具（mysqldump）
• 二进制日志增量备份
• 第三方工具备份等

物理冷备份

• 需要在数据库处于关闭状态下，能较好的保证数据库完整性
• 一般用于非核心业务，一般允许中断

特点

• 速度快
• 恢复最为简单
• 通常通过直接打包数据库文件夹实现备份

专用备份工具 mysqldump 或 mysqlhotcopy

mysqldump

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

mysqlhotcopy

• 由 Time Bunce 最初编写和贡献的 Peil 脚本
• 仅用于备份 MySAM 和 ARCHIVE 表
• 只能运行在 UNIX 或 Linux 上，因为范围很小

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

• MySQL 支持增量备份，进行增量备份时必须启用二进制日志
• 为用户提供复制，对执行备份点后进行的数据库更改所需信息进行恢复
• 需要刷新二进制日志

通过第三方工具备份

Percona XtraBackup

• 是一个免费的 MySQL 热备份软件
• 支持在线热备份 Innodb 和XtraDB
• 也可以支持 MySQL 表备份

不过 MyISAM 表的备份要在表锁的情况下进行

Percona XtrBackup 有三个主要的工具

• xtrabackup
• innobackupex
• xbstream

xtrabackup

• 是一个编译了的二进制文件
• 只能备份Innodb/Xtradb 数据文件

innodbackupex

• 是一个封装了xtrabackup 的Per 脚本
• 除了可以备份Innodb/Xtradb 之外
• 还可以备份 MySIAM

xbstream

• 是一个新组件
• 能够允许将文件格式转成 xbstream 格式或从 xbstream格式转到文件格式

xtrabackup 工具

• 可以单独使用

但推荐使用innobackupex来进行备份，因为innobackupex 本身就已经包含了 xtrabackup 的所有功能
xtrabackup

• 是基于 Innodb 的灾难恢复功能进行设计的
• 备份工具复制 Innodb 的数据文件
• 但是，由于不锁表，这样复制出来的数据将不一致
• Innodb维护了一个重做日志
• 包含 Innodb 数据的所有改动情况

备份 Innodb 数据的同时，xtrabackup 还有另外一个线程用来监控重做日志，一但日志发生变化，就把发生变化的日志数据复制走

Percona XtraBackup 对 MySIAM 的复制顺序

• 首先锁定表，然后复制，再解锁表

数据库完全备份操作

可分为

• 完全备份
• 差异备份
• 增量备份

物理冷备份与恢复

• 一般用 tar 命令直接打包数据库文件夹
• 进行备份之前需要使用'systemctl stop mysqld”命令关闭 mysqld 服务

备份数据库

创建一个/backup 目录作为备份数据存储路径，使用tar 创建备份文件

整个数据库文件夹备份属于完全备份

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost ~]# ls -l /backup
总用量 736
-rw-r--r-- 1 root root7516708月1 22:27 mysql _al1-2020-01-02.tar.g2

恢复数据库

将数据库文件/usr/local/mysqldata/转移至 bak 目录下，模拟故障

[root@localhost ~l# mkdir bak
[root@localhost ~]# mv /usr/local/mysqldata/ /bak/

从备份文件恢复数据

[root@localhost ~]# mkdir restore
[root@localhost ~]# tar zxf /backup/mysql all-2020-01-02.tar.gz -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql
[root@localhost ~]# systemctl start mysqld

mysqldump 备份与恢复

• 一般用 tar 命令直接打包数据库文件夹
• 进行备份之前需要使用"systemctl stop mysqld”命令关闭 mysqld 服务

备份数据库

创建一个/backup 目录作为备份数据存储路径，使用tar 创建备份文件

整个数据库文件夹备份属于完全备份

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost ~]# ls -l /backup/
总用量 736
rw-r--r-- 1 root root 751670 8月    1 22:27 mysql_all-2020-01-02.tar.gz

恢复数据库

将数据库文件/usr/local/mysql/data/转移至 bak 目录下，模拟故障

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

从备份文件恢复数据

[root@localhost ~]# mkdir restore
[root@localhost ~]# tar zxf /backup/mysql all-2020-01-02.tar.gz -C restore/
[root@localhost ~# mv restore/usr/local/mysqlldata/ /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld

mysqldump 备份与恢复

• 可以将指定的库、表或全部的库导出为 SQL 脚本
• 便于该命令在不同版本的 MySQL服务器上使用

备份数据库

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

格式1:备份指定库中的部分表

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

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

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

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

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

常用的选项包括

• “-u”
• “-p”

分别用于指定

• 数据库用户名
• 密码

分别使用格式 1、格式 2，将 mysql 库中的 user 表导出为mysql-user.sql，将整个 auth导出为 auth.sq 文件，所有操作均以root 用户身份验证

[root@localhost ~]# mysqldump -u root -p mysql user> mysql-user.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p --databases auth > auth.sql
Enter password:

将创建备份文件 all-data.sql,其中包括 MySQL 服务器中的所有库

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

查看备份文件

• 通过 mysqldump 工具导出的 SQL 脚本是文本文件
• 其中“!...*/"部分或以“-”开头的行表示注释信息

查看脚本内容使用文本工具

• grep
• less
• cat

过滤出 auth.sql 脚本中的数据库操作

[root@localhost ~]# grep -v "^.." auth.sql | grep -v "^!" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER
SET utf8 */;
USE auth;
DROP TABLE IF EXISTS 'users`;CREATE TABLE `users`(
user name char(16) NOT NULL,
user passwd` char(48) DEFAULT"
PRIMARY KEY(`user name”)
)ENGINE=InnODB DEFAULT CHARSET=utf8;
LOCK TABLES users WRITE;
INSERT INTO users VALUES
('zhangsan''*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
UNLOCK TABLES;

恢复数据库

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

mysql [选项] [库名] [表名]</备份路径/备份文件名

从备份文件mysql-user.sql 中将表导入test库。

其中“-e”选项是用于指定连接 MySQL后执行的命令，命令执行完后自动退出

[root@localhost ~]# mysql -u root -p test< mysql-user.sql
Enter password:
//验证导入结果
[root@localhost ~]# mysql -u root -p -e 'SHOW TABLES FROM test;'
Enter password:
+--------------+
|Tables_in_test|
+--------------+
|user          |
+--------------+

从备份文件 auth.sql 恢复 auth 库

[root@localhost ~]# mysql -u root -p -e 'DROP DATABASE auth;'
//删除 auth 数据库,模拟故障
Enter password:
[root@localhost ~]# mysql -u root -p -e 'SHOW DATABASES;'
//查看 auth 数据库是否存在
Enter password:
+------------------+
|Database          |
+------------------+
|information schema|
|mysql             |
|performance schema|
|test              |//执行导入恢复操作
[root@localhost data]# mysql -u root -p< ~/auth.sql
Enter password://确认恢复后结果
[root@localhost ~]# mysql -u root -p -e'SHOW DATABASES;'
Enter password:
+------------------+
|Database          |
|information schema|
|auth              | 
|mysql             | 
|performance schema|
|test              |
+------------------+

MySQL 增量备份与恢复

• 增量备份就是自上一次备份之后增加或改变的内容

MySQL 增量备份概述

增量备份的特点

• 与完全备份不同
• 没有重复数据
• 备份量不大
• 时间短

劣势

• 恢复麻烦
• 需要上次完全备份及完全备份之后所有的增量备份才能恢复
• 而且要对所有增量备份进行逐个反推恢复

MySQL 没有提供直接的增量备份办法，可以通过 MySQL 提供的二进制日志binary logs)间接实现增量备份

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

• 保存了所有更新数据库的操作
• 使用 max_binlog_size 配置可以设置二进制日志文件最大值
• 如果文件大小超过max_binlog_size，就会自动创建新的二进制文件

要进行 MySQL 的增量备份，首先要开启二进制日志功能

开启 MySQL的二进制日志功能的实现方法有很多种

最常用的是

• 在 MySQL配置文件的 mysqld 项下加入“log-bin=/文件路径/文件名"前缀

默认情况下，二进制日志文件的扩展名是一个六位的数字

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin
binlog format = MIXED
[root@localhost ~]# systemctl restart mysqld
//若 mysql无法启动，请确保配置文件内包含“server-id=1”
[root@localhost ~]#ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r-----1 mysql mysql 154 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.000001
-rw-r-----1 mysql mysql 19 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.index

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 密码

小阿轩yx-MySQL备份与恢复