Debian->ubuntu mysql upgrade gotcha

If you're upgrading from Debian to Ubuntu, note that ubuntu wraps MySQL in app-armor and Debian doesn't. This means that if you played around with the paths that MySQL uses, mysqld might not be able to start at all.

The first thing to note is that you get no error notifications when "service mysql start" fails -- just an indefinite hang, as this thread notes. If you become root and run /usr/sbin/mysqld, you will get an error like this:

# /usr/sbin/mysqld
100704 19:41:08 [Warning] The syntax '--log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '--slow_query_log'/'--slow_query_log_file' instead.
100704 19:41:08 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't create/write to file '/tmp/ib17ii5f' (Errcode: 13)
100704 19:41:09  InnoDB: Error: unable to create temporary file; errno: 13
100704 19:41:09 [ERROR] Plugin 'InnoDB' init function returned error.
100704 19:41:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
100704 19:41:09 [ERROR] Unknown/unsupported table type: innodb
100704 19:41:09 [ERROR] Aborting

100704 19:41:09 [Note] /usr/sbin/mysqld: Shutdown complete

errno 13 means "permission denied."

Because I was using an Amazon EBS root filesystem, I wanted to eliminate unnecessary EBS overhead, so I'd moved /tmp to /tmp-old, made the directory /mnt/tmp on the local storage, and symlinked /mnt/tmp to /tmp. I thought the perms were correct, and I was able to make files in /tmp as an unprivileged user. I even ran vipw to edit /etc/passwd to give the mysql user a shell (otherwise, you can't su to mysql) and noted that it was possible to write files to tmp. (I changed it back to /bin/false afterwards, of course.) Even when I told mysql to use the old directory ( /usr/sbin/mysqld --tmpdir=/tmp-old), I got the same error message.

I dug through the init files and noticed some references to app-armor. I checked the logs and found this:

Jul 4 19:41:09 domU-12-31-39-0E-C9-A1 kernel: [ 8095.267321] type=1503 audit(1278272469.041:18): operation="mknod" pid=5746 parent=4609 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=106 ouid=106 name="/mnt/tmp/ib17ii5f"

...aha.

The main reason for redirecting /tmp was an application of my own that produced thousands of cache files in /tmp. I changed the app to use /mnt/tmp instead. Now, mysql launched, but with a new error:

SSL error: Unable to get certificate from '/vol/etc/mysql/newcerts/server-cert.pem'
100704 19:52:14 [Warning] Failed to setup SSL
100704 19:52:14 [Warning] SSL error: Unable to get certificate

Unsurprisingly, in /var/log/messages, I found:

Jul 4 19:52:14 domU-12-31-39-0E-C9-A1 kernel: [ 8760.837526] type=1503 audit(1278273134.614:23): operation="open" pid=4609 parent=4602 profile="/usr/sbin/mysqld" requested_mask="r::" denied_mask="r::" fsuid=106 ouid=106 name="/vol/etc/mysql/newcerts/server-cert.pem"

The fix for this was pretty simple: I opened /etc/apparmor.d/usr.sbin.mysqld and below the line

/etc/mysql/*.pem r,

I added the line

/vol/etc/mysql/newcerts/*.pem r,

Fixed.