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 服务器上。

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> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/                    |
+---------------------------+
1 row in set (0.01 sec)

另外还需要确保运行 MySQL 服务的用户具有该目录的读写权限，本例中运行 MySQL 的用户是 mysql。创建 /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()

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

语法

导入导出配置说明

select into outfile 导出数据

MySQL 服务器参数设置

准备数据

导出数据

load data infile 导入数据

方式一：不使用 local 参数

MySQL 服务器参数设置

客户端参数设置

导入数据

方式二：使用 local 参数

MySQL 服务器参数设置

客户端参数设置

导入数据

导入数据例子

PyMySQL 导入数据

参考链接

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

语法

导入导出配置说明

select into outfile 导出数据

MySQL 服务器参数设置

准备数据

导出数据

load data infile 导入数据

方式一：不使用 local 参数

MySQL 服务器参数设置

客户端参数设置

导入数据

方式二：使用 local 参数

MySQL 服务器参数设置

客户端参数设置

导入数据

导入数据例子

PyMySQL 导入数据

参考链接

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像