第8章备份与恢复_mysqldump --threads=2-CSDN博客

本文链接：https://blog.csdn.net/weixin_41811413/article/details/90343142

文章目录

- 第8章备份与恢复

第8章备份与恢复

8.1 备份与恢复概述

根据备份的方法可以分为：

Hot Backup(热备)
Cold Backup(冷备)
Warm Backup(温备)

Hot Backup是指在数据库中直接备份，对正在运行的数据库没有任何影响。这种方式在MySQL官方手册中称为Online Backup(在线备份)。Cold Backup是指在数据库停止的情况下进行备份，这种备份最为简单，一般只需要拷贝相关的数据库物理文件即可。这种方式在MySQL官方手册中称为Offline Backup(离线备份)。Warm Backup备份同样是在数据库运行时进行，但是会对当前数据库操作有所影响，例如加一个全局读锁以保证备份数据的一致性。

根据备份后文件的内容可以分为：

逻辑备份
裸文件备份

在MySQL数据库中，逻辑备份是指备份后的文件内容是可读的，通常是文本文件，内容一般是SQL语句，或者是表内的实际数据，如mysqldump和SELECT*INTO OUTFILE的方法，这类方法的好处是可以看到导出文件的内容，一般适用于数据的升级、迁移等工作，但是恢复所需要的时间往往较长。
裸文件备份是指拷贝数据库的物理文件，数据库既可以处于运行状态(如ibbackup、xtrabackup这类工具)，也可以处于停止状态。这类备份的恢复时间往往较逻辑备份短很多。

根据备份数据库的内容可以分为：

完全备份
增量备份
日志备份

完全备份是指对数据库进行一个完整的备份。增量备份是指在上次的完全备份基础上，对更新的数据进行备份。日志备份主要是对MySQL数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做来完成数据库的point-in-time的恢复工作。MySQL数据库复制(Replication)的原理就是异步实时进行二进制日志重做。
对于MySQL数据库来说，官方没有提供真正的增量备份的方法，大部分是通过二进制日志实现的。这种方法与真正的增量备份相比，效率还是很低的。对于真正的增量备份来说，只需要记录当前每个页最后的检查点LSN，如果大于之前完全备份时的LSN，则备份改页，否则不用备份。这大大加快了备份的速度以及缩短了恢复的时间，同时这也是xtrabackup工具增量备份的原理。

8.2 冷备

对InnoDB存储引擎的冷备，只需要备份MySQL数据库的frm文件、共享表空间文件、独立表空间文件(*.ibd)、重做日志文件。
在同一台机器上对数据库进行冷备是远远不够的，还需要将本地的备份放入一台远程服务器中，以确保不会因为本地数据库宕机而影响备份文件的使用。
冷备的优点是：

备份简单，只要拷贝相关文件即可。
备份文件易于在不同的操作系统、不同的MySQL版本上进行恢复。
恢复相当简单，只需要把文件恢复到指定位置即可。
恢复速度快，不需要执行任何SQL语句，也不需要重建索引。

冷备的缺点是：

InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他数据，如Undo段、插入缓冲等信息。
冷备并不总是可以轻易地跨平台。操作系统、MySQL的版本、文件大小写铭感和浮点数格式都会成为问题。

8.3 逻辑备份

mysqldump

mysqldump的语法如下：

shell> mysqldump [arguments] > file_name

如果想要备份所有的数据库，可以使用–all-databases选项。
如果想要备份指定的数据库，可以使用–databases选项：

shell> mysqldump --databases db1 db2 db3 > dump.sql

如果想要对test这个架构进行备份，可以使用如下语句：

shell> mysqldump --single-transaction test > test_backup.sql

使用–single-transaction选项来保证备份的一致性，备份出的test_backup.sql是文本文件，可以通过cat命令查看文件内容。

mysqldump的参数选项有很多，可以通过mysqldump -help命令来查看所有的参数，有些参数有缩写，如–lock-tables 的缩写为-1，这里介绍些重要的参数：

–single-transaction：在备份开始前，先执行START TRANSACTION命令，以此来获得备份的一致性，当前该参数只对InnoDB存储引擎有效。当启用该参数并进行备份时，确保没有其他任何的DDL语句执行，因为一致性读并不能隔离DDL语句。
–lock-tables(-l)：在备份中，依次锁住每个架构下的所有表。对于InnoDB存储引擎，不需要使用这个参数，使用上面的参数就可以了。
–lock-all-tables(-x)：在备份的过程中，对所有架构中的所有表上锁。
–add-drop-database：在create database前先运行drop database.这个参数需要和–all-databases或者–databases选项一起使用，默认情况下，导出的文本文件中并不会有create database，除非指定了这个参数。
–master-data[=value]：通过该参数产生的备份转存文件主要用来建立一个slave replication。当value的值为1时，转存文件中记录CHANGE MASTER语句；当value的值为2时，CHANGE MASTER语句被写成SQL注释。默认情况下，value的值为空。
–master-data会自动忽略–lock-tables选项。如果没有使用–single-transaction选项，则会自动使用–lock-all-tables选项。
–events(-E)：备份事件调度器。
–routines(-R)：备份存储过程和函数。
–triggers：备份触发器。
–hex-blob：将BINARY、VARBINARY、BLOG、BIT列类型备份为十六进制的格式。mysqldump导出的文件一般是文本文件，但是，如果导出的数据中有上述这些类型，文本文件模式下可能有些字符不可见，若添加-he-blob选项，结果会以十六进制的方式显示。
–tab=path(-T path)：产生TAB分割的数据文件。

可以使用SELECT…INTO OUTFILE的方式来导出一张表，但是通过mysqldump一样可以完成工作，而且可以一次完成多张表的导出，并且保证导出数据的一致性。

–where=‘where_condition’(-w ‘where_condition’)：导出给定条件的数据，例如，导出b架构下的表a，并且表a的数据大于2，如下所示：

mysqldump -u root -p --single-transaction --where='b>2' test a > a.sql

SELECT…INTO OUTFILE

SELECT …INTO语句也是一种逻辑备份的方法，或者更准确地说是导出一张表中的数据。语法如下：

SELECT [columns 1],[columns 2]...
INTO
OUTFILE 'file_name'
[
{FIELDS|COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[
LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
FROM TABLES WHERE...

[TERMINATED BY ‘string’]表示每个列的分割符，[[OPTIONALLY] ENCLOSED BY ‘char’]表示对于字符串的包含符，[ESCAPED BY ‘char’]表示转义符，[STARTING BY ‘string’]表示每行的开始符号，[TERMINATED BY ‘string’]表示每行的结束符号。

逻辑备份的恢复

mysqldump的恢复操作比较简单，因为备份的文件就是到处的SQL语句，一般只需要执行这个文件就行，可以通过以下语句：

mysql -u root -p < test_backup.sql

如果在导出时包含了创建和删除数据库的SQL语句，则必须确保删除架构时架构目录下没有其他与数据库无关的文件。
通过mysqldump可以恢复数据库，但是常发生的一个问题是mysqldump可以导出存储过程，触发器、事件、数据，但是却不能导出视图。因此，如果你的数据库中还使用了视图，那么在用mysqldump备份完数据库后还需要导出视图的定义，或者保存视图定义的frm文件，并在恢复时进行导入，这样才能保证mysqldump数据库的完全恢复。

LOAD DATA INFILE

若是通过mysqldump --tab或 select into outfile 导出的数据需要恢复时，这时需要通过load data infile命令来进行导入，load data infile的语法如下：

LOAD DATA[LOW_PRIMARY|CONCURRENT][LOCAL]INFILE 'file_name'
[REPLACE|IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS|COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name=expr,...]

要对服务器文件使用LOAD DATA INFILE，必须拥有FILE权，其中导入格式的选项和之前介绍的SELECT INTO OUTFILE命令完全一样。IGNORE number LINES选项可以忽略导入的前几行。
为了加快InnoDB存储引擎的导入，如果希望导入过程忽略对外键的检查，可以使用如下方式：

mysql> set @@foreign_key_checks=0;
mysql> load data infile 'a.txt' into table a;
mysql> set @@foreign_key_checks=1;

mysqlimport

mysqlimport 是MySQL数据库提供的一个命令行程序，从本质上来说，是LOAD DATA INFILE 的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。其语法格式如下：

shell> mysqlimport [optioms] db_name textfile1 [textfile2...]

与LOAD DATA INFILE不同的是，mysqlimport命令是可以导入多张表的，并且通过–user-thread参数来并发导入不同的文件。这里的并发是指并发导入多个文件，并不是指mysqlimport可以并发地导入一个文件，这是有区别的，并且并发地对同一张表进行导入，效果一般都不会比串行的方式好。

shell> mysqlimport --use-threads=2 test t.txt s.txt

如果在上述命令运行的过程中查看MySQL的数据库线程列表，可以使用如下命令：

mysql> show full processlist;

8.4 二进制日志备份与恢复

MySQL数据库的复制同样需要二进制日志。默认情况下并不启用二进制日志，要使用二进制日志，首先必须启用它，在配置文件中进行如下设置：

[mysqld]
log-bin
sync_binlog=1
innodb_support_xa=1

备份二进制日志文件前，可以通过FLUSH LOGS命令来生成一个新的二进制日志文件，然后备份之前的二进制日志。
要恢复二进制日志通过mysqlbinlog即可：

shell> mysqlbinlog [options] log_file...
#比如要恢复某个二进制文件
shell> mysqlbinlog --start-position=107856 binlog.000001 | mysql -uroot -p test

–start-position和–stop-position选项可以用来指定从二进制日志的某个偏移量来进行恢复，这样可以通过某些不正确的语句。
–start-datetime和–stop-datetime选项可以用来指定从二进制日志的某个时间点来进行恢复。

也可以通过mysqlbinlog命令导出到一个文件，然后再通过SOURCE命令来导入。这样做法的好处是，可以对导出的文件进行修改后再导入，如：

shell> mysqlbinlog binlog.000001 > statements.sql
shell> mysqlbinlog binlog.000002 >> statements.sql
shell> mysql -u root -p -e "source statements.sql"

8.5 热备

ibbackup

ibbackup是InnoDB存储引擎官方提供的热备工具，可以同时备份MyISAM存储引擎表和InnoDB存储引擎表。InnoDB存储引擎表工作原理如下：

记录备份开始时，InnoDB存储引擎重做日志文件检查点的LSN。
拷贝共享表空间文件以及独立表空间文件。
记录拷贝完表空间文件后，InnoDB存储引擎重做日志文件检查点的LSN。
拷贝在备份时产生的重做日志。

可以发现，在备份期间不会对数据库本身有任何的影响，所做的操作只是拷贝数据库文件，因此任何对数据库的操作都是允许的，不会出现阻塞情况。因此，ibbackup的优点如下：

在线备份。不阻塞任何SQL语句。
备份性能好。备份的实质是复制数据库文件和重做日志文件。
支持压缩备份。通过选项，可以支持不同级别的压缩。
跨平台支持。

ibbackup对InnoDB存储引擎表的恢复过程如下：

恢复表空间文件。
应用重做日志文件恢复InnoDB存储引擎表。

但是ibbackup是收费软件，免费的XtraBackup可以实现ibbackup的所有功能，并且还扩展支持真正的增量备份功能。

XtraBackup

xtrabackup命令的使用方法如下：

xtrabackup --backup | --prepare [options]

XtraBackup实现增量备份

MySQL数据库本身提供的工具并不支持真正的增量备份，更准确地说，二进制日志的恢复应该是point-in-time的恢复而不是增量备份。XtraBackup工具支持对InnoDB存储引擎的增量备份，其工作原理如下：

首先完成一个完全备份，并记录下此时检查点的LSN。
在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。

8.6 快照备份

MySQL数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一文件分区中，然后对该分区执行快照工作。支持快照功能的文件系统和设备包括FreeBSD的UFS文件系统，Solaris的ZFS文件系统，GNU/Linux的逻辑卷管理器(Logical Volume Manager,LVM)等。这里以LVM为例进行介绍。
LVM是Linux系统下对磁盘分区进行管理的一种机制。LVM在硬盘和分区之上建立一个逻辑层来提高磁盘分区管理的灵活性。管理员可以通过LVM系统轻松管理磁盘分区，可以通过LVM由物理块设备(如硬盘等)创建物理卷，由一个或多个物理卷创建卷组，最后从卷组中创建任意数量的逻辑卷(不超过卷组大小)。

在这里插入图片描述

通过vgdisplay命令查看系统中有哪些卷组。
通过命令lvdisplay来查看当前系统中有哪些逻辑卷。

shell> vgdisplay

LVM使用了写时复制(copy-on-write)技术来创建快照。当创建一个快照时，仅拷贝原始卷里数据的元数据，并不会有数据的物理操作，因此快照的创建过程是非常快的。当快照创建完成后，原始卷上有写操作时，快照会跟踪原始卷块的改变，将要改变的数据在改变之前拷贝到快照预留的空间里，因此这个原理的实现叫做写时复制。而对于快照的读取操作，如果读取的数据块是创建快照后没有修改过的，那么会将读操作直接重定向到原始卷上；如果要读取的是已经修改过的块，则将读取保存在快照中的原始数据。因此，采用写时复制机制保证了读取快照时得到的数据与快照创建时的数据是一致的。

在这里插入图片描述

上图显示LVM的快照读取，可见B区块被修改了，因此历史数据放入了快照区域。读取快照数据时，ACD块还是从原有卷中读取，而B块就需要从快照读取了。
可用命令lvcreate来创建一个快照，–permission r表示创建的快照是只读的：

shell> lvcreate --size 100G --snapshot --permission r -n datasnapshot /dev/rep/repdata

在快照制作完成后，可以用lvdisplay命令来查看。
用LVM快照备份InnoDB存储引擎表相当简单，只要把与InnoDB存储引擎相关的文件如共享表空间、独立表空间、重做日志文件等放在同一个逻辑卷中，然后对这个逻辑卷进行快照备份即可。
在对InnoDB存储引擎文件做快照时，数据库无须关闭，可以进行在线备份。虽然此时数据库中可能还有任务需要往磁盘上写数据，但这不会妨碍备份的正常进行。因为InnoDB存储引擎是事务安全的引擎，在下次恢复时，数据库会自动检查表空间中页的状态，并决定是否应用重做日志，恢复就好像数据库被以外重启了。
启用LVM快照需要规划以下几个方面：

快照空间大小的划分。
快照启用后磁盘的性能。
不要把快照备份当做完全备份来使用，因为当数据库所在服务器发生硬件故障时，这时快照备份是不能进行恢复的。快照备份更偏向于对误操作的防范，可以将数据库迅速地恢复到快照产生的时间点，然后再根据二进制日志执行point-in-time的恢复。

8.7 复制

复制的工作原理
在这里插入图片描述

复制是MySQL数据库提供的一种高可用、高性能的解决方案，一般用来建立大型的应用。总体来说，复制的工作原理分为以下三个步骤：

主服务器把数据更新记录到二进制日志中。
从服务器把主服务器的二进制日志拷贝到自己的中继日志(Relay Log)中。
从服务器重做中继日志中的时间，把更新应用到自己的数据库上。

工作原理并不复杂，其实就是完全备份和二进制日志备份的还原。不同的是，这个二进制日志的还原操作基本上是实时进行的。注意，不是完全的实时，而是异步的实时。其中存在主从服务器之间的执行延时，如果主服务器的压力很大，这个延时可能更长。
从服务器有两个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。
要想查看当前延时，可以通过命令：

mysql> show slave status;
mysql> show master status;

通过show slave status命令，可以看到它的主要参数有：

Slave_IO_State：显示当前IO线程的状态。
Master_Log_File：显示当前同步的主服务器的二进制日志。
Read_Master_Log_Pos：显示当前同步到主服务器上二进制日志的偏移量位置，单位是字节。
Relay_Master_Log_File：当前中继日志同步的二进制日志。
Relay_Log_File：显示当前写入的中继日志。
Relay_Log_Pos：显示当前执行到中继日志的偏移量位置。
Slave_IO_Running：从服务器中IO线程的运行状态，YES表示运行正常。
Slave_SQL_Running：从服务器中SQL线程的运行状态，YES表示运行正常。
Exec_Master_Log_Pos：表示同步到主服务器的二进制日志偏移量的位置。

快照+复制的备份架构

复制可以用来作为备份，但其功能不仅限于备份，其主要功能如下：

数据分布。由于MySQL数据库提供的复制并不需要很大的带宽，因此可以在不同的数据中心之间实现数据的拷贝。
读取的负载平衡。通过建立多个从服务器，可将读取平均地分布到这些从服务器中，从而减少主服务器的压力。一般可以通过DNS的Round-Robin和Linux的LVS功能实现负载平衡。
数据库备份。复制对备份很有帮助，但是从服务器不是备份，不能完全代替备份。
高可用性和故障转移。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。

可见，复制的设计目的不是简单用来备份的，并且只用复制来进行备份是远远不够的。假设当前应用采用了主从式的复制架构，从服务器用来作为备份，一个没有经验的操作人员执行了误操作，这时从服务器也跟着运行了，该如何操作？
一种比较好的方法是通过对从服务器上的数据库所在的分区做快照，以此来避免复制对误操作的处理能力。当主服务器上发生误操作时，只需要恢复从服务器上的快照，然后再根据二进制日志执行point-in-time的恢复即可。

在这里插入图片描述