I use MySQL Workbench as GUI for connecting to my MySQL servers. It is notoriously slow when you want to import data. 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 'rn' IGNORE 1 LINES;