AppArmor and MySQL

转自MySQL 博客
By: 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.
TL;DR
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 themysql
user and group
Copied my data directory to/data
withcp -a

Now, when I start MySQL with the new data directory, I get the following:
[root@boxy ~]# mysqld_safe --datadir=/data130130 21:31:51 mysqld_safe Logging to syslog.130130 21:31:51 mysqld_safe Starting mysqld daemon with databases from /data130130 21:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
...and it dies.
As it's logging tosyslog
, let's look there:
Jan 30 21:31:51 boxy mysqld: 130130 21:31:51 InnoDB: Completed initialization of buffer poolJan 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=116Jan 30 21:31:51 boxy mysqld: InnoDB: The error means mysqld does not have the access rights toJan 30 21:31:51 boxy mysqld: InnoDB: the directory.
The final two lines saymysqld
doesn't have access to the directory, even though I've changed the ownership (both user and group) tomysql
. 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 withapparmor
, 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. /sbin/dhclient... /usr/sbin/cupsd /usr/sbin/mysqld /usr/sbin/tcpdump0 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 specialteardown
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 theapparmor-utils
package, which contains the utilitiesaa-complain
andaa-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. /sbin/dhclient... /usr/sbin/tcpdump1 profiles are in complain mode. /usr/sbin/mysqld2 processes have profiles defined....
So now it's in complain mode, we can check to see if it starts:
[root@boxy ~]# mysqld_safe --datadir=/data130130 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 thelocal
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#include
s the contents of thelocal
file.
Editing the Policy
To give MySQL the necessary access to the/data
directory, I edit the includedlocal
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 thelocal
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 profilesSkipping profile in /etc/apparmor.d/disable: usr.bin.firefoxSkipping 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=/data130130 21:38:42 mysqld_safe Logging to syslog.130130 21:38:42 mysqld_safe Starting mysqld daemon with databases from /data
Success!
Conclusion
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 theFILE
privilege, such asSELECT ... INTO OUTFILE
,LOAD DATA INFILE
, or theLOAD_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.