Using MySQL's LOAD DATA LOCAL

We're starting on a new project using MySQL that will bulk load CSV data once a day and then users can report on it whenever they want. In the days of old (ie. Oracle), we'd simply setup a load job using SQL*Loader or use external tables. In MySQL, loading data is just an extension of SQL using the LOAD DATA command. Since my data was going to be distributed, I wanted
to explore Mike Hillyer's suggestion of using the LOCAL option of LOAD DATA.

First, I created a small table called “testload” :

mysql> create table testload (empid integer, name varchar(20),
bonus integer);
Query OK, 0 rows affected (0.02 sec)

I then decided to try it from the server to verify everything worked as expected:

mysql> -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use user1
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> load data infile '/home/users/jeff/foo.txt' into table
testload fields terminated by '|';
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testload;
+------+-------+------+
| id   | name  |bonus |
+------+-------+------+
|    1 | jeff  |   30 |
|    2 | user1 |   20 |
|    3 | gail  |   60 |
|    4 | bob   |   40 |
|    5 | john  |   70 |
|    6 | jim   |  100 |
+------+-------+------+
6 rows in set (0.00 sec)

mysql> delete from testload;
Query OK, 6 rows affected (0.00 sec)

Now I know it works as the root user. Lets try as somebody else on the server. First, I check to make sure I have FILE privilege:

mysql> select host, user, password, file_priv from user
> where user = 'user1';
+------+-------+-------------------------------------------+-----------+
| host | user  | password                                  | file_priv |
+------+-------+-------------------------------------------+-----------+
| %    | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
+------+-------+-------------------------------------------+-----------+
1 row in set (0.00 sec)

mysql@sql1 $ mysql -u user1 -h sql1 -P3322 -p user1
Enter password:
ERROR 1045 (28000): Access denied for user 'user1'@'sql1' (using
password: YES)

Hmmm, I don't really get this one since I should be covered by the '%'. Have to investigate that later, but lets grant permission on this host anyway.

mysql@sql1 $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant file on *.* to 'user1'@'sql1' identified by 'nopassword';
Query OK, 0 rows affected (0.00 sec)

Let's check the privs and try again from the same user:

mysql@sql1 $ mysql -u user1 -h sql1 -P3322 -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.18


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> select host, user, password, file_priv from user
> where user = 'user1';
+------+-------+-------------------------------------------+-----------+
| host | user  | password                                  | file_priv |
+------+-------+-------------------------------------------+-----------+
| %    | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
| sql1 | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
+------+-------+-------------------------------------------+-----------+
2 rows in set (0.00 sec)

Now that I have given myself privileges on the server, it should
work, right?

mysql> load data infile '/home/users/jeff/foo.txt' into table
testload fields terminated by '|';
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


mysql> select * from testload;
+------+-------+------+
| id   | name  |bonus |
+------+-------+------+
| 1    | jeff  |  30  |
| 2    | user1 |  20  |
| 3    | gail  |  60  |
| 4    | bob   |  40  |
| 5    | john  |  70  |
| 6    | jim   | 100  |
+------+-------+------+
6 rows in set (0.00 sec)

Sure enough, that did the trick. On to loading from a client:

host1:/home/users/user1/tmp $ mysql -u user1 -h sql1 -P3321 -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 5.0.18


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> load data local infile '/home/users/jeff/foo.txt' into
table testload fields terminated by '|';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> quit

Now what? I go back to the documentation and re-read about the
parameter local_infile. I'm pretty sure I set it, but lets check
anyway:

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

That's what I thought. I went over the docs once again and saw a
mention of the local-infile argument to the mysql client. So I tried
that:

host1:/home/users/user1/tmp $ mysql -u user1 -h sql1 -P3321 -p
user1 --local-infile -p user1

Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 5.0.18


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> load data local infile '/home/users/jeff/foo.txt' into
table testload fields terminated by '|';
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

Nice. That is exactly what I am looking for. Knowing that you can set
preferences in your .my.cnf file, I setup the local-infile option in
my .my.cnf.

host1:/home/users/user1 $ more .my.cnf
[client]
loose-local-infile=1

As long as I'm at it, why not setup the host, port, and user in my
.my.cnf.

[client]
loose-local-infile=1
host=sql1
port=3321
user=user1

Then, it's a simple command to login.

user1@host1 13> mysql -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

I learned a lot about LOAD DATA during this exercise.

1. The local_infile parameter must be set to 1 in the my.cnf
   file on the server.


2. By default, the mysql client doesn't allow you to load data from the client. You must use the local-client flag or set the loose-local-client flag in your .my.cnf file.


3. You must have the FILE privilege.