Wednesday, April 30, 2008

Backup MySQL Databases

I run a Gallery2 website, locally hosted on my home PC. It serves our family's photo album, keeping our remote relatives happy.

The Gallery2 FAQ suggests three methods for backing up its MySQL database, all of which are manual due to the simple fact that you have to type in a password in order to perform database maintenance.

This is a case of regurgitated documentation - that particular Gallery2 FAQ entry is based on backup instructions taken from the Wordpress Codex, while the official methods are well documented in MySQL Backup and Recovery and mysqldump — A Database Backup Program.

To be fair, the official backup instructions do not provide a solution to the password entry problem. But after some more reading, I realized that all the MySQL client programs have a common command line options facility, that allow a password to be specified in an options file. This seemed useful, but not more secure than specifying the password on the mysqldump command line inside a backup script.

I figured out the missing piece in the puzzle after browsing the MySQL databases using phpMyAdmin. Looking at the users table in the mysql database I found a user called debian-sys-maint - a quick internet search revealed that this is the user name that's used by Debian scripts to access the database. The password for this user is defined in a MySQL options file called /etc/mysql/debian.cnf.

The following bash function can be used in a script (to be run by root) on a Debian machine, to dump MySQL databases to a given directory, that can later be backed up:

backup_mysql ()
{
echo "Backing up MySQL databases..."
rm -rf "$1"
mkdir -p "$1"
mysql --defaults-file=/etc/mysql/debian.cnf --batch --skip-column-names -e "show databases" |
while read DB ; do
echo Dumping "${DB}" ...
mysqldump --defaults-file=/etc/mysql/debian.cnf --add-drop-table "${DB}" > "$1/${DB}.sql"
done
}

You may want to avoid the while loop and simply dump all databases to a single .sql file using
mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --add-drop-table > backup.sql
but I wanted to be able to cherry pick which database to restore, if and when the need arises.

I also did not bother to compress the .sql files, because Bacula does that for me.

Thursday, April 24, 2008

gnome-about-me

I just got a new workstation at work. It's a rather powerful box running a customized openSUSE 10.3. This is quite an upgrade. One of the most visible differences between this system and my old one is the fact that I can finally switch from KDE (3.5.7) to GNOME (2.20). It's not that I'm such a GNOME fan - but it's what I'm used to on Debian, and I'd rather use it over KDE until I switch to an alternative window manager.

It took a few hours for me to realize that I got absorbed in personalizing my new box. No matter how hard I try to avoid it, the process of setting up a new machine does that to me - I lose track of time and priorities. The amount of details involved is quite amazing.

A problem that came up at an early stage was the fact that I could not launch one of my favorite tools - gnome-about-me. This is the perfect tool for a self-absorbed geek - you get a chance to setup your own persona: photo, email, home address, blog, home page, etc. I was trying to personalize the dialog box that comes up when you try to unlock the workstation after the screen-saver has kicked in: I wanted it to show my photo, as well as my name and username.

The tool is sadly missing from my box. I'm not sure why this happened - it's either the fault of our IT department, or an openSUSE problem. I decided it was probably possible to do without it. I guessed that gnome-about-me saves my personal info somewhere in my account, and tried searching for it on my home PC using grep. I lost my patience after a few minutes, and decided to leave it be - I happen to be more lazy than self-absorbed.

A few minutes later I stumbled upon a file named .face in my home PC home directory.
file ~/.face
told me that it's a GIF image, and
display ~/.face
confirmed that it was made in my own image.

So I just copied over that file from my home PC to my new workstation with scp, and was happy to discover my own face looking at me from the workstation unlock dialog box.

As for the whereabouts of the other details in my personal about box - I haven't the foggiest. And I intend to keep it this way.

Sunday, April 13, 2008

Hack the Ripper

I don't usually rip audio CDs, but when I do, it's usually for my wife. The optical drive on my laptop is busted, so until recently, I used to rip CDs on her Window$ laptop. It's rather easy. Windows Media Player even lets you listen to the music while it's being ripped to a bunch of MP3 files.

But now that I have an external optical drive, I figured I could do the same on my Linux box. GNOME provides Sound Juicer as the default application for playing and ripping audio CDs. Like many other GNOME applications it looks clean and simple, almost too simple. GNOME applications often leave me wondering if they can actually do the job they're meant to perform...

So I gave Sound Juicer a try - and was soon surprised to find out that it didn't offer any way to convert the audio tracks to MP3. I selected Edit->Preferences from the menu, and opened the drop-down list of output formats - nada.

I pressed the adjacent "Edit Profiles..." button and got a dialog box showing all the available profiles. Hey - there's an MP3 profile here! I happily selected it, pressed "Edit", expecting a dialog box with some MP3 related options. Well, I did get a dialog box - it allowed me to modify the name of the profile, its description and a meaningless "GStreamer pipeline" text box. Sound Juicer started to look suspiciously like a command-line junkie's GUI programming exercise...

I hit the "Close" button, but the drop down list didn't show any MP3 profile. This looked like a genuine bug, and I went over to the Debian BTS to verify. Sure 'nuff there's mention of some profile related problems, but these all seem quite old, and not directly related to my problem.

This seemed too obvious a problem to have gone unnoticed, so I decided to search around the Net for any clue, under the assumption that I may be missing something obvious. I hit upon this blog entry, where I learned that I needed to install the (non-Debian) GStreamer lame plugin:
aptitude install gstreamer0.10-lame
This fixed my problem, letting me select MP3 as the output format.

I also learned that what looked like bad GUI programming on the side of the Sound Juicer author, was actually good GNOME programming - he used a standard GNOME dialog box to edit the audio profile, which can be launched separately using gnome-audio-profiles-properties. It's just that this dialog box is rather cryptic, to anyone who's clueless about the inner workings of GStreamer.

The next problem to tackle was how to make Sound Juicer encode the MP3 files at a bit-rate of 192K bps instead of the default 128K bps ? Here's how:
  1. open the audio profiles properties dialog box from within Sound Juicer using "Edit->Preferences->Edit Profiles..." or by running gnome-audio-profiles-properties
  2. select the MP3 audio profile, press "Edit"
  3. add bitrate=192 to the lame stage of the pipeline, like this:
    audio/x-raw-int,rate=44100,channels=2 ! lame name=enc mode=0 vbr-quality=6 bitrate=192 ! id3v2mux
  4. press "Close" and you're done.
You can control other aspects of the encoding process with other options. You can discover which options are available with the following command:

gst-inspect lame

Wednesday, April 9, 2008

MPlayer, Nautilus and File Names with Spaces

The GNOME file manager - Nautilus - lets you specify alternative actions for known file types. This is rather convenient if you don't happen to like the default application that GNOME assigns for the job.

Case in point: multimedia files. I happen to prefer MPlayer, from the Debian multimedia package repository, over Totem - the default multimedia player in GNOME:
  1. open Nautilus, find the file you want to open with MPlayer
  2. right-click on the file
  3. from the pop-up menu select "Open with Other Application..."
  4. wait for the "Installed Aplications" list to be populated and then select "MPlayer"
Note that you may, instead, have on step 3 a menu item "Open With ->", with a sub-menu that may already include MPlayer and also the entry "Open with Other Application...".

This should open the file with gmplayer (MPlayer with a graphical user interface), and add an MPlayer entry to the pop-up menu.

The only problem with this is that MPlayer fails to open the file if its name happens to contain spaces. It issues an error message like this: "Failed to open file file:///home/zungbang/test%20clip.avi." The problem is rather obvious: the spaces are translated to %20, which is usual in URIs, but apparently MPlayer doesn't expect this.

The issue seems to be well known (see Debian bug report #471924 and the following Ubuntu Forums threads: Opening files with MPlayer and mplayer can't play file with space in file name). And the proposed solution is to replace the string gmplayer %U with gmplayer %F in /usr/share/applications/mplayer.desktop and/or ~/.local/share/applications/mplayer.desktop.

Well, I modified the first file and nothing happened, I then tried modifying the latter file, only to find out that there was no such file. Damn. But I did spot files named mplayer-usercustom.desktop and mplayer-usercreated.desktop. After examining their contents and comparing with the first file, I removed them, copied the first file to ~/.local/share/applications and restarted Nautilus. This did the trick. Hooray.

To make MPlayer the default player, so that it's launched directly by double-clicking the file in Nautilus:
  1. right-click a multimedia file, select "Properties"
  2. select the "Open With" tab
  3. mark the open circle next to "MPlayer"
  4. press "Close"
Enjoy.