LOAD DATA 默认读的是服务器上的文件，但是加上LOCAL参数后，就可以将本地具有访问权限的文件加载到数据库中。这在带来方便的同时。也带来了以下安全问题：
1、可以任意加载本地文件到数据库。
2、在WEB环境中，客户从WEB服务器连接，用户可以使用LOAD DATA LOCAL语句来读取WEB服务器进程有读访问权限的任何文件(假定用户可以运行SQL服务器的任何命令)。在这种环境中，MySQL服务器的客户实际上是WEB服务器，而不是连接WEB服务器的用户运行的程序。
解决方法：
1、 可以用--local-infile=0选项启动mysqld从服务器端禁用所有LOAD DATA LOCAL命令。
即是在/etc/my.cnf的[mysqld]下面添加local-infile=0选项。
2、 对于mysql命令行的客户端，可以通指定--local-infile[=1]选项启用LOAD DATA LOCAL命令，
或通过--local-infile=0选项禁用。
类似地，对于mysqlimport,--local or -L选项启用本地数据库文件装载。在任何情况下，成功进行本地装载需要服务器启有相关选项。
如下为实操例子：
1、/etc/my.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
local-infile=0 (增加此选项)
重启mysql
/etc/init.d/mysql restart
尝试用—user=root 连接
[root@localhost ~]# /home/mysql/bin/mysql--user=root --password --host=localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-logMySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| confluence |
| mysql |
| test |
| test01 |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test01;
Database changed
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select * from role;
+-----+--------------------+
| rid | name |
+-----+--------------------+
| 1| anonymous user |
| 2| authenticated user |
+-----+--------------------+
2 rows in set (0.01 sec)
mysql> load data local infile 'test_01.sql' into tablerole fields terminated by ',';
ERROR 1148 (42000): The used command is not allowed with thisMySQL version
2、/etc/my.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
local-infile=1 (将此选项改为1)
重启mysql，再用—user=root连接
[root@localhost ~]# /home/mysql/bin/mysql--user=root --password --host=localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-logMySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql>use test01;
Databasechanged
mysql>select * from role;
+-----+--------------------+
|rid | name |
+-----+--------------------+
| 1 | anonymous user |
| 2 | authenticated user |
+-----+--------------------+
2rows in set (0.00 sec)
mysql>load data local infile 'test_01.sql' into table role fields terminated by ',';
QueryOK, 1 row affected (0.00 sec)
Records:1 Deleted: 0 Skipped: 0 Warnings: 0
mysql>select * from role;
+-----+--------------------+
|rid | name |
+-----+--------------------+
| 1 | anonymous user |
| 2 | authenticated user |
| 3 | zhongyh user |
+-----+--------------------+
3rows in set (0.00 sec)
另一种方法是在客户端连接取用--local-infile[=0/1]
/etc/my.cnf采用
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
local-infile=1 (将此选项改为1)
[root@localhost ~]# /home/mysql/bin/mysql--user=root --password --host=localhost
--local-infile=0
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-logMySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql> use test01;
Database changed
mysql> select * from role;
+-----+--------------------+
| rid | name |
+-----+--------------------+
| 1| anonymous user |
| 2| authenticated user |
| 3| zhongyh user |
+-----+--------------------+
3 rows in set (0.00 sec)
mysql> load data local infile'test_02.sql' into table role fields terminated by ',';
ERROR 1148 (42000): The used command is notallowed with this MySQL version