Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata