PERL – Retrieving Data From MySQL Databases In Different Servers

Let us consider the following (hypothetical/real) case: we have a fruit basket with an assortment of fruits in it. Over a period of time, let us suppose we accumulate so much fruit [will they not stink? may be – but that’s a problem beyond the scope of PERL & MySQL] that one basket can no longer hold all the fruits. One possible [and meaningful] way to solve this problem is to introduce another basket and segregate fruits into either one of the two baskets. To make it bit more elegant, we should probably make sure that a given kind of fruit is stored in only one of the baskets. Then, when someone asks us for a fruit – say an Apple – we know which basket contains apples and we can readily pick them out; when someone else asks for an Orange, we can repeat the process; and so on.

What does fruit basket have anything to do with PERL/MySQL?

Analogically speaking, we can consider fruits as some form of data/information while baskets can be considered as different servers/MySQL databases. Also, our thought/action process – of deciding whether to choose from one or both baskets – can be formulated as a PERL script. While some parts of transforming this problem into a script were relatively easy, the task of merging the information acquired from different servers/databases and presenting them as one unit [consider this as packaging fruits] bugged me for a really long time.

Tweet [and Email] to find answers!

After many sleepless nights & weekends, several trips to Princeton, long discussions with myself and friends, I decided to seek help through couple of very trusted sources: Linux Users Group @ Michigan Tech and Twitter. And needless to say, the answer wasn’t too far along – thanks mainly to Derek LaHousse (@ManoftheSea), Jon DeVree (@nuxi) and Adam Jaskiewicz (@ajjaskie) took time out of their busy schedules to help out with blocks of code. Owing to the kind of problem at hand, Jaskie’s code block fit the requirements perfectly.

PERL script to query MySQL databases in different servers

For completeness sake, let us suppose that the two servers are named server1.domain.com and server2.domain.com; the two databases are named database1 and database2; these two databases have the exact same table names/structure but data pertaining to uniquely different sets of fruits. MySQL userid and password could be same or different, but for sake of generalization, let us assume username1/password1 applies to database1 while username2/password2 applies to database2. Needless to say, this set up can be generalized to N servers.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

#! /usr/bin/perl -wT
 
use DBI;
use CGI;
use CGI::Carp qw ( fatalsToBrowser );
use File::Basename;
 
# Server Name, Database, UserID and Password
$hostname1 = "server1.domain.com";
$database1 = "database1";
$userid1   = "userid1";
$password1 = "password1";
 
$hostname2 = "server2.domain.com";
$database2 = "database2";
$userid2   = "userid2";
$password2 = "password2";
 
# Sample Query Statements
# $stmt="SELECT * FROM FruitBasket";
# $stmt = "SELECT * FROM FruitBasket WHERE FruitName='Apple'";
# $stmt = "SELECT * FROM FruitBasket WHERE FruitName='Orange'";
$stmt="SELECT * FROM FruitBasket WHERE FruitName='Apple' OR FruitName='Orange'";
 
# Uncomment for debugging purposes only
# print "$stmt\n";
 
if($stmt =~ m/(FruitName)/) {
 
  # Uncomment for debugging purposes only
  # print "FruitName found.\n";
 
  if($stmt =~ m/(Apple|Banana|Pineapple)/) {
 
    # Uncomment for debugging purposes only
    # print "Apple|Banana|Pineapple :: Query server1.domain.com\n";
 
    $dbh = DBI->connect("dbi:mysql:$database1:$hostname1","$userid1","$password1")
           or die "Error!";
    $h1  = $dbh->prepare($stmt);
    $h1->execute;
    $r1  = $h1->fetchall_arrayref();
 
    @final_result=(@$r1);
  }
 
  if($stmt =~ m/(Grape|Orange|Peach|Tomato)/) {
 
    # Uncomment for debugging purposes only
    # print "Grape|Orange|Peach|Tomato :: Query server2.domain.com\n";
 
    $dbh = DBI->connect("dbi:mysql:$database2:$hostname2","$userid2","$password2")
           or die "Error!";
    $h2  = $dbh->prepare($stmt);
    $h2->execute;
    $r2  = $h2->fetchall_arrayref();
 
    @final_result=(@$r2);
  }
 
} else {
# FruitType not present in the query statement.
# Case where someone asks 'bring me some fruits'
 
  # Uncomment for debugging purposes only
  # print "Query server1.domain.com and server2.domain.com\n";
 
  $dbh = DBI->connect("dbi:mysql:$database1:$hostname1","$userid1","$password1")
         or die "Error!";
  $h1  = $dbh->prepare($stmt);
  $h1->execute;
  $r1  = $h1->fetchall_arrayref();
 
  $dbh = DBI->connect("dbi:mysql:$database2:$hostname2","$userid2","$password2")
         or die "Error!";
  $h2  = $dbh->prepare($stmt);
  $h2->execute;
  $r2  = $h2->fetchall_arrayref();
 
  @final_result=(@$r1, @$r2);
}
 
# Loop through the @final_result array and print the results
for $array_ref ( @final_result ) {
  print "@$array_ref \n";
}

Is there a better way of doing the same thing?

There probably is one – one could keep the information about fruits & server/database names in a flat text file (as a mapping file), read that in and compare it with $stmt. For now, this seems to be doing the job and as I find more time, I will look into it. If you find this useful and/or find bugs/mistakes and/or have other elegant methods of accomplishing the same thing, please do share them – readers, including me, will greatly appreciate it.