Automatically Download MySQL Enterprise Monitor Graphs as PNG Files Using Perl

I was giving a presentation of the MySQL’s Enterprise Monitor* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

*The MySQL Enterprise Monitor (MEM) continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly. (from: http://www.mysql.com/products/enterprise/monitor.html)

Of course, you have to install MEM and at least one agent. Let’s assume that you have already accomplished this task, and that MEM is running properly. Open MEM in your browser, login, click on the graphs tab, and then you will see a list of all of the available graphs.

For this example, we are going to automatically download the Agent Reporting Delay and the Disk IO Usage graphs. We will download the first graph for all of the servers in a particular group, and the second graph for an individual server. First, click on a server group in your server list on the left side of MEM.

Next, we will need to change the Time Range settings to “From/To”, so that we can enter the a timeline for the graph in our script. Don’t worry about the time settings that are in MEM, as we will change these settings later, but we need them so that they will be included in the URL that we will use (more on this later). After you have changed the Time Range settings, click on the “Filter” button.

Next, click on the plus sign for the graph that you want to use so that MEM will draw the graph. For this example, we will click on the “Agent Reporting Delay” graph:

You will notice two icons to the right of the graph name. The first icon (on the left) allows you click on the icon to download the graph as a .csv file. The second icon (on the right) allows you to click on the icon and download the graph as a PNG image file.

We need some information from the actual link that is used when you click on the PNG icon. So, we will need to right-click on the icon to get the URL link location information for the Agent Reporting Delay graph:

The URL for this graph is then copied to your clipboard. This is the URL location (which is for all servers in the group that I selected):

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

As you can see in the URL above, there are several variable values that we will include in our script to produce our graphs (in blue text above). In this example, we will only be working with the following variables:
– dims_height
– dims_width
– time_fromDate
– time_fromTime
– time_toDate
– time_toTime
– graph name/ID (which is a UUID and is constant)
– servers_group and servers_server
(the servers_server variable and value are not shown in the above example, but will be in the next example below)

We will be using a text file named files.txt to store some of the graph variable values that will be used by the script. Now that you know how to copy the URL for a graph, you will need to extract the value for the graph variable and the value for the servers variable and place the values into your files.txt file. The graph value for the above URL (shown again below) is in blue text, and the value for the server variable is in red text: (notice that all values are separated on the left by an equal sign “=” and on the right by an ampersand “&”)

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

In the above example, we had selected a group of servers in our server list (on the left side of MEM), and therefore the URL will not have a value for the individual server (variable named servers_server). The graph that we will extract will be for this group of servers (in this case servers_group has a value of zero, which is still a value). This is what we had chosen under our Servers list:

Now, we want to select an individual server. In this case, we will click on “iMac-Tony”:

Now that we have chosen an individual server, in the URL for that graph, you will have a value for the variable named “servers_server”, as well as a value for servers_group – and you will need both values together. So, if you want a graph for an individual server, you will need to click on that individual server in your servers list, reselect the “Time Range” value of “From/To”, click “Filter”, and re-copy the PNG graph URL. Once we have copied the URL for this graph for an individual server, you will see a different value for the graph variable (in red) and a value for servers_group and servers_server (in blue) like this:

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c&locale=en_US&noDefaults=false&servers_group=0&servers_server=111&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=15%3A27&time_toDate=2011-11-16&time_toTime=15%3A57&time_type=FROMTO&tzName=America%2FNew_York

We will use the above URL information for our second graph – the Disk IO Usage graph. You will need to copy all of the graph and server values for the graphs that you want to download. For the above URL, we will grab these values, to be placed in our files.txt file:
graph = 6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c
server group and server name = servers_group=0&servers_server=111

This is a tedious process at first, but you should only have to do this once for each graph. Mark Leith from the MySQL software development team in the UK gave me some great information for finding out the graph names along with the graph UUID value (be sure to also check out Mark’s blog). Mark stated “The uuid per graph does not change over time/versions – it’s how we maintain the constant between them in fact.” The graph name and graph value (UUID) is stored in the MEM Service Manager repository database, which contains all of the statistical information for MEM. To access this database, simply login to your MEM Service Manager MySQL repository database with the following syntax:

mysql -uroot -p -P13306 -h127.0.0.1

During the installation of MEM, if you decided to use your current MySQL database to store the repository information, you will need to just login to that database. Once you have logged into the repository database, you can get a list of the graph names and graph UUID’s with this command:

SELECT title, uuid FROM mem.graphs; mysql> SELECT title, uuid FROM mem.graphs; +--------------------------------------------+--------------------------------------+ | title | uuid | +--------------------------------------------+--------------------------------------+ | agent_lua_mem_usage.name | 545e6c5e-ccab-457e-89ca-cc6e5eeb1e1d | | agent_reporting_delay.name | f924cb42-fed5-11df-923c-a6466b4620ce | | avg_row_accesses.name | f289dae0-82be-11df-9df0-f1c3fca44363 | | binlog_cache.name | f84b270e-7a21-11df-9df0-f30c5eb77a3c | .....

This should make it easier than copying the variables in the links as described above, but I wanted to show you how to get the information and explain all of the variables in the graph URL links. (Thanks Mark!)

In the files.txt file, we also want a name for the graph (which will also be used for the PNG image file name), the graph value, the servers value and the server or server group values from the above URLs, as well as the name of the server group or individual server. You should separate the values with a delimiter of three tildes "~~~".

So, for the two example graphs above, your files.txt file should contain the following values - Graph Name~~~graph value~~~server information~~~server or group name: (please note that the graph values that I have here may not be the same values that you would have for the same graph)

Agent Reporting Delay~~~f924cb42-fed5-11df-923c-a6466b4620ce~~~servers_group=0~~~All Servers Disk IO Usage~~~6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c~~~servers_group=0&servers_server=111~~~iMac Tony

The first line above will produce an "Agent Report Delay" graph for "All Servers". The second line will produce a "Disk IO Usage" graph for only the server named "iMac-Tony".

Now that we have our files.txt file in place (it should be placed in the same folder as the Perl script - or you may modify the Perl script for a different file location), we will use this Perl script to download our graphs as PNG image files. In case you want to place this script in a cron job to run every X number of minutes, we will include a variable to allow you to select the previous number of minutes to include in your graph. For example, in the Perl script, if you set the value of the variable $time_interval to 60 (minutes) and run the job at 30 minutes past the hour, the script will retrieve a graph for the past 60 minutes from the time of script execution.

For this example, we will name the Perl script "get_graphs.pl". There will be some variables in the script that you will have to change once, to match your system's information. The variables that you need to change are highlighted in blue text in the script:

#!/usr/bin/perl use WWW::Mechanize; use Date::Calc qw(Add_Delta_DHMS); # file name for input - this contains the Graph Name and Graph URL $filename = "files.txt"; # time interval must be in minutes $time_interval = '60'; # the width of your graph $dims_width = "800"; # the height of your graph $dims_height = "300"; # IP and port number of your MEM server $server = "192.168.1.2:18080"; # get the current time using the display_time_now subroutine $unixtimenow = &display_time_now(); ($time_toDate, $time_toTime) = split(" ",$unixtimenow); # get the past time using the display_time_past subroutine $unixtimepast = &display_time_past(); ($time_fromDate, $time_fromTime) = split(" ",$unixtimepast); # fool the web server into thinking we are a person my $mech = WWW::Mechanize->new(); # look like a real person $mech->agent('User-Agent=Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5'); # we need cookies $mech->cookie_jar(HTTP::Cookies->new); # Login to the MySQL Enterprise Monitor $mech->get('http://192.168.1.2:18080/Auth.action'); $mech->success or die "login GET fail"; # you will need to substitute your user name and password for MEM here my $user = 'tonydarnell'; my $pass = 'tonyd999'; # find a fill out the login form my $login = $mech->form_name("DoAuth"); $login->value('username' => $user); $login->value('password' => $pass); $mech->submit(); $mech->success or die "login POST fail"; open(line, "$filename") || die (print "\nERROR - could not open file: $filename\n"); while (<line>) { chomp $_; print "\n$_\n"; ($imagefilename, $graph_to_get, $servers_to_get, $servers_name) = split(/~~~/); if (length($imagefilename) > 2) { $time_toDate_for_filename = $time_toDate; $time_toDate_for_filename =~ s/\-/_/g; $time_toTime_for_filename = $time_toTime; $time_toTime_for_filename =~ s/\:/_/g; $servers_name =~ s/ /_/g; $imagefilename =~ s/ /_/g; $imagefilename = $servers_name . "_" . $imagefilename . "_" . $time_toDate_for_filename . "_" . $time_toTime_for_filename . ".png"; # you will need to change your settings here to match your URL for your graphs $graph = "http://". $server . "/Graph.action?dims_height=" . $dims_height . "&dims_width=" . $dims_width . "&graph=" . $graph_to_get . "&locale=en_US&noDefaults=false&" . $servers_to_get . "&style=NORMAL&time_fromDate=" . $time_fromDate . "&time_fromTime=" . $time_fromTime . "&time_toDate=" . $time_toDate . "&time_toTime=" . $time_toTime . "&time_type=FROMTO&tzName=America%2FNew_York"; print "\n$graph\n"; #exit; # Get the PNG image file from the URL $mech->get($graph); $mech->save_content($imagefilename); } } exit; close($filename); # ------------------------------------------------ # sub-routines sub display_time_now { my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time(); $year += 1900; $mon += 1; return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min); } sub display_time_past { my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time() - ($time_interval*60); $year += 1900; $mon += 1; return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min); } # ------------------------------------------------

When we executed the script, two files were created and downloaded - All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png and iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png:

All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png

iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png

You could also create a similar script to download the information as a .csv file, but the syntax is very different (maybe I will do that in a future post). But for now, I have a possible solution for the client - and I hope that he likes it.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Advertisement

Using Java to Connect to MySQL Enterprise Monitor with Plugin for Connector/J

If you use MySQL for any number of databases, and if you are not using the MySQL Enterprise Monitor (MEM) to manage your databases, then you are missing out on a great DBA tool.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-introduction.html:

“MySQL Enterprise Monitor is a companion product for MySQL Server that monitors your MySQL instances, notifies you of potential issues and problems, and advises you how to fix the issues. MySQL Enterprise Monitor can monitor all kinds of configurations, from a single MySQL instance that is important to your business, all the way up to a huge farm of database server machines powering a busy web site. “

The query analyzer function of the Enterprise Monitor helps you to analyze/tune your queries. It can tell you which queries are being run the most often, which ones are taking the longest to run, it can even help you find queries that are returning a zero result set, and other query “violators”.

But the caveat is that in order to use the query analyzer function, you have to redirect your MySQL traffic through a proxy server (configured during the MEM installation). Using the proxy server is an additional step that might be acceptible for testing on development servers, but it might not be a great idea in a production environment.

However, if you are using Java for your database connections, you can bypass the proxy server and send your query information directly to the MEM Service Manager. This is accomplished with the MySQL Enterprise Plugin for Connector/J.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-qanal-using-cj.html:

“The MySQL Enterprise Plugin for Connector/J lets you use the Query Analyzer to monitor MySQL queries from any application using the Connector/J JDBC driver. As described in Section 3.2, “Query Analyzer User Interface“, the Query Analyzer can help you locate queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.”

As stated, the information about the queries is sent directly to the MySQL Enterprise Service Manager. Once you install the MySQL Enterprise Plugin for Connector/J and tweak your Java database connection string, query analysis becomes available for your Java applications. And, the Enterprise Monitor can show you the location of the “offensive” Java code. Here is an example, with the code location highlighted in blue:

There are a few things that you need to do before you can use this plugin. The prerequisites are:

– MySQL Connector/J version 5.1.12 or later.
– JDK-1.5.0 or later.
– MySQL Enterprise Service Manager version 2.2 or later.
– The MySQL instance that the application uses must be monitored by a MySQL Enterprise monitoring agent. The mysql.inventory table must be accessible to the configured user for the Connector/J connection to get the UUID of the monitored server.
– Apache Commons logging in the CLASSPATH of the application being analyzed. If you are not already using Commons Logging, modify the application’s CLASSPATH as described in the following section to point to the JAR file bundled with the MySQL Enterprise Monitor product.

I don’t write Java code, but I wanted to be able to test this functionality. I found an example script on MySQL’s web site, and so I was able to modify it to send a test query to my installation of MEM.

I did run into one problem – I wasn’t setting my CLASSPATH variable correctly. To solve this problem, I edited my .profile file for the root user, and added the following JAR file locations to my CLASSPATH: (the colors are added for clarity)

export set CLASSPATH=/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/mysql-connector-java-commercial-5.1.17-bin.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/c-java-mysql-enterprise-plugin-1.0.0.61.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/required/commons-logging-1.1.1.jar:

I am running MySQL on a Mac Pro Server, so you will need to modify the file paths in your CLASSPATH to match your system.

After you have completed the prerequisites, you are ready to see if you can connect to the Service Manager with a query via Java. Here is a sample script that you can use (you will need to modify it your system parameters):

import java.io.IOException; import java.sql.*; // Notice, do not import com.mysql.jdbc.* // or you will have problems! public class LoadDriver {    public static void main(String[] args) {          try {             // The newInstance() call is a work around for some             // broken Java implementations             Class.forName("com.mysql.jdbc.Driver").newInstance();             Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.1.2/database_name?"+                "user=root&password=root_password"+                "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters"+                "&serviceManagerUrl=http://192.168.1.2:18080/"+                "&serviceManagerUser=agent"+                "&serviceManagerPassword=agent_password");             //Get a Statement object                Statement stmt = conn.createStatement();                ResultSet rs = stmt.executeQuery("SELECT sleep(120)");                System.out.println("Closing connection");                       conn.close();                } catch (Exception ex) {                // handle the error                ex.printStackTrace();          }       } }

You will need to compile the script before running it. Here are the results from compiling and then executing the script: (since you are doing a SELECT SLEEP(120), the script will “sleep” for 120 seconds before finishing.)

macserver01:java root# javac LoadDriver.java macserver01:java root# java LoadDriver Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using direct result set size methods. Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using batch-aware prepared sql extractor. Fri Oct 14 14:08:57 EDT 2011 INFO: MySQL Enterprise Plugin for Connector/J version 1.0.0.61 started for MySQL instance 53e71576-026c-4798-961b-0025efc37db2. Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2 HTTP/1.1 200 OK Server: Apache-Coyote/1.1 Content-Type: application/json Transfer-Encoding: chunked Date: Fri, 14 Oct 2011 18:08:57 GMT {     "name": "53e71576-026c-4798-961b-0025efc37db2",     "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",     "values":     {         "auto_explain_min_exec_time_ms": 1,         "capture_examples": "true",         "capture_explain": "true",         "enabled": "true",         "frequency": null } } Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false] Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2 HTTP/1.1 200 OK Server: Apache-Coyote/1.1 Content-Type: application/json Transfer-Encoding: chunked Date: Fri, 14 Oct 2011 18:08:57 GMT {     "name": "53e71576-026c-4798-961b-0025efc37db2",     "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",     "values": {         "auto_explain_min_exec_time_ms": 1,         "capture_examples": "true",         "capture_explain": "true",         "enabled": "true",         "frequency": null } } Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]

And here are the results as show in the Query Analyzer tab of MySQL Enterprise Monitor:

By clicking on the SELECT sleep(?) link, I can then take a look at the query information:

For more complex queries, this tool really provides a wealth of information to help you tune your queries and increase the performance of your MySQL databases. MySQL Enterprise Monitor has a wealth of great features. For more information, see http://dev.mysql.com/doc/mysql-monitor/2.3/en/index.html.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Retrieving Data from MySQL via PHP

So far, we have looked at connecting to a MySQL database via Perl and Python. Next, we will look at connecting via PHP. I am assuming that you have already installed PHP or have the ability to run PHP scripts on your server, and that you have MySQL installed as well.

This example will use the same address table from the previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,
`address_state` varchar(20) NOT NULL,
`address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Be sure to populate your address table with some dummy data.

insert into `address` values('John','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
 ('Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
 ('Tom','Watson','123 Golf Course Lane','Suite 1','Macon','GA','31066'),
 ('Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044');

Here is a very simple PHP script which will connect to MySQL and display the results of a query. We aren’t going to build any HTML tables to display the data – we are simply going to perform a query and return the results. Be sure to change the database values in the PHP script for the variables host_name_or_IP_Address, username, password and database_name. Insert the following script into your HTML body tag.

<?php

//connect to the database
mysql_connect ("host_name_or_IP_Address","username","password") or die ('Cannot connect to MySQL: ' . mysql_error());
mysql_select_db ("database_name") or die ('Cannot connect to the database: ' . mysql_error());

//query
$query = mysql_query("select name_first, name_last from address") or die ('Query is invalid: ' . mysql_error());

//write the results

while ($row = mysql_fetch_array($query)) {
	echo $row['name_first'] . " " . $row['name_last'] . "
";
	
// close the loop
}

?>

Here are the results when executed in a browser:

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Connecting to MySQL with Perl

When I was designing web sites, for a long time I wrote my HTML code the “hard” way – by opening a text editor and manually typing in the code (this was before I purchased Adobe DreamWeaver).

During that time of manual HTML writing, I had a project that required forms on a web page, and I needed a place to store the information. After talking with a few tech friends, I decided to use MySQL as my web site database, and Perl as my scripting language.

I had written complex Bourne shell scripts before, but Perl was something entirely new. With a little help from a buddy of mine, after a few hours I was off and running. I was amazed at how easy it was to connect to a MySQL database with Perl.

This example will show you how to use Perl to connect to a MySQL database and simply retrieve the database version using an SQL statement. This script has the same functionality as my previous post Connecting to MySQL with Python.

For this example, we will assume that you have installed Perl and MySQL. You will also need to install the DBI and the DBD::mysql modules in Perl. These modules allow you to connect to the MySQL database.

In this script, we are going to use a text file to store our database connection information, as well as a sub-routine to help retrieve our database connection information and to create the connection string for connecting to the database.

Here is the script. (The first line of the script must be the path of your Perl executable – #!/usr/bin/perl.)

- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href=http://mysql.com/>MySQL</a> database
use DBI;
use DBD::mysql;

use warnings;

#----------------------------------------------------------------------
# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessDB") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# close the accessDB file
close(ACCESS_INFO);
#----------------------------------------------------------------------

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "SELECT VERSION()";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

# retrieve the values returned from executing your SQL statement
@data = $statement->fetchrow_array();
	
# print the first (and only) value from the @data array
# we add a \n for a new line (carriage return)
print "$data[0] \n";

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

In the sub-routine “ConnectToMySql” above, we are using a text file (named accessDB) to store our MySQL database name, host name, user name and password (we will call this our access file). In the previous post, we hard-coded the database name, host name, user name and password in the script itself. For this script, we will create a text file to store this information, and we will place the text file in the parent directory (from where the script is stored). I have my sample script in the /cgi-bin/blog/ directory, so we would place the access file in the parent directory /cgi-bin .

The purpose of this access file is to hide the connection information, but it also allows you to quickly change the information if you have a need to switch to a different database, host or user name. And you can use this same file for multiple scripts, but I would have separate scripts for each database name.

Here is a sample access file – which contains (in this order), the database name, host name, user name and password. To match the file name in the sub-routine, we are naming the file “accessDB”. This file should be saved as a text file and make sure that it has the correct read permissions (for Unix, the permission is 644).

- - - - START TEXT FILE - - - - (do not include this line in the file)
my_database_name
192.168.1.1
mysql_user
password
- - - - END TEXT FILE - - - - (do not include this line in the file)

Again, place this file in the parent directory of where your script is located. This prevents anyone from seeing the file in case you didn’t protect the directory contents from being listed via a web page.

We use the sub-routine to extract the database name, host name, user name and password from the file after reading the file into the program. You will place the sub-routine at the end of your Perl script.

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Connecting to MySQL with Python

Scripting Python to connect to a MySQL database is fairly simple, even if you don’t have any much experience with Python.

You will need to have Python and MySQL installed. Once you have Python installed, you will need to import the MySQLdb and sys modules. For MySQL, you will simply need a user that has permissions to connect to the database and perform a select statement.

For this example, I am using Python v2.5, MySQL version 5.5.8., with Mac OS X 10.6.8.

Here are the links to download the files and applications necessary for this example script:
MySQL – http://dev.mysql.com/downloads
Python – www.python.org/getit/
MySQLdb module – http://sourceforge.net/projects/mysql-python/

In this post, I am not going to cover installing Python, MySQL or the Python modules, so you will need to do this on your own. There are plenty of other websites and blogs that can assist you with these installations. You will also need to know how to run a Python script on whatever OS you are using.

Once you have everything installed and tested to make sure each application is working properly, you are ready to run this script. This script will simply connect to the MySQL database and fetch the MySQL server version. You will need to substitute your own host IP address, username, password and database name in the script.

Since I am using a unix-based OS, I can simply open up a text editor (or use the vi editor) to write the script. You do not want to save the script in rich-text format, as the script will most likely fail upon execution. You want to save the script as a plain-text file. You will also want to make sure that the line “#!/usr/bin/python” is on the first line of the script.

– – – – START SCRIPT – – – – (do not include this line in the script)
#!/usr/bin/python
# version.py – Fetch and display the MySQL database server version.

# import the MySQLdb and sys modules
import MySQLdb
import sys

# open a database connection
# be sure to change the host IP address, username, password and database name to match your own
connection = MySQLdb.connect (host = “192.168.1.1”, user = “username”, passwd = “password”, db = “database_name”)

# prepare a cursor object using cursor() method
cursor = connection.cursor ()

# execute the SQL query using execute() method.
cursor.execute (“SELECT VERSION()”)

# fetch a single row using fetchone() method.
row = cursor.fetchone ()

# print the row[0]
# (Python starts the first row in an array with the number zero – instead of one)
print “Server version:”, row[0]

# close the cursor object
cursor.close ()

# close the connection
connection.close ()

# exit the program
sys.exit()

– – – – END SCRIPT – – – – (do not include this line in the script)

Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.