Data migration – CSV directly import to DB

Here is the way how to simply csv importing to mysql DB 

(When importing csv in mysql workbench, it takes about to 5 hours, Direct importing via csv takes 0.15 seconds)

Before importing, make sure you don’t forget DB dump

1. Mysql Dump

# if want to backup DB
$ mysqldump -u [username] -p [db name] > [file_name].sql
 
# if want to backup Table
$ mysqldump -u [username] -p [db name] [table name] > [file_name].sql
 
 
# rollback plan
$ mysql -u [username] -p [db name] < [file_name].sql

2. File transfer to server with FTP

3. csv file import (if it’s same structure between table and csv column)

$ load data local infile '${file_name}' into table ${DB_name}.${table_name} character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 lines;

if there is a ‘NULL’ in csv, you should change all ‘NULL’ to ‘\n’ , then mysql will understand the data is null