MySQL bulk insert from CSV data files

Question

I have some CSV data files that I want to import into mySQL. I would like to do the insert in a shell script, so that it can be automated. However, I am a bit weary of having the username and password in clear text in the script

I have the following questions:

1. I am uncomfortable with the idea of a uname/pwd in clear text in the script (is there anyway around this, or am I being too paranoid)?. Maybe I can set up a user with only INSERT privelege for the table to be inserted?

2. The database table (into which the raw data is imported) has a unique key based on the table columns. It is also possible that there may be duplicates in the data that I am trying to import. Rather than mySQL barfing (i.e. the entire insertion fails), I would instead want to be able to tell mySQL to EITHER

(a) UPDATE the row with the new data OR (b) IGNORE the duplicate row.

Whichever setting I choose would be for the ENTIRE import and not on a row by row basis. Are there any flags etc I can pass to mysql in order for it to behave like (a) OR (b) above

1. Can anyone suggest a starting point on how to write such a (bourne) shell script?

Answer 1

You should read about mysqlimport, which is a command-line tool provided with MySQL. This tool is the fastest way to bulk-load CSV data.

The tool has two options, --replace and --ignore to handle duplicate key conflicts.

Regarding security and avoiding putting the password in plain text in the script, you can also use the MYSQL_PWD environment variable or the .my.cnf file (make sure that file is mode 400 or 600). See End-User Guidelines for Password Security.