how to connect to mysql database from shell script? is it possible?

You can simply use the CLI MySQL client mysql for querying or manipulating your database.

You could do something like

echo "insert into squidlog (time) values (now());" | mysql -u log -ploguserpassword log_db

I'm not a big shell scripter, but you get the idea. I'm sure with some fancy manipulation, you could get all the relevant info (like ip address) in there too.

You can use the mysql CLI command using...

mysql -hhostname -uusername -p'password' -e'select some sql here;'

Which language are you using for your script?

In Perl, f.e., you can definitely query a database.

If you're using Bash (or just any other shell scripting), you can't do it directly; you'll have to rely on command-line tools which can do the job (such as the MySql client tools) and parse their output.

In additon to the above answers this form may be more convienent:

mysql -h hostname -u username -p 'password' <<- _END_ | grep something
insert into....;
select * from....;
_END_

I added | grep something just to show you the option of piping the output. It is unessacary if you do not need to process the output of the SQL you are using. You could probably wrap the whole thing in a myvariable=$(....) to locally process the ouput without using another script. This is an example of a here document. Note that this is untested.

A more elegant alternative:

A more elegant way to do what your doing may be to pipe the log data to a script directly instead of processing the log file. This would give you real time data and you wouldn't need to worry about setting up a cron job to check the file for changes at intervals.

An example of this method is:

LogFile Path/To/Log/In/Your/Configuration

to

LogFile |/usr/local/custom_script_you_made_to_process_the_data

Notice the pipe at the start of the path. This will write output directly to your script instead of putting it in the log file. Personally I would use this piped method and then use a perl script to extract and format the data and then the Perl `DBI::mysql' package to send it to mysql. If you still needed the other data you could then append it to the original log file.

Note that to your script with this second method the data getting sent in appears to come from STDIN (standard input) like someone typed it in or you piped a file into the script.

Just my own humble comment if you wanted to do this only in shell.... When I first started I wanted to do everything with Bash. While shell is pretty awesome, I found that constricting myself to only Bash was actually making me jump through ridiculous hoops to accomplish some tasks. Once I added Perl to my scripting and jumped back and forth between the two to take advantage of what each excels at I saved alot of time, and found my solutions much more elegant and clean. At that point I became converted to using the best tool for the job instead of trying to use the same tool for all problems which works incredibly well with Linux since Shell makes a phenomenal versatile and easy to use glue between different scripts in different languages.