MySQL数据库的备份与恢复（1）——使用Select Into Outfile和Load Data Infile命令

一、使用Select Into Outfile命令导出数据

命令格式如下：

SELECT ... INTO OUTFILE 'file_name' fields terminated by 'char';

说明： （1）OUTFILE参数指定的文件所在的路径需要有mysql的访问权限，否则会报错。 （2）每一条记录的数据之间默认以 Tab 分隔，也可使用fields terminated参数指定分隔符。 （3）执行Select into outfile和Load data infile命令需要开启在my.cnf参数文件中设置secure_file_priv参数。该参数的设置如下： ——NULL：MySQL服务会禁止导入和导出操作； ——目录名：MySQL服务只允许在这个目录中执行文件的导入和导出操作。目录必须存在，MySQL服务不会创建它； ——空字符串（’ '）：代表文件可以在任意位置。

1、查看secure_file_priv参数的取值

mysql> show variables like '%secure_file_priv%'; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.01 sec)

2、修改secure_file_priv参数的取值

[root@Mysql11 ~]# vim /etc/my.cnf ##编辑MySQL配置文件 ####################################################################### [mysqld] .......... secure_file_priv='' .......... #######################################################################

重启MySQL服务，查看secure_file_priv参数的取值

mysql> show variables like '%secure_file_priv%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | secure_file_priv | | +------------------+-------+ 1 row in set (0.00 sec)

3、举例

（1）使用select导出数据——不指定分隔符

mysql> select * from stu into outfile '/tmp/stu.txt'; Query OK, 5 rows affected (0.00 sec)

查看stu.txt文件的内容，数据之间使用tab键分隔。

[root@Mysql11 ~]# cat /tmp/stu.txt 1 zhangsan 20 Xinxiang 15578941258 2 tom 20 Xinxiang 13778942222 3 jack 20 Zhengzhou 13675871454 4 john 21 Zhengzhou 13937681111 5 mark 22 Aanyang 13055882233

（2）使用select导出数据——指定分隔符

mysql> select * from stu into outfile '/tmp/stu2.txt' fields terminated by ','; Query OK, 5 rows affected (0.00 sec)

查看stu.txt文件的内容，数据之间使用逗号（,）分隔。

[root@Mysql11 ~]# cat /tmp/stu2.txt; 1,zhangsan,20,Xinxiang,15578941258 2,tom,20,Xinxiang,13778942222 3,jack,20,Zhengzhou,13675871454 4,john,21,Zhengzhou,13937681111 5,mark,22,Aanyang,13055882233

二、使用Load Data Infile命令导入数据

命令格式如下：

LOAD DATA [LOCAL] INFILE 'file_name' INTO TABLE tbl_name [TERMINATED BY 'string';

说明： （1）根据文件的格式指定相应的分隔符； （2）在非服务端执行Load data需要使用local。比如通过B机器登录A上的mysqld，就需要用到 local 。

1、使用stu.txt文件导入数据

（1）清空stu表中的数据

mysql> truncate stu; Query OK, 0 rows affected (0.04 sec) mysql> select * from stu; Empty set (0.00 sec)

（2）导入数据

mysql> load data infile '/tmp/stu.txt' into table stu; Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from stu; +----+----------+------+-----------+-------------+ | id | name | age | address | phone | +----+----------+------+-----------+-------------+ | 1 | zhangsan | 20 | Xinxiang | 15578941258 | | 2 | tom | 20 | Xinxiang | 13778942222 | | 3 | jack | 20 | Zhengzhou | 13675871454 | | 4 | john | 21 | Zhengzhou | 13937681111 | | 5 | mark | 22 | Aanyang | 13055882233 | +----+----------+------+-----------+-------------+ 5 rows in set (0.00 sec)

2、使用stu2.txt文件导入数据

（1）清空stu表中的数据

mysql> truncate stu; Query OK, 0 rows affected (0.02 sec) mysql> select * from stu; Empty set (0.00 sec)

（2）导入数据

如果不指定分隔符，则会出现如下错误：

mysql> load data infile '/tmp/stu2.txt' into table stu; ERROR 1265 (01000): Data truncated for column 'id' at row 1

指定分隔符，导入成功：

mysql> load data infile '/tmp/stu2.txt' into table stu fields terminated by ','; Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from stu; +----+----------+------+-----------+-------------+ | id | name | age | address | phone | +----+----------+------+-----------+-------------+ | 1 | zhangsan | 20 | Xinxiang | 15578941258 | | 2 | tom | 20 | Xinxiang | 13778942222 | | 3 | jack | 20 | Zhengzhou | 13675871454 | | 4 | john | 21 | Zhengzhou | 13937681111 | | 5 | mark | 22 | Aanyang | 13055882233 | +----+----------+------+-----------+-------------+ 5 rows in set (0.00 sec)