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!

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.