MySQL加载数据所需要的file权限实验

        在MySQL中经常需要导出一些表的数据，或者加载数据到某些表中，这时就涉及到一些权限的问题。今天就遇到一个开发加载数据的问题，我在测试环境进行了一些测试，并得出了一结论，过程和结果如下：

        在测试环境中，创建一个测试用户，然后以root身份 执行导入导出过程，导出导出过程正常：

$ mysqlplus.sh 
Login MySQL Srevice: lcoalhost_3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> select user,host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| fileuser | %         |
| mycat    | %         |
| zhao     | %         |
| root     | 127.0.0.1 |
| root     | ::1       |
|          | centos01  |
| root     | centos01  |
|          | localhost |
| root     | localhost |
+----------+-----------+
9 rows in set (0.01 sec)

mysql> 
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DataPlatform       |
| auction            |
| db1                |
| db2                |
| db3                |
| db_bcty365         |
| db_pursey          |
| discuzX3           |
| hibernate_20140223 |
| hivemeta           |
| jjbbs              |
| jjwiki             |
| jjwikidb           |
| liferay            |
| lportal            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
19 rows in set (0.10 sec)

mysql> 
mysql> 
mysql> 
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tabels;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tabels' at line 1
mysql> 
mysql> 
mysql> show tables;
+-----------------------------------+
| Tables_in_test                    |
+-----------------------------------+
| department                        |
| networkspeed_day_isp_r            |
| networkspeed_day_province_isp_r   |
| networkspeed_day_province_r       |
| networkspeed_day_system_ip        |
| networkspeed_month_isp_r          |
| networkspeed_month_province_isp_r |
| networkspeed_month_province_r     |
| networkspeed_month_system_ip      |
| networkspeed_week_isp_r           |
| networkspeed_week_province_isp_r  |
| networkspeed_week_province_r      |
| networkspeed_week_system_ip       |
| privilege                         |
| province_r                        |
| province_test                     |
| role                              |
| role_privilege                    |
| user                              |
| user_role                         |
+-----------------------------------+
20 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> select user,host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| fileuser | %         |
| mycat    | %         |
| zhao     | %         |
| root     | 127.0.0.1 |
| root     | ::1       |
|          | centos01  |
| root     | centos01  |
|          | localhost |
| root     | localhost |
+----------+-----------+
9 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON test.* TO 'mis_lda'@'192.168.226.121' IDENTIFIED BY 'fPUw2TgI';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> select user,host from mysql.user;
+----------+-----------------+
| user     | host            |
+----------+-----------------+
| fileuser | %               |
| mycat    | %               |
| zhao     | %               |
| root     | 127.0.0.1       |
| mis_lda  | 192.168.226.121 |
| root     | ::1             |
|          | centos01        |
| root     | centos01        |
|          | localhost       |
| root     | localhost       |
+----------+-----------------+
10 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> show tables;
+-----------------------------------+
| Tables_in_test                    |
+-----------------------------------+
| department                        |
| networkspeed_day_isp_r            |
| networkspeed_day_province_isp_r   |
| networkspeed_day_province_r       |
| networkspeed_day_system_ip        |
| networkspeed_month_isp_r          |
| networkspeed_month_province_isp_r |
| networkspeed_month_province_r     |
| networkspeed_month_system_ip      |
| networkspeed_week_isp_r           |
| networkspeed_week_province_isp_r  |
| networkspeed_week_province_r      |
| networkspeed_week_system_ip       |
| privilege                         |
| province_r                        |
| province_test                     |
| role                              |
| role_privilege                    |
| user                              |
| user_role                         |
+-----------------------------------+
20 rows in set (0.01 sec)

mysql> 
mysql> 
mysql> create table test01 as select user,host from mysql.user where 1=0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> select user,host from mysql.user into outfile '/tmp/test01.txt';
Query OK, 10 rows affected (0.01 sec)

mysql> 
mysql> select * from test02;
ERROR 1146 (42S02): Table 'test.test02' doesn't exist
mysql> 
mysql> select * from test01;
Empty set (0.00 sec)

mysql> 
mysql> load data infile '/tmp/test01.txt' into table test01;
Query OK, 10 rows affected (0.04 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> 
mysql> select * from test02;
ERROR 1146 (42S02): Table 'test.test02' doesn't exist
mysql> 
mysql> select * from test01;
+----------+-----------------+
| user     | host            |
+----------+-----------------+
| fileuser | %