每日MySQL之010：导出Delimited-Text格式文件

导出Delimited-Text格式文件

MySQL有两种方式导出表的数据到Delimited-Text格式文件中，本文简介这两种方式，并解决测试过程中遇到的报错
第一种是使用 mysqldump命令的--tab选项
第二种是使用 SELECT ... INTO OUTFILE 

1. mysqldump命令

之前已经知道，使用mysqldump备份表的话，输出文件中包含了表的定义和insert语句。例如，下面的语句会将数据库test1下的表t1的定义和数据以CREATE语句和
root@db2a:~# mysqldump -pqingsong test1 t1 > test.t1.sql
root@db2a:~# cat test.t1.sql | grep -iv -e '^*' -e '^--' -e '^/' -e '^$'
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (10,'aaa'),(13,'ddd'),(300,'mesfromdb2a');
UNLOCK TABLES;

mysqldump还可以导出表的内容为 Delimited-Text文件，需要加上--tab选项，加上之后它会在指定目录生成两个文件tablename.sql和tablename.txt。
以下操作目的是将数据输出到/tmp/t1tab/目录下：
root@db2a:~# mkdir /tmp/t1tab/
root@db2a:~# chmod 777 /tmp/t1tab/
root@db2a:~# mysqldump -pqingsong test1 t1 --tab='/tmp/t1tab/'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

遇到报错，现在看一下这个“--secure-file-priv” option的官方说明，它的目地是为了设置secure_file_priv变量：

This option sets the secure_file_priv system variable, which is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. For more information, see the description of secure_file_priv.

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.00 sec)

再来看secure_file_priv说明：
This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

secure_file_priv may be set as follows:

If empty, the variable has no effect. This is not a secure setting.

If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.

If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.

也就是说，1. 如果是空的话，对import和export没有限制 2. 如果设置为一个目录，那么import和export操作的文件必须在指定的目录里。 3. 如果为NULL的话，表示不允许有import和export操作

也就是说，至少有三种解决方案：
方案A，指定--tab选项为默认值
root@db2a:~# mysqldump -pqingsong test1 t1 --tab='/var/lib/mysql-files/'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@db2a:~# ll /var/lib/mysql-files/
total 16
drwxrwx---  2 mysql mysql 4096 Aug  3 05:49 ./
drwxr-xr-x 45 root  root  4096 Jul 12 18:34 ../
-rw-r--r--  1 root  root  1321 Aug  3 05:49 t1.sql
-rw-rw-rw-  1 mysql mysql   30 Aug  3 05:49 t1.txt
root@db2a:~# cat /var/lib/mysql-files/t1.sql | grep -iv -e '^*' -e '^--' -e '^/' -e '^$'
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
root@db2a:~# cat /var/lib/mysql-files/t1.txt
10      aaa
13      ddd
300     mesfromdb2a

请注意一点，t1.sql和t1.txt的owner是不一样的，前者是root，后者是mysql

方案B: 将变量secure_file_priv变为空值，这样在所的目录中都能进行操作了。
由于这个变量是只读的，所以只能在启动mysql的时候指定为空
root@db2a:~# mysqladmin -pqingsong shutdown
root@db2a:~# mysqld --user=mysql --server_id=1 --secure-file-priv='' &

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

root@db2a:~# mysqldump -pqingsong test1 t1 --tab='/tmp/t1tab/'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@db2a:~# ll /tmp/t1tab/
total 16
drwxrwxrwx 2 root  root  4096 Aug  3 05:50 ./
drwxrwxrwt 7 root  root  4096 Aug  3 05:50 ../
-rw-r--r-- 1 root  root  1321 Aug  3 05:50 t1.sql
-rw-rw-rw- 1 mysql mysql   30 Aug  3 05:50 t1.txt
root@db2a:~# mkdir /tmp/another/
root@db2a:~# chmod 777 /tmp/another/
root@db2a:~# mysqldump -pqingsong test1 t1 --tab='/tmp/another/'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@db2a:~# ll /tmp/another/
total 16
drwxrwxrwx 2 root  root  4096 Aug  3 05:51 ./
drwxrwxrwt 8 root  root  4096 Aug  3 05:51 ../
-rw-r--r-- 1 root  root  1321 Aug  3 05:51 t1.sql
-rw-rw-rw- 1 mysql mysql   30 Aug  3 05:51 t1.txt
可以看到，输出到/tmp/t1tab/和/tmp/another/都没有问题

方案C: 将变量secure_file_priv变为/tmp/t1tab/，这样只能输出到这个目录中。
root@db2a:~# mysqladmin -pqingsong shutdown
root@db2a:~# mysqld --user=mysql --server_id=1 --secure-file-priv='/tmp/t1tab/' &
root@db2a:~# mysqldump -pqingsong test1 t1 --tab='/tmp/another/'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
root@db2a:~# mysqldump -pqingsong test1 t1 --tab='/tmp/t1tab/'
mysqldump: [Warning] Using a password on the command line interface can be insecure.

可以看到，只能输出到/tmp/t1tab/目录，不能输出到/tmp/another/下。

2. SELECT ... INTO OUTFILE

示例如下：
mysql> select * from t1 into outfile '/var/lib/mysql-files/selectT1.out' ;
Query OK, 3 rows affected (0.06 sec)