MySQL 数据备份恢复(一)select into outfile & load data infile

语言: CN / TW / HK

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 以后使用以下命令查询,结果不为 NULL 则表示设置成功。

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  | [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

# 创建连接
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()

参考链接

欢迎关注