Lenz Grimmer's blog

Some days ago I had the pleasure to interview Andrey Hristov, who is a developer at MySQL AB. He joined the team after working on a new feature for the MySQL Server as part of his master's thesis. You can read the full interview on the MySQL Developer Zone. Enjoy!

I am happy to announce version 0.4 of mylvmbackup, a tool to perform consistent backups of a MySQL server's tables using Linux LVM snapshots.

For this release I'd like to especially thank Robin H. Johnson from the Gentoo project, who contributed another batch of useful changes and informed me that mylvmbackup is now in productive use to perform backups of the MySQL databases that power the project's Bugzilla bug tracking system. I am always glad to read about such use cases - how do you utilize mylvmbackup in your environment?

• The option handling has been improved. mylvmbackup now starts by using the builtin defaults, followed by the default configuration file (/etc/mylvmbackup.conf, followed by an alternative configuration file (specified via CLI arguments), followed by the remainer of the CLI arguments.
• Changed the capture of the position file and tarball creation to not use absolute path names, and instead use them relative to $mountdir. This means that the content of the backup tar archives has changed with regards to the directory structure, please keep this in mind when restoring from backups!
• Now utilizes a bindmount to get the position directory near the $mountdir for the tarball creation.
• Directory names are sanitized for excessive whitespace and trailing slashes
• Code cleanup: use my $variable instead of use vars

In addition to Robin's improvements, the following changes were made by me:

• Fixed DSN string handling (thanks to Peter Zaitsev for spotting this)
• Fixed logging to the console (log levels were not printed)
• Removed a few currently unused configuration options from the config file

After I published my article on how to build MySQL with the new Falcon storage engine from source, the free BitKeeper client was updated to version 2.0. I have now updated the instructions accordingly, our manual has been updated to reflect these changes, too.

However, it is currently still not possible to clone the Falcon tree with this client, some kind of obscure locking error message appears, even though the admin console on bkbits.net does not reveal any active locks in the repository:

$ bkf clone bk://mysql.bkbits.net/mysql-5.2-falcon mysql-5.2-falcon
ERROR-unable to lock repo for export, try later.

We're aware of this problem and have already contacted BitKeeper support about this. I hope it can be resolved quickly...

Now that the source tree for the new Falcon Storage Engine is finally public, here's a quick HOWTO on how to compile the server from source. This procedure is described in more detail in the MySQL Manual. I assume you use Linux and have the required development toolchain installed.

You first should get the free BK client from http://www.bitmover.com/bk-client2.0.shar, unpack and install it:

$ wget http://www.bitmover.com/bk-client2.0.shar
--17:34:34--  http://www.bitmover.com/bk-client2.0.shar
           => `bk-client2.0.shar'
Resolving www.bitmover.com... 192.132.92.2
Connecting to www.bitmover.com|192.132.92.2|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 17,676 (17K) [application/x-shar]

100%[========================================================>] 17,676        33.56K/s

17:34:35 (33.50 KB/s) - `bk-client2.0.shar' saved [17676/17676]

$ sh ./bk-client2.0.shar
x - creating lock directory
x - creating directory bk-client2.0
x - extracting bk-client2.0/Makefile (text)
x - extracting bk-client2.0/bkf.c (text)
x - extracting bk-client2.0/demo.sh (text)

$ cd bk-client2.0
$ make
cc -O2 -Wall -Wno-parentheses    bkf.c   -o bkf
$ sudo install -m755 bkf /usr/local/bin

Now that the BK client is installed, we can create a local clone of the Falcon development tree:

$ bkf clone bk://mysql.bkbits.net/mysql-5.2-falcon mysql-5.2-falcon

This will now perform a check out the most recent source tree from the mysql-5.2-falcon repository (~83 MB of source code). You can now enter the newly created directory and start the build:

$ cd mysql-5.2-falcon
$ ./BUILD/compile-pentium-debug-falcon

If you have a x86_64 Linux system, use compile-amd64-debug-falcon instead. Depending on the performance of your system, this step will take a while, as it performs a full compile of the MySQL server and all related tools. Once the build completed with no errors, you can create a binary tarball distribution:

./scripts/make_binary_distribution

This should leave you with a tarball mysql-5.2.0-falcon-alpha-linux-i686.tar.gz that you can now install to, say, /usr/local:

$ tar zxvf mysql-5.2.0-falcon-alpha-linux-i686.tar.gz -C /usr/local
$ cd /usr/local
$ ln -s mysql-5.2.0-falcon-alpha-linux-i686 mysql
$ cd mysql
$ ./configure

The last command will install the privilege tables and start up the mysql server. You can now fire up the mysql client:

$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.2.0-falcon-alpha-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Now make sure that falcon has indeed been included:

mysql> SHOW VARIABLES LIKE 'have_falcon';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_falcon   | YES   |
+---------------+-------+
1 row in set (0.01 sec)

Looks good! Now it's time to actually create a table that uses the Falcon engine:

mysql> use test;
Database changed
mysql> CREATE TABLE names (id INT, fname VARCHAR (20), lname VARCHAR (20)) ENGINE=Falcon;
Query OK, 0 rows affected (2.84 sec)

mysql> describe names;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| fname | varchar(20) | YES  |     | NULL    |       |
| lname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> INSERT INTO names VALUES (0, 'Lenz', 'Grimmer');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO names VALUES (1, 'Monty', 'Widenius');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM names WHERE lname='Widenius';
+------+-------+----------+
| id   | fname | lname    |
+------+-------+----------+
|    1 | Monty | Widenius |
+------+-------+----------+
1 row in set (0.00 sec)

Congratulations, you are ready to further test and explore this new storage engine! Have fun with testing it - we would appreciate your feedback and experiences! Please share your impressions via the Falcon Forum or report bugs via our Bug Database. Make sure to read the bug reporting guidelines on the MySQL Forge Wiki first!

UPDATE: The people at Bitkeeper updated the free BK client to version 2.0 after I wrote this. I updated the described procedure above accordingly. Currently, the repository seems to have a locking problem that only appears when using the free BK client. We are working with the BitKeeper team on resolving this issue.

Some time ago, our GUI tools development team started a contest to shake out the bugs in the MySQL GUI applications, especially the MySQL Administrator and the MySQL Migration Toolkit.

The contest ran for about 3 months and resulted in more than 170 bug reports, of which the team fixed all the significant ones (over 120) by now. This is quite impressive, especially considering the relatively small size of the GUI development team. I am very happy to see that the quality of these tools has been improved, they are quite useful, especially for users not keen on using the command line.

The new MySQL Administrator 1.2.8 and MySQL Migration Toolkit 1.1.8 have now been declared production quality (GA) are included in the MySQL GUI Tools Bundle 5.0 Release 8, which is now available from our download pages.

Today we announced the top 5 bug-reporters, which we will reward with an Apple iPod for their good bug reports:

• Heinz Schweitzer
• Andreas Påhlsson
• "Yahoo Serious"
• Cezary Okupski
• Mick Francis

Congratulations to you and thank you very much for the excellent support! You and all other bug reporters did a great job in helping us to improve the quality of these tools.

With great pleasure I today noticed that we finally have daily snapshot builds of all current versions of the MySQL server source trees (4.0-5.1) available on http://snaps.mysql.com/ again. Unfortunately this has not been the case for quite some time - a few issues that caused the 5.0 and 5.1 snapshot builds/tests to fail have now finally been resolved. I also made some changes to the build script that performs the snapshot builds, e.g. to use the Perl-based test suite where available instead of the Shell-based one, which is going to phased out in 5.1 anyway. Let's hope that from now on the snapshot builds will now be available more frequently again, I'll keep a close eye on that.

Sorry for the long delay, I know that some of you depend on receiving updated source tarball snapshots on a regular basis instead of having to use the free BK client...

Recently I stumbled over a posting on the German MySQL Forum from a user that accidentally removed all table files from a MySQL Server's data directory with a misbehaving shell script. He was surprised to find out that the server happily continued to serve requests and his web site was still fully operational, even though /var/lib/mysql/<database> was completely emtpy! The reason for this in a nutshell: the rm command only removed the reference to the table files from the database directory, the files itself were not removed from the file system yet as the mysqld process still had the files opened. So as long as a process keeps a file open, the kernel will not release the disk space occupied by the file and it will remain intact, albeit no longer visible.

Of course, the user was now desperate to recover the deleted tables files and was asking for help. Fortunately the recovery in this case is pretty simple. You should first shut down your application to avoid further activitiy on the affected database. Important: you must not shut down the MySQL Server, as this would close the last open reference to the table files! Now you can simply use mysqldump --opt <database> > database.sql to perform an SQL dump of the deleted tables. As the MySQL server still can access the open table files, the dump will contain the entire content and can then be used to restore the database again. Now you should restart the MySQL server so it closes the still open file descriptors of the deleted tables files. Alternatively, you could use DROP TABLE <table> or DROP DATABASE <database> to properly remove the references, in case you don't want to shut down the entire server. Now you can restore your missing tables from the SQL dump as usual and can restart your application!

Note that this trick only works on table files that were removed on the file system level, not after you used DROP TABLE/DATABASE, so it's not a magic undo function for these commands - only restoring from a recent backup (e.g. performed with mylvmbackup, hint, hint) will help in this case. In addition to that the MySQL server must have had opened the tables before. A freshly started MySQL server has not opened any table files apart from the ones in the mysql system database.

By the way, there is a related article "Bring back deleted files with lsof" on Linux.com that covers the subject of recovering deleted (but still open) files on a more general level and also provides some more background information about the Linux internals. Worth a read!

I am happy to announce version 0.3 of mylvmbackup, a tool that performs consistent backups of a MySQL server's tables using Linux LVM snapshots.

Special thanks go to Fred Blaise, who contributed the majority of the new features that have been added to this new release:

• It is now possible to use an external configuration file /etc/mylvmbackup.conf to store the options. This is probably more convenient than having to pass a slew of options on the command line or having to hack the script itself to change the default values. This new feature requires the Config::IniFiles Perl module to be installed, a sample configuration file is included in the package.
• The logging to the console has been visually enhanced by including a time stamp and the message category (e.g. Info, Warning or Error). In addition to that, it is now possible to log messages to a local or remote syslog server. This feature requires the Sys::Syslog Perl module.
• The man page has now been converted into an asciidoc file, which makes it easier to generate other document formats as well, e.g. a HTML version.
• Several small bugs have been fixed, too: see the ChangeLog for details.

If you are looking for a convenient backup tool to create fast and consistent MySQL backups, please give mylvmbackup a try! You feedback is appreciated. A tarball and RPM are now available for download from the project's home page. Thanks!

I am happy to announce that version 0.2 of the mylvmbackup tool is now available!

mylvmbackup is a Perl script for quickly performing backups of a MySQL server's databases using the Linux Logical Volume Manager (LVM). It creates a consistent LVM snapshot of the server's data directory which is then backed up without further blocking the server's operation.

After version 0.1 was published in May this year, I did not really get much feedback about it. I had some ideas for improvements (see the TODO file included in the package), but never got around to actually start working on them.

Thanks to Robin H. Johnson from the Gentoo project for contributing a number of new options and features as well as some code cleanups. His changes motivated me to make a few more modifications and improvements by myself, which have now been rolled into a new release.

The new options provide some more flexibility in the way the script handles the logical volumes and how the backup files are being created. I also overhauled the building and packaging and added a Makefile to automate these procedures. For details, please refer to the ChangeLog and check the manual page and the README for additional info.

A tarball and RPM of version 0.2 can now be downloaded from the project page.

The SVN repository can now be browsed using WebSVN as well.

Please give it a try! Your feedback is very welcome.

Yesterday we had our fourth MySQL User Group Meeting here in Hamburg. We had 19 attendees and a very informative talk about Ruby on Rails/Active record, held by Stefan Saasen. Thanks a lot, Stefan! It was quite insightful and we had good discussions and excellent food afterwards. I look forward to our next meeting, which I have already scheduled for February, 5th! So save the date and RSVP!

Some pictures of our meeting are in my Gallery, a PDF of Stefan's talk can be obtained from here. Enjoy and see you next time!

Just a quick reminder: the Hamburg MySQL User Group meets tonight at 7pm at the usual place. See the invitations on Meetup.com or OpenBC/Xing for details - around 25 people have signed up so far, so we should have a great time. Stefan Saasen will give a talk about Ruby on Rails/Active Record. See you there!

If you happen to live somewhere around Delhi, India and you are curious to learn more about MySQL Cluster, make sure to RSVP for Kai's Workshop on this subject, which will take place on Friday, November 17, 2006, 6:00 PM at Value One, D 21 NDSE 1, Delhi. Space is limited, so hurry!

I personally will also mention MySQL Cluster during my talk about High Availability Solutions with MySQL that I will give at the Fachhochschule Oldenburg/Ostfriesland/Wilhelmshaven tomorrow.

It is really noticable that the summer is over now. While it was quite silent during the past few months, the last quarter of the year will be packed with conferences and events again.

And we will contribute our share to it, too: in addition to sending speakers to various upcoming events, MySQL will also host two European MySQL User conferences next week. The MySQL Northern Europe Customer Conference will take place in London (UK), on Tuesday, October 17th 2006. Two days later, the MySQL Central Europe Customer Conference will be held in Munich (Germany) on Thursday, October 19th 2006. Both are one-day events, with two tracks of talks in parallel. The sessions will be held by both MySQL employees as well as customers, talking about their experiences in using MySQL in their environments.

I attended a similar event in Warsaw, Poland two weeks ago, where I spoke about MySQL, Open Source and Community in front of an audience of about 350 people. The organizers actually had to close the registration four weeks before the event, as they could not arranger a larger room on short notice. A few pictures of the confererence can be found in my Gallery. I was very impressed by the audience and the interest about OSS and MySQL in particular. We received a lot of good comments and had nice discussions with the attendees.

On Thursday this week (the 12th), I will be speaking at the Linux Business Day in Luebeck, Germany. It will be a general session about MySQL (both the company and the product) as well as the benefits of Open Source Software in general and MySQL in particular.

Our web team informed me that we received a few comments from users about MySQL AB supposedly shutting down the development of the MySQLFront utility. I’d just like to clarify that this was not the case at all – we merely asked the developers to stop violating our trade mark as outlined in our Trade Mark Policy. As I wrote some time ago, our trade marks are a very important asset to us as a company and we need to take action, if our marks are being violated.

We did not ask or force them to stop the development or to remove the program from the market completely – this was their very own decision. In fact, we actually encouraged them from the beginning of our discussion to continue the development of the product under a different name. The problem appeared resolved as the project was renamed to SQLFront and the old web site pointed to the new location – we regret that the developers decided to shut down the entire project shortly afterwards.

We encourage and support every application that broadens the MySQL Ecosystem. Why would we want to completely shut off applications that support us?

Just to remind you: tonight at 19:00 we'll meet at our usual place - please see me previous announcement for details. According to the RSVPs I received, we should be around 20 people. Paul McCullagh will talk about the PBXT Storage Engine, so this sounds like a fun evening. See you there!