mysql数据库的备份与还原

2022-08-21 11:00:25

1. 备份和恢复的类型

(1) 物理备份与逻辑备份

　　物理备份

　　物理备份是把MySQL数据库软件的数据存储目录复制并且保存到安全的存储位置，以防数据库出现启动故障后能够快速恢复，一般把数据库存储的目录进行压缩备份存储，生成*.tar.gz文件。如果数据库存储的目录不小心被删除了，这时就可以把备份的数据文件重新解压拷贝到数据库存储目录下，进行重新启动。

　　物理备份具有以下特点：

备份包含完全的数据存储目录文件，就是仅仅复制了MySQL所有的文件；
物理备份比逻辑备份快，因为它仅仅是文件复制而没有相关的转换；
备份文件比逻辑备份更紧凑，因为可以压缩；
对于备份速度、数据文件重要性方面，比较适合使用物理备份；
备份可以包括日志文件和配置文件；
内存中的表数据可能无法备份（企业版可以备份）；
备份只兼容同一机器或相似机器；
备份可以在shutdown状态下进行，也可以在running状态下（企业版），不过要确保服务器没有数据写入数据文件，企业版备份会自动加锁；
物理备份的工具有mysqlbackup(企业版)，或者使用系统的命令，如cp,scp,tar,rsync；
对于使用mysqlbackup热备份的文件，同样使用mysqlbackup进行还原；对于在系统层面复制文件的备份，在系统中复制粘贴还原备份文件即可。

　　另外，如果需要查看MySQL存储文件，可以在MySQL的终端下执行：

mysql> show global variables like "%datadir%";

　　逻辑备份

　　逻辑备份是备份数据库的逻辑信息，如创建的数据库、表及其表内的内容。逻辑备份可以把数据转移到另外的物理机上，也可以修改表的数据或结构，一般生成一个*.sql文件。

　　逻辑备份具有以下特点：

备份是通过查询MySQL服务器获得数据库的结构和内容信息；
备份比物理备份慢，因为需要访问数据库信息并且转换成逻辑结构；
备份不包括日志或配置文件；
备份的内容可以转移到任意物理机上，具有高度的可移植性；
备份执行时，服务器必须是running状态；
逻辑备份工具有mysqldump和SELECT ... INTO OUTFILE，支持任意存储引擎，甚至内存中的表数据；
逻辑备份使用mysqldump对应的恢复使用mysql客户端，使用SELECT ... INTO OUTFILE对应的恢复使用LOAD DATA语法或mysqlimport客户端。

(2) 热备份与冷备份

　　热备份方式是在MySQL服务器正在运行中，所以客户端可以获得服务器的数据信息；冷备份是在MySQL服务器停止状态下进行。还有一种方式是暖备份，暖备份是数据库虽然在运行中，但是数据访问被加锁了，无法进行修改。

　　热备份

　　热备份具有以下特点：

备份时，客户端可以访问数据并且执行相应的操作；
强制加上合适的锁防止数据修改导致备份的不一致，MySQL Enterprise Backup会自动地加锁

　　冷备份

　　冷备份具有以下特点：

在备份时，客户端无法访问数据库。由于数据库需要关闭，一般情况下，在从节点的数据库下进行，因为是从节点，所以不影响主数据库；
备份过程简单快速

(3) 本地备份和远程备份

　　本地备份是备份客户端操作执行在同一MySQL服务端运行的主机上，而远程备份是备份客户端操作执行不在同一MySQL服务端运行的主机上。某些备份方式，即使输出目的地只能是服务端主机，也可以使用远程方式进行。

mysqldump可以使用本地和远程方式。对于输出为SQL语句方式（CREATE 和 INSERT语句）的备份，不管是远程备份还是本地备份，都可以在客户端上输出。对于输出为分隔符方式（使用了--tab选项）的备份，输出目录只能在服务端；
SELECT ... INTO OUTFILE支持远程和本地方式，不过输出目录只能在服务端；
物理备份方式只能执行在本地的服务端主机。

(4) 全量备份和增量备份

　　全量备份包括MySQL服务器某一时间点上的所有的数据；增量备份由经过某一段时间改变的数据组成。增量备份通过使用开启了日志记录生成二进制日志文件进行备份。

(5) 完全恢复与基于时间点的不完全恢复

　　完全恢复是使用全量备份的文件恢复所有的数据，完全恢复后也可以使用日志文件进行不完全恢复，把数据库恢复到某一时间点的状态。基于时间点的不完全恢复是恢复到数据库的某一个时间状态，它基于二进制日志文件并且通常先进行完全恢复后，再进行基于时间点的不完全恢复，根据时间点得到了重做的操作，把服务器恢复的期望的状态。

2. 备份的方法

(1)使用`mysqlbackup`的热备份

　　mysqlbackup是企业版备份工具，可以进行热备份备份整个数据库或表的物理文件，属于物理备份方式。除此，工具还有压缩备份功能。使用InnoDB引擎的表格是热备份方式，而其他存储引擎的表格是使用暖备份方式。

(2)使用`mysqldump`的备份

　　mysqldump是逻辑备份工具，可以备份所有类型的表格。对于使用InnoDB存储引擎的表格，使用--single-transaction选项可以在备份过程中不锁表格。

(3)通过备份表格文件的备份

　　对于那些存储引擎的表格拥有自己的文件的，比如MyISAM存储引擎的表格，可以简单地备份复制文件（*.frm, *.MYD,*.MYI）。为了保证备份的数据一致性，最好停止服务器或刷新表格并加锁。

mysql>flush tables tabble_list with read load;

　　加上读取锁，可以在备份过程中使客户端可以继续查询表格；刷新表格是确保备份时所有的数据都被写入到了数据文件。

　　如果服务器没有在更新数据，备份可以是复制所有的表格文件，但注意，对于InnoDB存储引擎的表格就不行了。InnoDB存储引擎的表格，即使服务器没有在更新数据，也可能在缓存中有数据没有写入到数据文件。

(4)分隔符文本文件形式的备份

　　如果想创建包含表格数据的文本文件，可以使用select * into outfile ‘file_name‘ from table_name;语句，它会在数据库的存储目录下生成file_name的文本文件，并且要求file_name文件不能已存在。

(5)通过二进制日志的增量备份

　　MySQL支持增量备份，是使用二进制日志文件实现的。二进制日志文件提供所有你需要重新让数据库执行的操作信息。如果要使用增量备份，在进行完全备份后，使用flush log操作对当前的日志进行rotate（归档），然后对二进制日志文件进行备份，当需要恢复时，进行完全恢复操作，再使用备份的日志文件进行不完全恢复。

(6)通过副本节点的备份

　　如果数据库的设计是主从结构，主数据库的数据会同步到从数据库，那么就可以通过备份从数据库来达到备份的目的。如果通过副本节点进行备份，不管哪种备份方法，都要把主数据库的信息和传递日志信息配置进行备份，这些信息可以方便继续恢复副本节点。（可能有错）

(7)使用系统快照的备份

　　如果使用的文件系统支持快照，也可以进行物理文件的快照备份。如vxfs、LVM和ZFS.

3. 备份与恢复的使用

　　物理备份的冷备份只是把MySQL数据库的存储目录所有文件进行打包压缩以防数据文件损坏而无法使用数据库，当然这个备份也包括了写入了数据库数据文件的数据信息。物理备份的热备份mysqlbackup可以在不关闭数据库的状态下备份数据库的数据文件，不过mysqlbackup是企业版的功能。这里是演示逻辑备份和恢复。

(1) mysqldump

　　１)备份：

　　默认情况下，mysqldump会把数据信息转换成SQL语句作为标准输入，并且可以使用Linux的输出重定向符>存储到文件中：

shell>mysqldump [arguments] > file_name

　　假设数据库有root用户和player的账号，且密码都为10010，且数据库上有两个自建数据库schema，分别为neteasemusicplayer和onlineexam，其中player用户只有neteasemusicplayer这个schema的访问权，root用户拥有两个schema的访问权。下面演示备份：

shell>mysqldump -uplayer -p10010 --all-databases > player_dump.sql #备份player用户所有的数据库schema，存储到player_dump.sql文件，这里测试root用户备份所有数据库失败，root用户有数据库系统的数据库访问权，但因某些未知原因，备份报错
shell>mysqldump -uroot -p10010 --databases neteasemusicplayer onlineexam > root_dump.sql #备份root用户的neteasemusicplayer和onlineexam数据库schema，存储到root_dump.sql文件
shell>mysqldump -uroot -p10010 onlineexam > onlineexam.sql #这种备份方式得到的*.sql文件没有`create database xxx；user xxx;`，并且只能指定一个数据库scheme

　　第一种方式--all-databases会把当前用户下所有的数据库schema进行备份；第二种方式--databases可以制定当前用户的某一或多个数据库schema；第三种方式单单指定了数据库schema，它得到的文件就没有create database和use部分，并且这种方式只能指定一个数据库schema，这样可以把数据导入到不同数据库schema中。

　　2)恢复：

备份文件有create database和use语句的恢复

　　对于使用mysqldump导出的SQL语句文件，可以使用mysql客户端进行恢复，如果备份时使用了all-databases或--databases选项，它包含了create database和use语句，所以没必要去指定默认的导入数据库schema了，直接使用输入重定向<传递给mysql客户端即可恢复：

shell>mysql -uroot -p10010 < player_dump.sql #恢复player用户备份的所有数据库schema，这里使用root用户，我的player用户无创建数据库权限

　　或者在mysql下可以使用source命令恢复：

shell>mysql -uroot -p10010 #回车进入mysql终端,这里使用root用户，我的player用户无创建数据库权限
mysql>source player_dump.sql; /*恢复player用户备份的所有数据库schema*/

备份文件无create database和use语句的恢复

　　如果是上面第三种方式的备份产出的SQL语句，即没有create database和use语句的，如果有需要的话，可以使用mysqladmin先建立数据库schedule：

shell>mysqladmin -uroot -p10010 create onlineexam
shell>mysql -uroot -p10010 onlineexam < onlineexam.sql

　　或者在mysql下先创建数据库，然后使用source命令恢复：

shell>mysql -uroot -p10010 #使用root用户登录MySQL终端控制台
mysql>create database if not exists onlineexam; /*如果数据库不存在就创建数据库scheme*/
mysql>use onlineexam;	/*使用onlineexam数据库scheme*/
mysql>source onlineexam.sql;  /*恢复数据库onlineexam*/

(2) mysqlbinlog

　　mysqlbinlog是基于日志文件的不完全恢复，可以把数据库恢复到某一个时间点或某一个事件点上，对于误操作恢复很有用。下面演示错误删除onlineexam数据库schema的questions表的不完全恢复。

　　操作步骤：

　　1)备份onlineexamschema；

　　2)插入了数据到users表格，但不小心删除了questions表；

　　3)恢复onlineexamschema，这时的users表没有插入的数据；

　　4)查看日志存储文件并显示详细的记录，获取时间点或事件点；

　　5)使用基于时间点或基于位置的恢复到未错误删除questions表但是插入了user表数据的状态

1)备份

shell>mysqldump -uroot -p10010 onlineexam > onlineexam.sql

2)插入数据到users表但错误删除了questions表

shell>mysql -uroot -p10010
mysql>insert into users values(‘201615210416‘,‘10010‘);
mysql>insert into users values(‘201615210417‘,‘10010‘);
mysql>drop table questions;

3)使用备份的文件恢复onlineexam

shell>mysql -uroot -p10010 onlineexam < onlineexam.sql

4)查看日志存储文件并显示详细的记录，获取时间点或事件点

mysql>show master status; /*查看当前的日志文件位置*/

获取时间点

shell>mysqlbinlog /var/lib/mysql/binlog.000001 > binlog #转换成文本查看
shell>vim binlog #查看输出文件，并且搜索‘DROP TABLE‘

获取事件点

mysql>show binlog events in ‘binlog.000001‘;

5)恢复到某一状态

基于时间点恢复

　　因为上面查看得到的时间点是09:02:01执行了错误删除questions表操作，使用mysqlbinlog --stop-datetime恢复到这个时间点上，但这个操作是未被执行的，所以执行恢复：

shell>mysqlbinlog --stop-datetime="2020-06-09 09:02:01" /var/lib/mysql/binlog.000001 | mysql -uroot -p10010

基于事件点恢复

　　因为上面查看得到的事件点是856开始执行误删除questions表操作，执行成功后事件点为1002，使用mysqlbinlog --stop-position执行恢复时，--stop-position应该填写856：

shell>mysqlbinlog --stop-position="856" /var/lib/mysql/binlog.000001 | mysql -uroot -p10010

　　通过基于时间点或基于事件点方式都可以把数据库恢复到某一个期望状态下，如下所示恢复结果：

　　关于mysqlbinlog的参数说明：

参数	说明
--start-datetime=”datetime“	从二进制日志的时间戳等于或晚于datetime的第一个事件开始
--stop-datetime=”datetime“	从二进制日志的时间戳等于或早于datetime的第一个事件结束
--start-position=N	从二进制日志的事件点等于或大于N的第一个事件开始
--stop-position	从二进制日志的事件点等于或小于datetime参数的第一个事件结束

(4) 总结

　　本篇博客的参考文档是MySQL 5.7的官方使用手册，知识点较多的是根据文档进行翻译，也加入了一些自己的理解，第三部分是自己的实践，也有许多的坑。

码农公寓