ProblemYou're not sure how to tell LOAD DATA where to look for your datafile, particularly if it's located in another directory. SolutionIt's a matter of knowing the rules that determine where MySQL looks for the file. DiscussionYou 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:
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 option for mysql. If that doesn't work, your server has been configured not to allow LOAD DATA LOCAL at all. (Many of the examples in this chapter assume that LOCAL can be used. If that's not true for your system, you'll need to adapt these examples. Omit LOCAL from the statement, make sure that the file is located on the MySQL server host, and specify its pathname using the following rules. For example, specify the full pathname.) If the LOCAL keyword is not present in the LOAD DATA statement, the MySQL server reads the datafile. It looks for the file on the server host using the following rules:
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 LOCAL keyword is specified in the LOAD DATA statement, your client program looks for the file on the client host and sends its contents to the server. The client interprets the pathname the same way your command interpreter does:
If your file is located on the client host, but you forget to indicate that it's local, you'll get an error. 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 meaning of the error message is that the MySQL tried to open mytbl.txt on the server host and could not access it. If your MySQL server runs on the host from which you issue the LOAD DATA statement, "remote" and "local" refer to the same host. But the rules just discussed for locating datafiles still apply. Without LOCAL, the server reads the datafile. With LOCAL, the client program reads the file and sends its contents to the server. mysqlimport uses the same rules for finding files as LOAD DATA. By default, it assumes that the datafile is located on the server host. To indicate that the file is local to the client host, specify the --local (or -L) option on the command line. LOAD DATA assumes that the table is located in the default database unless you specify the database name explicitly. mysqlimport always requires a database argument: % mysqlimport --local cookbook mytbl.txt To use LOAD DATA to load a file into a specific database rather than the default database, qualify the table name with the database name. The following statement does this, indicating that the mytbl table is located in the other_db database: mysql> LOAD DATA LOCAL 'mytbl.txt' INTO TABLE other_db.mytbl; LOAD DATA assumes no relationship between the name of the datafile and the name of the table into which you're loading the file's contents. mysqlimport assumes a fixed relationship between the data-file name and the table name. Specifically, it uses the last component of the filename to determine the table name. For example, mysqlimport interprets mytbl, mytbl.txt, mytbl.dat, /tmp/mytbl.txt, /u/paul/data/mytbl.csv, and C:\projects\mytbl.txt all as files containing data for the mytbl table.
|