Shell Programming and Scripting

Hi All,

I want to run some commands and get the output into a mysql database.

I want to run the df cmd into a text file, then import it into my db sysinfo using a bash script.
My table "df" has the following 9 fields:

server, filesystem, size, used, available, useperc, mounted, date, time

cmd for df creates it in a comma separated output.

Code:

df -k | tr -s " " | sed 's/ /, /g' | grep -v Filesystem > df.txt

bash script for loading data:

Code:

#!/bin/bash
/usr/bin/mysql --user=<user> --password=<password> --database=sysinfo<<EOFMYSQL

load data local infile 'df.txt'  INTO TABLE df fields terminated by '\,' LINES
TERMINATED BY '\n'; 
EOFMYSQL

Unfortunately this is not working and I only get the first line imported.

Can some one show me how to fix this script, I think it may be to do with the line terminators but can't find a solution.

I would also like to prepend the host name for field 1 and the date and time in the last 2 fields.

I'm very new to sql would really apreciate some help and advice.

You could try something like this:

- assuming a table structure like this:

Code:

create table df (
  hostname varchar(50),
  filesystem varchar(100),
  blocks int,
  used int,
  available int,
  capacity varchar(4),
  mounted varchar(100),
  ts timestamp default current_timestamp 
  );

- assuming paths with no white space characters:

Code:

df -Pk | awk 'NR > 1 { 
  $1 = $1; printf "insert into df values ( \47%s\47,", h 
  for (i = 0; ++i <=NF;)
    printf "\47%s\47,", $i
  print " current_timestamp );"     
  }' OFS=, h="$(hostname)" | 
  mysql -u <username> -p<password> <db_name>

Thanks Radoulov,

Worked perfectly, now I just need to work out how it works

The awk script just parses the df output and generates insert statements that are send to mysql client for execution.
If you tell me which part is not clear, I'll try to explain it.