PlanetMysql.ru — информация о СУБД MySQL

I recently visited a client for the purpose of installing and demonstrating MySQL Enterprise Monitor.

If you are unfamiliar with the MySQL Enterprise Monitor – from the MySQL web site: The MySQL Enterprise Monitor 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.

The MySQL Enterprise Monitor is a distributed web application that is deployed within the safety of your firewall. It is comprised of a centralized Service Manager and lightweight Service Agent this is installed on each monitored MySQL server.

For this demo, we had to install the Enterprise Monitor Application on one server, and then the Enterprise Monitor Agent on another server that was hosting the MySQL database that we wanted to monitor. In this case, the servers that we were using for the install were in separate data centers, and both had firewalls restricting port access. We wanted to install the Monitor in one data center, and the Agent in another. Since both applications use various TCP/IP ports, during the install we were having problems finding openings in the firewalls to allow the programs to “talk to each other”.

So, we needed to know which ports were available on each server – without having to get a network admin involved. One easy way of checking to see if you can reach another server via a particular port is to simply telnet to that server on that particular port, and a successful telnet connection to port 3306 would look like this:

sh-3.2# telnet 192.168.1.2 3306
Trying 192.168.1.2...
Connected to 192.168.1.2.
Escape character is '^]'.


However, for this to work, you must have telnet enabled on the target machine (which is usually not a problem). For those cases where you don’t have telnet enabled, you can use a Perl script to act as a “TCP listener” – which will open a TCP socket and listen to a particular port. And of course, for this script to work, you need to have Perl installed on that machine as well.

I wrote this TCP listener script for a different application, but for this example, it works really well. The script simply opens up a TCP port, and waits for something to connect on that port. When a connection is made, it prints the current date/time and the information that it received via that port.

Here is the Perl script:

#!/usr/bin/perl

}


The reason that I have a "while ($count <= $limit)" loop is because for some reason, after the script received a connection, it would time out after a couple of minutes and quit working. With the while loop, if it drops the connection, the connection will be restarted automatically until the $limit variable value is reached.

Remember – you need to have Perl installed on the target machine for this to work. Once you have this script running, you can connect to that machine on the port specified via several methods. This is what it looks like when you connect via telnet: (I manually typed in “hello” and “goodbye” so you could see the output from the TCP listener script)

# telnet 192.168.1.2 3306
Trying 192.168.1.2...
Connected to 192.168.1.2.
Escape character is '^]'.
hello
goodbye
^]
telnet>

And this is what the output looks like on the server that you connected to:

# /usr/bin/perl tcp_listen.pl

-- 2011-09-06-14:26:24 -- hello
-- 2011-09-06-14:26:27 -- goodbye

You can also connect via http – as http://192.168.1.2:3306. Note that your browser will not display anything, as it is waiting for the Perl script to respond. But you will see something like this output from the script:

sh-3.2# /usr/bin/perl tcp_listen_test.pl

-- 2011-09-06-14:27:50 -- GET/HTTP/1.1
-- 2011-09-06-14:27:50 -- Host:192.168.1.2:3306
-- 2011-09-06-14:27:50 -- User-Agent:Mozilla/5.0(Macintosh;IntelMacOSX10.6;rv:6.0.1)Gecko/20100101Firefox/6.0.1
-- 2011-09-06-14:27:50 -- Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
-- 2011-09-06-14:27:50 -- Accept-Language:en-us,en;q=0.5
-- 2011-09-06-14:27:50 -- Accept-Encoding:gzip,deflate
-- 2011-09-06-14:27:50 -- Accept-Charset:ISO-8859-1,utf-8;q=0.7,*;q=0.7
-- 2011-09-06-14:27:50 -- Connection:keep-alive
........

This script is handy for testing the connectivity availability of any port from one machine to the next, to make sure that the port is not being blocked by a firewall. Or, if you need a TCP listener, it works great for that as well. But, you won’t be able to use this script on a port that is already being used by another application.

Here are the default ports commonly used by MySQL Enterprise Monitor and the Agent:

—————————————–

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.

Have you ever had a spreadsheet file or a large .csv file that you wanted to manipulate, but you want more power than a spreadsheet program could offer?

Before I started using MySQL, I would usually throw the .csv file into a desktop database program, like FileMaker. FileMaker would allow you to import the .csv file and it would automatically create the column headers for you. Recently, I was given a spreadsheet with 27,000 rows in it. I still use FileMaker for some databases, but I wanted the power of MySQL to manipulate the information contained in this file. So, I could have easily just typed out the database column names manually into a MySQL “create table” statement, guessed at the types and sizes of the columns and then imported the .csv file. Instead, I decided to write a Perl script to do the dirty work for me. Plus, this spreadsheet had 45 columns of varying lengths. Ouch.

Please keep in mind that this was a quick hack. I did some testing on some small data, and then crunched the big spreadsheet. Everything seemed to work out okay, but I did get a bunch of Perl errors “Use of uninitialized value…”. So, I just turned off “warnings” and proceeded. Feel free to show me how to correct these errors – I just didn’t want to take the time. I am not an expert at writing Perl code anyway.

The script is fairly simple. The .csv file has to have header information in order to be able to create the column names, the delimiter must be double-quotes and a comma – “,” – and so each line must look something like this:

"Name","Address","City","State","Zip","Phone","Amount1","Amount2","Amount3"


There are times when you will have a .csv file that doesn’t have the full “,” delimiter – such as when a column is a number, the “s are left off and only a comma is used as the delimiter – and I could have incorporated that functionality into the script, but my .csv file had the full delimiter for both numbers and text.

I created a test .csv file, and this is the data that I used to initially test the script:

"Name","Address","City","State","Zip","Phone","Amount1","Amount2","Amount3"
"Tony Davidson","100 Main Street","Suwanee","Georgia","30024","6785552222","440.03","4522.00","82837"
"Jack Smith","5000 Washington Ave.","Chattanooga","California","31069","(770)5552941","400","456.787","2828"
"Paul Davis","P.O. Box AB123","Jackson","Georgia","31069","(770)555-6532","325.1","837.3","3848"
"Jacky Smith-Davidson","400 Metropolitan Ave","Macon","Georgia","30024-2032","770-555-8987","503.920","392.4","292833"
"Davd Jackson","100 Oak Drive","Savannah","Tennessee","40024","770) 555- 8941","5492.0","15674.01","43"


The script only determines three data types – varchar, integer and decimal. If your data doesn’t fit into these three types, or if you want to use different data types, then the program probably won’t work for you. The script calculates the longest length for each column, so if you will be changing and increasing the length of the data in the columns, you will want to edit the length of the columns in the “create table” statement that is created.

Once the script was run against the above data, two files were created – mysql_create_table.sql, which is the SQL that you can use to create the actual table – and mysql_data_values.sql, which is the “insert into” file that you can use to load the data into the table.

Here is the SQL statement that was created by the script (mysql_create_table.sql), which can use used to create the table for the data above:

CREATE TABLE `Addresses` (
`Name` varchar (20),
`Address` varchar (20),
`City` varchar (11),
`State` varchar (10),
`Zip` varchar (10),
`Phone` varchar (14),
`Amount1` decimal (7,3),
`Amount2` decimal (8,3),
`Amount3` int (6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


And here is the “insert” data (mysql_data_values.sql) that is created that you can use to load the database:

insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Tony Davidson', '100 Main Street', 'Suwanee', 'Georgia', '30024', '6785552222', '440.03', '4522.00', '82837');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Jack Smith', '5000 Washington Ave.', 'Chattanooga', 'California', '31069', '(770)5552941', '400', '456.787', '2828');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Paul Davis', 'P.O. Box AB123', 'Jackson', 'Georgia', '31069', '(770)555-6532', '325.1', '837.3', '3848');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Jacky Smith-Davidson', '400 Metropolitan Ave', 'Macon', 'Georgia', '30024-2032', '770-555-8987', '503.920', '392.4', '292833');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Davd Jackson', '100 Oak Drive', 'Savannah', 'Tennessee', '40024', '770) 555- 8941', '5492.0', '15674.01', '43');


Here is the MySQL command that you can use to import your .sql file into the MySQL database:

mysql -u -p database_name < mysql_data_values.sql

You will want to edit the script and edit/enter your relevant information – such as .csv file name, output file names and storage engine.

I tried this on my 27,000 line .csv file, and it appeared to work. If the data in the .csv file had been a bit cleaner, I could state that it positively did work, but there was so much data that it was difficult to check. The theory is that with this script, you can take a unwieldy .csv file, import it into MySQL, do your work, and then export it again as a .csv file. And, you can always write a script to automatically execute the creation of your table and the importing of the data.

So, feel free to give this a shot and let me know if it worked for you or not.


#!/usr/bin/perl

—————————————–

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.

MySQL provides you with a nice utility for creating a backup of your databases. From the mysqldump documentation page: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

The mysqldump utility doesn’t provide you with a “hot” or live backup like MySQL Enterprise Backup does, and there may be issues with mysqldump locking tables during the backup process. So for critical data, you should consider MySQL Enterprise Backup.

There are too many options when using mysqldump to backup your data for me to discuss here, so this post will only provide you with a script to perform a basic mysqldump of your databases. For more information on mysqldump, see the mysqldump documentation page.

You run mysqldump from the command line. And while running a backup from the command line is easy, it is easier to have a script that will do this process for you at a regular interval. This script reads a list of the tables that you want to backup from a file, and then backs up each table separately into a named directory. I have been using this script on my home MySQL server for a couple of years now. Since my data isn’t critical, I have a cron job that runs once a day to backup my data.

You will still have to remember to clean out the directory where the backups are stored. For me, I use a simple Unix command to find any backup directories over seven days old (via the -ctime +7 option), and delete them. I run this command as a cron job and it runs once a week. Here is the command:

find /usr/local/backups/mysqlbak -depth 1 -ctime +7 -print -exec rm -rf {} \; 2>/dev/null >/usr/local/backups/mysqlbak/rm.txt

I use a text file named mysql_bak.config that contains a list of the database tables that I want to backup. I place this file in the same directory as the Perl script, but you may modify the Perl script to place this file anywhere on your system (as long as your user has permissions to access this file). Instead of using a list of databases from a file, you could use a mysql command “show databases” to find a list of your databases and incorporate that command into the Perl script. However, since my list of databases do not change often, I can use a static list.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| address            |
| cbgc               |
| twtr               |
+--------------------+


You may also tell the Perl script to ignore any database with a comment (#) before the database name. Using the list above, my backup list file looks like this:

information_schema
# address
cbgc
twtr

For this example, I don’t want to backup the address database. The rest of the databases will be backed up individually, and placed in a directory. The backup directory will be created by the Perl script with this naming convention: year-month-day-time, like this:

# pwd
/usr/local/backups/mysqlbak
# ls -lt
drwxr-xr-x 10 root staff 340 Aug 19 00:03 2011-08-19-0003
drwxr-xr-x 10 root staff 340 Aug 18 00:03 2011-08-18-0003
drwxr-xr-x 10 root staff 340 Aug 17 00:03 2011-08-17-0003
drwxr-xr-x 10 root staff 340 Aug 16 00:03 2011-08-16-0003


Inside each directory is a list of the databases that were backed up:

# pwd
/usr/local/backups/mysqlbak
# cd 2011-08-19-0003
# ls -l
total 318136
-rw-r--r-- 1 root staff 104043 Aug 19 00:03 cbgc.sql.Z
-rw-r--r-- 1 root staff 539 Aug 19 00:03 information_schema.sql.Z
-rw-r--r-- 1 root staff 159574979 Aug 19 00:04 twtr.sql.Z


And here is the Perl script. You will have to change parts of the script to match your system configuration.

#!/usr/bin/perl
# Perform a mysqldump on all the databases specified in the dbbackup.config file

—————————————–

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.

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;

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.

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;

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.

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;

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.

In the two previous posts, we looked at simply connecting to a MySQL database via Python and Perl. In this post, we will:

- use an insert statement to input data into a MySQL table via Perl
- use a select statement to view the same data to confirm our results

For this example, we will use a table named “address”. 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;

We are only going to insert one address line with our data. You can modify this script to loop the process and insert multiple rows of data.

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

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;

$database = "scripting_mysql";

#----------------------------------------------------------------------
# insert the values into the database
#----------------------------------------------------------------------

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

# set the value of your SQL query
$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code)
			values ('John', 'Smith', '100 Main Street', 'Suite 500', 'Buffalo', 'NY', '14201') ";

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

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

#----------------------------------------------------------------------

#----------------------------------------------------------------------
# retrieve the values from the database
#----------------------------------------------------------------------

# set the value of your SQL query
$query2 = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address where name_last = 'Smith'";

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

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

# we will loop through the returned results that are in the @data array
# even though, for this example, we will only be returning one row of data

   while (@data = $statement->fetchrow_array()) {
      $name_first = $data[0];
      $name_last = $data[1];
      $address_01 = $data[2];
      $address_02 = $data[3];
      $address_city = $data[4];
      $address_state = $data[5];
      $address_postal_code = $data[6];

print "RESULTS - $name_first, $name_last, $address_01, $address_02, $address_city, $address_state, $address_postal_code\n";

}

#----------------------------------------------------------------------

# exit the script
exit;

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

my ($db) = @_;

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

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

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

# close the accessDB file
close(ACCESS_INFO);

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

# 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)

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:

—————————————–

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.

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

# 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;

# 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) = @_;

# 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>;

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

# close the accessDB file
close(ACCESS_INFO);

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

# 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.

Every so often you need to use a queue to manage operations in an application. Python makes this very simple. Python also, as I’ve written about before, makes threading very easy to work with. So in this quick program I’ll describe via comments, how to make a simple queue where each job is processed by a thread. Integrating this code to read jobs from a mysql database would be trivial as well; simply replace the “jobs = [..." code with a database call to a row select query.

#!/usr/bin/env python
## DATE: 2011-01-20
## FILE: queue.py
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
from Queue import *
from threading import Thread, Lock

'''this function will process the items in the queue, in serial'''
def processor():
    if queue.empty() == True:
        print "the Queue is empty!"
        sys.exit(1)
    try:
        job = queue.get()
        print "I'm operating on job item: %s"%(job)
        queue.task_done()
    except:
        print "Failed to operate on job"

'''set variables'''
queue = Queue()
threads = 4

'''a list of job items. you would want this to be more advanced,
like reading from a file or database'''
jobs = [ "job1", "job2", "job3" ]

”’iterate over jobs and put each into the queue in sequence”’
for job in jobs:
     print “inserting job into the queue: %s”%(job)
     queue.put(job)

”’start some threads, each one will process one job from the queue”’
for i in range(threads):
     th = Thread(target=processor)
     th.setDaemon(True)
     th.start()

”’wait until all jobs are processed before quitting”’
queue.join()

Every so often you need to use a queue to manage operations in an application. Python makes this very simple. Python also, as I’ve written about before, makes threading very easy to work with. So in this quick program I’ll describe via comments, how to make a simple queue where each job is processed by a thread. Integrating this code to read jobs from a mysql database would be trivial as well; simply replace the “jobs = [..." code with a database call to a row select query.

#!/usr/bin/env python
## DATE: 2011-01-20
## FILE: queue.py
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
from Queue import *
from threading import Thread, Lock

'''this function will process the items in the queue, in serial'''
def processor():
    if queue.empty() == True:
        print "the Queue is empty!"
        sys.exit(1)
    try:
        job = queue.get()
        print "I'm operating on job item: %s"%(job)
        queue.task_done()
    except:
        print "Failed to operate on job"

'''set variables'''
queue = Queue()
threads = 4

'''a list of job items. you would want this to be more advanced,
like reading from a file or database'''
jobs = [ "job1", "job2", "job3" ]

”’iterate over jobs and put each into the queue in sequence”’
for job in jobs:
     print “inserting job into the queue: %s”%(job)
     queue.put(job)

”’start some threads, each one will process one job from the queue”’
for i in range(threads):
     th = Thread(target=processor)
     th.setDaemon(True)
     th.start()

”’wait until all jobs are processed before quitting”’
queue.join()