It's that time of the year again — the nice folks at FOSDEM have granted us a developer room at their upcoming conference (February 5+6 2011 in Brussels, Belgium)!

As usual there were more applications than they were able to accommodate, so we are very grateful for this opportunity for collaboration. Titled "MySQL and Friends", our room next year will be H.2213 with a capacity of 100 seats. It will be at our disposal on Saturday 5th, from 13:00 till 19:00. Like last year, we would like to set up a schedule of talks related to the MySQL server and the various projects that surround it. Each talk will last 20 minutes, plus 5 minutes of Q&A and a 5 minute break for switching speakers, giving us 12 slots in total to fill with …

When we published our 2008 report on the impact of open source on the database market the overall conclusion was that adoption had been widespread but shallow.

Since then we’ve seen increased adoption of open source software, as well as the acquisition of MySQL by Oracle. Perhaps the most significant shift in the market since early 2008 has been the explosion in the number of open source database and data management projects, including the various NoSQL data stores, and of course Hadoop and its associated projects.

On Tuesday, November 9, 2010 at 11:00 am EST I’ll be joining Robin Schumacher, Director of Product Strategy from EnterpriseDB to present a …

If you have used MySQL for some time you know that mysqld can write binlogs. This is usually used for backup purposes and JITR or for replication purposes so a slave can collect the changes made on the master and apply them locally.

Most of the time apart from configuring how long you keep these binlogs they are pretty much ignored.

Recently I came across an issue.  I have a slave server which is NOT configured read only and which has an additional database used to collect statistics from the replicated database and provided aggregation and business information. The typical sales per country, per product, per day, week, month, year, per whatever etc.  This is the usual datawarehouse type functionality.  It’s done on a slave and not the master so that the load is not passed on down stream to all boxes. …

Next time you have an error whilst running an sql script into mysql using the pretty easy redirect into eg: mysql -uroot -pmsandbox -h127.0.0.1 -P3306 dbname < filename you might want to give the -v option a shot.

MySQL normally outputs an error with the line number like:

[mysql@dcassar-ubuntu /mysql/dumps/stored_procs 11:10:12]$ mysql -uroot -pmsandbox -h127.0.0.1 -P5151 test < file
ERROR 1267 (HY000) at line 375: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '='


But 375 is not including comments and stuff so it’s a bit hard to go through the file to locate the exact section which is failing.

Run the same command with -v:

[mysql@dcassar-ubuntu /mysql/dumps/stored_procs 11:10:30]$ mysql -uroot -pmsandbox -h127.0.0.1 -P5151 -v test < file | tail -5
ERROR 1267 (HY000) at line 375: Illegal mix …

This is only a piece of trivia, however has anybody noticed that the MySQL logo has changed. Using the Legal Trademark page we can see the old logo and the new logo together.

Oracle has released news about changing policies of MySQL Enterprise Support effectively dropping annual support for Basic and Silver. The entry level support is now $3000 per server per year. The MySQL support team now part of Oracle has great resources however Oracle is in the business of making money. When a general company question for OOW is company income, and the first option is < $50 million it highlights that startups, and smaller companies are clearly not a focus.

The success of MySQL as an open source company has lead to other leading providers that now can provide enterprise level support. More importantly many organizations over per-incident support which is more cost effect. News in the past week has included Percona - …

This is one of the MySQL presentations I’m doing on the OTN LAD Tour in South America, starting today in Lima, Peru.

MySQL Best Practices for DBAs and Developers

Learn the right techniques to maximize your investment in MySQL by knowing the best practices for DBAs and Developers. Understand what subtle differences between MySQL and other RDBMS products are essential to understand in order to maximize the benefits and strengths of MySQL. We will be covering areas including the minimum MySQL configuration, ideal SQL, MySQL security and schema optimizations.

• MySQL Configuration default settings including SQL_MODE
• Documenting, formatting and future proofing your SQL
• Developing and reviewing all SQL paths
• MySQL physical and user security
• The best schema optimizations
• Essential Monitoring and …

In the last post, we saw how to create a test OpenLDAP server, populate it and secure it with SSL certificates. Now we are going to have a look at how to configure MySQL Enterprise Manager (MEM) to authenticate against LDAP. We will be examining a few different kinds of setup methods.

1. Using LDAP to fetch just the user password

The simplest form is to configure a user with MEM and set it to the LDAP type. The user’s role is setup in MEM during user creation time and is not fetched from LDAP. Below you can see the user definition page:

How to create a LDAP user in MEM - password only

The username is user1 as specified in MEM, but where do we get the password …

The latest 2.2 release of MySQL Enterprise Monitor (MEM) has the ability to authenticate against LDAP. I decided to test this setup and for that, I had to create and populate an OpenLDAP server, including STARTTLS/SSL certificates. This guide was done on CentOS 5.5 but it shouldn’t be much different in other Linux/Unix distributions. First, start off by installing the packages with:

root@shell> yum install openldap openldap-clients openldap-servers

Then head to /etc/openldap where you can set you domain and the DN for the LDAP manager user. I’ve inserted some useful comments into the slapd.conf file. Lines without comments have not been changed from the default slapd.conf file.

shell> grep -v "^#" /etc/openldap/slapd.conf | grep -v "^$"
include         /etc/openldap/schema/core.schema
include         /etc/openldap/schema/cosine.schema
include         /etc/openldap/schema/inetorgperson.schema
include …

With the release of MySQL Enterprise Monitor (MEM) 2.2, there is now the ability to monitor queries using the Query Analyzer (QUAN) without needing the agent proxy to be running. You can use a .NET or JDBC connector plugin to directly gather the query statistics. In the example below, we will use the MySQL Enterprise Plugin for Connector/J.

First, make sure both the Connector/J, the Connector/J plugin and the Apache Commons Logging jars are in the $CLASSPATH. At the time of writing, these are the files needed:

mysql-connector-java-5.1.12-bin.jar
c-java-mysql-enterprise-plugin-1.0.0.42.jar
required/commons-logging-1.1.1.jar

Then, add the plugin to the connection string so that it changes from something like this:

conn = …