FutureQuest, Inc. FutureQuest, Inc. FutureQuest, Inc.
Knowledgebase: PHP/MySQL
LOAD DATA INFILE (or SELECT INTO OUTFILE)?
Posted on 27 January 2004 08:12 PM

Question:

I need to import (or export) data into my MySQL database from a text file. Can I use "LOAD DATA INFILE" (or "SELECT INTO OUTFILE")? This doesn't appear to work.

Answer:

LOAD DATA INFILE and SELECT INTO OUTFILE will not work as these functions require that you are logged in to the same machine MySQL runs on. Our MySQL servers are run on dedicated machines for performance reasons, and we do not grant shell access to these machines for security reasons.

There are other methods you can use to import your data, depending on how it is formatted.

If your data is formatted from a 'mysqldump' on another MySQL server, you can simply load it using:

mysql -uxlogin -p -hmysql.example.com xdatabase <yourfile.sql

Replace "xlogin" with your MySQL login name and "xdatabase" with the actual database name you wish to use. Replace "mysql.example.com" with "mysql", a dot, and your actual domain name. (This information can be found in the MySQL Manager in your account's CNC (Command'N'Control) panel.) Replace "yourfile.sql" with the filename you wish to import (does not necessarily have to end in ".sql").

If your data has been exported from another database, such as a tab-delimited file, you can load data into your MySQL database using 'mysqlimport'. More information is available within our Community Forums:
http://www.FutureQuest.net/forums/showthread.php?s=&postid=44327#post44327
http://www.FutureQuest.net/forums/showthread.php?s=&postid=45879#post45879

You can also type 'man mysqlimport' for help with this command or check out the MySQL documentation for more information.

To export your data, the simplest way is to use 'mysqldump', which dumps it in a format that can be imported directly into another MySQL database (see above).

For more information on 'mysqldump', see:
How to Back Up a MySQL Database