AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL 数据备份恢复（一）select into outfile & load data infile

2021-12-23 303
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群版 2核4GB 100GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用版 2核4GB 50GB
简介： MySQL 数据备份恢复（一）select into outfile & load data infile

load data infile 和 select into outfile 是 MySQL 用于导入和导出数据的命令。select into outfile 语句用于将检索出来的数据按格式导出到文件中。load data infile 是将带有格式的数据文件导入到表中。使用 load data infile 的方式插入数据比直接执行 insert 语句插入至少快几十倍。

导出、导入数据时需要指定格式(如不指定，则使用默认)。格式涉及几个方面：字段分隔符、行分隔符、引用符号、转义符号。

语法

# 从MySQL服务器导出数据
SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
# 导入数据到MySQL服务器
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
        [export_options]
    [IGNORE number {LINES|ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
# 额外参数，导入导出都可以使用
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
  • 其中 'char' 表示只能使用一个字符，'string' 表示可以指定多个字符。
  • fields terminated by 'string' 指定字段分隔符。
  • enclosed by 'char' 指定所有字段都使用 char 符号包围。
  • lines starting by 'string'指定行开始符。
  • escaped by 'char' 指定转义符。
  • $ 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配如\n或\r。
  • () 标记一个子表达式的开始和结束位置。子表达式可以获取供以后使用。要匹配这些字符。
  • * 匹配前面的子表达式零次或多次。
  • + 匹配前面的子表达式一次或多次。
  • . 匹配除换行符 \n 之外的任何单字符。
  • [] 标记一个中括号表达式的开始。
  • ? 匹配前面子表达式零次或一次，或指明一个非贪婪限定符。
  • \ 将下一个字符标记为或特殊字符或原意字符。例如：n 匹配字符 n，而 \n 匹配换行符。 \\ 匹配 \ 。
  • ^ 匹配输入字符串的开始位置，如果在方括号表达式中使用，此时表示不接受该字符集合。
  • {} 标记限定符表达式的开始。
  • | 指明两项之间的一个选择。
  • 数据中含有特殊字符。
  • 字段分隔符含有特殊字符。
  • 在以下几种情况下需要使用转义符：
  • 需要转移的特殊字符

默认值为：

# 字段由\t分割               字段由''包围     转移符号为\
fileds terminated by '\t' enclosed by '' escaped by '\\'
# 每行由换行符\n结束          每行从空值''开始
lines terminated by '\n' starting by ''

导入导出配置说明

导出数据使用 select into outfile 命令，导出的文件只能位于 MySQL 服务器上。 导入数据使用 load data infile 命令，当使用 local 参数时，文件位于客户端上；当不使用 local 参数时，文件位于 MySQL 服务器上。image.png

select into outfile 导出数据

MySQL 服务器参数设置

导出数据必须要在 MySQL 服务器上设置 secure-file-priv 参数，默认值为 NULL，表示禁止 MySQL 服务器上的所有目录有用于导入或导出数据。这个变量是静态全局变量，只能通过 my.cnf 配置文件修改。

[mysqld]
# 允许导出或导入数据的目录
secure-file-priv=/data
# 目录为空表示允许所有目录
# secure-file-priv=

修改完成后重启 MySQL 服务：

systemctl restart mysql

登录 MySQL 以后使用以下命令查询，结果不为 NULL 则表示设置成功。

mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/                    |
+---------------------------+
1 row in set (0.01 sec)

另外还需要确保运行 MySQL 服务的用户具有该目录的读写权限，本例中运行 MySQL 的用户是 mysql。image.png创建 /data 目录，并且设置该目录属于 mysql 用户。

mkdir /data
chown -R mysql.mysql /data

准备数据

先在 MySQL 数据库中准备一些数据用于导出数据。

# 创建数据库
create database test;
# 切换数据库
use test;
# 创建表
create  table person(id int primary key,sex char(3),name char(20),ins_day date);
# 插入数据
insert into person values(1,'nan','longshuai1','2010-04-19'),
                    (2,'nan','longshuai2','2011-04-19'),
                    (3,'nv','xiaofang1','2012-04-19'),
                    (4,'nv','xiaofang2','2013-04-19'),
                    (5,'nv','xiaofang3','2014-04-19'),
                    (6,'nv','xiaofang4','2015-04-19'),
                    (7,'nv','tun\'er','2016-04-19'),
                    (8,'nan','longshuai3','2017-04-19');

导出数据

使用以下命令将 person 表的数据导出，导出数据的文件中字段由 , 分割。

mysql> select * from test.person into outfile '/data/person.txt' fields terminated by ',';

在 MySQL 服务器上的 /data 目录下查看 person.txt 文件：

[root@mysql-ocp tmp]# cat /data/person.txt 
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

load data infile 导入数据

使用 load data infile 命令导入数据时，如果使用 local 参数表示从客户端读取文件，指定的目录是客户端上的目录；如果没有使用 local 参数，表示从 MySQL 服务器上读取文件，指定的目录是 MySQL 服务器上的目录。

方式一：不使用 local 参数

不使用 local 参数时，读取的文件是在 MySQL 服务器上的。需要在 MySQL 服务器和客户端分别设置 local-infile 参数。

MySQL 服务器参数设置

编辑 MySQL 服务器的 my.cnf 文件：

[mysqld]
# 允许导出或导入数据的目录
secure-file-priv=/data
# 目录为空表示允许所有目录
# secure-file-priv=
# 允许MySQL 服务接受导入数据，
# MySQL 5.x版本默认是1，允许
# MySQL 8.x版本默认不允许
local-infile=1

另外 local-infile 参数可以使用 set 命令开启，以下三个命令等效：

mysql> SET GLOBAL local_infile = 'ON';
mysql> SET GLOBAL local_infile = 1;
mysql> SET GLOBAL local_infile = true;

设置完成后查看 MySQL 服务器的参数：

mysql> show variables like 'secure_file_priv';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| secure_file_priv | /data/ |
+------------------+--------+
1 row in set (0.04 sec)
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.22 sec)

编辑完成保存退出后，重启 MySQL 服务：

systemctl restart mysql
客户端参数设置

方式一：编辑客户端的 my.cnf 文件

[mysql]
# 允许客户端导入数据，默认不允许
local-infile=1

方式二：登录时指定参数

shell> mysql -uchengzw -h 192.168.1.15 -p123456 --local-infile=1
导入数据

导入数据之前先将 person 表的数据清空：

mysql> truncate test.person;

执行以下命令读取 MySQL 服务器上 /data 目录的 person.txt 文件，将数据导入 MySQL 数据库：

mysql> load data infile '/data/person.txt' into table test.person fields terminated by ',';

查看导入的数据：

mysql> select * from test.person;
+----+------+------------+------------+
| id | sex  | name       | ins_day    |
+----+------+------------+------------+
|  1 | nan  | longshuai1 | 2010-04-19 |
|  2 | nan  | longshuai2 | 2011-04-19 |
|  3 | nv   | xiaofang1  | 2012-04-19 |
|  4 | nv   | xiaofang2  | 2013-04-19 |
|  5 | nv   | xiaofang3  | 2014-04-19 |
|  6 | nv   | xiaofang4  | 2015-04-19 |
|  7 | nv   | tun'er     | 2016-04-19 |
|  8 | nan  | longshuai3 | 2017-04-19 |
+----+------+------------+------------+
8 rows in set (0.01 sec)

方式二：使用 local 参数

使用 local 参数时，读取文件的是在客户端上的。需要在 MySQL 服务器和客户端分别设置 local-infile 参数。

MySQL 服务器参数设置

编辑 MySQL 服务器的 my.cnf 文件：

[mysqld]
# 允许MySQL 服务接受导入数据，
# MySQL 5.x版本默认是1，允许
# MySQL 8.x版本默认不允许
local-infile=1
客户端参数设置

方式一：编辑客户端的 my.cnf 文件

[mysql]
# 允许客户端导入数据，默认不允许
local-infile=1

方式二：登录时指定参数

shell> mysql -uchengzw -h 192.168.1.15 -p123456 --local-infile=1
导入数据

将 MySQL 服务器上的 person.txt 文件拷贝到客户端上的 /tmp 目录上。执行以下命令读取客户端本地文件写入 MySQL 数据库。

mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',';

查看导入的数据：

mysql> select * from test.person;
+----+------+------------+------------+
| id | sex  | name       | ins_day    |
+----+------+------------+------------+
|  1 | nan  | longshuai1 | 2010-04-19 |
|  2 | nan  | longshuai2 | 2011-04-19 |
|  3 | nv   | xiaofang1  | 2012-04-19 |
|  4 | nv   | xiaofang2  | 2013-04-19 |
|  5 | nv   | xiaofang3  | 2014-04-19 |
|  6 | nv   | xiaofang4  | 2015-04-19 |
|  7 | nv   | tun'er     | 2016-04-19 |
|  8 | nan  | longshuai3 | 2017-04-19 |
+----+------+------------+------------+
8 rows in set (0.01 sec)

导入数据例子

查看文件内容：

❯ cat /tmp/person.txt
 "1","nan","longshuai1","2010-04-19"
 "2","nan","longshuai2","2011-04-19"
 "3","nv","xiaofang1","2012-04-19"
 "4","nv","xiaofang2","2013-04-19"
 "5","nv","xiaofang3","2014-04-19"
 "6","nv","xiaofang4","2015-04-19"
 "7","nv","tun\'er","2016-04-19"
 "8","nan","longshuai3","2017-04-19"

文件每行以 \t 开头，每个字段由 " 包围，使用以下语句将文件数据导入 MySQL：

mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',' enclosed
by '"' lines starting by '\t';

查看导入后的数据：

mysql> select * from test.person;
+----+------+------------+------------+
| id | sex  | name       | ins_day    |
+----+------+------------+------------+
|  1 | nan  | longshuai1 | 2010-04-19 |
|  2 | nan  | longshuai2 | 2011-04-19 |
|  3 | nv   | xiaofang1  | 2012-04-19 |
|  4 | nv   | xiaofang2  | 2013-04-19 |
|  5 | nv   | xiaofang3  | 2014-04-19 |
|  6 | nv   | xiaofang4  | 2015-04-19 |
|  7 | nv   | tun'er     | 2016-04-19 |
|  8 | nan  | longshuai3 | 2017-04-19 |
+----+------+------------+------------+
8 rows in set (0.03 sec)

除了前面介绍的基本导入和导出操作以外，还可以在导入和导出的时候对数据做过滤，修改等操作。

忽略前两行

mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',' enclosed
by '"' lines starting by '\t' ignore 2 rows;
mysql> select * from test.person;
+----+------+------------+------------+
| id | sex  | name       | ins_day    |
+----+------+------------+------------+
|  3 | nv   | xiaofang1  | 2012-04-19 |
|  4 | nv   | xiaofang2  | 2013-04-19 |
|  5 | nv   | xiaofang3  | 2014-04-19 |
|  6 | nv   | xiaofang4  | 2015-04-19 |
|  7 | nv   | tun'er     | 2016-04-19 |
|  8 | nan  | longshuai3 | 2017-04-19 |
+----+------+------------+------------+

id值增加5

mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',' enclosed
by '"' lines starting by '\t' set id=id+5;
mysql> select * from test.person;
+----+------+------------+------------+
| id | sex  | name       | ins_day    |
+----+------+------------+------------+
|  6 | nan  | longshuai1 | 2010-04-19 |
|  7 | nan  | longshuai2 | 2011-04-19 |
|  8 | nv   | xiaofang1  | 2012-04-19 |
|  9 | nv   | xiaofang2  | 2013-04-19 |
| 10 | nv   | xiaofang3  | 2014-04-19 |
| 11 | nv   | xiaofang4  | 2015-04-19 |
| 12 | nv   | tun'er     | 2016-04-19 |
| 13 | nan  | longshuai3 | 2017-04-19 |
+----+------+------------+------------+
8 rows in set (0.04 sec)

name字段后面拼接 @qq.com

mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',' enclosed
by '"' lines starting by '\t' set name=concat(name,'@qq.com');
mysql> select * from test.person;
+----+------+-------------------+------------+
| id | sex  | name              | ins_day    |
+----+------+-------------------+------------+
|  1 | nan  | longshuai1@qq.com | 2010-04-19 |
|  2 | nan  | longshuai2@qq.com | 2011-04-19 |
|  3 | nv   | xiaofang1@qq.com  | 2012-04-19 |
|  4 | nv   | xiaofang2@qq.com  | 2013-04-19 |
|  5 | nv   | xiaofang3@qq.com  | 2014-04-19 |
|  6 | nv   | xiaofang4@qq.com  | 2015-04-19 |
|  7 | nv   | tun'er@qq.com     | 2016-04-19 |
|  8 | nan  | longshuai3@qq.com | 2017-04-19 |
+----+------+-------------------+------------+
8 rows in set (0.04 sec)

PyMySQL 导入数据

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb。

使用以下命令安装最新版的 PyMySQL：

pip install PyMySQL

python 代码如下：

import pymysql
# 创建连接
# local_infile=True，表示允许客户端通过 load data infile 导入数据，和前面设置的 local-infile=1 含义相同
# autocommit=True 表示自动提交事务
db=pymysql.connect(host='192.168.1.15',
                             port=3306,
                             user='chengzw',
                             passwd='123456',
                             db='test',
                             charset='utf8',
                             local_infile=True,
                             autocommit=True)
# 获取cursor
cursor = db.cursor()
# 执行SQL语句
# 执行导入数据操作，| 可以用 \ 转义也可以不用
cursor.execute("load data local infile '/tmp/person.txt' into table test.person fields terminated by '|'")
#cursor.execute('INSERT INTO `person` (`id`,`sex`,`name`,`ins_day`) VALUES (%s,%s,%s,%s)', (1,'nv','chengzw','2021-01-02'))
print("执行结果为:",cursor.lastrowid)
# 查询数据
cursor.execute('SELECT * FROM `person`')
data=cursor.fetchall()
print(data)
# 关闭连接
cursor.close()
db.close()

参考链接

文章标签：
云数据库 RDS MySQL 版
Python
关系型数据库
MySQL
数据库
关键词：
云数据库 RDS MySQL 版into
云数据库 RDS MySQL 版select
云数据库 RDS MySQL 版数据备份
云数据库 RDS MySQL 版data
云数据库 RDS MySQL 版into outfile
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
Se7en258
目录
相关文章
wljslmz
|
8天前
|
存储 关系型数据库 MySQL
在 MySQL 中使用 Insert Into Select
【8月更文挑战第11天】
wljslmz
24 0
在 MySQL 中使用 Insert Into Select
不吃核桃
|
26天前
|
存储 关系型数据库 文件存储
面试题MySQL问题之简单的SELECT操作在MVCC下加锁如何解决
面试题MySQL问题之简单的SELECT操作在MVCC下加锁如何解决
不吃核桃
30 2
青欢
|
2月前
|
SQL 关系型数据库 MySQL
MySQL Online DDL（Data Definition Language）
MySQL Online DDL（Data Definition Language）
青欢
37 1
蓝易云
|
1月前
|
关系型数据库 MySQL 索引
MySQL之优化SELECT语句
以上只是一些基本的优化策略，具体的优化方案还需要根据实际的业务需求和数据情况来定制。
蓝易云
26 0
石宗昊
|
2月前
|
关系型数据库 MySQL Linux
mysql 将select结果导出文件 linux
mysql 将select结果导出文件 linux
石宗昊
28 3
啊猪是的读来过倒
|
2月前
|
关系型数据库 MySQL 数据库
MySQL SELECT查询实战：练习题精选，提升你的数据库查询技能
MySQL SELECT查询实战：练习题精选，提升你的数据库查询技能
啊猪是的读来过倒
55 0
啊猪是的读来过倒
|
2月前
|
SQL 关系型数据库 MySQL
深入探索MySQL SELECT查询：从基础到高级，解锁数据宝藏的密钥
深入探索MySQL SELECT查询：从基础到高级，解锁数据宝藏的密钥
啊猪是的读来过倒
95 0
。思索
|
6天前
|
存储 关系型数据库 MySQL
MySQL——数据库备份上传到阿里云OSS存储
MySQL——数据库备份上传到阿里云OSS存储
。思索
20 0
热爱技术的小郑
|
6天前
|
缓存 NoSQL Redis
一天五道Java面试题----第九天（简述MySQL中索引类型对数据库的性能的影响---------＞缓存雪崩、缓存穿透、缓存击穿）
这篇文章是关于Java面试中可能会遇到的五个问题，包括MySQL索引类型及其对数据库性能的影响、Redis的RDB和AOF持久化机制、Redis的过期键删除策略、Redis的单线程模型为何高效，以及缓存雪崩、缓存穿透和缓存击穿的概念及其解决方案。
热爱技术的小郑
29 2
小王老师呀
|
8天前
|
Oracle 关系型数据库 MySQL
Mysql和Oracle数据库死锁查看以及解决
【8月更文挑战第11天】本文介绍了解决MySQL与Oracle数据库死锁的方法。MySQL可通过`SHOW ENGINE INNODB STATUS`查看死锁详情，并自动回滚一个事务解除死锁；也可手动KILL事务。Oracle则通过查询V$LOCK与V$SESSION视图定位死锁，并用`ALTER SYSTEM KILL SESSION`命令终止相关会话。预防措施包括遵循ACID原则、优化索引及拆分大型事务。
小王老师呀
29 3

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    mysql加索引的时候到底会不会锁表.深入解析
  • 3
    k8s-部署实例(mysql+wordpress)
  • 4
    springboot+redis+rabbitmq实现模拟秒杀系统(附带docker安装mysql，rabbitmq，redis教程)
  • 5
    mysql存储过程
  • 6
    收集的mysql热复制的资料与常用语句
  • 7
    修改MYSQL登录密码
  • 8
    【转】源码安装Mysql，补装innodb引擎方法
  • 9
    mysql5.6 主从
  • 10
    MySQL Events学习笔记
  • 1
    自动化备份MySQL数据库并上传到远程FTP服务器
    87
  • 2
    DataWorks操作报错合集之DataWorks在同步mysql时报错Code:[Framework-02]，mysql里面有个json类型字段，是什么原因导致的
    87
  • 3
    【Go语言专栏】使用Go语言连接MySQL数据库
    772
  • 4
    DataWorks产品使用合集之在DataWorks中，如何通过PolarDB for MySQL来查看binlog日志
    74
  • 5
    datagrip连接mysql报错： No appropriate protocol (protocol is disabled or cipher suites are inappropriate
    63
  • 6
    Ubuntu 20.04 + mysql8 安装以及配置大小写不敏感
    260
  • 7
    DataWorks产品使用合集之DataWorks还有就是对于mysql中的表已经存在数据了，第一次全量后面增量同步的步骤如何解决
    53
  • 8
    centos7在线安装jdk1.8+tomcat+mysql8+nginx+docker
    348
  • 9
    DataWorks产品使用合集之在DataWorks中，将数据集成功能将AnalyticDB for MySQL中的数据实时同步到MaxCompute中如何解决
    57
  • 10
    DataWorks产品使用合集之在DataWorks中，将ODPS数据导入到MySQL的步骤如何解决
    62

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    通义千问API入门教程