Friday Apr 05, 2013

Running Out of Physical Disk Space

Problem: You've a large table (or two) in a database on a partition that's running out of space, and you want to see if you can move that table to another drive.

Solution: Well, several actually. No silver bullet, but several options, some with conditions and some that require preparation. Let's look at some background information first.

How MySQL Stores Data 

OK, that's somewhat of an ambitious heading for an incidental paragraph or two, so to tone it back a bit, I'll summarise briefly.

  • The data directory is where MySQL stores databases, and it's set by the datadir server option. Each database is stored in a subdirectory of the data directory.
    You can also save a considerable amount of space without moving data around, by using features of the various storage engines, for example if you enable compression on InnoDB tables. If you don't use InnoDB, you can consider using the ARCHIVE storage engine to compress data.
  • Storage engines are what MySQL uses to do the grunt work of storing and retrieving data. InnoDB is the default and is quite full-featured and robust, but some systems still use MyISAM, which is fast but not as well-featured or robust. Other storage engines are available.
  • MyISAM and InnoDB store data in the data directory. 
    • MyISAM stores data in .MYD files and index information in .MYI files, both in the database subdirectories mentioned above.
    • InnoDB stores data and index information in .ibd files in the database subdirectories, when you have the innodb_file_per_table option enabled. This is the default in MySQL 5.6, but must be enabled in 5.5; when disabled, InnoDB stores all data and indexes in a shared tablespace in the root of the data directory.

Now you know where the data lives, it's time to look at how to move it somewhere else.

Moving the Whole Data Directory 

As the datadir option controls where MySQL stores its data, one option we have is to move the contents of the current data directory to a new partition, and then change the datadir option to point to that location. 

For example, on my machine, the datadir option points to the /var/lib/mysql directory. As expected, it contains a directory for each of my databases, and it also contains the InnoDB system tablespace files. To increase the space available, I could add a new drive and point the datadir there.

Consider the following scenario:

  • Add a new hard drive, partitioned using a suitable file system such as ext4. That file system is visible to my OS as /dev/sdf1
  • Back everything up to provide a rollback route.
  • Ensure that the datadir option points to the /var/lib/mysql directory (or note the directory otherwise. we'll assume /var/lib/mysql here)
  • Mount the partition in /data by doing the following:
# mount /dev/sdf1 /data
  • Give the mysql user ownership of the new filesystem:
# chown mysql:mysql /data
  • Stop MySQL
  • Copy the contents of /var/lib/mysql to /data
  • # cp -a /var/lib/mysql/* /data 
  • Set the datadir option to point to /data
  • Restart MySQL

Beware of the various Mandatory Access Control subsystems that might affect you. If you've got AppArmor installed, you'll also need to change the profile for mysqld so it can access files in the new directory. If you've got SELinux installed, you'll need to add a context mapping for MySQL.

In fact, you don't even have to point MySQL to the new partition. If the mountain won't go to Mohammed... let's undo the last couple of steps:

  • Assume you haven't set the datadir option to point to /data, and that it still points to /var/lib/mysql. Also assume MySQL is still stopped. Now the data directory contents are at the new location, but on its next boot MySQL still looks in /var/lib/mysql.
  • Unmount the new partition /dev/sdf1 (or you can use its mountpoint, which is easier to type):

# umount /data

  • Mount /dev/sdf1 to /var/lib/mysql

# mount /dev/sdf1 /var/lib/mysql
  • Restart MySQL
Assuming it all works, you should then modify the file /etc/fstab to automatically mount /dev/sdf1 to /var/lib/mysql on reboot, so persisting your new configuration

But I Only Want to Move One Database

Moving a whole database to another location is achieved by using symbolic links. Simple summary (read the link for more details):

  • Make the new partition as in the example above
  • Rather than move the whole data directory, this time just move a single database directory; this only works with innodb_file_per_table enabled, because otherwise the InnoDB data is in the shared tablespace and can't be moved
  • At this point you can either mount the new partition into the database directory, or use symlinks as described in the link above.

But I Only Want to Move One Table

Ah, now we're getting interesting. The technique for doing this depends on the version of MySQL you're running—I'll talk about 5.5 and 5.6—and what storage engine the table uses.

Firstly, in MySQL 5.5 you can use symbolic links only for tables that use the MyISAM storage engine, and only on certain operating systems such as Linux and Oracle Solaris. The technique is similar to that described above, although you can also use the DATA DIRECTORY and INDEX DIRECTORY options to CREATE TABLE when creating the tables if you want to set up the links from within MySQL rather than at the Linux command prompt. You should not use symbolic links to InnoDB tables, because InnoDB stores some information about each table in the shared tablespace, even when you use the innodb_file_per_table option.

MySQL 5.6 also allows you to copy or move InnoDB tables to another server by performing a file copy, by using the transportable tablespaces feature.

In MySQL 5.6 you can also move InnoDB tables to a different location by using the DATA DIRECTORY option to CREATE TABLE. When you do so, the tablename.ibd file is moved to a subdirectory of that location named for the table's database, and a corresponding tablename.isl file created in the database's directory that acts as a link to the table's .ibd file. The .isl file is not an actual symbolic link, but is treated as such by MySQL.

Plenty of Space

All told, you have many options available to you regardless of which storage engine or version you use, although it won't surprise you to know that MySQL 5.6 gives more options than previous versions. Don't forget that modern UNIX-like operating systems often have mandatory access control systems such as AppArmor, SELinux, or Extended Policy, so be sure to do your homework before moving files around.

Friday Mar 22, 2013

SELinux and MySQL

I've previously written about AppArmor and MySQL, and how to change MySQL's default file locations on systems with AppArmor enabled. Ubuntu and SUSE ship with AppArmor enabled, but some other distributions such as Oracle Linux don't, along with related distrubutions such as Red Hat, CentOS and Fedora. Rather, these other distributions use another mandatory access control system called SELinux. 

Here's some technical detail that might come in handy later.

SELinux uses concepts such as types and domains. Types belong to resources such as files and ports; these are the "objects" in SELinux. Domains contain the "subjects" (processes) and object types that are associated with each other in some way, for example because they are all related to MySQL.

Each executable for a service that SELinux understands, including MySQL, has its own type. When the process runs, it is placed within a domain based on its type; members of that domain (normally processes) that wish to access objects (such as files and ports) must be authorised to do so by virtue of the object having a configured type that is approved for that domain.

This configuration is contained within policy files that usually ship with each distribution, but can be created using the appropriate tools.

SELinux (Security-Enhanced, if you're interested) "is a Linux feature that provides the mechanism for supporting access control security policies" according to Wikipedia. More simply, it stops things—like programs—from accessing things—like files and network ports—they shouldn't access. By "shouldn't access" I really mean "haven't been configured to access". For example, MySQL is allowed to write to its data directory in /var/lib/mysql, and read from /etc/my.cnf. It can open a socket on port 3306, but SELinux prevents it from writing to files in /home/jeremy or /sbin or anywhere else that isn't already configured as a MySQL location.

In short, if you try changing MySQL's port to a non-standard one, or try taking backups or configuring data files or log files to anywhere but the usual locations, you'll get some odd access-denied type errors in the MySQL error log. In addition, you'll get messages in  /var/log/audit/audit.log (if auditd is running, otherwise /var/log/messages or /var/log/syslog, depending how your system is configured).

What Error do I get? 

To set this demonstration up, I've installed MySQL 5.6 on an Oracle Linux 6.3 system, with SELinux enabled. When I change the datadir option to /datadir (which contains a copy of the MySQL data directory, and has the correct permissions) the service does not start. Let's look at the errors.

From the MySQL error log:

130321 11:50:51 mysqld_safe Starting mysqld daemon with databases from /datadir
2013-03-21 11:50:52 2119 [Warning] Can't create test file /datadir/boxy.lower-test
2013-03-21 11:50:52 2119 [Warning] Can't create test file /datadir/boxy.lower-test
2013-03-21 11:50:52 2119 [ERROR] /usr/sbin/mysqld: Can't create/write to file 
    '/datadir/' (Errcode: 13 - Permission denied)
2013-03-21 11:50:52 2119 [ERROR] Can't start server: can't create PID file: 
    Permission denied
130321 11:50:52 mysqld_safe mysqld from pid file /datadir/ ended

 Now, I'm very sure the permissions on that folder are correct, so let's have a look in /var/log/audit/audit.log:

type=AVC msg=audit(1363866652.030:24): avc:  denied  { write } for  pid=2119 
    comm="mysqld" name="datadir" dev=dm-0 ino=394 
    tcontext=unconfined_u:object_r:default_t:s0 tclass=dir

A similar error occurs if I try starting MySQL on port 3307, a non-default port:

In the MySQL error log:

2013-03-21 12:12:09 3436 [Note] Server hostname (bind-address): '*'; port: 3307
2013-03-21 12:12:09 3436 [ERROR] Can't start server: Bind on TCP/IP port: 
    Permission denied
2013-03-21 12:12:09 3436 [ERROR] Do you already have another mysqld server 
    running on port: 3307 ?
2013-03-21 12:12:09 3436 [ERROR] Aborting

In the audit log: 

type=AVC msg=audit(1363867929.432:42): avc:  denied  { name_bind } for  pid=3436 
    comm="mysqld" src=3307 
    tcontext=system_u:object_r:port_t:s0 tclass=tcp_socket

 Clearly something going on here. The Access Vector Cache (as seen in the "avc: denied" message) is where SELinux caches permissions for the kernel, so it's definitely SELinux doing the denying. 

Just Stop It!

I'm going to start with the hammer and work my way down to the scalpel. 

Here's the hammer:

[root@boxy ~]# setenforce 0
[root@boxy ~]# getenforce

The setenforce 0 command switches off SELinux enforcing until the next reboot, and getenforce shows you the current status. To stop SELinux from enforcing on any reboot, you'll need to change a configuration file:

[root@boxy ~]# cat /etc/selinux/config 
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.

 Change that enforcing to permissive (or disabled) and you're good to go. The difference:

  • enforcing blocks operations that SELinux does not allow
  • permissive does not block the operations, but logs them (to /var/log/audit/audit.log)
  • disabled switches off SELinux entirely, to the extent that you cannot use setenforce until you change it and reboot.

 For example, on a machine with SELinux set to permissive, I can do the following:

[root@boxy ~]# setenforce 1
[root@boxy ~]# getenforce

But if it's disabled, this happens:

[root@boxy ~]# setenforce 0
setenforce: SELinux is disabled
[root@boxy ~]# setenforce 1
setenforce: SELinux is disabled

 That's the hammer.

 So, to return to the example that generated the error, I can use the hammer:

[root@boxy ~]# setenforce 0
[root@boxy ~]# service mysql start --datadir=/datadir
Starting MySQL. SUCCESS! 
[root@boxy ~]# service mysql stop
Shutting down MySQL.. SUCCESS! 

If you're happy with that, you could then edit the configuration file to disable SELinux on next reboot, and thanks for reading. See you next time.

I'm intrigued. How do I configure it? 

Obviously, there's a lot more to SELinux than disabling it, and a responsible admin (that's you, right?) wants to know how to use it rather than disable it. I'm not going to get into too much detail here.

We can, however, look at how you can assign SELinux types to objects such as ports and files, so that members of the mysqld_t domain (specifically the mysqld_safe process, launced when you run service mysql start) can access those objects.

So here's the scalpel. First, let's configure SELinux to enable MySQL's use of port 3307:

 [root@boxy ~]# semanage port -a -t mysqld_port_t -p tcp 3307 
You'll need to install the policycoreutils-python package to use the semanage utility.

The semanage utility changes various SELinux settings.  In this case, it adds (-a) a type (-t  mysqld_port_t) to the port mappings for port 3307 using TCP as its protocol (-p tcp). When MySQL (through the mysqld_safe process) tries to access that port, SELinux recognises that the port has a type that is approved for such access by the policy.

We can also allow MySQL to use the /datadir directory: 

[root@boxy ~]# semanage fcontext -a -t mysqld_db_t "/datadir(/.*)?"
[root@boxy ~]# restorecon -Rv /datadir
restorecon reset /datadir context 
restorecon reset /datadir/mysql.sock context 

In this example, semanage is adding the type mysqld_db_t to the file context map (fcontext) for anything in the /datadir directory and subdirectories ("/datadir(/.*)?", a regular expression). File mappings such as this are contained in the file /etc/selinux/targeted/contexts/files/file_contexts.local; that file must subsequently be read in order to set the appropriate type on the file itself. That's done by the restorecon utility, and at system reboot.  If you want to change a file context immediately, but don't need it to survive a reboot, there's a chcon utility that performs that task.

The same principles and statements apply if you wish to use other ports or directories. There are similar types that apply to different kinds of files; I used mysqld_db_t above for database directories, but the standard SELinux policy for MySQL also include:

  • mysqld_etc_t for configuration files such as /etc/my.cnf
  • mysqld_log_t for logfiles such as those named /var/log/mysql*
  • Types for the PID file, tmp files, service startup files in the /etc/init.d directory, and the various executables you're likely to use
As you can see, you can get quite fine-grained as you wield your configuration scalpel. Personally, I've had mixed results using things like mysqld_log_t for custom logfile locations, but I got around it initially by using mysqld_db_t (as for data files), and subsequently by using a custom-made policy file.


This post is already long enough, so I won't get into the deeper topics in SELinux, such as the ability to compile your own policy files and configure new policies for services that SELinux doesn't yet know about. At this stage, you know how to add an SELinux type to an object such as a port or a file so that MySQL can access that object, even if it's not used by default. You also know how to disable SELinux in a couple of ways, but you're not going to do that now, are you? You've got a perfectly good scalpel, after all. Why use a hammer?

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.


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



« July 2016