MySQL Loading Files

The method of loading records one at a time (via SQL Insert) is too slow when dealing with a large number of records, even if you choose to disable indexes before the load starts. Loading via a 'LOAD DATA' command is much faster, but there are a number of hurdles which you might face.

Firstly, there are two variations of this command:

• LOAD DATA INFILE, and 
• LOAD DATA LOCAL INFILE

- the difference between the two is where the file to be imported resides. In this case, 'LOCAL' means on the local machine performing the SQL command (in which case the MySQL client reads, the file, and it is then transferred to the MySQL server and loaded) and ommiting 'LOCAL' means the file is already on the server hosting the MySQL server.

Therefore, in either case the appropriate user (either the local, client user) or the remote, server user, must be able to read the file in question. 

A number of issues can arise with access being required at all folder levels up to the location of the file. See various ServerFault discussions on the matter. Best to place the upload file in a general temporary area which is "world-readable".

With LOCAL there are also additional security concerns, covered here.

These concerns mean that most distributions of MySQL do not, out of the box, permit use of the 'LOCAL' parameter, and you receive the "not supported in this version" error. This isn't strictly true - to make it work you need to start both Server and Client with a parameter to enable the use of local files. For the Server, it's 

• local-infile=1 in /etc/mysql/my.cnf

For the client, it's necessary to set the local_infile option in the database connect command (which varies by client) e.g. local_infile: true in database.yml for a Rails application.

However, it's better to avoid these issues, and just upload from a file on the MySQL server (if you have access). Again, the file itself must be readable, by the (typically) 'mysql' user ... on *nix, /tmp would be one such location.

One further problem you might encounter is that you get a 'file not found' error still ... one that doesn't imply a permissions error (e.g. not a ErrFile 13) but simply that the file doesn't exist even though it does, and is world-readable. If this occurs then check either auth.log or syslog to see if AppArmor is the problem:

Jan 26 12:43:45 localhost kernel: [13726.977235] type=1400 audit(1422276225.103:76): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/tmp/upload_file" pid=20692 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=1000

Here, you can see that before MySQL is passed the file, AppArmor has denied it access - effectively saying to MySQL that the file doesn't exist. To resolve this issue, update the MySQL permissions in AppArmor to include read-access to the location you wish to upload from, by editing /etc/apparmor.d/usr.bin.mysqld (or the local/ version):

  /tmp/* r,                        (...... your upload location)

  /run/mysqld/mysqld.pid rw,

  /run/mysqld/mysqld.sock w,

If you change the MySQL or AppArmor configuration, then restart the service.

Additionally, the connecting MySQL user will need the FILE privilege setting.

Update
Latest MySQL (5.7+) now lets you declare the location you'd like to use for loading your data from in secure_file_priv. Use SHOW VARIABLES LIKE "secure_file_priv" to see the location, and put your files there (setting the value if need be - can't be dynamically changed, so add to mysql.conf.d/mysqld.cnf ... or wherever).