How to import really huge CSV files very fast in one shot in remote MYSQL database server?
SOLUTION: Load Data command on MySQL
LOAD DATA command in MySQL is a very fast & reliable way to import a huge CSV (containing several millilon lines/rows) to a remote MySQL server. Here is the syntax:
LOAD DATA LOCAL INFILE 'c:/csv/eventsbig.csv' IGNORE INTO TABLE 'testdb'.'eventsbig' FIELDS TERMINATED by ’,’ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY ’\n’ IGNORE 1 LINES (`evt_type`, `evt_name`, `billing_date`);
Please refer here for further reading on LOAD DATA command on MySQL.
NOTE: While using the mysqlimport command with SSH, keep the following in mind:
- Be sure that you have an SSH Account/access to the terminal on the remote server/computer with the MySQL Server.
- Remember if your database is big, be sure to put the index/primary/unique keys on the tables created before you upload the CSV file to populate data into the database.