MySQL Enterprise Monitor – send advisor events to your chat client with Perl and Jabber

MySQL Enterprise Monitor (MEM) is part of the MySQL Enterprise Edition, and MEM provides real-time visibility into the performance and availability of all your MySQL databases. MEM and the MySQL Query Analyzer continuously monitor your databases and alerts you to potential problems before they impact your system. It’s 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.

With MEM, you have a couple of notification options for receiving information when MEM has received an event alert. An event alert is a “significant deviation from the baseline performance trends, and best-practice Advisors recommended changes to configuration and variable settings to improve performance”. From: http://www.mysql.com/products/enterprise/monitor.html

You may choose to receive these alerts via email or SNMP. I am not going to go over how to setup these alerts – but instructions may be found here.

I have MEM installed on my home server, and since I have been using it for a while, I have been able to tune my instances and databases so that I rarely receive any alerts. I normally receive these alerts via email, but I don’t always check email for this particular account during the day. Since most of my colleagues are in other parts of the world, we use a Jabber chat client for quick communications during the day. I wanted to figure out a way for me to receive a chat message whenever MEM had an alert. For my chat client, I use adium – which is an open-source multi-protocol instant messaging client for Mac OS X – it supports MSN, Jabber, Yahoo! and other networks. But this should work with any XMPP-based chat client application.

You will probably want to create a new POP3 email address for these alerts, as this script will delete the email messages from the server. If you want to keep an email copy of these alerts, you may add an additional email address to the alert notification group. If you use a free email service (like gmail), remember that it has a daily limit of the number of messages that you can send. I ran into this problem when testing the scripts, so I created my own email under one of my domains. I found two Perl scripts; one that acts as a POP3 client, and another that sends the Jabber message – and I combined them into one Perl script. You might need to do a little tweaking to get it to work with your service.

This is a sample email message from MEM. The first “Subject” line is the actual email subject. In MEM, you can customize what information you want in your email subject line via Email Notification Groups. I combined several parts of the email message into one message to be sent via chat.


(Email Subject Line)
(macserver01, MacServer01) - MEM WARNING Alert: User Has Rights To Database That Does Not Exist

(Email Body)

Subject: macserver01, MacServer01
Time: 2013-12-30 17:01:44 UTC (2013-12-30 12:01:44 EST)
Category: Security
Event: User Has Rights To Database That Does Not Exist
Problem Description
When a database is dropped, user privileges on the database are not automatically dropped. This has security implications as that user will regain privileges if a database with the same name is created in the future, which may not be the intended result.
Advice
Revoke privileges for all users on any databases that do not exist. The following users have privileges on databases that do not exist.

    ''@'%' on database test_%

Recommended Action
REVOKE ALL PRIVILEGES ON db_name.* FROM 'user_name'@'host_name';
Links and Further Reading
MySQL Manual: DROP DATABASE Syntax
MySQL Manual: GRANT Syntax
MySQL Manual: REVOKE Syntax
MySQL Manual: Privileges Provided by MySQL
MySQL Manual: How the Privilege System Works
Securing Your MySQL Installation
Securing a MySQL Server on Windows
Expression
%user% != THRESHOLD
Evaluated Expression

    ''@'%' on database test_% != ''

Copyright © 2005, 2013, Oracle and/or its affiliates. All rights reserved.

And here is the script. You may run it as a cron job, where it will check your email every few minutes and then send you a chat message when an email with an alert has arrived. You will need to modify the script to match your email and chat settings, and I have placed notes in the script to help guide you:

#!/usr/bin/perl -w

# POP3 client script source:
# http://forums.devshed.com/perl-programming-6/how-to-get-gmail-mail-by-mail-pop3client-in-perl-555889.html
# Author: a user named keath

# Jabber message script source: 
# ttp://dipinkrishna.com/blog/2010/12/perl-send-chat-message-gmail-buddy-jabber/
# Author: Dipin Krishna

use strict;
# for the email
use Mail::POP3Client;
# for the chat
use Net::Jabber;

# I was having a problem with the Debug module, so I just
# commented line 154 in /Library/Perl/5.16/Net/XMPP/Debug.pm

# this is the email address that you want to use to receive 
# the alert messages from MySQL Enterprise Monitor
my $user = 'MEMalerts@scriptingmysql.com';
my $pass = 'mypassword';

# you will need to use your POP3 server name
my $host = "pop.emailserver.com";

my $pop = new Mail::POP3Client(
	USER     => $user,
	PASSWORD => $pass,
	HOST     => $host,
	PORT     => 995,
	USESSL   => 'true',
);

# I have commented most of the print messages
# - you may uncomment them as you wish
my $count = $pop->Count();
if ($count Message();
} elsif ($count == 0) {
	print "no messages\n";
} else {
	
	#print "$count messsages\n\n";
	
	for my $i (1 .. $count) {
	
	my $subject = "";
	my $message = "";
	
		# if you want to extract data from the head of the email
		# I am extracting data from the body
		#foreach ($pop->Head($i)) {
		foreach ($pop->Body($i)) {

			#print "$_\n" if /^(From|Subject|Date):/i;
			#print "$_\n" if /^(Subject|Date):/i;
			# my $message = "$_\n" if /^(Subject):/i;

			# I am building my message so that it contains the information in this order:
			# Category, Subject, Event

			if ($_ =~ "^Subject")
			{
				#print "$_\n";
				chomp $_;
				$subject = $_;
				$subject =~ s/Subject: //;
 			}

			if ($_ =~ "^Category")
			{
				#print "$_\n";
				chomp $_;
				$message = "$_ || $subject";
				$message =~ s/Category: //;
 			}

			if ($_ =~ "^Event")
			{
				#print "$_\n";
				chomp $_;
				
				$message = "$message || $_";
				$message =~ s/Event: //;
  						
					my $sttime=time;
						
					#print "Message: $_\n";
						
					# this is my Google Talk chat user name and password
					my $username = 'mem.scripting.mysql';;
					my $password = 'mypassword';
					my $to = 'my_email_address';
					my $msg = "$message";
					#print "$to: $msg\n";
						 
					my $resource = "dipin";
					my $hostname = 'talk.google.com';
					my $port = 5222;
					my $componentname = 'gmail.com';
					my $Contype = 'tcpip';
					my $tls = 1;
						 
					my $Con = new Net::Jabber::Client();
					$Con->SetCallBacks(presence=>\&presence,
					message=>\&message );
						 
					my $status = $Con->Connect(
					hostname => $hostname, port => $port,
					componentname => $componentname,
					connectiontype => $Contype, tls => $tls);
						 
					if (!(defined($status))) {
						print "ERROR:  XMPP connection failed.\n";
						print "        ($!)\n";
						exit(0);
					} 
						 
					# Change hostname
					my $sid = $Con->{SESSION}->{id};
					$Con->{STREAM}->{SIDS}->{$sid}->{hostname} = $componentname;
						 
					# Authenticate
						
					#my @result = $Con->AuthSend(
					my @result = $Con->AuthIQAuth(
					username => $username, password => $password,
					resource => $resource);
						
					#print "Result:  $result[0] $result[1]\n";
						 
					if ($result[0] ne "ok") {
						print "ERROR: Authorization failed: $result[0] - $result[1]\n";
					}
					else
					{
						#print "Logged in Sucessfull!\n";
						$Con->PresenceSend(show=>"Available");
						#print "Sending Message!\n";
						$Con->MessageSend(to=>"$to",
						subject=>"Test",
						body=>"$msg\n",
						priority=>10);
					}
						
						# END send Jabbar message
 						# # # # # # # # # # # # # # # # # # # # # # # # # # #

				# this deletes the message from the server
				$pop->Delete( $i );
				
				# if you only want to send one email message as a test, 
				# uncomment this line
				exit;
			}
		}
		print "\n";
	}
}

$pop->Close();

exit;

 


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.

Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2

Be sure to check out my other posts on mysqldump:
Scripting Backups of MySQL with Perl via mysqldump
Splitting a MySQL Dump File Into Smaller Files Via Perl

Part 1 of 2: (part two)
If you have used MySQL for a while, you have probably used mysqldump to backup your database. In part one of this blog, I am going to show you how to create a simple full and partial backup using mysqldump. In part two, I will show you how to use MySQL Enterprise Backup (which is the successor to the InnoDB Hot Backup product). MySQL Enterprise Backup allows you to backup your database while it is online and it keeps the database available to users during backup operations (you don’t have to take the database offline or lock any databases/tables).

This post will deal with mysqldump. For those of you that aren’t familiar with mysqldump:

The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

The best feature about mysqldump is that it is easy to use. The main problem with using mysqldump occurs when you need to restore a database. When you execute mysqldump, the database backup (output) is an SQL file that contains all of the necessary SQL statements to restore the database – but restoring requires that you execute these SQL statements to essentially rebuild the database. Since you are recreating your database, the tables and all of your data from this file, the restoration procedure can take a long time to execute if you have a very large database.

There are a lot of features and options with mysqldump – (a complete list is here). I won’t review all of the features, but I will explain some of the ones that I use.

If you have InnoDB tables (InnoDB is the default storage engine as of MySQL 5.5 – replacing MyISAM), when you use mysqldump you will want to use the option –single-transaction or issue the command FLUSH TABLES WITH READ LOCK; in a separate terminal window before you use mysqldump. You will need to release the lock after the dump has completed with the UNLOCK TABLES; command. Either option (–single-transaction or FLUSH TABLES WITH READ LOCK;) acquires a global read lock on all tables at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long-updating statements are running when the FLUSH statement is issued, the MySQL server may get stalled until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.
(from http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html)

Here is the command to use mysqldump to simply backup all of your databases (assuming you have InnoDB tables). This command will create a dump (backup) file named all_databases.sql.

mysqldump --all-databases --single-transaction --user=root --pass > all_databases.sql

After you hit return, you will have to enter your password. You can include the password after the –pass option (example: –pass=my_password), but this is less secure and you will get the following error:

Warning: Using a password on the command line interface can be insecure.

Here is some information about the options that were used:

--all-databases - this dumps all of the tables in all of the databases
--user - The MySQL user name you want to use for the backup
--pass - The password for this user.  You can leave this blank or include the password value (which is less secure)
--single-transaction - for InnoDB tables

If you are using Global Transaction Identifier’s (GTID’s) with InnoDB (GTID’s aren’t available with MyISAM), you will want to use the –set-gtid-purged=OFF option. Then you would issue this command:

mysqldump --all-databases --single-transaction --set-gtid-purged=OFF --user=root --pass > all_databases.sql

Otherwise you will see this error:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

You can also execute a partial backup of all of your databases. This example will be a partial backup because I am not going to backup the default databases for MySQL (which are created during installation) – mysql, test, PERFORMANCE_SCHEMA and INFORMATION_SCHEMA

Note: mysqldump does not dump the INFORMATION_SCHEMA database by default. To dump INFORMATION_SCHEMA, name it explicitly on the command line and also use the –skip-lock-tables option.

mysqldump never dumps the performance_schema database.

mysqldump also does not dump the MySQL Cluster ndbinfo information database.

Before MySQL 5.6.6, mysqldump does not dump the general_log or slow_query_log tables for dumps of the mysql database. As of 5.6.6, the dump includes statements to recreate those tables so that they are not missing after reloading the dump file. Log table contents are not dumped.

If you encounter problems backing up views due to insufficient privileges, see Section E.5, “Restrictions on Views” for a workaround.
(from: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html)

To do a partial backup, you will need a list of the databases that you want to backup. You may retrieve a list of all of the databases by simply executing the SHOW DATABASES command from a mysql prompt:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| comicbookdb        |
| coupons            |
| mysql              |
| performance_schema |
| scripts            |
| test               |
| watchdb            |
+--------------------+
8 rows in set (0.00 sec)

In this example, since I don’t want to backup the default mysql databases, I am only going to backup the comicbookdb, coupons, scripts and watchdb databases. I am going to use the following options:

--databases - This allows you to specify the databases that you want to backup.  You can also specify certain tables that you want to backup.  If you want to do a full backup of all of the databases, then leave out this option
--add-drop-database - This will insert a DROP DATABASE statement before each CREATE DATABASE statement.  This is useful if you need to import the data to an existing MySQL instance where you want to overwrite the existing data.  You can also use this to import your backup onto a new MySQL instance, and it will create the databases and tables for you.
--triggers - this will include the triggers for each dumped table
--routines - this will include the stored routines (procedures and functions) from the dumped databases
--events - this will include any events from the dumped databases
--set-gtid-purged=OFF - since I am using replication on this database (it is the master), I like to include this in case I want to create a new slave using the data that I have dumped.  This option enables control over global transaction identifiers (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output.
--user - The MySQL user name you want to use
--pass - Again, you can add the actual value of the password (ex. --pass=mypassword), but it is less secure than typing in the password manually.  This is useful for when you want to put the backup in a script, in cron or in Windows Task Scheduler.
--single-transaction - Since I am using InnoDB tables, I will want to use this option.

Here is the command that I will run from a prompt:

mysqldump --databases comicbookdb coupons scripts watchdb --single-transaction --set-gtid-purged=OFF --add-drop-database --triggers --routines --events --user=root --pass > partial_database_backup.sql

I will need to enter my password on the command line. After the backup has completed, if your backup file isn’t too large, you can open it and see the actual SQL statements that will be used if you decide that you need to recreate the database(s). If you accidentally dump all of the databases into one file, and you want to separate the dump file into smaller files, see my post on using Perl to split the dump file.

For example, here is the section of the dump file (partial_database_backup.db) for the comicbookdb database (without the table definitions). (I omitted the headers from the dump file.)

--
-- Current Database: `comicbookdb`
--

/*!40000 DROP DATABASE IF EXISTS `comicbookdb`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `comicbookdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `comicbookdb`;

--
-- Table structure for table `comics`
--

DROP TABLE IF EXISTS `comics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `comics` (
  `serial_id` int(7) NOT NULL AUTO_INCREMENT,
  `date_time_added` datetime NOT NULL,
  `publisher_id` int(6) NOT NULL,
....

If you are using the dump file to create a slave server, you can use the –master-data option, which includes the CHANGE MASTER information, which looks like this:

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=79338;

If you used the –set-gtid-purged=OFF option, you would see the value of the Global Transaction Identifier’s (GTID’s):

--
--GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED='82F20158-5A16-11E2-88F9-C4A801092ABB:1-168523';

You may also test your backup without exporting any data by using the –no-data option. This will show you all of the information for creating the databases and tables, but it will not export any data. This is also useful for recreating a blank database on the same or on another server.

When you export your data, mysqldump will create INSERT INTO statements to import the data into the tables. However, the default is for the INSERT INTO statements to contain multiple-row INSERT syntax that includes several VALUES lists. This allows for a quicker import of the data. But, if you think that your data might be corrupt, and you want to be able to isolate a given row of data – or if you simply want to have one INSERT INTO statement per row of data, then you can use the –skip-extended-insert option. If you use the –skip-extended-insert option, importing the data will take much longer to complete, and the backup file size will be larger.

Importing and restoring the data is easy. To import the backup file into a new, blank instance of MySQL, you can simply use the mysql command to import the data:

mysql -uroot -p < partial_database_backup.sql

Again, you will need to enter your password or you can include the value after the -p option (less secure).

There are many more options that you can use with a href=”http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html”>mysqldump. The main thing to remember is that you should backup your data on a regular basis, and move a copy of the backup file off the MySQL server.

Finally, here is a Perl script that I use in cron to backup my databases. This script allows you to specify which databases you want to backup via the mysql_bak.config file. This config file is simply a list of the databases that you want to backup, with an option to ignore any databases that are commented out with a #. This isn’t a secure script, as you have to embed the MySQL user password in the script.

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

use warnings;
use File::Basename;

# set the directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/mysqlbak';

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";

# example config file
# You may use a comment to bypass any database that you don't want to backup
# # Unwanted_DB    (commented - will not be backed up)
# twtr
# cbgc

# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));

# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);

$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min  $folder/$file.Z`;

	print "Done\n";
}
print "Done\n\n";

# this subroutine simply creates an array of the list of the databases

sub getFileContents {
	my $file = shift;
	open (FILE,$file) || die("Can't open '$file': $!");
	my @lines=;
	close(FILE);

	return @lines;
}

# remove any commented tables from the @lines array

sub removeComments {
	my @lines = @_;

	@cleaned = grep(!/^\s*#/, @lines); #Remove Comments
	@cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines

	return @cleaned;
}

 


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.

Retrieving List of MySQL Users and Grants with Perl

Before I upgrade MySQL to the latest and greatest version, one of the first things that I do is export the user and grant information. In the past, I would keep all of my user information (user name, password, grants) in a text file, with the SQL for each user/grant ready to be executed on the upgraded server. I did use my own form of “mental encryption” for my passwords, so the passwords weren’t in plain English. But then I would have to decode my passwords each time before I executed the SQL statements.

When I upgrade, I usually like to dump all of the data and import it into the new version, so I have a fresh copy of the database. The MySQL server that I have is for my personal use and the data size is relatively small, so for my case it doesn’t take long to import the data.

But there were times when I would add a user in the MySQL database and forget to add it to my text file. Then, when it came time to upgrade and I would refer to my text file, these new users would not get recreated. For me it wasn’t that big of a deal, as I am only dealing with my own home-office server, and not a production server. I would eventually figure out that these users weren’t available, and I would simply recreate them. But I often add temporary users for testing purposes. Every once in a while I would want to keep some of the temporary users, and some users would be deleted after the testing was completed. So my text file was rarely up to date.

I am in the process of upgrading my servers to MySQL 5.6.9, and I decided to write a quick Perl script to export all of the users, passwords and grants. Since I will be doing a fresh install of MySQL, I can then just use the output from this script to recreate my users. Of course, you need to run this script before the upgrade.

There are two SQL statements that we will execute with this script. The first, is simply retrieving a list of the user and host names from the mysql.user table:

mysql> SELECT user, host FROM user order by user, host;
+-------------+---------------+
| user        | host          |
+-------------+---------------+
| replicate   | 192.168.1.121 |
| replicate   | 192.168.1.2   |
| replicate   | 192.168.1.4   |
| replicate   | localhost     |
| root        | 127.0.0.1     |
| root        | 192.168.1.2   |
| root        | localhost     |
| root        | macserver01   |
| scripting   | 192.168.1.121 |
| scripting   | 192.168.1.122 |
| scripting   | 192.168.1.2   |
+-----------+---------------+
11 rows in set (0.00 sec)

Then, we will loop through each of the user and host names to retrieve their grants and passwords. Here is a sample of retrieving this data for just one user and one host name:

mysql> SHOW GRANTS FOR 'replicate'@'192.168.1.121';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for replicate@192.168.1.121                                                                                |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.121' IDENTIFIED BY PASSWORD '*BF6F715A6EBFE63005BEB705C' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The script will create the necessary SQL statements to recreate the users and grants, with the output displayed in the terminal window. You will just need to copy and save the output from the terminal window into a text file. Or you can change the print statement to output the information directly to a file. Here are the changes to output to a file:

Add this to the top of the script after the “my $Database” line.

open(OUTFILE, ">mysql_usernames.txt") || die "Can't redirect stdout";

Add OUTFILE after each of your print commands:

print OUTFILE "CREATE user \'$user\'\@'$host\'\ identified by '';\n";
print OUTFILE "$privileges;\n\n";

And close the OUTFILE after the last right curly bracket “}” and before the subroutine:

close OUTFILE;

For the script, you will need to install the Perl::DBI module. You will also need to change the values for the $host, $userid and $passwd variables in the sub routine ConnectToMySql to match your system. Here is the Perl script.

#!/usr/bin/perl

use DBI;

my $Database = "mysql";

        $dbh = ConnectToMySql($Database);

        # retrieve a list of users and host names
	$query = "SELECT user, host FROM user order by user, host";
	
        $sth = $dbh->prepare($query);

        $sth->execute();
    
          while (@data = $sth->fetchrow_array()) {
            my $user = $data[0];
            my $host = $data[1];
            
            print "CREATE user \'$user\'\@'$host\'\ identified by '';\n";

                $dbh2 = ConnectToMySql($Database);

	        # retrieve the grants for each user and host combination
                $query2 = "SHOW GRANTS FOR '$user'\@'$host'";
	
                $sth2 = $dbh2->prepare($query2);

                $sth2->execute();

                        while (@data2 = $sth2->fetchrow_array()) {
                            my $privileges = $data2[0];
                            print "$privileges;\n\n";
                        }

          # end first while statement           
          }

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

   my ($db) = @_;

   my $host ="";
   my $userid = "";
   my $passwd = "";
   my $connectionInfo = "dbi:mysql:$db;$host";

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

}

And here is the output from running the script.

root@macserver01: $ perl get_mysql_users.pl
CREATE user 'replicate'@'192.168.1.121' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.121' IDENTIFIED BY PASSWORD '*BF6F715A6EBF367E76X705C';

CREATE user 'replicate'@'192.168.1.2' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.2' IDENTIFIED BY PASSWORD '*BF6F715A6EBF367E76X705C';

CREATE user 'replicate'@'192.168.1.4' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.4' IDENTIFIED BY PASSWORD '*2A9C19E10B309BF1BE40E4A9C';

CREATE user 'replicate'@'localhost' identified by '';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'localhost' IDENTIFIED BY PASSWORD '*2A9C19E10B309BF1BE40E4A9C';

CREATE user 'root'@'127.0.0.1' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION;

CREATE user 'root'@'192.168.1.2' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION;

CREATE user 'root'@'localhost' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION;

CREATE user 'root'@'macserver01' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'macserver01' WITH GRANT OPTION;

CREATE user 'scripting'@'192.168.1.121' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.121' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION;

CREATE user 'scripting'@'192.168.1.122' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.122' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION;

CREATE user 'scripting'@'192.168.1.2' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.2' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION;

Now when I upgrade my server, I can simply run this script prior to my upgrade and save the information. I don’t have to worry about missing a user or keeping my user information in a text 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.

How To Sort Columns of MySQL Data on a Web Page With Perl

A friend of mine was building a web site so his customers could view his current inventory of transportation containers, and he asked me for help on how to sort the rows of information that appeared on his site. So, in this post, I will give you a quick example on how to sort columns on a web page.

First, let’s start with an inventory database that we will build in MySQL:

CREATE TABLE `inventory` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`item_name` varchar(30) NOT NULL,
`item_SKU` varchar(20) NOT NULL,
`item_cost` decimal(4,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1

Next, here are some SQL statements to populate the MySQL database with some sample data.

use inventory;
insert into inventory (item_name, item_SKU, item_cost) values ('Apple', '10001', '1.04');
insert into inventory (item_name, item_SKU, item_cost) values ('Peach', '10004', '1.28');
insert into inventory (item_name, item_SKU, item_cost) values ('Plum', '10301', '1.17');
insert into inventory (item_name, item_SKU, item_cost) values ('Apricot', '13033', '1.92');
insert into inventory (item_name, item_SKU, item_cost) values ('Grapes', '20422', '1.34');
insert into inventory (item_name, item_SKU, item_cost) values ('Kiwi', '98561', '2.78');
insert into inventory (item_name, item_SKU, item_cost) values ('Mango', '56231', '0.99');
insert into inventory (item_name, item_SKU, item_cost) values ('Strawberry', '24689', '1.52');
insert into inventory (item_name, item_SKU, item_cost) values ('Banana', '65213', '0.39');
insert into inventory (item_name, item_SKU, item_cost) values ('Tangerine', '47112', '1.22');

For this example, I am not going to show you how to create a new record, edit or delete your information in a MySQL database, as I did that in an earlier post.

Now that we have our data, we are going to need a Perl script that will retrieve all of the data and create our web page at the same time. I have named the Perl script inventory_view. Don’t forget to change all of the variables to match your system. I will explain the variables that we will use in the Perl Script after the script below:

#!/usr/bin/perl
#--------------------------------------------------------------------------
# inventory_view.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

my $Database = "scripting_mysql";

# get the sort parameters from the URL
my $query = new CGI;
# $sort is the order by which to sort - ascending or descending
# you only need to set a value if it is descending (desc)
$sort = $query->param("sort");
# $sortby is the field name to be used for sorting
$sortby = $query->param("sortby");

# check for sort order - ascending or descending
if ($sort =~ "asc")

{
$order_by = "order by $sortby";
}

else

{
$order_by = "order by $sortby $sort";
}

# if the sort isn't set to a value, set it to a default sort of item_name
if (length($sort) < 1)

{
$order_by = "order by item_name";
}

# print HTML header
print header;

# connect to the database and pull every record
$dbh = ConnectToMySql($Database);
$query = "select item_name, item_SKU, item_cost from inventory $order_by";
$sth = $dbh->prepare($query);
$sth->execute();

# print the table header
print <<HTML;
<table border=0>
<tr>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;"><a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=asc&sortby=item_name><img alt="sort ascending" title="sort ascending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_up.png></a>  <a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=desc&sortby=item_name><img alt="sort descending" title="sort descending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_down.png></a></td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;"><a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=asc&sortby=item_SKU><img alt="sort ascending" title="sort ascending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_up.png></a>  <a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=desc&sortby=item_SKU><img alt="sort descending" title="sort descending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_down.png></a></td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;"><a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=asc&sortby=item_cost><img alt="sort ascending" title="sort ascending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_up.png></a>  <a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=desc&sortby=item_cost><img alt="sort descending" title="sort descending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_down.png></a></td>
</tr>

<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;">Item Name</td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;">Item SKU</td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;">Item Price</td>

HTML

# set the second line background color
$background_color = "#FFFFFF";

# loop through the data fetched from the query
while (@data = $sth->fetchrow_array()) {

$item_name = $data[0];
$item_SKU = $data[1];
$item_cost = $data[2];

# alternate the background colors
if ($background_color =~ "#FFFFFF")

{
$background_color="#FFFFCC";
}

else

{
$background_color="#FFFFFF";
}

# print the table rows, one for each item from the database
print <<HTML;

<tr bgcolor="$background_color">
<td style="text-align:right;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;"</td>$item_name </td>
<td style="text-align:right;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;"</td>$item_SKU </td>
<td style="text-align:right;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;"</td>$item_cost </td>
</tr>
HTML
}

# close the table
print "</table>";

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

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

}

When you run the script for the first time in a browser, you should see something like this:

In our Perl script, we are using the $query variable to select the three columns from our MySQL database – item_name, item_SKU and item_cost. And we will decide which one to sort by with the variable $sortby. And we will assign ascending or descending order to the variable $sort. This will give us a total of six options for sorting columns and in which order:

$sortby $sort
item_name ascending order
item_name descending order
item_SKU ascending order
item_SKU descending order
item_cost ascending order
item_cost descending order

To build the URL for each, we simply use the script name – inventory_view.pl – and add the variables $sortby and $sort along with their values, using an ampersand for the delimiter.

inventory_view.pl?sortby=[column_name]&sort=[asc or desc]

Example:

inventory_view.pl?sortby=item_name&sort=asc

We will then use an image of an arrow pointing upwards (for ascending order) and downwards (for descending order). Here is an example of the HTML for “sort by item_name and ascending order”: (don’t forget to add the full path names for both the location of the Perl script and the location of your image file)

<a href="cgi-bin/inventory_view.pl?sortby=item_name&sort=asc"><img src="images/arrow_blue_up.png"></a>

You simply have to build a link for each of the six sorting options (two per database column) to correspond to each of the blue up/down arrows. All six links would look like this:

<a href="cgi-bin/inventory_view.pl?sort=asc&sortby=item_name"><img alt="sort ascending" title="sort ascending" height="15" src="images/arrow_blue_up.png"></a>
<a href="cgi-bin/inventory_view.pl?sort=desc&sortby=item_name"><img alt="sort descending" title="sort descending" height="15" src="images/arrow_blue_down.png"></a>

<a href="cgi-bin/inventory_view.pl?sort=asc&sortby=item_SKU"><img alt="sort ascending" title="sort ascending" height="15" src="images/arrow_blue_up.png"></a>
<a href="cgi-bin/inventory_view.pl?sort=desc&sortby=item_SKU"><img alt="sort descending" title="sort descending" height="15" src="images/arrow_blue_down.png"></a>

<a href="cgi-bin/inventory_view.pl?sort=asc&sortby=item_cost"><img alt="sort ascending" title="sort ascending" height="15" src="images/arrow_blue_up.png"></a>
<a href="cgi-bin/inventory_view.pl?sort=desc&sortby=item_cost"><img alt="sort descending" title="sort descending" height="15" src="images/arrow_blue_down.png"></a>

When the inventory_view.pl script is executed the first time, we have not set the $sortby column or $sort order, so it will default to sorting by item_name and ascending order (which really doesn’t have a value, as ascending order is the default sort order). Each time a sort request is made, a new connection will be made to the database, so that is something to consider when you decide which columns you want to be available to sort. And we aren’t putting a limit on the number of rows that are retrieved, so that you have multiple pages of items – but I will try to cover that in a future post.

While there are other solutions that don’t require you to hit the database each time, this should give you a quick (and dirty) way to sort columns of information in a table on a web page.

 


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.

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.

Using MySQL and Perl to Create, Edit and Delete Information Via a Web Page

A friend of mine was asking me for my recommendation of a good desktop database program to use to keep track of his inventory of cargo containers. I suggested to him that he should use MySQL and write a web page interface to do everything that he needed. He then reminded me that he is a lawyer by trade, and that he doesn’t have any computer programming experience. Then I remembered that he has almost zero computer skills. And his Texas Hold-Em skills are even worse, but I don’t mind taking his money. In his case, he should just use a notepad and a pencil. (As for the question – what is a lawyer doing with cargo containers? – that is a different story.)

If he did decide to broaden his horizons a bit, he could easily write his own software web application for creating and storing almost any kind of data. In this post, I will show you how to create a MySQL database and then the web pages needed to create new addresses, edit the same data and delete the data as well.

Of course, you will need to download and install MySQL. There are a ton of resources on the web for doing this, so let’s assume that you have already this part completed – and that you know how to use MySQL. First, let’s create a MySQL table. In this example, we will create a simple address book, and populate it with a few fake names. Here is the SQL, complete with the fake data to be inserted:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`serial` int(4) NOT NULL AUTO_INCREMENT,
`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(2) NOT NULL,
`address_postal_code` varchar(10) NOT NULL,
PRIMARY KEY (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

insert into `address` values('1','1','Clark','Kent','344 Clinton St','Apt. #3B','Metropolis','NY','10001'),
('2','2','Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
('3','3','Tom','Watson','123 Golf Course Lane','Suite A','Macon','GA','31066'),
('4','4','Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044'),
('5','1','Betty','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
('6','2','Bruce','Wayne','1007 Mountain Drive','','Gotham City','NY','10000');

SET FOREIGN_KEY_CHECKS = 1;

We will be creating several Perl scripts as follows. To keep it simple, we will use Perl to create all of the pages, even though the “add_entry.pl” script could be a standard HTML page. This will allow you to put all of the scripts in your cgi-bin directory. Here are the scripts that we will be creating and using:
  — dashboard.pl -> This is your “home page”, and will list all of the entries in your address database.
  — add_entry.pl -> This page will allow you to complete a form containing the new address information.
  — add.pl -> This Perl script will save the information from the add.html web page.
  — edit.pl -> This Perl script will allow you to edit an entry from your address database.
  — edit_save.pl -> This will save the changes made from the edit.pl page.
  — delete.pl -> This is step one in deleting a record, it takes you to the delete_confirm.pl page.
  — delete_confirm.pl -> This will confirm the deletion of a record, or allow you to cancel the request.

For our home page (called dashboard), we need to create a web page (via a Perl script) that will list all of the addresses in the database. In this example, we aren’t using any logic to restrict the number of entries that are displayed, so this page will just display all of them (adding limits to a web page like this is a bit more complicated, and I might try to cover this in a future post). We will use a Perl script to display the HTML, as we will need to pull data from the database to be displayed in the web page. So, you will need to put the “directory.pl” script (home page) and all of the other scripts in your cgi-bin directory (and don’t forget to make all of your Perl scripts executable – via “chmod 755″). You could create a regular HTML page (.htm) using frames, with the top frame using HTML and then use a Perl script to pull the data for the bottom frame, but let’s just stick with this example for now.

Here is what the opening home page (dashboard.pl) should look like:

In this script, we will also create a link that will allow us to edit the information for a single address entry, as well as a link to delete the entry. We will use the serial database field to point to the record that we want to edit or delete. We will also display a link at the top for creating a new address entry, and this Perl script will be named “add.pl”. But first, here is the “dashboard.pl” script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# dashboard.pl
#--------------------------------------------------------------------------

# 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 CGI qw(:standard);

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Dashboard</title>
</head>

<body>
<center>

HTML
# leave the above line to the left of the page

# list my addresses

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address order by serial";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/add_entry.pl>Create New</a></font><p>";
print "<table border=0>";
# print table row headers
print "<tr><td>ID</td><td>First</td><td>Last</td><td>Address</td><td>Address</td><td>City</td><td>ST</td><td>ZIP</td></tr>";

# set your initial row background color
$bgcolor = "#EEEEEE";

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

# print rows of data
# for the $serial, we include the link to the edit.pl script - edit.pl?$serial
print "<tr bgcolor=$bgcolor><td><a href=edit.pl?id=$serial>$serial</a></td><td>$name_first</td><td>$name_last</td><td>$address_01</td><td>$address_02</td><td>$address_city</td><td>$address_state</td><td>$address_postal_code</td></tr>";

# alternate background colors
if ($bgcolor =~ "#EEEEEE") { $bgcolor = "white"; }

else

{$bgcolor = "#EEEEEE";}

# end while (@data = $sth->fetchrow_array())
}
# print bottom of page
print <<HTML;
<table>
</body>
</html>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

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

The add_entry.pl web page will look like this (with the data for a new entry already entered):

And here is the script to create the add_entry.pl web page:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# add_entry.pl
#--------------------------------------------------------------------------

use CGI qw(:standard);

print header;

print <<HTML;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Add Entry</title>
</head>

<body>
<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>
<table>
<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/add.pl">

<table>

<tr><td>Name First</td><td><INPUT TYPE=text NAME="name_first" id=name_first size=30></td></tr>
<tr><td>Name Last</td><td><INPUT TYPE=text NAME="name_last" id=name_last size=30></td></tr>
<tr><td>Address 1</td><td><INPUT TYPE=text NAME="address_01" id=address_01 size=40></td></tr>
<tr><td>Address 2</td><td><INPUT TYPE=text NAME="address_02" id=address_02 size=40></td></tr>
<tr><td>City</td><td><INPUT TYPE=text NAME="address_city" id=address_city size=30></td></tr>
<tr><td>State</td><td><INPUT TYPE=text NAME="address_state" id=address_state size=2></td></tr>
<tr><td>Zip</td><td><INPUT TYPE=text NAME="address_postal_code" id=address_postal_code size=10></td></tr>

<tr><td colspan=2><center><input type="submit" value="Add Address" alt="Add Address"></td></tr>
</form>

</body>
</html>

HTML

exit;

The form on this web page calls a Perl script named “add.pl”, which will insert the information to the MySQL database.

Here is the Perl script add.pl:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# add.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});

$buffer =~ tr/+/ /;
$buffer =~ s/\r/ /g;
$buffer =~ s/'/ /g;
$buffer =~ s/\n/ /g;
$buffer =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
$buffer =~ s/<!--(.|\n)*-->/ /g;
$buffer =~ tr/\\|[|]|<|!|"|$|{|}|*|#|'|>|||;|%/ /;

@pairs = split(/&/,$buffer);
foreach $pair(@pairs){
($key,$value)=split(/=/,$pair);
$formdata{$key}.="$value";
}

# here are the values from the HTML form
$name_first = $formdata{'name_first'};
$name_last = $formdata{'name_last'};
$address_01 = $formdata{'address_01'};
$address_02 = $formdata{'address_02'};
$address_city = $formdata{'address_city'};
$address_state = $formdata{'address_state'};
$address_postal_code = $formdata{'address_postal_code'};

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - New</title>
</head>

<body>
<center>

HTML
# leave the above line to the left of the page

# list my addresses

$dbh = ConnectToMySql($Database);

$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code) values (?,?,?,?,?,?,?)";

$sth = $dbh->prepare($query);

$sth->execute("$name_first", "$name_last", "$address_01", "$address_02", "$address_city", "$address_state", "$address_postal_code");

$dbh->disconnect;

print "$name_first $name_last - was added to the database.<p>";

print "Return to the <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a>";

# print bottom of page
print <<HTML;
<table>
</body>
</html>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

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

After you add an entry to the database, this is the confirmation web page after a record has been added.

The add.pl page gives you a link to go back to the dashboard, which will then display all of your address database entries, including the one you just entered.

To edit an entry, from the dashboard web page, you simply click on the ID of the entry that you want to modify. For example, when you click on the first entry with a serial number of “1”, you will see this link:

http://192.168.1.2/cgi-bin/scripting_mysql/edit.pl?id=1

Clicking on an serial number link will call the Perl script named “edit.pl”. This will create a web page where you can edit the information and save it back to the database. The edit.pl web page looks like this:

Here is the edit.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# edit.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

$query = new CGI;

$id = $query->param("id");

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Edit</title>
</head>

<body>
HTML

# grab the information for this id

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "<table border=0>";

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

print <<HTML;

<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/edit_save.pl"&gt;
<input type=hidden name=id value="$id">
<tr><td align=right>Name First  </td><td><INPUT TYPE=text NAME="name_first" id=name_first size=30 value="$name_first"></td></tr>
<tr><td align=right>Name Last  </td><td><INPUT TYPE=text NAME="name_last" id=name_last size=30 value="$name_last"></td"></tr>
<tr><td align=right>Address 1  </td><td><INPUT TYPE=text NAME="address_01" id=address_01 size=40 value="$address_01"></td></tr>
<tr><td align=right>Address 2  </td><td><INPUT TYPE=text NAME="address_02" id=address_02 size=40 value="$address_02"></td></tr>
<tr><td align=right>City  </td><td><INPUT TYPE=text NAME="address_city" id=address_city size=30 value="$address_city"></td></tr>
<tr><td align=right>State  </td><td><INPUT TYPE=text NAME="address_state" id=address_state size=2 value="$address_state"></td></tr>
<tr><td align=right>Zip  </td><td><INPUT TYPE=text NAME="address_postal_code" id=address_postal_code size=10 value="$address_postal_code"</td></tr>
<tr><td colspan=2><center><input type="submit" value="Save Changes" alt="Save Changes"></td></tr>
</form>
</table>

</body>
</html>

HTML

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

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

The edit.pl script will create the web page for you to make your changes. After you have made the changes, the form will call another Perl script named “edit_save.pl” to apply those changes to the database. Here is the edit_save.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# edit_save.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);
use LWP::UserAgent;

my $Database = "address";

read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});

$buffer =~ tr/+/ /;
$buffer =~ s/\r/ /g;
$buffer =~ s/'/ /g;
$buffer =~ s/\n/ /g;
$buffer =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
$buffer =~ s/<!--(.|\n)*-->/ /g;
$buffer =~ tr/\\|[|]|<|!|"|$|{|}|*|#|'|>|||;|%/ /;

@pairs = split(/&/,$buffer);
foreach $pair(@pairs){
($key,$value)=split(/=/,$pair);
$formdata{$key}.="$value";
}

$id = $formdata{'id'};
$name_first = $formdata{'name_first'};
$name_last = $formdata{'name_last'};
$address_01 = $formdata{'address_01'};
$address_02 = $formdata{'address_02'};
$address_city = $formdata{'address_city'};
$address_state = $formdata{'address_state'};
$address_postal_code = $formdata{'address_postal_code'};

$dbh = ConnectToMySql($Database);

$query = "update address set name_first = '$name_first', name_last = '$name_last', address_01 = '$address_01', address_02 = '$address_02', address_city = '$address_city', address_state = '$address_state', address_postal_code = '$address_postal_code' where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Edit Saved</title>
</head>

<body>
<center>
<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>

The following information was updated:<p>
<table>
<tr><td align=right>Name First  </td><td>$name_first</td></tr>
<tr><td align=right>Name Last  </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1  </td><td>$address_01</td></tr>
<tr><td align=right>Address 2  </td><td>$address_02</td></tr>
<tr><td align=right>City  </td><td>$address_city</td></tr>
<tr><td align=right>State  </td><td>$address_state</td></tr>
<tr><td align=right>Zip  </td><td>$address_postal_code</td></tr>
</table>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

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

}

After you have edited your record, the edited results web page will look like this:

You can then click on the Dashboard link to go back to the main dashboard page.

If you want to delete a record, from the dashboard.pl page, you simply click on the red X image on the row that you want to delete. For example, when you click on the X for the serial number of “1”, you will see this link:

http://192.168.1.2/cgi-bin/scripting_mysql/delete.pl?id=1

This link takes you to the delete.pl page, where you are asked if you want to confirm the deletion, or you may cancel and go back to the dashboard. When you click on an X to delete a row, you will see a page like this:

And here is the delete.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# delete.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

$query = new CGI;
$id = $query->param("id");

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Delete Record</title>
</head>

<body>
<center>

HTML

# grab the information for this id

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "Are you sure you want to delete this record?<p>";
print "<table border=0>";

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

print <<HTML;

<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/delete_confirm.pl?id=$id">
<input type=hidden name=id value="$id">
<tr><td align=right>Name First  </td><td>$name_first</td></tr>
<tr><td align=right>Name Last  </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1  </td><td>$address_01</td></tr>
<tr><td align=right>Address 2  </td><td>$address_02</td></tr>
<tr><td align=right>City  </td><td>$address_city</td></tr>
<tr><td align=right>State  </td><td>$address_state</td></tr>
<tr><td align=right>Zip  </td><td>$address_postal_code</td></tr>
<tr><td><center><input type="submit" value="Delete Address" alt="Delete Address"></form></td><td><center><form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl"><input type="submit" value="Cancel" alt="Cancel"></form></td></tr>
</table>

</body>
</html>

HTML
# leave the above line to the left of the page

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

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

If you choose to not delete the record, you are simply taken back to the Dashboard. If you choose to delete the record, then the delete-confirm.pl script is executed, and the record is deleted. Here is the confirmation web page for a deletion:

And here is the delete_confirm.pl script:

#!/usr/bin/perl

#--------------------------------------------------------------------------
# delete_confirm.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

$query = new CGI;
$id = $query->param("id");

my $Database = "scripting_mysql";

print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Delete Record Confirmed</title>
</head>

<body>
<center>

Are you sure you want to delete this record?

HTML

# grab the information for this id

$dbh = ConnectToMySql($Database);

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "The following information was deleted.<p>";
print "<table border=0>";

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

print <<HTML;

<tr><td align=right>Name First  </td><td>$name_first</td></tr>
<tr><td align=right>Name Last  </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1  </td><td>$address_01</td></tr>
<tr><td align=right>Address 2  </td><td>$address_02</td></tr>
<tr><td align=right>City  </td><td>$address_city</td></tr>
<tr><td align=right>State  </td><td>$address_state</td></tr>
<tr><td align=right>Zip  </td><td>$address_postal_code</td></tr>
</table>

</body>
</html>

HTML
# leave the above line to the left of the page

# here we delete the record
$dbh = ConnectToMySql($Database);

$query = "delete from address where serial = '$id'";

$sth = $dbh->prepare($query);

$sth->execute();

$dbh->disconnect;

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

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

}

One alternative to deleting the record would be to include a separate database field called “active”, set the field value to “yes” for all current records (if you have already imported records) and set the default value for this field to “yes” for any new records. Then, instead of deleting the information, you simply set “active” to “no”. So when you retrieve your list on the dashboard.pl page, you will only look for records where “active” is equal to “yes”. You will still have the “deleted” information available to you in the database. You could even duplicate the dashboard.pl script to have a “delete” page that displays the records that have been deleted.

To use the “active” option, you will need to make the following changes:

You would need to add this line in your create table SQL statement: (I would insert the line after the “serial” field in your create table statement)

`active` varchar(3) NOT NULL DEFAULT 'yes',

Then, in your dashboard.pl script, you would simply change the $query to:

$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where active = 'yes' order by serial";

For your add.pl and add_entry.pl scripts. you don’t need to do anything as the “active” field has a default value of “yes” for new records.

For your edit.pl and edit_save.pl scripts, you don’t need to do anything as you aren’t changing the “active” field.

And in your delete_confirm.pl script, instead of deleting the record, simply change the $query = “delete from address where serial = ‘$id'”; to:

$query = "update address set active = 'no' where serial = '$id'";

With these scripts, you should be able to install everything and have it running in a few minutes. And, if my lawyer friend gets tired of spending all day at the courthouse, he could always learn a new skill.

 


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.

Checking on the Progress of Large DML Commands in MySQL Using Perl – Part Two

Part Two of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part Two: Monitoring the activity via Perl and SHOW ENGINE INNODB STATUS. (part of the InnoDB Monitors)

In part one, I showed you how to use a Perl script to insert a million rows of dummy data into a table. I needed a large database in order to test a Perl script that I would use to monitor the activity when running a large DML statement.

The original reason for creating both of these scripts was to find a quick way to see if a large DML statement was actually being executed. A customer was performing some modifications on tables with tens of millions of rows, and they wanted to know if they were making any progress. Since the customer was using the InnoDB storage engine, I thought of a way that you could check on the progress – but only given the fact that nothing else was happening in the database (more on this reason later).

With InnoDB, you can issue the SHOW ENGINE INNODB STATUS and you will get a wealth of information. I am not going to list any of it here as it would consume too much space. However, towards the bottom of the output, you will notice under “ROW OPERATIONS” a line that contains the words “Number of rows inserted…”:

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 2960240640, state: waiting for server activity
Number of rows inserted 1202598, updated 97249, deleted 806, read 56448551
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

The above line gives you a quick snapshot of how many rows have been inserted, updated, deleted or selected (read). So, by invoking the SHOW ENGINE INNODB STATUS command, you can figure what database activity is occurring. But, if you invoke this command while the database is being used for other purposes, it will be difficult to figure out your progress on any of the four values shown.

So, all I needed to do was to write a quick Perl script to monitor the changes to that particular line of output, and then I could figure out if a certain statement was indeed being executed. My original thinking was that I needed a database with a lot of records in it. So, I decide to first create a dummy table and then shove a million records into it. But I figured out that by creating a database with a million rows, I could simply just monitor this activity versus issuing a command on the database once it had been built.

I created a quick Perl script that issues the SHOW ENGINE INNODB STATUS command every X number of seconds. It then looks for the output total for whatever I want to track – either inserts, updates, deletes or selects (reads). And yes, you could just do this manually, but if you have a command that you want to run overnight, you could execute this script and at least see what happened when you return in the morning.

One caveat – SHOW ENGINE INNODB STATUS does not correspond to a particular point in time, so it may not be consistent – as to ensure consistency would require a global lock which would consume too many resources. But it is good enough for what we want to do here.

The script will check the status, and then depending upon what variable (inserts, updates, deletes, selects) you want to search for, it will show you the total for that variable as it increases. I didn’t take time to add a lot of information about how long it would take to complete the original command, as this would be just a wild guess, given the fact that other things could be happening with the database. You will need to change the variable that you want to look for, how many rows you are expecting to change, how many seconds you want it to refresh, and your database information:

#!/usr/bin/perl -w

# 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;
# use CGI for forms
#use CGI qw(:standard);

$start = '';
$begin = '';
$now = 1;

# How many rows are being changed?
# (whole numbers only)
$rows = 200000;

# how long do you want it to sleep? (seconds)
# you can modify this to be a longer time interval than 60 seconds
# which is advisable, as the SHOW ENGINE query will consume resources
$sleep_time = 60;

# what action do you want to monitor?
# based upon this output: Number of rows inserted xxxxx, updated xxxxx, deleted xxxxx, read xxxxx
# (use the word to the right of the equal sign)
# inserts = inserted
# update = updates
# deletes = deleted
# selects = read
$action = "inserted";

$Database = "scripting_mysql";

     $dbh = ConnectToMySql($Database);
     $query = "SHOW ENGINE INNODB STATUS";
     $sth = $dbh->prepare($query);
     $sth->execute();

$data = $sth->fetchrow_array();

@all_rows = split("\\n",$data);

foreach (@all_rows) {

     if ($_ =~ "Number of rows")

     {
          #print "Line $_ \n";
     
          @total = split (" ", $_);
          
          # viewing this output from SHOW ENGINE INNODB STATUS
          # Number of rows inserted 912268, updated 96931, deleted 806, read 52052215
          if ($action =~ "inserted") { $column = 4 }
          if ($action =~ "updated") { $column = 6 }
          if ($action =~ "deleted") { $column = 8 }
          if ($action =~ "read") { $column = 10 }

               if ($_ =~ "Number of rows ")
               {
                    $total[$column] =~ s/,//;
                    $begin = $total[$column];
               }               
     }
}

print "Execute your SQL statement, and then press enter/return: ";
$start = <>;

print "Beginning with $begin $action:\n";

while ($now < $rows) {

$print_date_time = &get_date_time;

     $dbh = ConnectToMySql($Database);
     $query = "SHOW ENGINE INNODB STATUS";
     $sth = $dbh->prepare($query);
     $sth->execute();

     $data = $sth->fetchrow_array();

     @all_rows = split("\n",$data);

     foreach (@all_rows) {

               @total = split (" ", $_);
          
          if ($action =~ "inserted") { $column = 4 }
          if ($action =~ "updated") { $column = 6 }
          if ($action =~ "deleted") { $column = 8 }
          if ($action =~ "read") { $column = 10 }
          
               if ($_ =~ "Number of rows ")
               {
                    $total[$column] =~ s/,//;
                    $now = $total[$column];
                    $now = $now - $begin;
                    $progress = $now / $rows;
                    $progress = $progress * 100;
                    print "$print_date_time - $progress\% changed - $now out of $rows.\n";
               }
}

# you can modify this to be a longer time interval than 60 seconds
# which is advisable, as the SHOW ENGINE query will consume resources
sleep $sleep_time;

}

     $sth->finish;
     $dbh->disconnect;

# from Connecting to MySQL with Perl
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

     my ($db) = @_;

     open(ACCESS_INFO, "<..\/accessBLOG") || die "Can't access login credentials";
     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);
#print "<br>$database $host $userid $passwd <br>";

     my $connectionInfo="dbi:mysql:$database;$host";
     close(ACCESS_INFO);

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

}

# ----------------------------------------------------------------------------------
sub get_date_time {
# ----------------------------------------------------------------------------------

     my ($sec,$min,$hour,$mday,$mon,$year) = localtime time;

     $year = $year + 1900;
     $mon = $mon + 1;

     # add a zero if the value is less than 10

     if ($sec < 10) { $sec = "0$sec"; }
     if ($min < 10) { $min = "0$min"; }
     if ($hour < 10) { $hour = "0$hour"; }
     if ($mday < 10) { $mday = "0$mday"; }
     if ($mon < 10) { $mon = "0$mon"; }
     if ($year < 10) { $year = "0$year"; }

     $DateTime = "$year-$mon-$mday $hour:$min:$sec";
     return $DateTime

}
#----------------------------------------------------------------------

Here is the output from the Perl script. For this example, I was assuming that I would be inserting 200,000 rows.

sh-3.2# /usr/bin/perl monitor.pl
Execute your SQL statement, and then press enter/return:
Beginning with 1196128 inserted:
2011-11-01 17:48:06 - 0.029% changed - 58 out of 200000.
2011-11-01 17:48:16 - 0.464% changed - 928 out of 200000.
2011-11-01 17:48:26 - 0.8645% changed - 1729 out of 200000.
2011-11-01 17:48:36 - 1.259% changed - 2518 out of 200000.
2011-11-01 17:48:46 - 1.628% changed - 3256 out of 200000.
2011-11-01 17:48:56 - 2.0045% changed - 4009 out of 200000.
2011-11-01 17:49:06 - 2.3845% changed - 4769 out of 200000.
2011-11-01 17:49:16 - 2.7685% changed - 5537 out of 200000.
2011-11-01 17:49:26 - 3.1415% changed - 6283 out of 200000.

This script was just a quick hack. Another way to potentially see if your script is still running is to check out the “TRANSACTIONS” section of the SHOW ENGINE INNODB STATUS output. In this example below, you can see the insert statement that the Perl script was performing during part one (where I was inserting the 1,000,000 rows). However, since the database that I was using doesn’t have a lot of activity on it, I was able to see the insert statement multiple times when I resubmitted the SHOW ENGINE INNODB STATUS command. If you have a database with a lot of transactions running, you might not be able to see your statement in the output.

------------
TRANSACTIONS
------------
Trx id counter 1818DA1
Purge done for trx's n:o < 1817E3F undo n:o < 0
History list length 3830
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1818DA0, not started, OS thread id 2961469440 flushing log
mysql tables in use 1, locked 1
MySQL thread id 335232, query id 2818136 192.168.1.2 WebUser query end
insert into test_large (name_first, name_last, address_01, address_02, city, state, zip) values ('1BPnJiuWsyajA4b3SH7OjS4BFJgedK','JPuJ4xh_QEbNokZZGlpcHEHKLk2W__','24IT20mW0moAwWmoYTMOwsv44yRL9mAMoo0mLyLoGoTjaW78O6','6zX2j4PLShQ_IfiOzd0LbTVi8ZaYGk3_6LIafpnFfdFL7kRMTa','mbENuc0kHQz9NLGkn5iy','xR','81941')
---TRANSACTION 0, not started, OS thread id 2960855040

MySQL thread id 326621, query id 2818137 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1815A3B, not started, OS thread id 2961059840
MySQL thread id 322078, query id 2778540 192.168.1.5 WebUser
---TRANSACTION 1815A32, not started, OS thread id 2960650240
MySQL thread id 319917, query id 2778444 192.168.1.5 WebUser
---TRANSACTION 0, not started, OS thread id 2962083840
MySQL thread id 319845, query id 2771552 192.168.1.5 WebUser
---TRANSACTION 0, not started, OS thread id 2963107840
MySQL thread id 43198, query id 2817803 localhost 127.0.0.1 WebUser

I am sure that there are better ways of monitoring large DML statements, and this will only work for InnoDB tables. If you try the same command for MyISAM, SHOW ENGINE MyISAM STATUS, the output is blank:

mysql> SHOW ENGINE MyISAM STATUS;
Empty set (0.00 sec)

If you know of a better or different way to do this, please add your thoughts in the comment section below.

 


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

Get every new post delivered to your Inbox.

Join 39 other followers