前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >MySQL 数据备份恢复（一）select into outfile & load data infile

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

作者头像
Se7en258
发布于 2021-07-01 15:02:49
3.6K0
发布于 2021-07-01 15:02:49
举报
文章被收录于专栏：Se7en的架构笔记Se7en的架构笔记

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

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

语法

代码语言：javascript
复制
# 从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 匹配换行符。 \\ 匹配 \ 。
    • ^ 匹配输入字符串的开始位置，如果在方括号表达式中使用，此时表示不接受该字符集合。
    • {} 标记限定符表达式的开始。
    • | 指明两项之间的一个选择。
    • 数据中含有特殊字符。
    • 字段分隔符含有特殊字符。
    • 在以下几种情况下需要使用转义符：
    • 需要转移的特殊字符

默认值为：

代码语言：javascript
复制
# 字段由\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 服务器上。

高清图片：https://www.processon.com/view/link/60b5eafbe0b34d06f2021455

select into outfile 导出数据

MySQL 服务器参数设置

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

代码语言：javascript
复制
[mysqld]
# 允许导出或导入数据的目录
secure-file-priv=/data

# 目录为空表示允许所有目录
# secure-file-priv=

修改完成后重启 MySQL 服务：

代码语言：javascript
复制
systemctl restart mysql

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

代码语言：javascript
复制
mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/                    |
+---------------------------+
1 row in set (0.01 sec)

另外还需要确保运行 MySQL 服务的用户具有该目录的读写权限，本例中运行 MySQL 的用户是 mysql。

创建 /data 目录，并且设置该目录属于 mysql 用户。

代码语言：javascript
复制
mkdir /data
chown -R mysql.mysql /data
准备数据

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

代码语言：javascript
复制
# 创建数据库
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 表的数据导出，导出数据的文件中字段由 , 分割。

代码语言：javascript
复制
mysql> select * from test.person into outfile '/data/person.txt' fields terminated by ',';

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

代码语言：javascript
复制
[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 文件：

代码语言：javascript
复制
[mysqld]
# 允许导出或导入数据的目录
secure-file-priv=/data

# 目录为空表示允许所有目录
# secure-file-priv=

# 允许MySQL 服务接受导入数据，
# MySQL 5.x版本默认是1，允许
# MySQL 8.x版本默认不允许
local-infile=1

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

代码语言：javascript
复制
mysql> SET GLOBAL local_infile = 'ON';
mysql> SET GLOBAL local_infile = 1;
mysql> SET GLOBAL local_infile = true;

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

代码语言：javascript
复制
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 服务：

代码语言：javascript
复制
systemctl restart mysql
客户端参数设置

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

代码语言：javascript
复制
[mysql]
# 允许客户端导入数据，默认不允许
local-infile=1

方式二：登录时指定参数

代码语言：javascript
复制
shell> mysql -uchengzw -h 192.168.1.15 -p123456 --local-infile=1
导入数据

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

代码语言：javascript
复制
mysql> truncate test.person;

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

代码语言：javascript
复制
mysql> load data infile '/data/person.txt' into table test.person fields terminated by ',';

查看导入的数据：

代码语言：javascript
复制
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 文件：

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

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

代码语言：javascript
复制
[mysql]
# 允许客户端导入数据，默认不允许
local-infile=1

方式二：登录时指定参数

代码语言：javascript
复制
shell> mysql -uchengzw -h 192.168.1.15 -p123456 --local-infile=1
导入数据

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

代码语言：javascript
复制
mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',';

查看导入的数据：

代码语言：javascript
复制
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)
导入数据例子

查看文件内容：

代码语言：javascript
复制
❯ 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：

代码语言：javascript
复制
mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',' enclosed
by '"' lines starting by '\t';

查看导入后的数据：

代码语言：javascript
复制
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)

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

忽略前两行

代码语言：javascript
复制
mysql> load data local infile '/tmp/person.txt' into table test.person fields terminated by ',' enclosed
by '"' lines starting by '\t' ignore 2 rows;
代码语言：javascript
复制
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

代码语言：javascript
复制
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;
代码语言：javascript
复制
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

代码语言：javascript
复制
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');
代码语言：javascript
复制
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：

代码语言：javascript
复制
pip install PyMySQL

python 代码如下：

代码语言：javascript
复制
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()

参考链接

  • https://www.cnblogs.com/f-ck-need-u/p/9013643.html
  • https://www.cnblogs.com/applelife/p/10516326.html
  • https://www.cnblogs.com/shockerli/p/python3-pymysql.html
  • https://www.runoob.com/python3/python3-mysql.html
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2021-06-01，如有侵权请联系 cloudcommunity@tencent.com 删除
云数据库 SQL Server
数据库
sql
https
网络安全

本文分享自 Se7en的架构笔记 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

云数据库 SQL Server
数据库
sql
https
网络安全
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 语法
  • 导入导出配置说明
  • select into outfile 导出数据
    • MySQL 服务器参数设置
      • 准备数据
        • 导出数据
        • load data infile 导入数据
          • 方式一：不使用 local 参数
            • 方式二：使用 local 参数
              • 导入数据例子
                • PyMySQL 导入数据
                • 参考链接
                相关产品与服务
                云数据库 SQL Server
                腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
                产品介绍产品文档
                云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
                领券

                腾讯云开发者

                扫码关注腾讯云开发者

                扫码关注腾讯云开发者

                领取腾讯云代金券

                热门产品

                热门推荐

                更多推荐

                Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

                深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

                腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

                问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

                Copyright © 2013 - 2024 Tencent Cloud.

                All Rights Reserved. 腾讯云 版权所有

                登录 后参与评论