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

您所在的位置：网站首页 › mysql数据备份恢复命令 › MySQL 数据备份恢复（一）select into outfile & load data infile

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

2023-12-28 09:29| 来源: 网络整理| 查看: 265

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

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

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 服务：

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 ',';

查看导入的数据：

查看文件内容：

❯ 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> 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 | [email protected] | 2010-04-19 | | 2 | nan | [email protected] | 2011-04-19 | | 3 | nv | [email protected] | 2012-04-19 | | 4 | nv | [email protected] | 2013-04-19 | | 5 | nv | [email protected] | 2014-04-19 | | 6 | nv | [email protected] | 2015-04-19 | | 7 | nv | tun'[email protected] | 2016-04-19 | | 8 | nan | [email protected] | 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() 参考链接https://www.cnblogs.com/f-ck-need-u/p/9013643.htmlhttps://www.cnblogs.com/applelife/p/10516326.htmlhttps://www.cnblogs.com/shockerli/p/python3-pymysql.htmlhttps://www.runoob.com/python3/python3-mysql.html

【本文地址】

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

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

今日新闻

推荐新闻