Thursday Jan 31, 2013

AppArmor and MySQL

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/ 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.

Friday Jan 25, 2013

Performing an Unattended Uninstall of MySQL in Windows

The MySQL Installer is an easy way to install and maintain your MySQL installations on Microsoft Windows.

It comes not only with the database server and documentation, but also with the underrated MySQL Workbench, and various connectors and support utilities such as MySQL for Excel. Modifying the installed products or uninstalling them is also fairly straightforward: 

 Start -> All Programs -> MySQL -> MySQL Installer -> MySQL Installer

This launches the Maintenance window of the Installer GUI, and gives you the options:

  • Add/Modify Products and Features
  • Check for Updates
  • Remove MySQL Products.

I had a question from someone who wanted to automate a process in an environment where they tear down and reinstall MySQL to test deployments. The GUI-based way to change or remove MySQL is manual and requires human intervention, and they wanted to automate as much as possible, and thereby install and uninstall MySQL repeatedly and automatically, from a script run at the command prompt.

This is quite easy to script if you've got the MSI file you used to install MySQL, as documented here. I thought I'd go a bit further into it and explore the mechanism a little more.

Control Panel and the Registry

Control Panel Uninstallation

Before we get into the how of unattended uninstallations, the Windows Control Panel also lets you uninstall from the Programs and Features applet (which used to be called Add/Remove Programs). For MySQL, this option offers similar options to the Installer.

This mechanism (which isn't specific to MySQL) relies on a centralised repository of change/uninstall commands.

So, after a bit of investigation, I found the location in the registry:


In that location, the UninstallString key contains the following text:

MsiExec.exe /I{26FFE68D-7BD4-472A-9AB8-3517B6BF9E51} 

You might recognise part of that statement from the article Automating MySQL Installation linked to above. Running that MsiExec.exe command launches the uninstall GUI, just as if you'd clicked it from Control Panel.

The GUID (the value between brackets) is correct on my machine, but differs from version to version.  You can find the correct value by searching HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\ in the Windows Registry for “MySQL Server” and finding  the GUID that the Windows Installer uses for the program.

(Note: The Registry is a dangerous place to play around in, even for experienced people, so you should be exceptionally careful in the registry and using MsiExec.)

Tweaking MsiExec.exe

Now, to automate it, there are options to MsiExec.exe that help:

  • /X bypasses the front screen and goes straight to the uninstall process
  • /quiet disables the GUI entirely and automates the uninstall (if possible, which it is in MySQL's case)
  • /norestart disables the post-uninstall reboot that is needed in some cases
  • /L enables logging, with various options (the following example uses /L*V to log in verbose fashion)

Performing an Unattended Uninstall

Bringing all of this together, you can do the following. In a command-line started with the “Run as Administrator” option, type the following (all on one line):

MsiExec.exe /x {26FFE68D-7BD4-472A-9AB8-3517B6BF9E51} /quiet /norestart /L*V "C:\MySQL-uninstall.log"

This performs a quiet (no-gui, i.e. unattended) uninstall, without a reboot, logging in verbose format to the file C:\MySQL-uninstall.log.

Follow a similar technique to automate the uninstallation of other components that the MySQL Installer installs, or indeed of any well-behaved Windows application.

Thursday Jan 17, 2013

Automating Backups

If you need to automate backups, you might wonder about the different techniques available to you.

With regards to scheduling backups using built-in features of MySQL, you have two main options:

  • Either run mysqldump (or mysqlbackup if you have an Enterprise licence) from an operating system scheduler, for example in Linux using "cron" or in Windows using the "Task Scheduler". This is the most commonly used option.
  • Alternatively, use the Event Scheduler to perform a series of  SELECT ... INTO OUTFILE ... commands, one for each table you need to back up. This is a less commonly used option, but you might still find it useful.

Scheduling mysqlbackup with cron

mysqldump is a client program, so when you run it, you run it from a shell script, or at a terminal, rather than inside a MySQL statement. The following statement backs up the sakila database using settings stored in the file /home/bk/bk.cnf:

mysqldump --defaults-file=/home/bk/bk.cnf sakila > /home/bk/backup.sql

Typically, you would place login information into an options file to avoid specifying a password on the command line. Bear in mind that the configuration file stores the password in plain text, so you should ensure it is secured against unauthorised access. An option file such as that used in the preceding statement could look like the following, assuming that the user bk exists with the password as shown:


To schedule a backup using mysqldump, use an operating system scheduler such as cron in Linux. Without getting into too much detail, cron uses a configuration file (called a crontab) that allows you to specify a recurring schedule based on minutes, hours, day (of the month or of the week), and month. The following line (placed in the system's crontab)  runs a backup every Sunday at 3:15am:

# m h dom mon dow user command
15 3 * * 7 bk mysqldump --defaults-file=/home/bk/bk.cnf sakila > /home/bk/backup.sql

For a complete discussion of cron and the crontab, your operating system's manpages should have an entry visible with the man crontab command. Wikipedia has a good overview too. 

The Event Scheduler

The Event Scheduler runs from within MySQL, so you can use it to schedule any valid MySQL statement that you can use within a query or stored routine. In particular, you can use the Event Scheduler to schedule a statement such as SELECT ... INTO OUTFILE ... , but you cannot invoke a command-line program such as mysqldump.

For example, you could schedule a regular dump of the sakila.payment table with the following sequence: 

CREATE EVENT payment_backup
STARTS '2013-01-20 03:15:00'
   SELECT * INTO OUTFILE '/home/bkuser/payment.tsv'
   FROM sakila.payment;

The preceding statement creates a scheduled event that runs weekly starting at 3:15am on 20th January 2013, a time that is still in the future as I write this. If you create an event starting some time in the past, it is immediately discarded with a warning, so bear that in mind if you're copying and pasting from this post.

The Event Scheduler is enabled with the global event_scheduler variable. This variable is set to OFF by default, so to enable it you must set it to ON.

Comparing the Techniques 

Backups created with mysqldump are files containing SQL statements to recreate the database and its data; one backup typically consists of a single SQL file that can be executed as a whole to recreate the database(s) along with all data. On the other hand, the files created by SELECT ... INTO OUTFILE ... statements are tab-separated data values, by default, and are usually one file per table; although you have the flexibility of dumping data from multiple tables in a single query, the resulting file can not be used as a backup, because restoring from such a file would be impractical.

The files are of very different formats, so you would choose your automated backup method based not only on how the schedule and syntax works, but also how best to restore files created using the two techniques and whether you wish to use the files in some other way. For example, many common spreadsheet programs can read TSV files, so you could use files created with SELECT ... INTO OUTFILE ... for analysis as well as for backups.

Also bear in mind the need to build a recovery strategy around the file formats you have; there's no point going to all the effort of taking backups if you can't recover from them.

There are several other alternatives you can use to back up your databases, including MySQL Enterprise Backup (with similar conditions to those of mysqldump mentioned earlier in this post). You can also use file-system snapshots or raw file copies, neither of which makes use of mysqldump or the Event scheduler, and each of which has its own interesting features and limitations.

    Friday Jan 11, 2013

    Excluding a Table From Backup

    Let's say you have a database that stores not only current transactional data, but also historic data that's unchanging. In particular, you have a large table containing hundreds of gigabytes worth of last year's data, and it won't change. Having backed it up already, you don't need to back it up every time. Is there any way to exclude this table from a backup?

    For InnoDB tables with innodb-file-per-table enabled (the default as of MySQL 5.6), MySQL Enterprise Backup supports this feature in inverse. Specifically, you can choose to include specific innodb-file-per-table tables in addition to those stored in the system tablespace.

    In order to exclude a specific table, you need to provide a regular expression to the --include option that includes every table except the one you want to exclude. For example, in my sakila database, I know that the payment table is the only one beginning with "p", so if I want to exclude it, I can do this:

    # mysqlbackup --datadir=/var/lib/mysql --backup_dir=/backups \
    >        --include 'sakila\.[^p].*' backup-and-apply-log

    The following link contains detailed information on the --include option:

    You can also specify filters for non-InnoDB tables using options such as --databases and --databases-list-file. The following link covers a variety of different partial backup techniques: 


    Jeremy Smyth writes MySQL training courses, and likes exploring interesting questions that come up from novices and experts alike.



    January 2013 »