MySQL Cookbook, 2nd Edition

Specifying the Datafile Location

Problem

You’re not sure how to tell LOAD DATA where to look for your datafile, particularly if it’s located in another directory.

Solution

It’s a matter of knowing the rules that determine where MySQL looks for the file.

Discussion

You can load files that are located on the server host or on the client host from which you issue the LOAD DATA statement. By default, the MySQL server assumes that the datafile is located on the server host. However, that might not be appropriate in all cases:

• If you access the MySQL server from a remote client host and have no means of transferring your file to the server host (such as a login account there), you won’t be able to put the file on the server.

• Even if you have a login account on the server host, your MySQL account must be enabled with the FILE privilege, and the file to be loaded must be either world-readable or located in the data directory for the default database. Most MySQL users do not have the FILE privilege (because it enables them to do dangerous things), and you might not want to make the file world readable (for security reasons) or be able to put it in the database directory.

Fortunately, you can load local files that are located on the client host using LOAD DATA LOCAL rather than LOAD DATA. The only permission you need to import a local file is the ability to read the file yourself. One caveat is that the LOCAL keyword might be disabled by default. You may be able to turn it on using the --local-infile ...