Bulk Importing Data - Learning MySQL and MariaDB - page 396

WARNING
You may have noticed that the --lines-starting-by option was not included in the previous ex-
ample. That's because there is no such option for mysqlimport . Paul Dubois,a famous writer specializing
in MySQL software, reported this oversight in 2006. So far, nothing has been done to add that option,
which tells us that this is not a well-supported utility. In fact, in testing it on my server, I had difficulty
getting it to work. If it works on your server, though, that's fine. If you're constructing a script to load
data, you may want instead to use the LOAD DATA INFILE statement as part of an API script (see
Chapter16 ). Most scripting languages include modules that can be used to convert data text files.
Importing Without FILE Privileges
Some web hostingcompanies do not allow the use of LOAD DATA INFILE due tose-
curity vulnerabilities it would present for them. They block its use by not giving you
FILE privileges. If your database is located on a server on which you don't have this
privilege, it's possible to get around it, but that requires some extra steps.
First, you will need access to another MySQL server on which you do have FILE priv-
ileges. It could be on your own personal computer. We'll call whatever computer you use
your staging server and the other the live server . On the staging server, you will need to
create a table identical to the one on the live server into which you want to load the data.
You should also create an import table on the live server as we did in earlier examples in
this chapter, rather than import directly into the ultimate table.
After you've created tables on the staging and live server, execute the LOAD DATA
INFILE statement on the staging server to load the data from the text file.
Next, export the data from the table on the staging serverusing the mysqldump utility (this
utility was covered extensively in Chapter14 ) . Be sure to use the --tables option so
that you dump only the import table (see Backing Up Specific Tables ) , and usethe --
no-create-info option so that the utility doesn't include CREATE DATABASE and
CREATE TABLE statements in the dump file.
After you've created the dump file of the table, upload it to the live server. On the live
server, use the mysql client to process the dump file to insert the rows of data into the im-
port table on that server (this method was covered in Restoring Backups ) . From there, you
can use the INSERT INTO...SELECT statement to copy the rows to the appropriate
table.
This method is the same as the other methods for loading data, but with the extra steps of
loading the data on a staging server and then using mysqldump to dump the data and