文章/答案/技术大牛

发布

社区首页 >专栏 >MySQL 数据备份恢复（一）select into outfile & load data infile

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

Se7en258

发布于 2021-07-01 07:02:49

3.9K00

代码可运行

文章被收录于专栏：Se7en的架构笔记Se7en的架构笔记

运行总次数：0

代码可运行

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> 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()