11. MySQL 备份、恢复

• 问题背景
  尽管采取了一些管理措施来保证数据库的安全，但是在不确定的意外情况下，总是有可能造成数据的损失。
  常见数据库备份的应用场景如下：
  • 数据丢失应用场景：
    人为操作失误造成某些数据被误操作；
    软件 BUG 造成部分数据或全部数据丢失；
    硬件故障造成数据库部分数据或全部数据丢失；
    安全漏洞被入侵数据恶意破坏。
  • 非数据丢失应用场景：
    特殊应用场景下基于时间点的数据恢复；
    开发测试环境数据库搭建；
    相同数据库的新环境搭建；
    数据库或者数据迁移。
• 所以为了保证数据的安全，我们需要 定期对数据进行备份。如果数据库中的数据出现了错误，就需要使用备份好的数据进行数据还原，这个备份可能是整个数据库的备份，也可以仅仅只是被误操作的表的备份。这样可以将损失降至最低。

【 1. MySQL 备份类型 】

• 根据备份的方法（是否需要数据库离线）可以将备份分为：

  • 热备（Hot Backup） ，热备份可以在 数据库运行中直接备份，对正在运行的数据库操作没有任何的影响：数据库的读写操作可以正常执行。这种方式在 MySQL 官方手册中称为 Online Backup（在线备份） 。按照备份后文件的内容，热备份又可以分为逻辑备份和裸文件备份：
    • 逻辑备份 是指 备份出的文件内容是可读的，一般是文本内容，内容一般是由一条条 SQL 语句，或者是表内实际数据组成。
      如 mysqldump 和 SELECT * INTO OUTFILE 的方法，这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作，但其缺点是恢复的时间较长。
    • 裸文件备份 是指 复制数据库的物理文件，既可以在数据库运行中进行复制（如 ibbackup、xtrabackup 这类工具），也可以在数据库停止运行时直接复制数据文件，这类备份的恢复时间往往比逻辑备份短很多。
  • 冷备（Cold Backup），冷备份必须在 数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup（离线备份）。
  • 温备（Warm Backup），温备份同样是在 数据库运行中进行的，但是会对当前数据库的操作有所影响：备份时仅支持读操作，不支持写操作。

• 按照备份数据库的内容来分，备份又可以分为完全备份和部分备份：

  • 完全备份 是指 备份整个数据库，如果数据较多会占用较大的时间和空间。
  • 部分备份 是指 备份部分数据库（例如，只备份一个表）。部分备份又分为增量备分和差异备份：
    • 增量备份 需要使用专业的备份工具。指的是 在上次完全备份的基础上，对更改的数据进行备份，也就是说每次备份只会备份自上次备份之后到备份时间之内产生的数据。因此每次备份都比差异备份节约空间，但是恢复数据麻烦。
    • 差异备份 指的是 自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单。

• 备份与存储引擎
  MySQL 中进行不同方式的备份还要考虑存储引擎是否支持，
  如 MyISAM 不支持热备，支持温备和冷备；而 InnoDB 支持热备、温备和冷备。

• 一般情况下，我们需要备份的数据分为以下几种：

  • 表数据
  • 二进制日志、InnoDB 事务日志
  • 代码（存储过程、存储函数、触发器、事件调度器）
  • 服务器配置文件

• 几种常用的备份工具：

  • mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。
  • cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
  • lvm2 snapshot：借助文件系统管理工具进行备份。
  • mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。
  • xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。

【 2. 备份数据库 mysqldump 】

• 数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。
• mysqldump 命令执行时，可以将数据库中的数据备份成一个文本文件，数据表的结构和数据将存储在生成的文本文件中。

2.1 备份单个数据表

基本语法：

• mysqldump 命令必须在 cmd 窗口下执行，不能登录到 MySQL 服务中执行 。
• username：表示用户名称；
• dbname：表示需要备份的数据库名称；
• tbname：表示数据库中需要备份的数据表，可以指定多个数据表。省略该参数时，会备份整个数据库；
• 右箭头 >：用来告诉 mysqldump 将备份数据表的定义和数据写入备份文件；
• filename.sql：表示备份文件的名称，文件名前面可以加绝对路径。通常将数据库备份成一个后缀名为.sql的文件。

mysqldump -u username -p dbname [tbname ...] > filename.sql

mysqldump 命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的。例如，后缀名为.txt的文件。通常情况下，建议备份成后缀名为.sql 的文件，这是因为后缀名为.sql的文件给人第一感觉就是与数据库有关的文件。

• 实例
  使用 root 用户备份 test 数据库下的 student 表。打开命令行（cmd）窗口，输入备份命令和密码。
  mysqldump -u root -p mytest tb_student > H:\tb_student.sql
  

2.2 备份多个数据库

• 基本语法
  • 使用 mysqldump 命令备份多个数据库，需要使用 --databases 参数。

mysqldump -u username -P --databases dbname1 dbname2 ...  > filename.sql

• 实例
  使用 root 用户备份 test 数据库和 mysql 数据库。
  mysqldump -u root -p --databases mytest mysql > H:\testandmysql.sql
  

2.3 备份所有数据库

• 基本语法
  • 使用“–all-databases”参数时，不需要指定数据库名称。

mysqldump -u username -P --all-databases > filename.sql

• 实例
  mysqldump -u root -p --all-databases > H:\all.sql
  

2.4 备份文件解析

• 以上面备份单个数据表中的实例为代表，在指定路径中用记事本打开 tb_student.sql
  • student.sql 文件开头记录了 MySQL 的版本、备份的主机名和数据库名。
  • 文件中，以 – 开头的都是 SQL 语言的注释。
  • 以 /*!40101 等形式开头的是与 MySQL 有关的注释。40101 是 MySQL 数据库的版本号，这里就表示 MySQL 4.1.1。
    • 如果恢复数据时，MySQL 的版本比 4.1.1 高，/!40101 和*/之间的内容被当作 SQL 命令来执行。
    • 如果比 4.1.1 低，/*!40101 和 */ 之间的内容被当作注释。/*!和 */中的内容在其它数据库中将被作为注释忽略，这可以提高数据库的可移植性。
  • DROP 语句、CREATE 语句和 INSERT 语句都是数据库恢复时使用的；
    • DROP TABLE IF EXISTS ‘tb_student’ 语句用来判断数据库中是否还有名为 tb_student 的表，如果存在，就删除这个表；
    • CREATE 语句用来创建 tb_student 表；INSERT 语句用来恢复所有数据。文件的最后记录了备份的时间。
  • tb_student.sql 文件中没有创建数据库的语句，因此，student.sql 文件中的所有表和记录必须恢复到一个已经存在的数据库中。恢复数据时，CREATE TABLE 语句会在数据库中创建表，然后执行 INSERT 语句向表中插入记录。
    

【 3. 恢复数据库 mysql 】

• 在 MySQL 中，可以使用 mysql 命令来恢复备份的数据。mysql 命令可以执行备份文件中的 CREATE 语句和 INSERT 语句，也就是说，mysql 命令可以通过 CREATE 语句来创建数据库和表，通过 INSERT 语句来插入备份的数据。
  基本语法：
  • mysql 命令也是直接在 命令行（cmd） 窗口下执行。
  • username 表示用户名称；
  • dbname 表示数据库名称，该参数是可选参数。如果 filename.sql 文件为 mysqldump 命令创建的包含创建数据库语句的文件，则执行时不需要指定数据库名。如果指定的数据库名不存在将会报错；
    如果使用备份了所有的数据库，那么恢复时不需要指定数据库。因为，其对应的 sql 文件中含有 CREATE DATABASE 语句，可以通过该语句创建数据库。
  • filename.sql 表示备份文件的名称。

mysql -u username -P [dbname] < filename.sql

• 实例
  用 root 用户恢复 tb_student数据库。
  执行完后，MySQL 数据库就已经恢复了 all.sql 文件中的所有数据库。

mysql -u root -p mytest < H:\tb_student.sql

【 4. 导出表数据 OUTFILE 】

• 通过对数据表的导入导出，可以 实现MySQL 数据库服务器与其它数据库服务器间移动数据， 导出 是指将 MySQL 数据表的数据复制到文本文件。
• 基本语法
  • 用 SELECT 来查询所需要的数据，
  • 用 INTO OUTFILE 来导出数据。
  • 目标文件用来指定将查询的记录导出到哪个文件，目标文件不能是一个已经存在的文件。
  • [OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，FIELDS 和 LINES 两个子句都是自选的，但是如果两个都被指定了，FIELDS 必须位于 LINES的前面。其常用的取值有：
    • FIELDS TERMINATED BY ‘字符串’：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。
    • FIELDS [OPTIONALLY] ENCLOSED BY ‘字符’：设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
    • FIELDS ESCAPED BY ‘字符’：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
    • LINES STARTING BY ‘字符串’：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
    • LINES TERMINATED BY ‘字符串’：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。

SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]

• 实例1
  1. 使用 SELECT…INTO OUTFILE 语句来导出 mytest 数据库中的 tb_student 表中的记录。
     SELECT * FROM mytest.tb_student INTO OUTFILE 'F://ProgramData/MySQL/MySQL Server 8.4/Uploads/tb_student_name.txt';
     
     注意：导出时可能会出现下面的错误：
     The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
     解决方法：首先使用show variables like '%secure%';语句查看 secure-file-priv 变量配置，执行结果中 secure_file_priv 的值指定的是 MySQL 导入导出文件的路径，将 SQL 语句中的导出文件路径修改为该变量的指定路径，再执行导入导出操作即可。
     如果 secure_file_priv 值为 NULL，则为禁止导出，可以在 MySQL 安装路径下的 my.ini 文件中添加secure_file_priv=设置路径语句，然后重启服务即可。
  2. 在指定文件夹中用记事本打开
     
• 实例2
  1.将 mytest 数据库中的 tb_student 表中的记录导出到文本文件，使用 FIELDS 选项和 LINES 选项，要求字段之间用、隔开，字符型数据用双引号括起来，每条记录以-开头。
  下面代码中，FIELDS TERMINATED BY '、’：表示字段之间用、分隔；ENCLOSED BY ‘"’：表示每个字段都用双引号括起来；LINES STARTING BY ‘-’：表示每行以-开头；TERMINATED BY ‘\r\n’ 表示每行以回车换行符结尾，保证每一条记录占一行。

SELECT * FROM mytest.tb_student INTO OUTFILE 'F://ProgramData/MySQL/MySQL Server 8.4/Uploads/tb_student_name2.txt'
    FIELDS TERMINATED BY '\、' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\-'
TERMINATED BY '\r\n';

2. 在指定文件夹中用记事本打开 tb_student_name2.txt。

【 5. 恢复表数据 INFILE 】

• 系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。
• 数据库恢复机制设计的两个关键问题是：第一，如何建立冗余数据；第二，如何利用这些冗余数据实施数据库恢复。建立冗余数据最常用的技术是数据转储和登录日志文件，通常在一个数据库系统中，这两种方法是一起使用的。
  数据转储 是 DBA 定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程，这些备用的版本成为后备副本或后援副本。
• 使用 LOAD DATA…INFILE 语句来恢复先前备份的数据。
  基本语法：

LOAD DATA INFILE '路径' INTO TABLE 表名;

• 实例
  将之前导出的数据备份文件 tb_student.txt 导入数据库 mytest 的表 tb_student_copy 中，其中 tb_student_copy 的表结构和 tb_student 相同。
  1. 创建表 tb_student，
     CREATE TABLE tb_student_copy LIKE tb_student;
SELECT * FROM tb_student_copy;
     
  2. 导入数据与查询表 tb_student。

LOAD DATA INFILE 'F://ProgramData/MySQL/MySQL Server 8.4/Uploads/tb_student_name.txt'
INTO TABLE mytest.tb_student_copy;

SELECT *FROM tb_student_copy;