Importing data faster with MySQL

I use MySQL Workbench as GUI for connecting to my MySQL servers. It is notoriously slow when you want to import data. I recently tried to do this for a Schengen Visa data set. Often takes minutes to load data of even several 100 KBs.

To counter this, I use LOAD DATA directly via the command line. I logged in with the command line using mysql -u root and selected the database. I ran:

mysql> LOAD DATA INFILE '/data.csv' INTO TABLE data;

I got an error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Then I ran it with LOCAL and series of errors & solutions I tried.

mysql> LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE data;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
mysql> set global local_infile=true;
mysql> LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE data;
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

This meant that I had to change how I logged in to the shell.

mysql -u root  --local_infile=1

Then the data import worked successfully (and FAST!)

mysql> LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE data;
Query OK, 1767 rows affected, 8835 warnings (0.07 sec)

Now the CSV format was not being considered, so I had to split up the columns.

LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;