shell脚本学习笔记（三）mysql与ARP表的绑定源码

shell脚本学习笔记（三）

 

                     －－－－－－mysql与ARP表的绑定源码
 这个程序中除了有shell脚本，还用到了mysql批处理脚本
主要实现用mysql对ARP表的更新

shell脚本：
#!/bin/bash
#create IP-MAC items in database and add some IP-MAC items in the ARP table

#First cheek if there is a database
DataBasePath='/var/lib/mysql'  #the path is where the mysql store the databse
dataPath='/var/lib/mysql/Data'   #where mysql place the database
homePath='/home/shiyan/mysql'    #normal place to put the data

state=''  #hold for state,1 for err state,0 for correct state
##################################function#######################################

#update the ARP table with the database
updateARP(){
sudo chmod 777 $dataPath
sudo cp $dataPath/data.txt $homePath/
cd $homePath
 Line=`wc -l data.txt|awk '{printf $1}'`
 Hold1=$Line
 while [ $Hold1 -ne "0" ]
 do
 IPhold=` sed -n /`echo $Hold1/`p data.txt|awk '{printf $1}'`
 MAChold=` sed -n /`echo $Hold1/`p data.txt|awk '{printf $2}'`
 sudo arp -s $IPhold $MAChold
 Hold1=`expr $Hold1 - 1`
done
}

#delete the data.txt in $dataPath
delete(){
sudo chmod 777 $dataPath
cd $dataPath
hold=`ls|grep 'data.txt'`
if [ -z $hold ];then #$hold is empty
   echo -e "/n"
else
  sudo rm data.txt   #delete the data.txt in order to update the data.txt
fi
}

##################################main#############################################
sudo chmod 777 $DataBasePath
sudo chmod 777 $DataPath
cd $DataBasePath
hold1=`ls|grep 'Data'`
if [ -z $hold1 ];then #if hold1 is empty,means there is no Data DataBase yet
  mysql -uroot -p123 <$homePath/exe/createData.txt
fi

echo "1---add IP-MAC items from file"
echo "2---add IP-MAC items from keyboard"
echo "3---see ARP table"
echo "4---see Ip-Mac table in dataBase"
echo "5---delete IP-MAC items"
echo "6---update ARP with dataBase"
echo "0---quit"

read selection
while [ $selection -ne "0" ] #when it is not equal 0
do
 case $selection in
    1)  #add IP-MAC items from file
     state=1;
     while [ $state -eq "1" ]
     do
       echo "Enter the name of the file:"
       read name
       echo "Enter the location of the file(such as /home/shiyan/a.txt):"
       read addr
       hold=`ls $addr|grep $name`
       if [ -z $hold ];then #if it is empty there is no such file in the $addr
         echo "The path or the file's name is not correct,enter again"
         state=1
         hold=''
       else
        cat $addr/$name>$homePath/loadfile.txt #copy the file to be loaded to the
                                               # loadfile.txt
         cd $homePath
         awk '{printf $1}' loadfile.txt|awk -F. '{if($1>255 || $2>255 || $3>255 || $4>255)(ip=1)}'
         if [ $ip -eq "1" ];then
            echo "The IP format in the file is incorrect,check it"
            exit 1
         else
            delete   #delete the data.txt in $dataPath
            mysql -uroot -p123<$homePath/exe/addFromFile.txt   #load mysql batch file
            updateARP       #update the ARP table with the database
            state=0
          fi     
       fi
      done
      ;;
      2) #dd IP-MAC items from keyboard
        echo "Enter the IP and MAC"
        echo "IP:"
        read IP
        echo "MAC:"
        read MAC
        echo -e "$IP/t$MAC">$homePath/loadfile.txt
        delete   #delete the data.txt in $dataPath
        mysql -uroot -p123<$homePath/exe/addFromFile.txt     #load mysql batch file
        updateARP     #update the ARP table with the database
       ;;
      3)  #see ARP table
       arp -a
       ;;
      4) #see Ip-Mac table in dataBase
        mysql -uroot -p123<$homePath/exe/see.txt        #load mysql batch file
      ;;  
      5) #delete IP-MAC items
        echo "Enter the IP"
        echo "IP:"
        read IP
        echo -e "$IP/t//N">$homePath/loadfile.txt
        delete   #delete the data.txt in $dataPath
        mysql -uroot -p123<$homePath/exe/delete.txt      #load mysql batch file
        sudo arp -d $IP
      ;;
      6)  #update ARP with dataBase
      sudo chmod 777 $dataPath
      cd $dataPath
      hold=`ls|grep 'data.txt'`
      if [ -z $hold ];then #$hold is empty
        echo "could not update"
      else
        updateARP       #update the ARP table with the database
      fi       
      ;;
      0)  #quit
        exit 0
      ;;
      *)
       echo "Illegal input enter again"
      ;;
      esac  
 echo "1---add IP-MAC items from file"
 echo "2---add IP-MAC items from keyboard"
 echo "3---see ARP table"
 echo "4---see Ip-Mac table in dataBase"
 echo "5---delete IP-MAC items"
 echo "6---update ARP with dataBase"
 echo "0---quit"

 read selection
done

mysql批处理脚本：
createData.txt
create database Data;
use Data;
create table IpMac(
ip char(15) not null,
mac char(17) not null primary key);
quit



addFromFile.txt
use Data
load data local infile "/home/shiyan/mysql/loadfile.txt" into table IpMac;
select * from IpMac into outfile "data.txt";
quit

delete.tx t
use Data;
load data local infile "/home/shiyan/mysql/loadfile.txt" into table IpMac;
set @hold="";
select IP from IpMac where Mac="" into @hold;
delete from IpMac where IP=@hold;
select * from IpMac into outfile "data.txt";
quit

see.txt
use Data;
select * from IpMac;
quit