MySql: LOAD DATA INFILE

Question

I am using MySQL v5.1 on Ubuntu machine.

MySQL data directory is /var/lib/mysql/

I have a test.dat file located on /var/lib/mysql/tmp/test.dat

I would like to load data from the test.dat file into my database table, so I execute the following SQL statement:

LOAD DATA INFILE '/var/lib/mysql/tmp/test.dat'
            INTO TABLE myapp.cars;

But I got the following error:

Mysql2::Error: Can't get stat of '/var/lib/mysql/tmp/test.dat' (Errcode: 2): LOAD DATA INFILE '/var/lib/mysql/tmp/test.dat'

What could be the reason??

P.S. one thing come to my mind is that /var/lib/mysql/ can only be accessed by root user, but I am not sure if it is the reason.

Solution 1

$ ls -ld /var/lib/mysql
drwx------ 21 mysql mysql 4096 2011-11-18 14:07 /var/lib/mysql

Yes, you have no permissions for that directory (it may be root:root depending on your setup). Use:

sudo mysql -u mysqluser -e "LOAD DATA INFILE '/var/lib/mysql/tmp/test.dat' INTO TABLE myapp.cars;"

Or just copy the file to your home directory (or wherever) and chown it to yourself.

Solution 2

if your file is on localhost, use

LOAD DATA LOCAL INFILE ...

instead of LOAD DATA INFILE.

Plus, have the file permission to be readable by mysql user, and plus have this run by mysql root:

GRANT FILE ON *.* to <your_db_user_name>@localhost;