Lenz Grimmer's blog

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!

While browsing the fabolous ThinkWiki pages I stumbled over this little gem: the lt_hotswap kernel module finally allows me to eject the CD-ROM drive in my Thinkpad T42's UltraBay without having to shut down Linux first. This module makes sure that the device is properly unregistered from the kernel's device list. Now I can replace it with e.g. a second battery on the fly and don't have to shutdown the OS completely! The installation on my SUSE Linux 10.1 system was quite easy: after downloading the source tarball from the SourceForge.net download page I performed the following commands:

tar zxvf lt_hotswap-0.3.6.tar.gz
cd lt_hotswap-0.3.6
make
sudo make install
sudo rcacpid restart
modprobe lt_hotswap

You need to have the kernel-source package installed, as well as the gcc compiler, of course. Now when I eject the little lever that allows me to pull out the CD-ROM drive from the UltraBay, the following message appears in /var/log/messages:

lt_hotswap: Requesting IDE eject!
lt_hotswap: Attempting to eject

Now the CD-ROM drive can be safely pulled from the UltraBay and e.g. exchanged with an additional battery. Inserting the battery yields the following kernel message:

kernel: ACPI: Battery Slot [BAT1] (battery present)

The battery info also appears in /proc/acpi/battery and is fully operational. Unfortunately battery monitoring tools like KPowersave or gkrellm don't display the second battery, but at least the kernel is happily using it. I can remove the battery again, but this event does not seem to get registered properly - the second battery remains visible in /proc/acpi/battery, but shows zero capacity and the "present" state does not change. But at least replacing the CD-ROM drive with the battery now workes fine, which was my major concern. And it is still possible to re-insert the CD-ROM drive again:

kernel: ide1: BM-DMA at 0x1868-0x186f, BIOS settings: hdc:pio, hdd:pio
kernel: Probing IDE interface ide1...
kernel: hdc: HL-DT-STCD-RW/DVD DRIVE GCC-4242N, ATAPI CD/DVD-ROM drive
kernel: ide1 at 0x170-0x177,0x376 on irq 15
kernel: hdc: ATAPI 24X DVD-ROM CD-R/RW drive, 2048kB Cache, UDMA(33)

If you want to enable the lt_hotswap module at bootup, you need to add its name to the MODULES_LOADED_ON_BOOT variable in the /etc/sysconfig/kernel configuration file.

If your Laptop uses an external docking station to host a CD-ROM drive, there are some good news for you: according to Andreas Jaeger's blog, the upcoming openSUSE 10.2 will actually provide a generic Dockutils framework that will support docking/undocking functionality for different laptops and vendors. It will be interesting to find out if this covers UltraBay devices as well.

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.

If you happen to live close to Hamburg, Germany, and you are keen on learning more about High Availability Solutions on Linux, you don't want to miss Lars Marowsky-Brée's talk about "Cluster and Data Center Automation with Linux HA". It will be held this Thursday at 19:00 at the SAGE@GUUG Hamburg Meeting. If you want to participate, please RSVP via OpenBC. See you there!

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.