Recently I needed to replicate between MySQL and another database technology. You might say, why on earth would you want to do something like that, but believe me there are reasons and definitely not (to go away from MySQL to some other DB technology like Oracle or SQL server). Unsurprisingly there are quite a few different tools to do it from any platform towards MySQL but very few which do it the other way round, just to name a couple: Golden Gate and DSCallards.

I came across the following configuration today on a Production MySQL system (5.0.67) running 30+ blogs using Wordpress MU.

$ cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database

No I did not truncate the output. I could say I’ve seen worse, but that’s a stretch.

So the quiz and a prize for the best response, for the next 48 hours I’ll accept your comments as responses to this post for the top 5 settings you would add, and additionally what information you may need to add these settings. Bonus points for giving a reason why you would add the settings as well.

For example, I’ll give you the most obvious.

key_buffer_size = ????

To determine a key_buffer_size to start with I would look at the size of all Indexes via I_S, and combine with some estimate of growth, say 2x-5x.

For the best answer …

Just a short announcement: I am going to join Ulf for his PHP Barbeque Tour across Germany, which will take place between June 15th until the 21st. We will start in the south of Germany and will work our way up north in one week, stopping by at various cities in Germany to enjoy a barbecue with local PHP User Groups and to talk about PHP (of course), MySQL, Open Source, The Web and anything else. We've set up a Wiki page that outlines the various stations of our journey. At the Moment, we will visit the following cities:

• Monday, 15th: Munich
• Tuesday, 16th: Frankfurt
• Wednesday, 17th: Karlsruhe
• Thursday, 18th: Berlin
• Friday, 19th: …

It's been an incredible journey coming from the Oracle world (which I love) and having the privilege of joining MySQL and watching MySQL be on the verge of going public and then being part of the Sun acquisition and now watching the Oracle acquisition move forward. To say its been one of the richest experiences in my professional career would be an understatement.Which makes me think, what is

Yesterday, I (Zardosht) posted an entry introducing clustering indexes.  Here, I elaborate on three differences between a clustering index and a covering index:

1. 
   
2. Clustering indexes can create indexes that would otherwise bounce up against the limits on the maximum length and maximum number of columns in a MySQL index.
   
3. Clustering indexes simplify syntax making them easier and more intuitive to use.
   
4. Clustering indexes have smaller key sizes leading to better performance.
   

Expanding MySQL’s Limits

MySQL allows at most 16 columns in an index and at most 3072 bytes per index. For tables that have more than 16 columns or a row size of greater than 3072 bytes, one cannot create a covering index that includes …

I wrote a small DTrace script to understand InnoDB IO statistics. This script shows statistics about different kinds of Innodb IO requests and how many of them result in actual IO. Sample output is shown below

#./inniostat -h
Usage: inniostat [-h] [-d] [-p pid] [interval]
                 -h : Print this message
                 -p : MySQL PID
                 -d : Dump dtrace script being used

# ./inniostat
 __physical__  ___Innodb___ ____read____     ______write______
   r/s    w/s    r/s    w/s   data    pre    log dblbuf dflush     Time
    24    121     24     50     24      0     50      0      0 16:00:57
    26    130     26     51     26      0     51      0      0 16:00:58
    18    134     18     54     18      0     54      0      0 16:00:59
    25    129     25     51     25      0     51      0      0 16:01:00
    29    116     46     47     17     29     47      0      0 16:01:01
    10    140     10    132     10      0     52      0     80 …

I’ve had several customers in the last week or so who need a way to verify that their application will work well after an upgrade. I’m seeking input on a new tool to help with MySQL upgrades. Please add comments, either here or on the bug report, or on the mailing list topic. If someone wants to sponsor this work, that would also be welcomed.

It uses MySQL proxy, and MySQL proxy quietly refuses to work with these versions. The client reports 'Lost connection to MySQL server' during the authentication phase with 'error 0'.

I've filed Bug #45167 to track the issue.

As a workaround you can comment out the check for the version in src/network-mysqld.c +920

      if (recv_sock->challenge &&
          recv_sock->challenge->server_version > 50113 && recv_sock->challenge->server_version < 50118) {


        /**
         * Bug #25371
         *
         * COM_CHANGE_USER returns 2 ERR packets instead of one
         *
         * we can auto-correct the issue if needed and remove the second packet
         * Some clients handle this issue and expect a double ERR packet.
         */


        con->state = CON_STATE_ERROR;
      }

Apparently "we can …

"The support model doesn’t scale well." - Matthew Aslett, The 451 Group

How do you make money with open source? I’ve written about open source business models previously, but I thought it might be valuable to quantify the impact of open source on your business model. The following analyzes the differences between a closed source model and an open source + paid support business model, using an apples-to-apples comparison based on a $100 license fee for the closed source product.

Option 1: Closed source
License Fee: $100
Annual Maintenance & Support: $18 (18%)
5-Year Present Value of License + Support: $175 (1)
Conversion Rate: 100%
Userbase: 1X
Relative Revenue: $175

Option 2: Open source
License Fee: $0
Annual Maintenance & Support: $18 (2)
5-Year Present …

If you can do either of these on your MySQL production server, you need to correct immediately.

1. Login directly to your MySQL server as the ‘root’ Linux Operating System user. For example:

$ ssh root@server-name
Password:  ************

2. Connect to MySQL database as the ‘root’ MySQL user without a password.

$ mysql -uroot

Here are the 60 second fixes to address these major security flaws.
To disable direct root access to your server, first ensure you can login as a normal user, then su - or sudo su - appropriately. Then, disable ssh root access with the following configuration change.

$   vi /etc/ssh/sshd_config
    # ensure this is commented out and set to no
    PermitRootLogin no

$   /etc/init.d/sshd restart

This will stop any brute force attack on your server by automated bots and password generators.

Second, the default installation …