11-3. 数据库备份

〇. 说明

在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。

为了有效防止数据丢失，并将损失降到最低，应定期对MysQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

1. 物理备份

备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份。

2. 逻辑备份

对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL中常用的逻辑备份工具为 mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

Ⅰ. mysqldump 备份

mysqldump是MySQL提供的一个非常有用的数据库逻辑备份工具

1. 备份数据库

mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

查出需要备份的表的结构，在文本文件中生成一个CREATE语句
将表中的所有记录转换成一条INSERT语句。

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql

1.1 备份示例

# 备份文件存储在当前目录下
mysqldump -uroot -p ruoyi>ruoyi.sql 

# 备份到指定目录
mysqldump -uroot -p ruoyi > /var/lib/mysql/ruoyi.sql

1.2 备份文件查看

里面是一些数据库信息，和SQL语句。

-- 工具版本，mysql版本，Linux
- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)本，Linux
--
-- Host: localhost  Database: sgugo
-- ------------------------------------------------------
-- Server version    8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
......
DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client   = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
 `studentno` int NOT NULL,
 `name` varchar(20) DEFAULT NULL,
 `class` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`studentno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `student` VALUES (1,'张三_back','一班'),(3,'李四','一班'),(8,'王五','二班'),
(15,'赵六','二班'),(20,'钱七','>三班'),(22,'zhang3_update','1ban'),(24,'wang5','2ban');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
......

后面的DROP语句、CREATE语句和INSERT语句都是还原时使用的。例如，“DROP TABLE IF EXISTS 'student""语句用来判断数据库中是否还有名为student的表，如果存在，就删除这个表;CREATE语句用来创建student的表;INSERT语句用来还原数据。

备份文件开始的一些语句以数字开头。这些数字代表了MySQL版本号，告诉我们这些语句只有在制定的MysQL版本或者比该版本高的情况下才能执行。例如，40101表明这些语句只有在MysQL版本号为4.01.01或者更高的条件下才可以被执行。文件的最后记录了备份的时间。

2. 备份全部数据库

若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql

3. 备份部分数据库

使用 --databases 或 -B 参数，该参数后面跟数据库名称，多个数据库间用空格隔开。

如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在

mysqldump –u user –h host –p --databases 
[数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

# 示例
mysqldump -uroot -p -B ruoyi sgugo >two_database.sql

4.备份部分表

比如，在表变更前做个备份。

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

# 示例：备份sgugo数据库下的user表
mysqldump -uroot -p sgugo user > user.sql

## 示例：备份sgugo数据库下的多个表
mysqldump -uroot -p sgugo user account > tables_bak.sql

查看备份的文件内容，和直接备份数据库的文件类似，只是SQL文件中只包含了指定的表。

5. 备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了，where后面附带需要满足的条件。

#示例：备份student表中id小于10的数据：
mysqldump -uroot -p sgugo student --where="id < 10 " > student_part_id10_low_bak.sql

备份文件的内容如下：insert语句只有id小于10的部分

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,100002,'JugxTY',157,280),
(2,100003,'QyUcCJ',251,277),(3,100004,'lATUPp',80,404),
(4,100005,'BmFsXI',240,171),(5,100006,'mkpSwJ',388,476),
(6,100007,'ujMgwN',259,124),(7,100008,'HBJTqX',429,168),
(8,100009,'dvQSQA',61,504),(9,100010,'HljpVJ',234,185);

6. 排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 --ignore-table 可以完成这个功能。

mysqldump -uroot -p sgugo --ignore-table=sgugo.student > no_stu_bak.sql

7. 只备份结构或只备份数据

7.1 只备份结构

只备份结构的话可以使用 --no-data 简写为 -d 选项；

mysqldump -uroot -p sgugo --no-data > sgugo_no_data_bak.sql

7.2 只备份数据

只备份数据可以使用 --no-create-info 简写为-t 选项。

mysqldump -uroot -p sgugo --no-create-info > sgugo_no_create_info_bak.sql

8. 备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件。

8.1 查看当前库的储存过程或函数

使用下面的SQL可以查看当前库有哪些存储过程或者函数

SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA  FROM
information_schema.Routines WHERE ROUTINE_SCHEMA="sgugo";

8.2 备份示例

备份整个 sgugo 数据库，并包含储存过程及事件

mysqldump -uroot -p -R -E --databases sgugo > fun_asgugo_bak.sql

9. mysqldump常用选项

运行帮助命令 mysqldump --help，可以获得特定版本的完整选项列表。mysqldump其他常用选项如下：

--add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。
--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。
--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
--all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。
--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skip-comments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。
--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者no_field_options。
--complete_insert, -c：使用包括列名的完整的INSERT语句。
--debug[=debug_options], -#[debug_options]：写调试日志。
--delete，-D：导入文本文件前清空表。
--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。
--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master-data。
--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可以加速插入。
--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
--force，-f：在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭--single-transaction和--lock-tables。
--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
--no-create-db，-n：该选项禁用CREATE DATABASE /!32312 IF NOT EXIST/db_name语句，如果给出-
-database或--all-database选项，就包含到输出中。
--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。
--no-data，-d：不写表的任何行信息，只转储表的结构。
--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。
--password[=password]，-p[password]：当连接服务器时使用的密码。
-port=port_num，-P port_num：用于连接的TCP/IP端口号。
--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。
--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
--silent，-s：沉默模式。只有出现错误时才输出。
--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。
--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。
--verbose，-v：冗长模式，打印出程序操作的详细信息。
--xml，-X：产生XML输出。

Ⅱ. 使用命令恢复数据

使用mysqldump命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用mysql命令来恢复备份的数据。

mysql命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表。通过INSERT语句来插入备份的数据。

1. 基本语法

mysql -u root -p [ dbname ] < backup.sql

其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。

指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。
不指定数据库名时，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MysQL服务器中已存在这些数据库

2. 单库备份中恢复单库

使用root用户，将之前备份的sgugo.sql文件中的备份导入数据库中

#如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称
mysql -uroot -p < sgugo.sql

#否则需要指定数据库名称
mysql -uroot -p sgugo < sgugo.sql

3. 全量备份恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql
mysql -uroot -pxxxxxx < all.sql

执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。

如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。对应的sql文件包含有CREATE DATABASE语句，可通过该语句创建数据库。创建数据库后，可以执行sql文件中的USE语句选择数据库，再创建表并插入记录。

5. 从单库备份中恢复单表

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。

举例：我们有sgugo整库的备份，但是由于class表误操作，需要单独恢复出这张表。

cat sgugo.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >
class_structure.sql

cat sgugo.sql | grep --ignore-case  'insert into `class`' > class_data.sql

#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复
use sgugo;
mysql> source class_structure.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> source class_data.sql;
Query OK, 1 row affected (0.01 sec)

Ⅲ. 物理备份和恢复

这种方式不建议使用。

1. 物理备份

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。

1.1 MySQL的数据库目录的默认位置

在Linux平台下，数据库目录位置通常为 /var/lib/mysql/
在MAC OSX平台下，数据库目录位置通常为 /usr/local/mysql/data

1.2 一致性保证

为了保证备份的一致性，需要保证：

方式1：备份前，将服务器停止。
方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

1.3 注意事项

种方式方便快速，但不是最好的备份方法，因为实际情况可能不允许停止MySQL服务器或者锁住表
这种方法对InnoDB存储引擎的表不适用。
物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。
在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同。

2. 物理恢复

直接将备份的数据复制到数据库目录

2.1 步骤

演示删除备份的数据库中指定表的数据
将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器
查询相关表的数据是否恢复。需要使用下面的 chown 操作

2.2 要求

必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同
- 因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的
这种方式对 MyISAM类型的表比较有效，对于InnoDB类型的表则不可用
- 因为InnoDB表的表空间不能直接复制
在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql
- chown -R mysql.mysql /var/lib/mysql/dbname
- 其中，两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录
- 提示 Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户组下的mysql用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用chown命令将文件夹的用户组变为mysql，将用户变为mysql。

Ⅳ. 表的导入导出

1. 导出：SELECT ... INTO OUTFILE

在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。

mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定内置的目录进行操作。

示例：使用SELECT...INTO OUTFILE将atguigu数据库中account表中的记录导出到文本文件。

-- 查询内置的导出目录：secure_file_priv值
SHOW GLOBAL VARIABLES LIKE '%secure%';
-- 结果：/var/lib/mysql-files/，该目录就是导出目录

-- 选择数据库atguigu
use atguigu;

-- 将account表导出
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";
-- 文件中只有数据

2. 导出：mysqldump

使用 mysqldump 命令也可以将某个表导出为文本文件

# 示例1：将atguigu数据库中account表中的记录导出到文本文件：
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account

执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。

打开account.sql文件，其内容包含创建account表的CREATE语句。
打开account.txt文件，其内容只包含account表中的数据。

可以额外使用FIELDS选项，要求account.txt文件中，字段之间使用逗号“,”间隔，所有字符类型字段值用双引号括起来：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

3. 导出：mysql命令

使用mysql语句进行表的导出

# 示例1：导出atguigu数据中account表中的记录到文本文件：
mysql -uroot -p --execute="SELECT * FROM account;" atguigu > "/var/lib/mysql-files/account.txt"
# 打开account.txt文件，其内容包含创建account表的数据

# 示例2：使用--veritcal参数将记录分为多行显示：
mysql -uroot -p --vertical --execute="SELECT * FROM account;" 
atguigu > "/var/lib/mysql-files/account_1.txt"

# 示例3：使用--xml参数，导出到xml文件
mysql -uroot -p --xml --execute="SELECT * FROM account;" 
atguigu >"/var/lib/mysql-files/account_3.xml"

如果要将表数据导出到html文件中，可以使用 --html 选项。然后可以使用浏览器打开。

4. 导入：LOAD DATA INFILE

使用LOAD DATA INFILE方式导入文本文件

4.1 示例1

使用SELECT...INTO OUTFILE将atguigu数据库中account表的记录导出到文本文件

-- 先将数据导出
SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';

-- 再删除account表中的数据：
DELETE FROM atguigu.account;

-- 最后从文本文件account.txt中恢复数据：
LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;

4.2 举例2

选择数据库atguigu，使用SELECT...INTO OUTFILE将atguigu数据库account表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号"，"间隔，所有字段值用双引号括起来：

-- 先将数据导出
SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

-- 再删除account表中的数据：
DELETE FROM atguigu.account;

-- 最后从文本文件account.txt中恢复数据：
LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE 
atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

5. 导入：mysqlimport

使用mysqlimport方式导入文本文件

示例：将account.txt文件内容导入到数据库atguigu的account表中：

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt'

Ⅴ. 数据库迁移

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。

数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。

根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL数据迁移方案大致可以分为物理迁移和逻辑迁移两类。通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。

1. 物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。

物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。

不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

2. 逻辑迁移

逻辑迁移适用范围更广，无论是部分迁移还是全量迁移，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具。

3. 迁移的版本问题

3.1 相同版本的数据库之间的迁移

指的是在主版本号相同的MySQL数据库之间进行数据库移动。

方式1：因为迁移前后MySQL数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。
方式2：最常见和最安全的方式是使用 mysqldump命令导出数据，然后在目标数据库服务器中使用MySQL命令导入。

# 示例 host1的机器中备份所有数据库,并将数据库迁移到名为host2的机器上
mysqldump –h host1 –uroot –p –-all-databases | mysql –h host2 –uroot –p

在上述语句中，“|”符号表示管道，其作用是将mysqldump备份的文件给mysql命令； “--all-databases”表示要迁移所有的数据库。通过这种方式可以直接实现迁移。

3.2 不同版本的数据库之间迁移

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷，因此需要把数据库升级到8.0版本

旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对默认字符集进行修改，不然可能无法正常显示数据。

高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。

3.3 不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。

迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会不同。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQLServer数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET类型，这些SQL Server数据库不支持。

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL的SQL语句兼容。

不同类型数据库之间的差异造成了互相迁移的困难，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并不是完全不可能。例如，可以使用 MyODBC 实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后手动更改 sql文件中的CREATE语句

4. 小结