After I wrote about things you need to know about MySQL backups, a customer contacted me and asked me what I know about SecoBackup for MySQL. I see it has a very low cost and Percona has Amazon accounts for testing purposes, so I quickly downloaded s3sql_2.2.0.1-2.01_i386.deb, installed it, configured it, and gave it a whirl.

Since I just want to see what it does to take a backup, I started up a sandbox running from /tmp/12345 and configured it to backup msandbox:msandbox@127.0.0.1 (I shut down my main mysqld on my laptop to make sure it can’t connect to the default instance).

Then I configured a backup set and tried to take a backup. Right away I saw it isn’t full featured enough. It doesn’t let you specify a port to connect to. This …

bencher is a test program that allows you to benchmark requests on MySQL Cluster. I have used this utility a lot of customers, because it lets me:

• specify a simple query that I want to benchmark on the command line
  
• implement more complex use cases.
  
• implement NDBAPI requests

and I don't have to reinvent the wheel every time. It is all there: connectivity, multi-threading support, timers, and some basic statistics, and it compiles on most platforms. I just have to focus on the queries I want to optimize or benchmark.

The simple use case is to specify the SQL query you want to benchmark, the number of threads, and how many times. You can also customize this very easily to benchmark more elaborate SQL requsts, and NDBAPI requests.

…

I don’t want to post the link to this (perhaps, it was left public unintentional?) but here is what I stumbled upon recently. This is a log of production maintenance of IT systems in Perth, Western Australia (as far as I could say):

Good idea but shouldn’t companies keep this sort of information private?

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the information_schema database, but it’s scattered through several different tables.

Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.

In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.

CREATE DATABASE IF NOT EXISTS dba;
USE dba;

DROP PROCEDURE IF EXISTS `dba`.`get_objects`;
DELIMITER $$
CREATE …

One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER TABLE, for example).

The benefit is that you have another server with all the same data, up and running, ready to serve queries. In theory, it's a truly hot standby (stay with me -- that's not really guaranteed). You don't get this with shared storage or DRBD, although those provide stronger guarantees against data loss if mysqld crashes. And you can use the standby (passive) master for serving some SELECT queries, taking backups, etc as …

The main fixes in cmon 0.13 are:

• filtering of clusterlog
• better graphs interface with filtering
  
• fixed bug in load average graphs. Now the loadavg graphs works
• added replication role, socket when adding a mysql server to be monitored from the web interface.
  
• corrected bug with how often mysql_variables are collected
• fixed problems with graphs
• added images directory
• revamped graphs - now producing files in images directory,
• ajax support for Storage graphs
• divided helpers into smaller files

Upgrade from cmon 0.12

• Stop cmon 0.12 (e.g. killall cmon from the command line)
• Execute this sql script …

Developer.Com has selected MySQL Workbench as the best database tool of 2009!

Competing behind Workbench were:

• Altova DatabaseSpy® 2008
• LINQ (Microsoft® .NET Language Integrated Query)
• SQL Server® 2008 Reporting Services
• Oracle SQL Developer

Developer.Com has selected MySQL Workbench as the best database tool of 2009!

Competing behind Workbench were:

• Altova DatabaseSpy® 2008
• LINQ (Microsoft® .NET Language Integrated Query)
• SQL Server® 2008 Reporting Services
• Oracle SQL Developer

Developer.Com has selected MySQL Workbench as the best database tool of 2009!

Competing behind Workbench were:

• Altova DatabaseSpy® 2008
• LINQ (Microsoft® .NET Language Integrated Query)
• SQL Server® 2008 Reporting Services
• Oracle SQL Developer

These days I'm working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven't worked much with stored procedures, I though it's going to be a piece of cake. In the end - it was, but there's a catch.

My initial idea was - I'll just analyze queries in the slow query log generated by our mysql build running with long_query_time=0, get the slowest ones and work on them. It wasn't really all the way I expected..

For a showcase I have created a function "whatstheweatherlike". Let's call it and see what shows up in the slow query log:

PLAIN TEXT SQL:

1. mysql> SELECT whatstheweatherlike(5);
2. +----------------------------------------------------------+
3. | …