Friday, October 14, 2011

PERL scripting: Export data from MySQL database in XML format

I have a MySQL database containing all the data but the application I am trying to use only supports XML. How can I export data from MySQL database in XML format?

Here is your answer. You need to select a programming language that support MySQL connection and does pretty good string manipulation. I found PERL programming very easy and powerful doing both MySQL connection and String Manipulation.

Assumptions: I will be running my PERL script on the same server with MySQL database. MySQL Username and Password are 'username' and 'password' respectively. Database name is 'phonesDB' and table name is 'phones'.

MySQL

Database: phonesDB
Table: phones



full_name phone_number department
Peter Griffin 25962 Technology
Stewie Griffin 25963 Finance
Sam Burns 25234 HR

You should have some basic concept of XML, MySQL and PERL programming knowledge.

[Note: Make sure that you have perl-DBD-mysql, perl-DBI and perl installed. If those package are missing, you can use YaST, YUM, ZYPPER, APT-GET to install those missing packages based on your distro.]

Let's write the PERL script that will access our MySQL database and generate XML file for us.

#vi myPerlscript.pl
#!/usr/bin/perl
use strict;
use DBI;
use DBD::mysql;

#connect to the database phonesDB using login credentials
my $dbh = DBI->connect("DBI:mysql:phonesDB","username","password",{RaiseError => 1, PrintError => 0});

#run the SELECT query
my $sth = $dbh->prepare("SELECT full_name,phone_number,department FROM phones");
$sth->execute();

print "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
print "<directory>\n";
print " <local>true</local>\n";
print " <sorted>true</sorted>\n";
print " <name>Company_Name</name>\n";
while (my ($full_name,$phone_number,$department) = $sth->fetchrow_array ())
{

print " <user>\n";

#Using string manipulation techniques available in PERL scripting
#Extract first and last name from full name
#index() function returns the occurrence of $myspace in $full_name
#substr() function allows to copy part of a string from another by specifying beginning position and the length of string
my $myspace =" ";
my $myindex =index($full_name,$myspace);
my $last_name=substr($full_name,$myindex+1);
my $first_name=substr($full_name,0,$myindex);

print " <name>$last_name, $first_name</name>\n";
print " <company>$site_name</company>\n";
print " <contact>\n";
print " <type>phone</type>\n";
print " <uri>sip:$phone_number</uri>\n";
print " </contact>\n";
print " </user>\n";
}

#Disconnect the database connection
$dbh->disconnect ();
print "</directory>\n";



Let's make the script executable
#chmod u+w myPerlscript.pl

Now run the script, you will get output in XML format
#./myPerlscript.pl

If you want to save the output of the script to the file,
#./myPerlscript.pl > myXMLfile.xml


Output:

Monday, October 10, 2011

Configured server with multiple NICs on different subnet. Can't PING IP add on second NIC? Here's the solution

Let's say we have a network problem as shown below (User can't access 10.1.1.10 from the workstation. User fails to ping 10.1.1.10 from workstation):


Note: In Linux, usually NIC1 is presented as eth0 and NIC2 is presented as eth1

How to fix the problem associated with accessing 10.1.1.10 from workstation?

You must configure multiple default routes in the server.
You can possibly achieve this in different ways, however I prefer the use of IP ROUTE and IP RULES. It's easy to implement and understand.

Step 1: Create a new policy routing table
# echo "1 TenNetwork" >> /etc/iproute2/rt_tables

Routing tables are declared in rt_tables. Here we declared TenNetwork table as we are going to write a set of rules associated with 10 network. You can give it any name you want.

Step2: Define routes in the table
#ip route add 10.1.0.0/16 dev eth1 src 10.1.1.10 table TenNetwork

#ip route add default via 10.1.1.1 dev eth1 table TenNetwork

Here we simply declared that NIC2(eth1) is associated with 10.1.0.0 subnet and it's IP address is 10.1.1.10. We also defined the default route via 10.1.1.1 on eth1 interface. (This is second default route. The first one is defined in 'main' routing table and the default route is via 192.168.2.1 on eth0 interface. OS automatically picks the first default route from eth0. You can check that by executing #ip rule show or #netstat -anr command)

#ip rule show

Since we haven't defined any rule associated with TenNetwork table yet, we can't see TenNetwork table in the rules.

Step3: Define the rules associated with TenNetwork table

#ip rule add from 10.1.1.10/32 table TenNetwork
#ip rule add to 10.1.1.10/32 table TenNetwork

Here we are defining a rule that says, if any packet is FROM/TO to 10.1.1.10, lookup the TenNetwork table.

#ip rule show
#netstat -anr

Now you can see the active routing rules associated with TenNetwork table as well.

You should be able to ping 10.1.1.10 from workstation now. (However I can't guarantee). If you are lucky, it will work like charm. If you are more lucky, you will discover more on routing and fix the problem by yourself. Good Luck!!!

Run WireShark on the server before and after applying the rule. You can visualize the problem and see how the problem is resolved. I love WireShark. I think people can find and fix more then 90% network problems using WireShark.


Warning!!!! :
1. Restarting the server will loose the configuration
2. Restarting the network will loose the configuration

Let's solve the problem associated with restarting the server. We will write startup script.

#vi /etc/init.d/TenNetwork
#!/bin/bash
#Copyright (c) 2011 DShah
# All rights reserved
#
#Author: DShah, 2011
# /etc/init.d/TenNetwork
#PLEASE READ /etc/init.d/skeleton to understand various parameters in startup scripts
#
### BEGIN INIT INFO
# Provides: TenNetwork
# Required-Start: $network
# Required-Stop:
# Default-Start: 3 5
# Default-Stop: 0 1 2 6
# Short-Description: Fixes 10 Network routing issue
### END INIT INFO

$logFile=/var/log/ten-network-log
ip route add 10.1.0.0/16 dev eth1 src 10.1.1.10 table TenNetwork
ip route add default via 10.1.1.1 dev eth1 table TenNetwork
ip route show 2>&1 >> $logFile
ip rule add from 10.1.1.10/32 table TenNetwork
ip rule add to 10.1.1.10/32 table TenNetwork
ip rule show 2>&1 >> $logFile
ip route show 2>&1 >> $logFile

Save and close the file

#chmod 700 /etc/init.d/TenNetwork

'insserv' command can be used to insert the script in desired runlevel as specified in script file
# insserv TenNetwork

You can go to /etc/init.d/rc3.d and /etc/init.d/rc5.d and look the startup order of TenNetwork.

Restart your server and see if it is working as you expected.


Updated info on 03/28/13 [Easy fix ]:

Multiple NICs routing issue can be resolved by making some modification in systctl.conf

/etc/sysctl.conf
# Disable response to broadcasts.
# You don't want yourself becoming a Smurf amplifier.
net.ipv4.icmp_echo_ignore_broadcasts = 1
# Disable route verification on all interfaces
net.ipv4.conf.all.rp_filter = 0
# enable ipV6 forwarding
#net.ipv6.conf.all.forwarding = 1
# increase the number of possible inotify(7) watches
fs.inotify.max_user_watches = 65536
# avoid deleting secondary IPs on deleting the primary IP
net.ipv4.conf.default.promote_secondaries = 1
net.ipv4.conf.all.promote_secondaries = 1



#sysctl -p   (to reload the changes done on the sysctl config)



Reference:
http://www.policyrouting.org/PolicyRoutingBook/ONLINE/TOC.html