Resolving MySQL SELECT INTO OUTFILE Errcode 13 Permission Error: A Deep Dive into AppArmor Configuration

Problem Background and Symptom Analysis

When using MySQL's SELECT INTO OUTFILE statement to export query results to a CSV file, developers often encounter the Errcode 13 permission error. Specifically, the operation succeeds when the output file path is set to the default database directory, but fails when attempting to write to other directories (e.g., /data/), even with correct directory permissions (e.g., chmod 777 and chown mysql:mysql). The error message appears as: ERROR 1 (HY000): Can't create/write to file '/data/outfile.csv' (Errcode: 13). This issue is common on Ubuntu systems, especially in newer versions where the AppArmor security module is enabled by default.

Core Role of the AppArmor Security Module

AppArmor is a mandatory access control (MAC) system in the Linux kernel that restricts resource access for processes via configuration files. In Ubuntu Server Edition (e.g., 10.04 and later), the MySQL service (mysqld) is managed by a default AppArmor profile, which defines the allowed files and directories for the MySQL process. When MySQL tries to write to a directory not explicitly permitted in this profile, AppArmor blocks the action, even if the operating system-level file permissions appear sufficient.

Diagnosis and Verification Steps

First, confirm whether AppArmor is active and affecting MySQL. Check by executing:

sudo aa-status

Sample output:

5 profiles are loaded.
5 profiles are in enforce mode.
   /usr/lib/connman/scripts/dhclient-script
   /sbin/dhclient3
   /usr/sbin/tcpdump
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/sbin/mysqld
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode :
   /usr/sbin/mysqld (1089)
0 processes are in complain mode.

If mysqld appears in the "enforce mode" list, AppArmor is actively restricting MySQL's access. Additionally, system logs (e.g., /var/log/messages) record detailed denial events, aiding in root cause analysis.

Modifying AppArmor Configuration to Allow Directory Access

To resolve the Errcode 13 error, edit MySQL's AppArmor profile, typically located at /etc/apparmor.d/usr.sbin.mysqld. Open the file with a text editor (e.g., vi or nano) and add permission rules for the target directory near the end. For example, to allow MySQL to read and write to the /data/ directory:

/usr/sbin/mysqld  {
    ...
    /var/log/mysql/ r,
    /var/log/mysql/* rw,
    /var/run/mysqld/mysqld.pid w,
    /var/run/mysqld/mysqld.sock w,
    /data/ r,
    /data/* rw,
}

Here, /data/ r allows MySQL to read the directory itself (list contents), and /data/* rw permits reading and writing all files within the directory. Ensure correct rule formatting to avoid syntax errors.

Reloading Configuration and Testing

After saving the configuration file, reload AppArmor to apply changes:

sudo /etc/init.d/apparmor reload

Or on systemd-based systems:

sudo systemctl reload apparmor

Once reloaded, re-execute the SELECT INTO OUTFILE query:

SELECT column1, column2
INTO OUTFILE '/data/outfile.csv'
FIELDS TERMINATED BY ','
FROM table_name;

The operation should now succeed, generating the file outfile.csv in the /data/ directory.

Security Considerations and Best Practices

When modifying AppArmor profiles to grant MySQL access to additional directories, security risks must be considered. Overly permissive rules can lead to data breaches or system vulnerabilities. Recommendations include:

• Add only necessary directory paths, avoiding excessive use of wildcards.
• Regularly review AppArmor configurations to remove obsolete rules.
• Implement multi-layered defense by combining operating system permissions (e.g., file ownership and ACLs).
• Test changes in a controlled environment before deploying to production systems.

For instance, if export is needed only to a specific subdirectory, refine rules to /data/exports/ rw instead of the entire /data/ directory.

Alternative Solutions and Additional Notes

Beyond modifying AppArmor, other approaches may mitigate the issue, but each has limitations:

• Using the /tmp directory: Since /tmp often has lenient AppArmor rules or system defaults, it can serve as a temporary workaround, but is unsuitable for long-term or sensitive data storage.
• Adjusting MySQL data directory: Set the output path to MySQL's default data directory (e.g., /var/lib/mysql/), though this may not align with file organization needs.
• Disabling AppArmor (not recommended): Use sudo aa-complain /usr/sbin/mysqld to set the MySQL profile to "complain mode" or disable AppArmor entirely, but this significantly reduces system security and should only be used in controlled environments with full risk understanding.

In summary, by properly configuring AppArmor, you can flexibly manage MySQL's file export permissions without compromising security, effectively resolving the Errcode 13 error.