This shows you the differences between two versions of the page.
Previous revision | |||
— | linux:scripts:mysqlautowrite [Unknown date] (current) – external edit (Unknown date) 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ===== MySQL - autowrite in database ===== | ||
+ | This script can be used to write the content of textfiles splittet into a database. The script will look automatically for 1 .txt file, reads it, write in database, deletes the file and looks for another one. A logfile will be written. | ||
+ | |||
+ | <code bash mysqlwrite.sh> | ||
+ | #!/bin/bash | ||
+ | |||
+ | #endless loop | ||
+ | while true | ||
+ | do | ||
+ | |||
+ | #Path to logfile | ||
+ | LOGPATH=/ | ||
+ | |||
+ | #Path to local textfiles | ||
+ | PATH=/ | ||
+ | |||
+ | #Path for textfiles after insert into mysql | ||
+ | READPATH=/ | ||
+ | |||
+ | #Username of MYSQL | ||
+ | MYSQLUSER=root | ||
+ | |||
+ | #Password of MYSQL | ||
+ | MYSQLPW=root | ||
+ | |||
+ | # | ||
+ | DBTABLE=mysql.textfiles | ||
+ | |||
+ | #DATABASE - Columns of table | ||
+ | SCOL1=Column1 | ||
+ | SCOL2=Column2 | ||
+ | SCOL3=Column3 | ||
+ | SCOL4=Column4 | ||
+ | SCOL5=Column5 | ||
+ | SCOL6=Column6 | ||
+ | |||
+ | #get the name of the local textfile. | ||
+ | #every data with .txt ending will be read, because of head -1 only one file will be taken and the name will be saved in var DATA | ||
+ | DATA=`ls $PATH. | grep .txt | head -1` | ||
+ | |||
+ | #if data with ending .txt exists | ||
+ | if [ -n " | ||
+ | then | ||
+ | |||
+ | #init i | ||
+ | i=1 | ||
+ | |||
+ | #this is needed for spaces, if not used, all tablerows will be in one row | ||
+ | IFS=$' | ||
+ | |||
+ | #for every row in DATA do | ||
+ | for i in $(cat $PATH$DATA); | ||
+ | do | ||
+ | # cut columns with -d " | ||
+ | COL1=$(echo $i | cut -d " | ||
+ | COL2=$(echo $i | cut -d " | ||
+ | COL3=$(echo $i | cut -d " | ||
+ | COL4=$(echo $i | cut -d " | ||
+ | COL5=$(echo $i | cut -d " | ||
+ | |||
+ | #if COL1 is " | ||
+ | if [ $COL1==hello ] | ||
+ | then | ||
+ | |||
+ | #Connects to MySQL with -u USERNAME and --password PASSWORD. if other hostname use -h HOSTNAME | ||
+ | #INSERT in DATABASE.TABLE, | ||
+ | mysql -u $MYSQLUSER --password=$MYSQLPW | ||
+ | |||
+ | INSERT INTO $DBTABLE ($SCOL1, $SCOL2, $SCOL3, $SCOL4, $SCOL5, $SCOL6) VALUES (' | ||
+ | EOFMYSQL | ||
+ | |||
+ | else | ||
+ | #Connects to MySQL with -u USERNAME and --password PASSWORD. if other hostname use -h HOSTNAME | ||
+ | #INSERT in DATABASE.TABLE, | ||
+ | mysql -u $MYSQLUSER --password=$MYSQLPW | ||
+ | |||
+ | INSERT INTO $DBTABLE ($SCOL1, $SCOL2, $SCOL3, $SCOL4, $SCOL5, $SCOL6) VALUES (' | ||
+ | EOFMYSQL | ||
+ | fi | ||
+ | |||
+ | done | ||
+ | |||
+ | #write the vars into the log | ||
+ | echo " | ||
+ | echo " | ||
+ | echo " | ||
+ | echo " | ||
+ | echo " | ||
+ | echo " | ||
+ | echo " | ||
+ | |||
+ | #move the local textfile into the read dir, otherwise it will be read again over and over... | ||
+ | mv $PATH$DATA $READPATH$DATA | ||
+ | fi | ||
+ | #short wait time -> less cpu usage (effects = 1 File/sec, otherwise 2-3 Files/sec) | ||
+ | sleep 1 | ||
+ | done | ||
+ | </ | ||
+ | \\ | ||
+ | \\ | ||
+ | ~~DISCUSSION: |