Tuesday, April 11, 2006

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.

5 comments:

Anonymous said...

I've found that setting a grant for 'user'@'%' doesn't include localhost when connected throught a socket or pipe. If you conntect using -h localhost it should work. Also 'user'@'' will resolve as ANY host including localhost via socket/pipe. I rarely use this syntax and instead elect to add a two users instead; 'user@'%' and 'user'@'localhost'.

Anonymous said...

Wasn't really looking for this, came by by accident.
But I really appreciate the way you written down this stuff.
Thanks for sharing
Jan Wessel

Anonymous said...

Thanks for sharing this info. It's just what I needed. Appreciate it very much.

kehan said...

Brilliant thanks for the help

Anonymous said...

Appreciate your insight. Mahalo.