MySQL备份与恢复

一、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 仅适用于某些存储引擎。
        mysqlhotcopy 是由 Tim Bunce 最初编写和贡献的 Per 脚本。mysqlhotcopy 仅用于备份 MyISAM 和 ARCHIVE 表。它只能运行在 UNIX 或 Linux 上。

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

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

4.通过第三方工具备份

        Percona XtraBackup 是一个免费的 MySQL 热备份软件，支持在线热备份 Innodb 和XtraDB，也可以支持 MySQL 表备份，不过 MyISAM 表的备份要在表锁的情况下进行。本节对于 Percona XtraBackupr 的叙述是基于 2.4 版本的。
        Percona XtrBackup 有三个主要的工具:xtrabackup、innobackupex、xbstream.

• xtrabackup:是一个编译了的二进制文件，只能备份Innodb/Xtradb 数据文件。
• innodbackupex:是一个封装了xtrabackup 的 Perl 脚本，除了可以备份Innodb/Xtradb 之外，还可以备份 MySIAM。
• xbstream:是一个新组件,能够允许将文件格式转成 xbstream 格式或从 xbstream格式转到文件格式。

        xtrabackup 工具可以单独使用，但推荐使用 innobackupex 来进行备份，这是因为innobackupex本身就已经包含了 xtrabackup 的所有功能。
        xtrabackup 是基于 Innodb 的灾难恢复功能进行设计的，备份工具复制 Innodb 的数据文件。但是，由于不锁表，这样复制出来的数据将不一致。Innodb 维护了一个重做日志，包含 Innodb 数据的所有改动情况。在 xtrabackup 备份 Innodb 数据的同时，xtrabackup 还有另外一个线程用来监控重做日志，一但日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。
        以上是备份过程，如果需要恢复数据，则在准备阶段，xtrabackup 就需要使用之前复制的重做日志对备份出来的Innodb 数据文件进行灾难恢复，此阶段完成之后，数据库就可以进行重建还原了。

        Percona XtraBackup 对 MySIAM 的复制，是按这样的一个顺序进行的:首先锁定表,然后复制，再解锁表。

二、数据库完全备份操作

部署实验环境

创建实验所需的库和表

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.1物理冷备份与恢复

1.备份数据库

[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

2.模拟故障

[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;'

3.恢复数据库

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

在服务启动状态下使用

(1)导出数据

[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 

三、MySQL增量备份与恢复

3.1MySQL增量备份概述

增量备份（incremental backup）是备份的一个类型，是指在一次完全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

1.启用二进制日志功能

（1）修改主配置文件

[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）生成新的日志文件

[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

2.向表添加数据（多添加一些）

mysql> use auth;

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

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

3.模拟误操作将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;'  ##查看表，已经不存在

4.常规恢复操作

（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：不读取任何选项文件，其后需要直接跟日志文件或开始停止位置

5.基于位置的恢复

（1）模拟误操作

[root@localhost mysql]# mysql -uroot -p -e 'drop table auth.users;'
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'   ##没有了数据

（2）恢复完全备份

[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql 

（3）查看日志内容

[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

（4）恢复到指定的地方

[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;'   

（5）从指定的位置向后开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' mysql-bin.000002 | mysql -u root -p     ##从521之后开始恢复，包含521

（6）恢复指定范围内的数据

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002  | mysql -u root -p

6.基于时间点恢复

（1）恢复到指定的时间

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p

（2）从指定的时间开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002  | mysql -u root -p

（3）恢复指定时间范围内的数据

[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

四、制定企业备份策略的思路

        在企业中备份策略并不是千篇一律的,而是根据每个企业的实际生产环境与业务需求制定合适的备份策略。无论是选择完全备份，还是选择增量备份，都需考虑它们的优缺点，是否适合当前的生产环境。同时，为了保证恢复的完整性，建议开启二进制日志功能，二进制日志文件给恢复工作也带来了很大的灵活性,可以基于时间点或位置进行恢复。考虑到数据库性能，可以将二进制日志文件保存到其他安全的硬盘中。
        在进行热备份时，备份操作和应用服务在同时运行，这样就十分消耗系统资源了，导致数据库服务性能下降，这就需要选择一个合适的时间(如在应用负担很小的时候)再来进行备份操作。
        需要注意的是，不是备份完就万事大吉，最好确认备份是否可用，所以备份之后的恢复测试是非常有必要的。同时备份时间也要灵活调整，如:

• 数据更新频繁，则应该频繁地备份。
• 数据的重要性，在有适当更新时进行备份。
• 在数据库压力小的时间段进行备份，如一周一次完全备份，每天进行增量备份中小公司，完全备份一般一天一次即可。
• 大公司可每周进行一次完全备份，每天进行一次增量备份。
• 尽量为企业实现主从复制架构，以增加数据的可用性。