All geek questions in one place

MySQL bulk insert from CSV data files

I have CSV data files that I want to import into mySQL. I would like to make an insert in a shell script so that it can be automated. However, I'm a little tired of having the username and password clear in the script text

I have the following questions:

  • Am I uncomfortable with the idea of ​​uname / pwd in clear text in a script (anyway around this, or am I too paranoid) ?. Maybe I can only configure the user with the INSERT privelege for the table to be inserted?

  • The database table (into which the raw data was imported) has a unique key based on the columns of the table. It is also possible that there may be duplicates in the data I'm trying to import. Instead of barfing mySQL (i.e. the whole insert doesn't work), I would instead want to point mySQL to EITHER

(a) UPDATE the line with the new data OR (b) GAME duplicate line.

Regardless of which option I selected, for ENTIRE import, and not for line by line. Are there any flags, etc.? I can go to mysql so that it can behave like (a) OR (b) above

  1. Can anyone suggest a starting point on how to write such a (bourne) shell script?
+4
source share
1 answer

You should read about mysqlimport , which is the command line tool that ships with MySQL. This tool is the fastest way to collect CSV data with bulk downloads.

The tool has two parameters , --ignore and --ignore for handling repeated key conflicts.

Regarding security and preventing text password entry in a script, you can also use the MYSQL_PWD environment MYSQL_PWD or the .my.cnf file (make sure the file is in 400 or 600 mode). See the user manual for password protection .

+7
source

Source: https://habr.com/ru/post/1309422/

More articles:


All Articles