Site Tools


linux:scripts:mysqlautowrite

This is an old revision of the document!


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.

#!/bin/bash
 
#endless loop 
while true
do
 
#Path to logfile
LOGPATH=/var/mysqlinsert.log
 
#Path to local textfiles
PATH=/var/textfiles/
 
#Path for textfiles after insert into mysql
READPATH=/var/textfiles/read/
 
#Username of MYSQL
MYSQLUSER=root
 
#Password of MYSQL
MYSQLPW=root
 
#Database.Table
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 "$DATA"  ]
then
 
#init i
i=1
 
#this is needed for spaces, if not used, all tablerows will be in one row
IFS=$'\012'
 
#for every row in DATA do
for i in $(cat $PATH$DATA); 
do 
		# cut columns with -d "|" ("|" is the cut sign) IFS is used for spaces.
		COL1=$(echo $i | cut -d "|" -f1 | tr "$IFS" '\n')
		COL2=$(echo $i | cut -d "|" -f2 | tr "$IFS" '\n')
		COL3=$(echo $i | cut -d "|" -f3 | tr "$IFS" '\n')
		COL4=$(echo $i | cut -d "|" -f4 | tr "$IFS" '\n')
		COL5=$(echo $i | cut -d "|" -f5 | tr "$IFS" '\n')
 
#if COL1 is "hello" then set a 1 to COL6, else set 0
if [ $COL1==hello ]
then		
 
#Connects to MySQL with -u USERNAME and --password PASSWORD. if other hostname use -h HOSTNAME
#INSERT in DATABASE.TABLE, add columns and the columnvalues
mysql -u $MYSQLUSER --password=$MYSQLPW  << EOFMYSQL
 
INSERT INTO $DBTABLE ($SCOL1, $SCOL2, $SCOL3, $SCOL4, $SCOL5, $SCOL6) VALUES ('$COL1', '$COL2', '$COL3', '$COL4', '$COL5', 1);
EOFMYSQL
 
else
#Connects to MySQL with -u USERNAME and --password PASSWORD. if other hostname use -h HOSTNAME
#INSERT in DATABASE.TABLE, add columns and the columnvalues
mysql -u $MYSQLUSER --password=$MYSQLPW  << EOFMYSQL
 
INSERT INTO $DBTABLE ($SCOL1, $SCOL2, $SCOL3, $SCOL4, $SCOL5, $SCOL6) VALUES ('$COL1', '$COL2', '$COL3', '$COL4', '$COL5', 0);
EOFMYSQL
fi
 
done
 
#write the vars into the log
echo "----------------------------------------------" >> $LOGPATH; #line between files
echo "Data" $PATH$DATA >> $LOGPATH;
echo "Column1" $COL1 >> $LOGPATH;
echo "Column2" $COL2 >> $LOGPATH;
echo "Column3" $COL3 >> $LOGPATH;
echo "Column4" $COL4 >> $LOGPATH;
echo "Column5" $COL5 >> $LOGPATH;
 
#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


linux/scripts/mysqlautowrite.1506949983.txt.gz · Last modified: 2017/10/02 15:13 (external edit)