MySQL and HeatWave

  • January 31, 2013

AppArmor and MySQL

Jeremy Smyth
Manager, MySQL Curriculum

MySQL accesses files in various places on the file system, and usually this isn't something to worry about. For example, in a standard MySQL 5.5 installation on Ubuntu, the data goes in /var/lib/mysql, and the socket is a file in /var/run/mysqld. It puts configuration files in /etc, logs and binaries in various locations, and it even needs to access some operating system files such as /etc/hosts.allow.

This is all very well until you start trying to be clever and get MySQL to access other parts of the file system. After all, you can configure the location of data, log files, socket, and so on, so why shouldn't you use those settings to optimize your system? Unfortunately, on many modern Linux distributions, it's not that always easy.

Take Ubuntu, for example. Ubuntu comes with something called AppArmor, a kernel-integrated application security system that controls how applications can access the file system. This goes above and beyond normal permissions, and as a result can sometimes be a bit confusing.


First, here's the quick version of this post: If you want to relocate the data directory in MySQL (in this example, to the /data/directory), and AppArmor is not letting you, add the following two lines to the bottom of /etc/apparmor.d/local/usr.sbin.mysqld:

/data/ r,
/data/** rwk, 

...and then reload the AppArmor profiles: 

# service apparmor reload 

The Demonstration

To demonstrate this in a bit more detail, I've done the following:

  • Installed a stock MySQL 5.5 from the Ubuntu 12.04 repository
  • Created the /data directory, owned by the mysql user and group
  • Copied my data directory to /data with cp -a

Now, when I start MySQL with the new data directory, I get the following:

[root@boxy ~]# mysqld_safe --datadir=/data
130130 21:31:51 mysqld_safe Logging to syslog.
130130 21:31:51 mysqld_safe Starting mysqld daemon with databases from /data
130130 21:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

...and it dies.

As it's logging to syslog, let's look there:

Jan 30 21:31:51 boxy mysqld: 130130 21:31:51 InnoDB: Completed initialization of 
    buffer pool
Jan 30 21:31:51 boxy mysqld: 130130 21:31:51  InnoDB: Operating system error 
    number 13 in a file operation.
Jan 30 21:31:51 boxy kernel: [81703.213926] type=1400 audit(1359581511.909:36): apparmor="DENIED" operation="open" parent=16198 profile="/usr/sbin/mysqld" 
    name="/data/ibdata1" pid=16538 comm="mysqld" requested_mask="rw" 
    denied_mask="rw" fsuid=116 ouid=116
Jan 30 21:31:51 boxy mysqld: InnoDB: The error means mysqld does not have 
    the access rights to
Jan 30 21:31:51 boxy mysqld: InnoDB: the directory.

The final two lines say mysqld doesn't have access to the directory, even though I've changed the ownership (both user and group) to mysql. If you haven't come across AppArmor before, this is about where you start to get confused. However, that big "DENIED" is a bit of a giveaway, and it's associated with apparmor, so let's have a look at AppArmor's status:

[root@boxy ~]# aa-status 
apparmor module is loaded.
18 profiles are loaded.
18 profiles are in enforce mode.
0 profiles are in complain mode.
2 processes have profiles defined.

There's a profile loaded for the mysqld process, which could be what's blocking it from accessing /data.

There are a couple of quick and dirty ways to get past this. You could, for example, disable AppArmor; it's a service, so you could uninstall it, or stop it with the special teardown command to unload all profiles. You could even delete the offending profile if you want rid of it. Another less extreme option is to use the apparmor-utils package, which contains the utilities aa-complain and aa-enforce that allow you to work with existing profiles without removing them or stopping AppArmor entirely:

[root@boxy ~]# aa-complain /usr/sbin/mysqld 
Setting /usr/sbin/mysqld to complain mode.

As you can probably guess, complain mode simply whines when a process accesses something on the file system that it shouldn't, whereas enforce mode is what stops such access.

[root@boxy ~]# aa-status 
apparmor module is loaded.
18 profiles are loaded.
17 profiles are in enforce mode.
1 profiles are in complain mode.
2 processes have profiles defined.

So now it's in complain mode, we can check to see if it starts:

[root@boxy ~]# mysqld_safe --datadir=/data
130130 21:34:16 mysqld_safe Logging to syslog.
130130 21:34:16 mysqld_safe Starting mysqld daemon with databases from /data

So now we know that AppArmor is the reason why MySQL is not starting, we can enforce again, before going through the proper configuration:

[root@boxy ~]# aa-enforce /usr/sbin/mysqld 
Setting /usr/sbin/mysqld to enforce mode.

Time to look under the covers.

Under the Covers: AppArmor's Policy Files

When you install MySQL on Ubuntu, it places an AppArmor policy file in /etc/apparmor.d/usr.sbin.mysqld. Another policy file gets placed in /etc/apparmor.d/local/usr.sbin.mysqld, which is initially empty (aside from comments) but exists to let you add non-standard policies such as those specific to this machine. In practice, you could add such policies to either file, but for now I'll put them in the local file. There's also a cached policy file, which is a binary compiled version of the policy. We can happily ignore that; it's automatically generated from the policy text files.

Here are some of the contents of /etc/apparmor.d/usr.sbin.mysqld:

# vim:syntax=apparmor
# Last Modified: Tue Jun 19 17:37:30 2007
#include <tunables/global>
/usr/sbin/mysqld {
  #include <abstractions/base>
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>

In the middle are the file system policies. Looking at the settings for the existing data directory /var/lib/mysql, you can see that the profile gives read (r) access to the directory itself, and read, write, and lock access (rwk) to its contents recursively (controlled by the **). Conveniently, it also #includes the contents of the local file.

Editing the Policy 

To give MySQL the necessary access to the /data directory, I edit the included local file so it looks like the following:

[root@boxy ~]# cat /etc/apparmor.d/local/usr.sbin.mysqld
# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/data/ r,
/data/** rwk,

As you can see I haven't been particularly creative; I've just copied the policy that applies to the standard data directory /var/lib/mysql, and copied it to this file, mapping the same settings to the new /data directory. Also, although I've put this in the local version of the policy file, I could just as easily have modified the main policy file.

Finally, reload the AppArmor profiles:

[root@boxy ~]# service apparmor reload
 * Reloading AppArmor profiles
Skipping profile in /etc/apparmor.d/disable: usr.bin.firefox
Skipping profile in /etc/apparmor.d/disable: usr.sbin.rsyslogd

No errors about my new profile settings; the default configuration disables some AppArmor policies, but nothing I have to be concerned with. Finally, the moment of truth: Can we start MySQL?

[root@boxy ~]# mysqld_safe --datadir=/data
130130 21:38:42 mysqld_safe Logging to syslog.
130130 21:38:42 mysqld_safe Starting mysqld daemon with databases from /data



It's worth pointing out that this technique applies if you want to change where MySQL puts anything on the file system. Although the use case described here is a common first reason to bump up against AppArmor's security policies, the data directory is not the only thing that you might want to move. Logs, the UNIX socket, and even configuration files are subject to the controls placed in the AppArmor policy. This also includes any files you access with anything that uses the FILE privilege, such as SELECT ... INTO OUTFILE, LOAD DATA INFILE, or the LOAD_FILE() function. While you can secure this sort of access with MySQL's secure_file_priv option, AppArmor provides a further layer of security that prevents even currently unknown exploits from accessing parts of the file system that they really, really shouldn't.

Join the discussion

Comments ( 6 )
  • Josh Thursday, September 12, 2013

    Thanks - I just ran into this issue today, and it solved my problem.

  • Bill Thursday, October 24, 2013

    Thanks! Very clear solution to my recurring problem!

  • guest Friday, October 25, 2013

    Two remarks for us novices.

    First, do not think that "#" in #include is the start of a comment. On the other hand, "# " in column 1 IS the start of a comment. (This seems very strange to me, but that seems to be the convention....)

    Second, it is odd to me that on the one had you will find:

    /var/log/mysql/ r,

    /var/log/mysql/* rw,

    but on the other hand there is :

    /usr/share/mysql/** r,

    alone, with no "/usr/share/mysql/ r," for the directory. But again, that seems to be the convention: if you do anything more in the directory than read, you need to set the directory to read.

    Thanks again!

  • guest Saturday, November 23, 2013

    I have my /var soft-linked to a larger data-partition, which causes problems if you don't specify the full path on the linked partition. In my case:

    /var/lib/mysql ... /mnt/volume1/var/lib/mysql

    /var/log/mysql ... /mnt/volume1/var/log/mysql

    so in /etc/apparmor.d/local/usr.sbin.mysqld I had to add the lines

    /mnt/volume1/var/lib/mysql/ r,

    /mnt/volume1/var/lib/mysql/** rwk,

    /mnt/volume1/var/log/mysql/ r,

    /mnt/volume1/var/log/mysql/* rw,

    I took me the best part of 4 hours to find the problem:

    tail -f /var/log/syslog

    helped in the end (apparmor spat out the access denied messages for the full paths)

  • guest Tuesday, May 13, 2014

    The solution is very well explained. Thank you very much.

  • Kartik Sunday, March 1, 2015

    What an excellent way of publishing information. Thank You so much. You inspire me on the way to write an article

    Clear and precise. Thanks for your time and effort. More Power to You!!

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.