MySQL中的数据备份与还原(导出导入)实践总结-阿里云开发者社区

数据备份与还原的常用方式：数据表备份，单表数据备份，SQL备份和增量备份。

物理备份与逻辑备份

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

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

【1】mysqldump实现逻辑备份

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

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

基本语法

mysqldump -u 用户名称 -h 主机名称 -p密码 待备份的数据库名称[tbname,[tbname...]] > 备份文件名称.sql
# 如果在Windows环境下则是mysqldump.exe

当然，备份的文件并非一定要求后缀名称为.sql , 后缀名为.txt也是可以的。

① 备份单个数据库

如下备份数据库testindex，包含该库所有表和数据。

mysqldump -uroot -h localhost -p testindex > /var/lib/mysql/backup/testindex.sql

如下所示是备份的文件，其中下面这部分是MySQL的数据库版本信息，非MySQL将不会识别。

# 将系统变量赋值给用户变量，末尾会再恢复 40101表示数据库版本 4.01.01
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0                                                                                                                                                              */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--开头的都是SQL语句的注释。以 /*! 开头、*/ 结尾的语句为可执行的MySQL注释，这些语句可以被MySQL执行，但在其他数据库管理系统中被作为注释忽略，这可以提高数据库的可移植性。

可以看到文件开头指明了备份文件使用的mysqldump工具的版本号，接下来是备份主机信息以及备份的数据库的名称，最后是MySQL服务器的版本号，这里是8.0.25。

② 备份全部数据库

mysqldump -uroot -h localhost -p  --all-databases> /var/lib/mysql/backup/alldatabases.sql
#或
mysqldump -uroot -h localhost -p  -A> /var/lib/mysql/backup/alldatabases.sql

③ 备份部分数据库

使用 --databases 或 -B 参数，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。

语法如下：

mysqldump -uroot -h localhost -p  --databases [dbname1 [dbname2...]]> /var/lib/mysql/backup/备份文件名称.sql
#或
mysqldump -uroot -h localhost -p  -B [dbname1 [dbname2...]]> /var/lib/mysql/backup/备份文件名称.sql

④ 备份部分表

mysqldump -uroot -h localhost -p  数据库名称 [表名1 [表名2...]]> /var/lib/mysql/backup/备份文件名称.sql

⑤ 备份单表的部分数据

有时候我们只需要备份某个表的一部分数据，这时可以使用--where选项。

举例备份用户表中id小于5的数据：

mysqldump -uroot -p testindex tb_sys_user --where="id<5" > user_part_id5_low_bak.sql

⑥ 排除某些表的备份

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

mysqldump -uroot -p testindex --ignore-table=testindex.tb_sys_user > no_user_bak.sql

⑦ 只备份结构或只备份数据

只备份结构可以使用 --no-data 简写为 -d选项，只备份数据可以使用--no-create-info简写为-t选项

只备份结构

mysqldump -uroot -p testindex --no-data > no_data.sql

只备份数据

mysqldump -uroot -p testindex --no-create-info > no_create_info.sql

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

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

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

select specific_name,routine_type,routine_schema from information_schema.routines where ro

【2】mysql命令恢复数据

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

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

基本语法：

mysql -u 用户名 -p [dbname] < 备份文件.sql

其中，dbname参数表示数据库名称，该参数是可选参数。指定数据库名称时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时SQL文件中包含有create database语句，不需要MySQL服务器中已存在这些数据库。

① 单库备份中恢复单库

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称：

mysql -uroot -p < testindex.sql

否则需要指定数据库名称：

mysql -uroot -p testindex < testindex.sql

② 全量备份恢复

如果我们有数据库昨天的全量备份，可以如下操作

mysql -uroot -p < all.sql

如果使用 --all-databases 参数备份了所有的数据库，那么恢复时不需要指定数据库。对应的SQL文件包含有create database语句，可通过该语句创建数据库。

③ 从全量备份中恢复单库

如下所示从全量备份中分离出单个库的备份：

sed -n '/^-- Current Database: `testindex`/,/^-- Current Database: `/p' alldatabases.sql

分离完成后我们再导入testindex.sql即可恢复单个库。

④ 从单库备份中恢复单表

如我们有testindex库的备份，但是只需要恢复tb_sys_user表。

cat testindex.sql|sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `tb_sys_user`/!d;q' >tb_sys_user_structure.sql
cat testindex.sql| grep --ignore-case 'insert into `tb_sys_user`' >tb_sys_user_data.sql

用shell语法分离出创建表的语句及插入数据的语句后，再依次导入即可完成恢复。

⑤使用SQL指令进行还原

语法格式如下：

source 文件目录

示例如下：

source /var/lib/mysql/backup/testindex.sql

【3】物理备份与恢复

① 物理备份：直接复制整个数据库

直接将MySQL中的数据库文件复制出来，这种方法最简单速度页最快。但为了保证备份的一致性，需要保证：

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

这种方式方便、快捷，但不是最好的备份方法。因为实际情况可能不允许停止MySQL服务器或者锁住表，而且这种方法对InnoDB存储引擎的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便。但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。

注意，物理备份完毕后，执行 UNLOCK TABLES来结束其他客户对表的修改行为。

# 将数据库文件复制到backup下
cp -r /var/lib/mysql/testindex /var/lib/mysql/backup/
#删除数据库文件
rm -rf /var/lib/mysql/testindex
# 这时候进行验证，可以看到该数据库下表数据时不存在的

② 物理恢复：直接复制到数据库目录

需要注意如下两点

必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同，才能保证这两个MySQL数据库文件类型是相同的
这种方式对MyISAM类型的表有效，对于InnoDB类型的表无效。因为InnoDB的表空间不能直接复制

在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql：

#赋予权限
chown -R mysql.mysql /var/lib/mysql/dbname

其中，两个mysql分表表示组合用户，“-R”参数可以改变文件夹下所有子文件的用户和组。

#恢复数据库文件
cp -r  /var/lib/mysql/backup/testindex /var/lib/mysql/
#赋予权限
chown -R mysql.mysql /var/lib/mysql/testindex
#重启MySQL服务
systemctl restart mysqld

【4】单表数据备份与还原

① select…into outfile表数据备份

特点：每次只能备份一张表，只能备份数据(表结构不能备份)；

通常的使用方式：将表中的数据导出到文件；
备份：从表中选出一部分数据保存到外部的文件中；

通常在数据(表)库备份中不会使用该方式。

语法格式如下：

select * /字段列表 into outfile 文件所在路径 from 数据源;
//前提：外部文件不存在。

mysql默认对导出的目录有权限限制，也就是说使用命令进行导出的时候，需要指定目录操作。可以查询secure_file_priv 变量值来查看目录：

mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

参数secure_file_priv 的可选值和作用分别是：

如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；
如果设置为一个表示路径的字符串，就要去生成的文件只能放在这个指定的目录或者它的子目录
如果设置为NULL，就表示禁止在这个MySQL实例上执行 select…info outfile操作。

mysql> select * from tb_sys_user into outfile "/opt/tb_sys_user.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it canno                                                                                                                                               t execute this statement

我们导出到/var/lib/mysql-files/ 则正常执行：

select * from tb_sys_user into outfile "/var/lib/mysql-files/tb_sys_user.txt";

高级备份–自己指定字段和行的处理方式

语法格式如下：

select */字段列表 into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源;

Fields ：字段处理。

Enclosed by：字段使用什么内容包裹，默认是 ‘’—空字符串。

Terminated by：字段以什么结束，默认是’\t’----Tab键。

Escaped by：特殊符号用什么方式处理，默认是’\'，使用反斜杠转义。

Lines：行处理。

Starting by：每行以什么开始，默认是 ‘’-----空字符串。

Terminated by：每行以什么结束，默认是’\r\n’----换行符。

测试如下：

select * into OUTFILE 'D:/temDirectory/backup.txt' 
-- 字段处理
FIELDS
ENCLOSED by '"'-- 数据使用双引号包裹；
TERMINATED by '|' -- 使用竖线分隔字段数据；
-- 行处理
LINES
STARTING by 'START:'
FROM p_user

② 使用mysqldump命令导出文本文件

mysqldump  -uroot -p -T "/var/lib/mysql-files" testindex tb_sys_use

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

上述命令生成的tb_sys_user.sql文件内容包含表的create语句，没有数据的insert into语句。

b_sys_user.txt内容只包含表中的数据：

[root@localhost mysql-files]# cat tb_sys_user.txt
1       jane    11      男
2       tom     12      男
3       lucy    18      女
4       jack    17      男
5       janus   16      男
6       lily    17      女

mysqldump导出到文本文件时，也可以使用fields选项，要求字段之间使用逗号分隔，所有字符类型字符值用双引号括起来：

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

③ 使用mysql命令导出文本文件

如下所示导出数据库testindex中tb_sys_user表数据到文本文件：

mysql -uroot -p --execute="select * from tb_sys_user;" testindex > "/var/lib/mysql-files/tb_sys_user.txt"
[root@localhost mysql-files]# cat tb_sys_user.txt
id      name    age     sex
1       jane    11      男
2       tom     12      男
3       lucy    18      女
4       jack    17      男
5       janus   16      男
6       lily    17      女

使用 --veritcal参数将该提交记录分为多行显式：

mysql -uroot -p  --vertical --execute="select * from tb_sys_user;" testindex > "/var

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

将一个在外部保存的数据重新恢复到表中(前提是表结构必须存在)。

语法格式如下：

Load data infile 文件所在路径 into table 表名[(字段列表)] [fields 字段处理]  [lines 行处理]
//怎么导出去的就怎么还原(fields，lines)

示例如下：

#Linux下
load data infile '/var/lib/mysql-files/tb_sys_user.txt' into table testindex.tb_sys_user;
# windows下 如果导出的时候使用了fields lines选项...
Load data infile 'D:/temDirectory/backup.txt'  into table p_user 
FIELDS
ENCLOSED by '"'-- 数据使用双引号包裹；
TERMINATED by '|' -- 使用竖线分隔字段数据；
-- 行处理
LINES
STARTING by 'START:'

⑤ 使用mysqlimport方式导入文本文件

导出的时候字段之间使用逗号隔开，字段值使用双引号括起来:

select * from testindex.tb_sys_user into outfile '/var/lib/mysql-files/tb_sys_user.txt' fields terminated by ',' enclosed by '\"' ;

使用mysqlimport命令将tb_sys_user.txt文件内容导入到数据库testindex的tb_sys_user表中：

mysqlimport -uroot -p testindex '/var/lib/mysql-files/tb_sys_user.txt' --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

MySQL中的数据备份与还原(导出导入)实践总结

【1】mysqldump实现逻辑备份

① 备份单个数据库

② 备份全部数据库

③ 备份部分数据库

④ 备份部分表

⑤ 备份单表的部分数据

⑥ 排除某些表的备份

⑦ 只备份结构或只备份数据

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

【2】mysql命令恢复数据

① 单库备份中恢复单库

② 全量备份恢复

③ 从全量备份中恢复单库

④ 从单库备份中恢复单表

⑤使用SQL指令进行还原

【3】物理备份与恢复

① 物理备份：直接复制整个数据库

② 物理恢复：直接复制到数据库目录

【4】单表数据备份与还原

① select…into outfile表数据备份

② 使用mysqldump命令导出文本文件

③ 使用mysql命令导出文本文件

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL中的数据备份与还原(导出导入)实践总结

【1】mysqldump实现逻辑备份

① 备份单个数据库

② 备份全部数据库

③ 备份部分数据库

④ 备份部分表

⑤ 备份单表的部分数据

⑥ 排除某些表的备份

⑦ 只备份结构或只备份数据

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

【2】mysql命令恢复数据

① 单库备份中恢复单库

② 全量备份恢复

③ 从全量备份中恢复单库

④ 从单库备份中恢复单表

⑤使用SQL指令进行还原

【3】物理备份与恢复

① 物理备份：直接复制整个数据库

② 物理恢复：直接复制到数据库目录

【4】单表数据备份与还原

① select…into outfile表数据备份

② 使用mysqldump命令导出文本文件

③ 使用mysql命令导出文本文件

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像