MySQL Cookbook

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

When you issue a LOAD DATA statement, the MySQL server normally assumes the datafile is located on the server host. However, you may not be able to load data that way:

• 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 current database. Most MySQL users do not have the FILE privilege (because it allows you to do dangerous things), and you may not want to make the file world readable (for security reasons) or be able to put it in the database directory.

Fortunately, if you have MySQL 3.22.15 or later, you can load local files that are located on the client host by 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.^[43]

If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using the following rules:

• An absolute pathname fully specifies the location of the ...