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.

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 …

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 …

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

Part One: Inserting a million rows into a database.

A friend of mine had asked a question – “Is there any way you can track how far you have advanced in a slow-moving ALTER or OPTIMIZE statement?”. A customer was performing some modifications on a database with tens of millions of rows, and they wanted to be able to see if the command was 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 (major) was happening in the database (more on this reason later).

With InnoDB, you can …

If you have ever built a form on a web page, you might have used a drop-down menu to display the choices available for a particular field. With a drop-down menu, you restrict the choices a user may select so that the user doesn’t enter invalid data (among other reasons). If a user misspells an entry, then a subsequent search for that value would not produce a found result.

A friend of mine who runs an online forum sent me an email about a problem he was having. He was trying to modify an existing registration web page using jQuery to auto-populate the state names, and then pass the state abbreviation back to his MySQL database. Believe it or not, he was actually having problems with people knowing their own state abbreviation. He had searched and found an example of what he wanted to do, but he couldn’t get it to work. So, I took the …

I remember my first computer. It was a TI-99/4A. I bought it back in 1982 (I think), and it cost around $300. The entire computer fit inside what looked like a really thick keyboard. It had a slot on the right for cartridges, and I had a cassette tape drive that I used for backing up the BASIC computer programs that I wrote. I thought that it was a great computer at the time, but I really didn’t have anything to compare it to. The games were the best part of the computer (really the only fun part – my BASIC skills were lacking). And even though the games were fairly lame by even 1982 standards, but they were still plenty of fun to play.

I also remember when I saw a Macintosh for the first time. I had followed Apple for some time, but I had never had the opportunity to actually see a Macintosh. I think it was in 1987 or 1988, when I was a student at the University of Georgia. I believe it was a Mac SE, and it had one megabyte of RAM and a …

I received a phone call from a friend of mine who had some questions about storing image files as blobs in MySQL. He wanted to design a web site that would allow someone to upload an image, and then that image would be stored in the MySQL database for viewing later. He stated that he was going to be storing thousands of images. I mentioned that it might be better to store the images on the file system, and then to just store the location of the image in the database. But, he still wanted to know how to do it and would decide which solution he would incorporate later.

I already had a Perl script that allowed me to upload files to a web site, as I would give out this URL to people that wanted to send me large files. And, I know that you can store images (and other files) in MySQL as a blob, but I wasn’t sure how to display the blob as an image in HTML (once I had retrieved it from the MySQL database). A search on Google provide bits and …

I was trolling the MySQL forums web site and noticed a post regarding someone who was trying to load a 50-gigabyte MySQL dump file. The author of the post had stated that the loading of the file had been running for days, and was wondering if there was a better way to import the file. They did not have access to anything else (i.e. – original database) but this file.

I have had to restore several databases in the past from a single large MySQL dump file – which led me to start backing up each database individually. These databases are for my own personal use and are not updated that often, so I don’t need to have point-in-time recovery – and so a MySQL dump works just fine. If I had a production system, I would invest in the MySQL Enterprise Backup and the MySQL Enterprise Monitor …

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

If you are unfamiliar with the MySQL Enterprise Monitor – from the MySQL web site: The MySQL Enterprise Monitor continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly.

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

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

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

Please keep in mind that this was a quick hack. …