MySQL备份与恢复

1、MySQL数据库备份概述

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

1.1、数据备份的重要性

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

程序错误

人为操作错误

运算错误

磁盘故障

灾难（如火灾，地震）和盗窃

1.2、数据库备份类型

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

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

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

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

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

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

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

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

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

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

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

增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。

1.3、常见的备份方法

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

1、物理冷备份

物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。通常通过直接打包数据库文件夹（文件夹位于/usr/local/mysql/data）

2、专用备份工具mysqldump或mysqlhotcopy

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

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

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

2、数据库完全备份操作

2.1、物理冷备份与恢复

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

1、创建实验所需的库和表

create database auth;
use auth;
create table users (user_name char(16) not null,user_passwd char(48) default '');
insert into users values('zhangsan',password('123456'));
insert into users values('lisi',password('123456'));

2、物理冷备份与恢复

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

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar zcvf /backup/mysql_$(date +%F).tar.gz mysql/
[root@localhost local]# cd /backup/
[root@localhost backup]# ls
mysql_2023-06-08.tar.gz

3、模拟故障

[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'drop database auth;'
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'

4、恢复数据库

再次关闭mysqld服务，解压，然后进行数据库的恢复。

[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.2mysqldump备份与恢复

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

1、备份数据库

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

格式1：备份指定库中的部份表。

格式2：备份一个或多个完整的库（包括其中所有的表）

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

其中，常用的选项包括“-u”，“-p”，分别用于指定数据库用户名、密码。

具体示例如下：

[root@localhost ~]# mysqldump -u root -p auth users > /opt/auth-users.sql\\导出数据表
[root@localhost ~]# mysqldump -u root -p auth > /opt/auth.sql\\导出数据库
[root@localhost ~]# mysqldump -u root -p --opt --all-databases >/opt/all-data.sql##备份所有库

--opt：优化执行速度

2、导入表

如果已经存在数据库，可以将表导入到任何一个现有的数据库

[root@localhost ~]# mysql -u root -p -e 'create database test;'  
[root@localhost ~]# mysql -u root -p test < /opt/auth-users.sql##导入到其他库
[root@localhost ~]# mysql -u root -p auth< /opt/auth-users.sql##导入到原始所在的库
[root@localhost ~]# mysql -u root -p -e 'show tables from test;'

3、导入库

如果备份了完整的数据库，可以在mysql中没有该库信息的情况下直接导入该库

[root@localhost ~]# mysql -u root -p -e 'drop database auth;'  
[root@localhost ~]# mysql -u root -p -e 'show databases;' 
[root@localhost ~]# mysql -u root -p -e 'create database auth;' 
[root@localhost ~]# mysql -u root -p auth</opt/auth.sql
[root@localhost ~]# mysql -u root -p -e 'show databases;' 
[root@localhost ~]# mysql -uroot -p -e 'select * from auth.users;'

4、全库还原

保证至少已经创建了这些库

[root@localhost ~]# mysql -u root -p </opt/all-data.sql

2.3、增量备份与恢复

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

增量备份的特点：与完全备份不同，增量备份没有重复数据，备份量不大，时间短;但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。

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

二进制日志保存了所有更新数据库的操作。二进制日志在启动MySQL服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到flush logs命令后重新创建新的日志文件，生成二进制文件序列。

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

启用二进制日志功能

修改配置文件/etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf 
在[mysqld]模块中添加
log-bin=mysql-bin
server-id=1
binlog_format = MIXED
[root@localhost ~]# systemctl restart mysqld

备注：

开启mysql事务日志的方法二：

在my.cnf中增加server-id字段

使用系统命令行执行mysqld--user=mysql --log-bin=/usr/local/mysql/data/文件前缀

然后重新启动mysql服务。

2.3.1MySQL增量恢复

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

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

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

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

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

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

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

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

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

具体案例步骤如下：

（1）生成新的日志文件

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

（2）查看日志内容

mysqlbinlog --no-defaults mysql-bin.000002

注意：如果sql语句被加密了，可以使用以下指令

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

（3）向表中添加数据

为了验证效果，逐个添加新的数据，并生成一个日志文件

use auth;
insert into users values('aaa','123456');
insert into users values('bbb','123456');
insert into users values('ccc','123456');
insert into users values('ddd','123456');
insert into users values('eee','123456');
insert into users values('fff','123456');
[root@localhost data]# mysqladmin -u root -p flush-logs
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

（4）模拟误操作将auth.users表删除

[root@localhost data]# mysql -u root -p -e 'drop table auth.users;'
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;'##查看表，已经不存在

（5）常规恢复操作

1、先恢复完全备份

[root@localhost data]# mysql -u root -p auth </opt/auth.sql 
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;'##查看表，已经存在，但数据不全

2、再恢复增量备份

[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
注意：要先查看日志内容，确定是需要恢复的数据
[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'    ##查看表的内容

--no-defaults：不读取任何选项文件，其后需要直接跟日志文件或开始停止位置

（6）基于位置的操作

[root@localhost mysql]# mysql -uroot -p -e 'drop table auth.users;'   模拟误操作
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'
[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql  恢复完全备份
[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002
从日志中分析哪些数据还没有恢复，找出想要恢复到哪个数据的操作ID
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p##恢复到521之前的一个数据，不包含521
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'

（7）基于时间点恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p  恢复到指定的时间
[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002  | mysql -u root -p  从指定的时间开始恢复
[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p 恢复指定时间范围内的数据