I’ve always believed that the MySQL Information_schema.tables.table_rows figure for Innodb tables to be while approximate, approximately accurate.

Today I found that the figures varied on one table from 10x to 100x wrong.

Before performing an ALTER I always verify sizes for reference.

+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| table_s | table_name | engine | row_format | table_rows | avg_row_length | total_mb   | data_mb    | index_mb  | today      |
+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| XXXXX   | s_a        | InnoDB | Compact    |     208993 |           7475 |  1491.5312 |  1490.0156 |    1.5156 | 2009-09-09 |

mysql> alter table s_a modify col VARCHAR(255);
Query OK, 23471 rows affected (4 min 26.23 sec)

After

 …

MySQL provides a level of statistics for your INSERT, UPDATE, DELETE, REPLACE Data Manipulation Language (DML) commands using the STATUS output of various Com_ variables, however it is per server stats. I would like per table stats.

You can achieve this with tools such as MySQL Proxy and mk-query-digest, however there is actually a very simple solution that requires no additional tools.
The following 1 line Linux command (reformatted for ease of reading) gave me exactly what I wanted, and it had ZERO impact on the database.

$ mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  \
   sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" |  \
   sort | uniq -c | sort -nr  

  33389 update e_acc
  17680 insert into r_b
  17680 insert into e_rec
  14332 insert into rcv_c
  13543 update e_rec
  10805 …

I wanted to play with the new version of MyQuery that was just released. In order to use it, I needed to use a ssh tunneling tool. I have used various hacks in the past but I wanted something that was at least moderately well done.

A bit of googling uncovered Bitvise’s Tunnelier. It is a free for personal use tool that is designed just for tunneling. It seems to handle it with some aplomb. Ten minutes after I downloaded it, I was online with a remote server using MyQuery.

If you are using a Windows desktop and want to use some of the various graphical client programs that don’t include port forwarding this could very easily fill your needs. I just wish I had found it a long time ago.

What is Nagios? Nagios IMHO is the best Open Source monitoring system out there. It supports hosts checks, a level to determine on a host level if a box is considered "up". It supports service check, a level to determine if a particular service such as mySQL is up. It has features to log all events to a flat file or to a DB. It can notify you when a service is in a warning state, error state or unknown state.

For the purpose of this article, I am going to talk about handling events such as a clearing up swap.

First, let us look at some configuration of Nagios. We are going to define a command, then service acting on that command. Let us assume that the nagios install is in /usr/local/nagios.

Therefore, in /usr/local/nagios/ a few configuration files are key:
- /usr/local/nagios/etc/objects/commands.cfg - the command file where the checks are …

MyQuery 3.1 is now ready for download from Sourceforge! There is a bunch of cool features, but the theme was Convenience and DBA Features. On top of this, UTF-8 support has also been added, not in a terribly advanced way, but it should work for most needs.

Among the new features are:

• Events dialog to manage MySQL events
• Much enhanced Drag-and-drop support. Drag a tables, index or column from the dictionary window to the editor, and the CREATE statement will be pasted for you.
• SQL variables dialog, with helptext and manual links for most variables.
• SQL Profiling support, including the ability to compare the profile of two different queries.

In addition, there has been some bug fixes, memory leak fixes, and as usual (you are sitting down now, right), updated documentation. I still have a …

Just a quick note to let the masses know that I will be hosting a MySQL University session tomorrow, based on the talk that I gave at the MySQL UC in April - Customizing MySQL Enterprise Monitor.

It will be at 14:00 UTC - so if you are at all interested in MEM, and want to know how to bend it towards your needs, then come along! I’ll see you there.

EDIT: OOOPS, it’s 13:00 UTC

I just wanted to tip my hat to the MySQL Enterprise Tools Engineering team for another great release of the Enterprise Monitor. Not to name names, but I want to give a special thanks to a team that always over delivers on a collective commitment to producing quality software. So, a mega thanks to:

Andy Bang, Sloan Childers, Darren Oldag, Eric Herman, Jan Kneschke, Kay Roepke, Mark Matthews, Bill Weber, Diego Medina, Marcos Palacios, Carsten "Pino" Segieth, Josh Sled, Keith Russell, Mark Leith, Heidi Bergh-Hoff, and Gary Whizin (and also welcome Michael Schuster!)

Yet another great job guys!

The new version, 2.1, was posted as GA early on Tuesday and it is quite possibly the best release of the Enterprise Monitor to date.

For those not familiar with the Enterprise Monitor, it is included in a MySQL Enterprise …

If you're leaving the secure world of ASCII characters you're usually enter a dangerous land of confusion. It's actually a very complex topic and from a technical point of view there is no one to blame.

But if you don't care about the basics, don't care why or how, and just want a reliable and working system, simply follow these three advices to attain happiness and inner peace in your developer's life.

Advice 1

Define the character set for every column where you plan to store international character data:

CREATE TABLE t1 ( col1 VARCHAR(42) CHARACTER SET utf8 );

Or at least for every table:

CREATE TABLE t1 ( col1 VARCHAR(42) ) CHARACTER SET utf8;

The later example saves you some typing, but usually you don't need UTF-8 in every string column.

If you omit this parameter, MySQL assumes a default value which may be different from system to system.

Advice 2

…

The MySQL Community Team maintains a calendar to keep track of Open Source events and conferences that might be relevant from a MySQL point of view (either by submitting a talk, sponsoring or attending it). This calendar is now public - you can either look at the HTML version on the MySQL Forge or subscribe to the iCal feed (e.g. using Mozilla Sunbird or Lightning).

Are we missing an event? If you know any other events that should be included in this calendar, please submit your suggestions via this …

Devart announced the release of dbForge Query Builder for MySQL v 1.10, a professional tool for quick creation of any queries without code and extended management of query results.

The highlights of Query Builder for MySQL v 1.10 include:

Unlimited database connectivity

To provide more flexibility, dbForge Query Builder for MySQL v 1.10 offers two more approaches to connect to a database in addition to a direct connection. When you can’t connect to the MySQL Server directly or need secure transmitting of private data, you can leverage secure connections based on SSL and SSH protocols.

The latest version takes care about web developers working with remote databases and offers HTTP tunneling with simple tunneling script uploading to the web site. Employment of remote …