Lenz Grimmer's blog

I'm happy to announce that my talk "Making MySQL administration a breeze - a look into a MySQL DBA's toolchest" has been accepted for this year's edition of the MySQL Conference & Expo in Santa Clara, which will take place on April 12-15, 2010. The session is currently scheduled for Wednesday 14th, 10:50 in Ballroom E.

My plan is to provide an overview over the most popular utilities and applications that a MySQL DBA should be aware of to make his life easier. The focus will be on Linux/Unix applications available under opensource licenses that ease tasks related to user administration, setting up and administering replication setups, performing backups and security audits.

Of course I will cover the usual suspects (e.g. Maatkit), some of these are actually collections of different utilities by themselves. As it's impossible to go over each individual component in the given time frame, I will try to pick out the most popular/useful parts related to the scopes mentioned above. But I will also cover some lesser known gems that migh be worth taking a look at. What's your the most valued tool in your toolchest? I am still looking for more inspiration.

I look forward to being at the conference again and meeting with colleagues and friends in the MySQL community. Judging from the current schedule, it will be a very interesting mix of talks.

If you're interested in attending, you should consider registering soon! The early registration ends on March 15th. Until then, I encourage you to make use of this "Friend of Speaker" discount code (25% off): mys10fsp

I recently received a question from Robin Schumacher at Calpont, the makers of the InfiniDB analytics database engine for MySQL: "How would you recommend we try and get bundled in with the various Linux distros?"

Since this question has come up several times before, I thought it might make sense to blog about my take on this.

First of all, please note that there is a difference between "being part of the core distribution" and "being available from a distributor's package repository". The latter one is relatively easy, the former can be hard, as you need to convince the distributor that your application is worth devoting engineering resources to maintain and support your application as part of their product. It's also a space issue – distributions need to make sure that the core packages still fit on the installation media (e.g. CD-ROMs or a DVD). Therefore they take a very close look at each package and if it's really needed to be part of the installation medium or if it's fine to provide it for download from a package repository instead.

Distributors prefer to keep their core product small and restricted to the "basic OS building blocks". While MySQL might still be considered to be a part of this, this probably does not apply to the various plugins and extensions that are available for it. Therefore the best approach is to invest some engineering time and start doing the packaging yourself, either by hiring an engineer capable of creating and maintaining the packages, or by finding someone in your community who has the required experiences and is willing to do it.

While it's of course possible to set up and maintain your own build and package hosting infrastructure for that, I recommend to make use of the existing services provided by the distributors.

The top tier distributors all provide means of offloading the maintenance of "non-core" packages to their community, offering various options for packages to be made available. For example, Novell/openSUSE provide the free "Build Service", which is capable of building packages for other distributions as well (e.g. Fedora, Mandriva, Debian/Ubuntu, etc.). In addition to automating the builds, the Build Service also takes care of the distribution via their download mirror network and ensures that your application can be found via their package search interface.

Red Hat/Fedora provide something similar, named "Koji" – but it's "Fedora only". Here's a HOWTO that outlines the process of becoming a Fedora package maintainer.

Ubuntu/Canonical have "Personal Package Archives (PPAs) – if your project is hosted on Launchpad already, that might be something to look into for providing Debian/Ubuntu packages. Alternatively you could join the Debian project and start building and maintaining your package there. They maintain a list of "Work-Needing and Prospective Packages", a description of the process on how to become a new maintainer is outlined here.

If you'd like to target Solaris/OpenSolaris as well, there is the OpenSolaris Source Juicer – a web service which allows OpenSolaris community developers to build packages (using RPM spec files) and publish them for review, so they will be included in an official package repository. The Software Porters Community Group coordinates, advocates, encourages and helps with the porting of Software from multiple Platforms to the OpenSolaris Platform.

CMake is a cross-platform, open-source build system, maintained by Kitware, Inc.

From the CMake.org home page:

CMake is a family of tools designed to build, test and package software. CMake is used to control the software compilation process using simple platform and compiler independent configuration files. CMake generates native makefiles and workspaces that can be used in the compiler environment of your choice.

It has been used for building the MySQL Server on Windows since MySQL 5.0 – the initial CMake build support was added in August 2006.

For building MySQL on all other platforms, the GNU autotools (autoconf, automake and libtool) are currently being used.

CMake is used in some other MySQL projects as well, e.g.

• MySQL Connector/C
• MySQL Connector/C++
• MySQL Proxy

On February 22nd, Vladislav Vaintroub pushed the changes required to implement WorkLog#5161 "CMake-based unified build system" into the "mysql-next-mr" branch (aka the "Celosia" mile stone).

From this version on, CMake can also be used to build MySQL on Linux and other Unix platforms. For the time being, the autoconf/automake files are still available as well, but will be phased out once the CMake build enviroment has reached the desired level of maturity. The change was announced on February 28th on our "internals" developer discussion list.

The purpose of WL#5161 is to simplify the MySQL build system. It is much easier and less error-prone to maintain a unified build system for all platforms than two separate ones.

CMake has been chosen because of several reasons; the worklog description lists a few pro-CMake arguments (slightly rephrased):

• CMake works on Windows. The GNU buildsystem does not really work and likely never will work natively on Windows (Using Cygwin is not really an option).
• Traditionally, new MySQL features that required changes in the build environment (e.g. the plugin system, unit tests, most recently googletest integration) were always implemented on Unix first, leaving Windows behind (sometimes for years). This would not happen with a unified build system.
• MySQL already uses CMake since 2006 on Windows, so we do not need to start from scratch, only port what we have to Unix.
• CMake runs on every OS and compiler we support.
• It is simple to obtain and install on a wide range of platforms. It is available in all major Linux package repositories (e.g. Ubuntu, Fedora, OpenSUSE). It is also in the OpenSolaris repository, known as SUNWCmake. It's in FreeBSD ports and available for Mac OS X. It is also very simple to compile it from source, the single prerequisite is a working C++ compiler and make utility.
• CMake has support for features we need and might need, e.g. system checks or cross-compiling.
• CMake provides integrated support for packaging. It can handle both simple packages (tar.gz or zip archives) and more complex things like DEB and RPM without much extra coding.
• Good integration with the popular IDEs (Visual Studio, Xcode, Eclipse CDT, KDevelop). Developing in an IDE makes the development process more enjoyable, and potentially it lowers the barrier for external contributors. Of course, CMake can generate traditional Unix Makefiles, which appear to be are superior to the ones generated by GNU autotools (for example, they have progress indicators, colored output and working dependencies).
• The scripting language used by CMake is simpler than m4 used by autotools.
• CMake is a single small tool, not a bunch of different tools as in GNU system (autoconf, autoheader, automake, libtool)

I'd like to mention a few additional reasons:

• Out-of-source builds – CMake can separate the build directory from the source directory. This is convenient, as your working source tree is not cluttered with object files and other fragments of the build process.
• Build configuration using a GUI. The cmake-gui package (based on Nokia/TrollTech's Qt library) provides a convenient way of enabling and configuring the various available build options. This is much better than having to memorize all the required defines and configuration flags.
• Integrated support for creating a wide range of package formats.

The CMake Wiki lists a number of other "nice to have" features.

From a developer perspective, I hope that it will make it much easier to finally implement two things that many developers working with MySQL have been waiting for (now that the build code has been cleaned up):

• Compiling the embedded MySQL Server (libmysqld) as a shared library
• Better support for cross-compilation

Building MySQL with CMake is quite simple and straighforward – the process is outlined on the MySQL Forge Wiki. The document is still work in progress and we'd like to encourage you to take a look at it, try to follow the steps and update/improve the Wiki page, if needed! Your feedback on the build process is appreciated. Feel free to join our internals mailing list to discuss your impressions and observations or submit a bug report via the Bug Database. It's likely that the build still has a few rough edges that we'd like to fix quickly (e.g. BUG#51502 – a fix for this one is already commited to the mysql-next-mr-bugfixing source tree and will be merged into the mysql-next-mr trunk soon).

If you're new to CMake, you might want to take a look at the "Getting Started With CMake (An End-User's Perspective) For Cross-Platform Building" screencast or the "Running CMake" article.

Happy hacking!

So you're a small startup company, ready to go live with your product, which you intend to distribute under an Open Source License. Congratulations, you made a wise decision! Your developers have been hacking away frantically, getting the code in good shape for the initial launch. Now it's time to look into what else needs to be built and setup, so you're ready to welcome the first members of your new community and to ensure they are coming back!

Keep the following saying in mind, which especially holds true in the Open Source world: "You never get a second chance to make a first impression!". While the most important thing is of course to have a compelling and useful product, this blog post is an attempt to highlight some other aspects about community building and providing the adequate infrastructure. This insight is based on my own experiences and my observations from talking with many people involved in OSS startups and projects.

This time of the year is usually a very busy one, as there are plenty of events and conferences to attend. Just take a look at our calendar of OSS events on the MySQL Forge to see what I mean! Here's a quick summary of the ones that I will attend and speak at until the end of this year:

On November 14-15, I'll attend the openSQL Camp in Portland (OR), USA. I missed the first one that took place in Charlottesville (VA) in 2008, but had a lot of fun organizing the European Edition earlier this year. The upcoming one will be more like an unconference again - the list of proposed sessions looks very interesting and the attendee list reads like a "who is who" list of the OSS database community.

On December 3-5, I'll be joining Giuseppe at SAPO Codebits in Lisbon, Portugal, which is going to be a very cool event: "3 days. 24 hours a day. 600 attendees. Talks. Workshops. Lots of food and beverages. 24 hour programming/hacking competition. Quizz Show. Rock Band Contest. Lots of gaming consoles. More food. More beverages. More coding. Sleeping areas. More fun. An unforgettable experience". I will be talking about my favorite topic of MySQL High Availability (I'm currently working on revising my slides based on several excellent discussions about MySQL HA that happened on Planet MySQL in the past weeks) and about the benefits (both social and technical) of using a distributed revision control system (DVCS) like bazaar, git or mercurial for your open source project.

Shortly after Codebits, I will attend SLAC 09, the "Secure Linux Administration Conference" in Berlin, Germany (December 10-11), where I will give two MySQL-related talks (in German) - my usual suspects, but in revised and extended form: MySQL High Availability solutions and MySQL Backup & Security best practices.

The summer break seems to be over and the event season is heating up again! There is a number of conferences and events coming up in the next months — here is a quick summary of the events that I plan to attend.

This Friday I will attend an event here in Hamburg: the "Silpion Sommerfest", organized by Silpion (a local IT solutions provider which is a partner of Sun Microsystems as well). I will be there to network and talk about MySQL.

This coming weekend (2009-09-12/2009-09-13), there will be the PHP Unconference here in Hamburg, Germany . It will consist of two days of Barcamp-style sessions about PHP. Sun/MySQL are sponsors of the event and I expect several of my team mates to be there as well. With more than 180 participants, the event is already sold out.

The following week I will be attending the openSUSE Conference in Nuremberg, Germany on 2009-09-17/2009-09-20. I will give the opening keynote on Thursday morning. Titled "Working in a Virtual Community", I will talk about the pros and cons of working in a virtual organization, giving an overview about some of the technical and social aspects that play a role in working with virtual communities.

On December 10th and 11th I will be attending the 4. Secure Linux Administration Conference 2009 (SLAC) in Berlin, Germany. I've been invited to talk about MySQL and will give two sessions about MySQL Backup & Security as well as MySQL High Availability Solutions. The Call for Papers for this event is still open, so if you have a technical, "best practice" talk that might be relevant for system administrators, consider submitting your proposal!

 

I am happy to announce that mylvmbackup version 0.13 has now been released. This release includes a fix for a nasty bug in on of the recently added Perl hooks (precleanup.pm) and some added functionality (better support for remote rsync backups).

From the ChangeLog: 

• Deleted sample precleanup.pm hook as it has potential to cause harm and is too specialized on a particular use case (BUG#394668)
• Added support for rsync via SSH (BUG#392462)
• Fixed InnoDB recovery in case a relative path to the MySQL data directory is defined (BUG#38337), improved the documentation of relpath in the man page.

 

Shortly after I created the initial packages of embedded InnoDB on the OpenSUSE Build Service, Oracle/Innobase released an updated version (1.0.3.5325). In addition to many improvements and bug fixes, they slightly changed the versioning scheme to better indicate what version of the InnodDB plugin their code is based on (see Vasil's posting on the InnoDB Forums for more information).

I've now updated my InnoDB packages on the Build Service to this version as well - please note that the naming scheme of the shared library package has been changed from "embedded_innodb1" to "libinnodb2" — RPM will take care of replacing the old package during update, even though the name has changed.

After a long hiatus, I am happy to announce that mylvmbackup version 0.12 has now been released. This release includes a large number of improvements, minor code cleanups, as well as some new functionality. In particular, I would like to thank Matthew Boehm, Tim Stoop, Baron Schwartz, Ville Skyttä and Ronald Bradford for their contributions.

Some notable highlights from the ChangeLog:

• Removed the absolute path names to external tools (make sure $PATH is correct)
• Added --log-err to the startup options of the recovery instance to avoid cluttering the server's error log
• Added support for hooks written as Perl Modules. (Matthew Boehm)
• Added support for date/time-formatted path names for backupdir and mountdir (Matthew Boehm)
• Backupdir and mountdir are now created automatically (Matthew Boehm)
• Added new hook "logerr" when an error is logged. (Matthew Boehm)
• Added Option --keep-mount... (Tim Stoop)
• Removed the bind mount, now requires LVMv2
• Support reading login/password from ~/.my.cnf (Baron Schwartz)
• Documentation fixes and improvements (Ville Skyttä) (Bug #302144)

XtraBackup is an Open Source online (non-blockable) backup solution for the InnoDB and XtraDB storage engines. It works with both MySQL 5.0 and 5.1 (and possibly 5.4 as well) and is distributed under the GPLv2.

Some weeks ago Vadim announced the availability of xtrabackup-0.7, stating that they consider it stable enough now to label this version a "Release Candidate". I've been maintaining RPM packages of xtrabackup on the fine openSUSE Build Service for quite some time now, RPMs of 0.7 for a number of distributions are now available for download. Please report any bug reports via the bug tracker on Launchpad.

My last post about Basic MySQL Security generated a number of interesting comments, thanks for all your feedback! I'd like to address a few points that were mentioned there:

While the problem seems to be a non-issue on Linux, Keith Murphy stated that the password might still be visible on other Unix operating systems (e.g. Solaris), as described in Bug#11952 in our bug database. According to the bug report, it depends on the implementation of "ps" — there seems to be a BSD variant (/usr/ucb/ps) as well as a SysV implementation (/usr/bin/ps).

However, on my tests on OpenSolaris (2008.11), both still displayed the password! So be aware of this when working on non-Linux systems and better double check the behaviour on your particular platform. The bug report provides a few more details about this issue, apparently it cannot be fixed for all platforms.

I also pointed out that the password will end up in your shell history and Jay Pipes emphasized this in his comment. As I wrote, you need to make sure that your shell history file is properly protected against access by other users! Usually, a chmod 600 ~/.bash_history will fix this. Most shells create these files with appropriate permissions automatically or can be configured to do so (check your shell's manual page with man `basename $SHELL`).

But there are more potential password leaks that I would like to mention, while we're on the topic: the mysql command line client maintains a history file of its own, that you should be aware of. The history is convenient for easily going back in your list of previous SQL statements by using the Up/Down cursor keys or searching for a particular query by using the CTRL+R shortcut. However, the MySQL client stores all your SQL statements in a file ~/.mysql_history in your home directory by default, similar to how your unix shell does it. So if you are adding new MySQL user accounts using the GRANT ... IDENTIFIED BY PASSWORD... statement, the user's password will be written to the history file in plain text, visible to everyone who has the appropriate file system privileges. Keep that in mind when performing administrative tasks on a MySQL server and make sure to restrict access to that file! By default, the client creates the file with only read and write permissions for the user (600), but if you want to be on the safe side you can of course remove it after you entered passwords on the MySQL command line. As an alternative, you can start the MySQL command line client by using the "-q / --quick" option, which skips using the history file for this particular session. If you can live without a command line history in general, you could simply replace that history file with a symbolic link to /dev/null:

$ ln -fs /dev/null ~/.mysql_history

Alternatively you can set the environment variable $MYSQL_HISTFILE to point to either a different file name or to /dev/null directly. By the way, all of this is documented in the mysql(1) man page as well as in the Reference Manual.

Another attack vector for local users to obtain MySQL passwords are the MySQL server log files — anyone with file system access to the binary log files can extract possible GRANT statements from there using the mysqlbinlog command! So you need to make sure that these files are properly secured from being accessed by regular users as well.

In general, the best approach is to not allow regular users to log into your MySQL Server system in the first place. Shell access should be restricted to the system's admin accounts, access to the MySQL server should strictly take place via the MySQL Client/Server protocol. Which, by the way, is not using encryption by default — make sure to use SSL or an SSH tunnel when accessing a MySQL server through an untrusted network. Otherwise you may also reveal confidential information like user passwords to unauthorized entities...

Reading through the comments in Ronald's second post about More Basic MySQL Security, I noticed that there seems to be a misunderstanding about the implications of providing passwords to the mysql command line client via the "-p" option:

Jaka Jančar wrote:

What’s more insecure is passing password as an argument to MySQL, like you’ve written (-p[password]), since that can really be seen by anyone.

Shlomi Noach wrote:

While Linux security is often considered good, an astonishing weakness is “ps aux”, where every user can see every process running. Therefore, even user “games” can see that user root is running “mysql -pmypassword”. I find this a much higher risk than putting the MySQL’s root password in file, where a user need to gain access to machine’s “root”

Well, this isn't actually the case! Try it for yourself and start the MySQL command line client by providing a users's password via the "-p" option:

$ mysql -u root -p<somepassword>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34 SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:(none) >

Now, open a second shell and check the process list:

$ ps aux | grep "mysql -u"
lenz     19357  0.0  0.0   7868  2884 pts/4    S+   12:30   0:00 mysql -u root -px xxxxx

As you can see, the password has been obfuscated by replacing the password with "x" characters. This action is performed by the mysql client after parsing the -p option — let's take a look at the sources:

  case 'p':
    if (argument == disabled_my_option)
      argument= (char*) "";     // Don't require password
    if (argument)
    {
      char *start= argument;
      my_free(opt_password, MYF(MY_ALLOW_ZERO_PTR));
      opt_password= my_strdup(argument, MYF(MY_FAE));
      while (*argument) *argument++= 'x';   // Destroy argument
      if (*start)
  start[1]=0 ;
      tty_password= 0;
    }

In theory, there is a very short window in which the password can be seen in plaintext (after the mysql process has started up until it has performed the obfuscation), but capturing this information takes really good timing.

But it's of course true that this information also gets stored in the user's shell history file, e.g. ~/.bash_history, where it potentially could be seen by other users, if the file permissions are not set up correctly. So always make sure that you entire home directory (or at least the history file) are protected against being read by other users (using chmod/chown appropriately)!

On early Tuesday morning, I made a quick trip to Paris, France, to attend and speak at the Solutions Linux / Open Source 2009 Conference. I've never been to this conference before and was quite surprised about its size - it's actually the largest Open Source event in France and it reminded me a lot of LinuxTag in Germany. Many well-known vendors (e.g. Sun, Novell, Canonical, Bull, etc.) were exhibiting. The also was a large "DotOrg" section for various Open Source projects and I was very happy to see that LeMUG.fr, the official MySQL User Group of France, had a table there, too! A big Thank You goes to Pascal Borghino, who manned that table on his own most of the time and answered questions about MySQL. I walked around the exhibition floor and took some pictures, which I have now posted to my Flickr account.

In addition to the exhibition, there were several parallel tracks with sessions. I was invited to speak about MySQL HA Solutions in the "Aquarium". Unfortunately I had the last slot at that day and they were running a bit behind schedule, so I had only 15-20 people in the audience. But I still had a great time and I received several positive comments about my presentation. I travelled back home early the next day - I wish I had scheduled some more time to attend the conference. I look forward to going there again next year, it was a nice event.

My calendar for the upcoming months is already filling up with conferences, trade fairs and other events at which I'll speak about MySQL. Here's a quick overview:

• This coming Thursday at 15:00 CET, I'll be speaking about "Backing up MySQL using file system snapshots" at the MySQL University. The session will be hosted live using DimDim, which is a great online conferencing and presentation system (Flash required). Attendance is free, so come and join me if you want to learn more about this backup technique!
• On Friday, 6th of March at 15:15 I'll give a presentation about "MySQL Backup and Security" in the CeBit Open Source Forum in Hannover, Germany. By the way, Sun will have a large presence themed "Open Source for Open Minds" in Hall 6, Booth E36. There'll be a MySQL info pod as well, which I will help manning from Friday until Sunday, 8th. So make sure to stop by and say hello!
• On March 23-24, there'll be a PHP/MySQL Conference in Warsaw, Poland, organized by IDG. I submitted three talks about various MySQL topics, currently I am still waiting for the confirmation which of these they selected.
• On April 20-23, there is of course the MySQL Conference & Expo in Santa Clara, to which I am very much looking forward to. I'm helping to organize the DotOrg Pavilion in the exhibition hall and will likely give a BoF about a project I'm currently involved in.
• In May 4-5 there is the AMOOCON in Rostock, Germany, which is a conference about Open Source VoIP and communication. I'll talk about "MySQL High Availability Solutions" and "Backup & Security Best Practices" (in German). My colleague Geert will be there as well, giving an in-depth talk about MySQL Cluster.

There is a number of additional events in the pipeline, I'll blog about these once my session proposals have been accepted.

Sorry for the downtime of this site - until around a week ago I hosted my home page on a trusty Genesi Pegasos II system (powered by a PowerPC G4 Processor clocked at 1GHz, using Debian 4.0 PPC with 512 MB of RAM), serving these pages from my home DSL connection. Unfortunately this system provided no means of redundancy - the hard disk drive died.

Luckily I perform frequent backups, so I moved most parts of the site to a shared hosting space now - the picture gallery is unfortunately too big to fit into the space that I have there. I'll try to move the pictures into my Flickr account instead, but this will take some time.

Note that the primary domain name of this site is now lenzg.net - lenzg.org, (the domain that I tried to promote as the official domain for my site) used to redirect to the home machine at lenz.homelinux.org. Both now redirect to the new address instead. I've initiated the move of the lenzg.org domain to the other provider as well, so soon this site will be available from both the .org and .net domain. Please don't link to lenz.homelinux.org anymore, as that site will eventually go out of service. Until then, a small openSUSE Linux box (Intel PIII, 500 MHz, 192 MB of RAM) running lighttpd will perform the URL redirection.