30 Nov

Using LOAD DATA INFILE with Stored Procedure Workaround-MySQL

Okay! So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround.

Download MySQL UDF:

[root@localhost kedar]# wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz

[refer: http://www.mysqludf.org/]

Extract and Install:

[root@localhost kedar]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz
install.sh
lib_mysqludf_sys.c
lib_mysqludf_sys.html
lib_mysqludf_sys.so
lib_mysqludf_sys.sql
Makefile

[root@localhost kedar]# sh install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
MySQL UDF compiled successfully

Please provide your MySQL root password
Enter password:
MySQL UDF installed successfully

Create function sys_exec as follows:

CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so';

sys_exec – executes an arbitrary command, and returns it’s exit code.

You also can similarly create functions:
sys_eval – executes an arbitrary command, and returns it’s output.
sys_get – gets the value of an environment variable.
sys_set – create an environment variable, or update the value of an existing environment variable.

Example – How to load txt file to MySQL using Stored Procedure & Load Data syntax:

Step-1. Creating table:

CREATE TABLE `t` ( `id` int(2) default NULL ) ENGINE=MyISAM

Step-2. Create a sample file to load:

vi loadtest.txt
1
2
3

Step-3. Create a shell script:

vi /tmp/load.sh
mysql -u mysql_user -p mysql_password -e “load data local infile \”$1\” into table $2;”

Step-4. Create a Stored Procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `load_data_SP` $$
CREATE PROCEDURE `load_data_SP` (in_filepath varchar(100),in_db_table varchar(100))
BEGIN
declare exec_str varchar(500);
declare ret_val int;
set exec_str=concat(“sh /tmp/load.sh “,in_filepath,” “, in_db_table);
set ret_val=sys_exec(exec_str);
if ret_val=0 then
select “Success” as Result;
else
select “Please check file permissions and paths” as Result;
end if;
END $$
DELIMITER ;

Step 5. Execute:

CALL `load_data_SP`(‘/tmp/loadtest.txt’ , ‘test.t’);

…and that’s it Stored Procedure will return Success or Failure accordingly.

Make sure you’re having file permissions well set and MySQL can access the files.
Here I’ve kept the files under /tmp directory with chmod 777 & chown mysql:mysql to remove the permission-issue possibility.

Hope this helps.

18 Nov

10 Steps: MySQL Monitoring through Nagios: Install & Configure

Nagios is a powerful monitoring system and here we will learn how to monitor MySQL through Nagios. We will be installing Nagios, required plugins and configuring it to monitor MySQL Database Server.

Let’s unleash the power step by step:

Installing and configuring Nagios

Step-1 : Install required stuff:
yum install httpd
yum install gcc
yum install glibc*
yum install gd*

Step-2 :Create Nagios user account and group
useradd nagios
passwd nagios
groupadd nagcmd
usermod -G nagcmd nagios
usermod -G nagcmd apache

Step-3: Downloads:
Create directory:
mkdir NagiosSetup
cd NagiosSetup

Download nagios
wget -X Get "http://sourceforge.net/projects/nagios/files/nagios-3.x/nagios-3.2.1/nagios-3.2.1.tar.gz/download"

Download Nagios Plugins:
wget -X Get "http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download"

Follow up articles:
1: Installing Percona Monitoring Tools for MySQL Nagios.
2: Adding remote MySQL Host to Nagios Setup.

Step-4: Install Nagios

tar -xzvf nagios-3.2.1.tar.gz
cd nagios-3.2.1
./configure --with-command-group=nagcmd
make all
make install
make install-config
make install-commandmode
make install-init
chkconfig --add nagios

[If you miss step “make install-init” you may get:: error reading information on service nagios: No such file or directory ]

Configure Nagios Web Interface:
make install-webconf
htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin

[specify password for nagios admin]

Step-5: Install plugins
tar xvf nagios-plugins-1.4.11.tar.gz
cd nagios-plugins-1.4.11
./configure --with-nagios-user=nagios --with-nagios-group=nagios
make
make install

Step-6: Verify Installation, Starting nagios for the first time

service nagios start
Browse: http://localhost/nagios

Here if you get Error:
“You don’t have permission to access /nagios/ on this server.”

Check /etc/httpd/conf/httpd.conf for DirectoryIndex.
If it’s not having index.php add it as follows:

vi /etc/httpd/conf/httpd.conf
DirectoryIndex index.php index.html index.html.var

Make sure you do restart apache(httpd) and nagios every time you change the config file. You must have php installed.

Monitoring MySQL:

Step-7: Download, Extract and install the MySQL Plugin:

wget http://labs.consol.de/wp-content/uploads/2010/10/check_mysql_health-2.1.3.tar.gz

tar -zxvf check_mysql_health-2.1.3.tar.gz
cd check_mysql_health-2.1.3
./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl=/usr/bin/perl
make
make install

Step-8: Create database user:
grant usage, replication client on *.* to 'nagios'@'localhost' identified by 'nagios';

Step-9: Provide email address for nagiosadmin:
[Change contacts.cfg file accordingly.]
vi /usr/local/nagios/etc/objects/contacts.cfg

define contact{
contact_name nagiosadmin ; Short name of user
use generic-contact ; Inherit default values from generic-contact template (defined above)
alias Kedar ; Full name of user
email kedar@nitty-witty.com ; <<***** CHANGE THIS TO YOUR EMAIL ADDRESS ******
}

Step-10: Configuring Nagios to Monitor MySQL Server

vi /usr/local/nagios/etc/nagios.cfg
add following line:
cfg_file=/usr/local/nagios/etc/objects/mysqlmonitoring.cfg

Define check_mysql_health command as follows:

vi /usr/local/nagios/etc/objects/commands.cfg

define command{
command_name check_mysql_health
command_line $USER1$/check_mysql_health -H $ARG4$ --username $ARG1$ --password $ARG2$ --port $ARG5$ --mode $ARG3$
}

Enter services to be monitored in mysqlmonitoring.cfg:

vi /usr/local/nagios/etc/objects/mysqlmonitoring.cfg
Add:

define service{
use local-service
host_name localhost
service_description MySQL connection-time
check_command check_mysql_health!nagios!nagios!connection-time!127.0.0.1!3306!
}


define service{
use local-service
host_name localhost
service_description MySQL slave-io-running
check_command check_mysql_health!nagios!nagios!slave-io-running!127.0.0.1!3306!
}


define service{
use local-service
host_name localhost
service_description MySQL slave-sql-running
check_command check_mysql_health!nagios!nagios!slave-sql-running!127.0.0.1!3306!
}

Here we’ve monitored 3 services: Connection-time, io thread and sql thread (replication) status. You can monitor more parameters described here: http://labs.consol.de/nagios/check_mysql_health/

Note: Every time you change configuration file, verify before starting nagios using command:
/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg

Finally start nagios service and you’re done with nagios installation and configuration for monitoring MySQL.

Download PDF

I hope you’ve found this useful.

12 Nov

How to Get Stock Quote on Linux using Google ,Curl, Grep, Awk

If you are a Stock Market Addict and a Techie; normally working on Linux(es); this post will interest you as you still can get quote of your stock on your Linux prompt.

Consider, we have Google Finance url for Tata Consultancy Services Stock: http://www.google.com/finance?q=NSE:TCS

So, how do you get above stock price while you’re on Linux / Unix server?
It’s a sinlge line command and you get your stock’s current price. :)

curl --silent -X Get "http://www.google.com/finance?q=NSE:TCS" > /tmp/ChangeIsInevitable && cat /tmp/ChangeIsInevitable | grep -m1 -E 'span id="ref_' | awk -F ">" '{print $2}' | awk -F "<" '{print $1}'

This work for other Stock Exchanges like NYSE as well:

curl --silent -X Get "http://www.google.com/finance?q=nyse:goog" > /tmp/bgz && cat /tmp/bgz | grep -m1 -E 'span id="ref_' | awk -F ">" '{print $2}' | awk -F "<" '{print $1}' && date
617.19
Fri Nov 12 17:07:52 IST 2010

and that’s it. Well, I’m thinking thinking more about further extensions of this.
Special thanks goes to: Jignesh Bhai, Google, Curl, Grep, Awk.

-- Kedar Vaijanapurkar --