3

I have created a shell script to connect to a DB and INSERT IPs and MACs from dhcpd.log file
the scripts worked properly:

#/!bin/bash
#Defining Variables
dhcpLogFile="/var/log/dhcpd.log"

#Begin Code
# extrcting the Information of IPs and MACs from log file
# and eleminating the duplicate Entry
NumberOfLines=$(awk '/DHCPACK/ { print $8} ' $dhcpLogFile | awk '!x[$0]++'|awk 'BEGIN{i=0}{i++;}END{print i}')
j=1
while [ $NumberOfLines -gt 0 ]
do
ip=$(awk '/DHCPACK/ { print $8} ' $dhcpLogFile | awk '!x[$0]++' |cut -f$j -d$'\n')
mac=$(awk '/DHCPACK/ { print $10} ' $dhcpLogFile | awk '!x[$0]++' |cut -f$j -d$'\n')
echo $ip
echo $mac   
let "j +=1"
let "NumberOfLines -=1"
mysql -u root --password='pw' MatchingDB <<- _END_ 
INSERT INTO IP_MACTable (IP_Address, MAC) VALUES ('$ip','$mac');
_END_
done

The code is correct but as you can see it connects to the DB every insert query inside the while, my question is how can I connect to the DB only once then do multiple queries then exit without connecting to it every insertion operation

1 Answer 1

5

You can do it by piping to mysql:

while …
do
    ⋮
    echo "INSERT INTO IP_MACTable (IP_Address, MAC) VALUES ('$ip','$mac');"
done | mysql -u root …

Note how I changed your while loop to output the queries it wants to run to stdout. Then I piped your while do...done loop to mysql.

You can pipe a subshell as well, so for example you could do:

(
    echo "BEGIN;"
    while … ; do
        ⋮
        echo "INSERT INTO IP_MACTable (IP_Address, MAC) VALUES ('$ip','$mac');"
    done
    echo "COMMIT;"
) | mysql -u root …

so that if you abort processing in the middle MySQL will roll back the transaction, so you don't wind up with a half-loaded file. Also, running inside a transaction (as opposed to committing each row individually) is generally much faster.

7
  • I have piped the while loop to the mysql connect but when I run my shell an error occured
    – Nidal
    Commented May 19, 2014 at 20:27
  • @Networker Did you put echo in front of that line? Hard to figure out where the syntax error is without seeing the edited script.
    – derobert
    Commented May 19, 2014 at 20:30
  • @Networker Also, I didn't notice, you're doing a few more echo's in that loop, you need to send those to stderr instead as well (otherwise, MySQL will try to execute themas queries). E.g., echo $ip >&2
    – derobert
    Commented May 19, 2014 at 20:31
  • Sorry, Now I put echo but another error occured bash: warning: here-document at line 27 delimited by end-of-file (wanted _END_') bash: logDHCP.sh: line 34: syntax error: unexpected end of file` and this is the edited code#/!bin/bash dhcpLogFile="/var/log/dhcpd.log" while [ $NumberOfLines -gt 0 ] do ...... echo "INSERT INTO IP_MACTable (IP_Address, MAC) VALUES ('$ip','$mac'); _END_" done | mysql -u root --password='centos' MatchingDB <<- _END_
    – Nidal
    Commented May 19, 2014 at 20:35
  • 1
    A technique I often use for stuff like this is to launch at the beginning of the script something like exec 3> >(mysql -u root). Then any time you want to send something to mysql, it's just echo 'select foo' >&3
    – phemmer
    Commented May 19, 2014 at 20:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .