MySQL LOAD DATA LOCAL INFILE to a remote server

Question

I want to import a csv file from my local computer to a mysql server on a remote machine using LOAD DATA LOCAL INFILE. When I try this without LOCAL and my file is stored at the remote computer it works (file and mysql on the same server), but is not working when the mysql server is on a remote computer.

LOAD DATA LOCAL INFILE 'C:/wamp/www/accesos/uploaded_files/wawawa.csv'
INTO TABLE eventos
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@skip,@skip,@skip,@skip,@skip,@skip,@skip,@skip,@skip,nombre,@skip,num_servo,@fecha,hora,@skip,num_tarjeta,@archivo_procedencia)
set archivo_procedencia = 'test_file', fecha = STR_TO_DATE(@fecha, '%d/%m/%Y');

So, what I'm doing from my computer: I'm connecting to phpmyadmin on the remote computer. Executing the above code but is not working. But it works if I remove "LOCAL" and move my file to the remote computer and change the path to match the file path.

The error is:

Can't find file 'C:/wamp/www/accesos/uploaded_files/wawawa.csv'.

Any ideas on how to do this?

Answer 1

From documentation:

The LOCAL keyword affects expected location of the file and error handling, as described later. LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

The LOCAL keyword affects where the file is expected to be found:

• If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location.

In Your case client program is phpmyadmin. So it expects csv file is on server where phpmyadmin is installed. To work with files on your local computer, you must use MySQL client program installed locally on your computer.