Database Reference
In-Depth Information
Importing from Outside MySQL
So far in this chapter,we have covered ways to load from within MySQL. It is possible to
import data while not logged into MySQL, per se. At a minimum, you can execute the
LOAD DATA INFILE
statement through the
mysql
client with the
--execute
option.
However, there is another client made specifically for importing data, the
mysqlimport
util-
ity. We'll cover it in this section. This utility, as well as the
LOAD DATA INFILE
state-
ment, require
FILE
privileges. But if you don't have this privilege, there is a way around
it. First, let's cover how to load a data text file located locally without uploading it to the
server.
Importing Local Files
If you are not allowed to upload a data text file to the server, you canuse
LOAD DATA
INFILE
to load it locally through the
mysql
client. For this operation, add the
LOCAL
op-
tion. You don't log onto the server first and start the
mysql
client on the server with the host
as localhost. Instead, you log locally onto the server by entering something like this on
your local computer:
mysql
--
user
=
admin_import
--
password
\
--
host
=
mysqlresources
.
com
--
database
=
rookery
Once you have established the connection through the local client, you can execute the
SQL statement like so:
LOAD DATA LOCAL INFILE '
/tmp/birdwatcher-prospects.csv
'
REPLACE INTO TABLE birdwatchers.birdwatcher_prospects_import
FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '[' TERMINATED BY ']\n'
IGNORE 1 LINES
(prospect_name, prospect_email, prospect_country);
Basically, the data text file is read by the client and the contents sent to the server to store
in the operating system's temporary directory (e.g.,
/tmp
).
This works only if the server and client have been configured to allow the
LOCAL
option.
This requires someone to add
local-infile=1
to the MySQL configuration file on
both systems. Additionally, the user account must have
FILE
privileges on the server from
the remote location. Normally, this isn't given to a user. But if it's your server, you can al-
low it. See
Chapter13
for more information on how to do this.
