Importing and Exporting Data - MySQL - page 373

Many of the examples in this chapter assume that LOCAL can be used.
If that's not true for your system, adapt the examples: omit LOCAL from
the statement, make sure that the file is located on the MySQL serv‐
er host and readable to the server, and specify the file pathname using
the following rules. For example, specify the full pathname.
If the LOAD DATA statement includes no LOCAL keyword, the MySQL server looks for the
file on the server host using the following rules:
• Your MySQL account must have the FILE privilege, and the file to be loaded must
be either located in the data directory for the default database or world readable.
• An absolute pathname fully specifies the location of the file in the filesystem and
the server reads it from the given location.
• A relative pathname is interpreted two ways, depending on whether it has a single
component or multiple components. For a single-component filename such as
mytbl.txt , the server looks for the file in the database directory for the default da‐
tabase. (The operation fails if you have not selected a default database.) For a
multiple-component filename such as xyz/mytbl.txt , the server looks for the file
beginning in the MySQL data directory. That is, it expects to find mytbl.txt in a
directory named xyz .
Database directories are located directly under the server's data directory, so these two
statements are equivalent if the default database is cookbook :
mysql> LOAD DATA INFILE 'mytbl.txt' INTO TABLE mytbl;
mysql> LOAD DATA INFILE 'cookbook/mytbl.txt' INTO TABLE mytbl;
If the LOAD DATA statement includes the LOCAL keyword, your client program reads the
file on the client host and sends its contents to the server. The client interprets the
pathname like this:
• An absolute pathname fully specifies the location of the file in the filesystem.
• A relative pathname specifies the file location relative to your current directory.
If your file is located on the client host, but you forget to indicate that it's local, an error
occurs:
mysql> LOAD DATA 'mytbl.txt' INTO TABLE mytbl;
ERROR 1045 (28000): Access denied for user: ' user_name @ host_name '
(Using password: YES)
That Access denied message can be confusing: if you're able to connect to the server
and issue the LOAD DATA statement, it would seem that you've already gained access to
MySQL, right? The error message means the server (not the client) tried to open
mytbl.txt on the server host and could not access it.