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
FILEprivilege, 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 theFILEprivilege (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 ...