Tip : Using mysqlimport to import data into mysql database

[dman_2007]

Mysql server ships with a handy utility program called mysqlimport which can be used for importing data into your mysql database. It is specially useful for importing large amount of data which can be difficult to import with phpmyadmin due to restriction in max file size upload allowed or script execution time. Here's the basic syntax :

mysqlimport [options] db_name textfile1 ...

various options specfiy how the data is to be imported, db_name specifes the database name into which the data is to be imported and textfile1 etc. after removing the file extension part is used to find out the table names which will populated with the imported data.

Apart from usual options such as -u, -h, -p which are used to specify how to connect to the mysql server, following are the major options which can be used :

1) --columns=column_list, -c column_list

Used to specify the comma separated list of table data columns. Order of the table data column names should match the order of data columns in the data file.

2) --compress, -C

Use compression while communicatng, if both the client and server support compression.

Will discuss more options in my next post.

 

[dman_2007]

3) --delete, -D

Used to indicate that all rows currently in the table should be deleted before proceeding with the data import.

4) --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=...,
--fields-escaped-by=..., --lines-terminated-by=...

Group of related options used to indicate character used to terminate file data field, enclose file data field, optionally enclose file data field, escape character in file data field, terminate file data line respectively.

5) --force, -f

Ignore errors, if any occurs. For example, if a corresponding table doesn't exist for a textfile then mysqlimport will stop execution immediately if -f , --force option is not given. If this option is given, mysqlimport will continue to processing other files.

6) --ignore-lines=N

This option is used to specify that first N lines of the data file should be ignored. Useful when the first few linesin the data file is used for labeling and describing data fields.

7) --local, -L

Used to indicate that the input file should be read from client machine.

8) --lock-tables, -l

This option when used makes mysqlimport lock all tables for writing before processing any text files.


Here's a sample mysqlimport command used for import data into sample-tabl command :

mysqlimport -u sample-user -p -h mysql.example.com sample-db sample-table.txt