Creating a MySQL db on Ubuntu as a normal user

Lately I tried to create a MySQL db on Ubuntu (version 11 which has MySQL 5.1 preinstalled). I was logged in under my normal username but I got a surprise when running the mysql_install_db command.

$ /usr/bin/mysql_install_db --datadir=./mysql/data
Installing MySQL system tables...

130806 22:17:21 [Warning] Can't create test file /home/andreash/mysql/data/andreas-Ub-2.lower-test
130806 22:17:21 [Warning] Can't create test file /home/andreash/mysql/data/andreas-Ub-2.lower-test

Installation of system tables failed!  Examine the logs in
./mysql/data for more information.
...

There were not log files though and checking directories and permissions didn't reveal any problems.
So I started to search and found that Ubuntu uses a security mechanism called apparmor which can be used to control certain aspects of an application.
In regards to MySQL that means that there exists a MySQL profile which defines which directories can be accessed (and how) by the MySQL programs. The profile for the daemon mysqld is defined in /etc/apparmor.d/usr.sbin.mysqld and looks like this:

# Last Modified: Tue Jun 19 17:37:30 2007
#include <tunables/global>

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

  capability dac_override,
  capability sys_resource,
  capability setgid,
  capability setuid,

  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /{,var/}run/mysqld/mysqld.pid w,
  /{,var/}run/mysqld/mysqld.sock w,

  /sys/devices/system/cpu/ r,

  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}

So in order to enable MySQL to access a subdirectory of my $HOME I had to edit the file as root (sudo vi ...) and add this line to the list (I put it right under the /sys/devices line)

  /home/andreas/mysql/** rw,

The apparmor man page explains the syntax and attributes in detail. For my purposes it suffices to know that ** stands for the directory and all subdirectories underneath and rw is of course read/write.

Then this new profile needs to be activated replacing the old one via

$ sudo apparmor_parser -rv /etc/apparmor.d/usr.sbin.mysqld
Replacement succeeded for "/usr/sbin/mysqld".

Finally running the MySQL program again did create the databases.

Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

...

---

Not knowing much about apparmor yet I wonder how one would go about to allow all users (on a bigger multi-user server) to use MySQL or any other application which is secured in the same way. It would be impractical to add all users home directories to the profile file so I guess there must be some shortcut. This needs more reading.