When working with MySQL 5.5 on Ubuntu 12.04, many administrators encounter a stubborn situation where the open_files_limit remains stuck at 1024 despite making all the apparent correct configuration changes. Let's break down why this happens and the proper way to enforce higher limits.

The actual file descriptor limit for MySQL is determined by the lowest of these three values:

1. System-wide kernel limit (/proc/sys/fs/file-max)
2. User process limits (set via limits.conf or ulimit)
3. MySQL's configured open_files_limit

The core issue lies in how Ubuntu's MySQL init script handles startup. The service wrapper often ignores both system limits and my.cnf settings. Here's the proper fix:

# Edit the init script (path varies by Ubuntu version)
sudo nano /etc/init/mysql.conf

# Add these lines before the 'exec' command:
limit nofile 24000 24000

For newer Ubuntu versions using systemd:

# Create override directory if it doesn't exist
sudo mkdir -p /etc/systemd/system/mysql.service.d

# Create limits override file
sudo nano /etc/systemd/system/mysql.service.d/limits.conf

# Add these contents:
[Service]
LimitNOFILE=24000

After making changes, verify with:

# Check system limits
cat /proc/$(pgrep mysqld)/limits | grep 'Max open files'

# Check MySQL's view of the limit
mysql -e "SHOW VARIABLES LIKE 'open_files_limit';"

• Check if AppArmor is blocking the change: sudo aa-status | grep mysql
• Verify the mysql user's session limits: sudo -u mysql bash -c "ulimit -n"
• Ensure no other my.cnf files are overriding your settings: mysqld --verbose --help | grep -A 1 "Default options"

When you launch mysqld directly, it inherits the shell's ulimit settings. The service wrapper often resets these values to more restrictive defaults. This explains why your manual launch showed 32000 while the service didn't.

Remember that raising this limit increases memory usage. Calculate approximately 1KB per file descriptor when setting your limit. For busy servers, also consider:

# Kernel-level adjustments
echo "fs.file-max = 500000" >> /etc/sysctl.conf
sysctl -p

After properly configuring both system limits and MySQL configuration, many administrators encounter a frustrating scenario where SHOW VARIABLES LIKE 'open_files_limit'; stubbornly reports 1024 despite all apparent corrections. Let's dissect the complete solution.

# /etc/security/limits.conf (essential but not sufficient)
* soft nofile 32000
* hard nofile 32000
mysql soft nofile 32000  # Explicit setting for mysql user
mysql hard nofile 32000

However, Ubuntu's Upstart system introduces additional complexity. The MySQL init script may override these settings unless properly configured.

For MySQL 5.5 on Ubuntu 12.04, you must modify the Upstart job configuration:

# /etc/init/mysql.conf (or mysqld.conf)
limit nofile 32000 32000

After this change, reload Upstart configuration:

sudo initctl reload-configuration

The correct syntax in my.cnf should be:

[mysqld]
open_files_limit = 24000

But here's the critical detail - this must appear under the [mysqld] section header, not globally. Common mistake alert!

Run this comprehensive check sequence:

# Check system limits for mysql user
sudo -u mysql bash -c "ulimit -n"

# Check current MySQL value
mysql -e "SHOW VARIABLES LIKE 'open_files_limit';"

# Check process limits
cat /proc/$(pgrep mysqld)/limits | grep 'Max open files'

The discrepancy occurs because:

• Manual launch inherits shell environment limits
• Service launch uses init system defaults unless explicitly configured

The solution combines all three elements: system limits, Upstart configuration, and proper my.cnf placement.

For newer Ubuntu versions using systemd, create this override:

# /etc/systemd/system/mysqld.service.d/limits.conf
[Service]
LimitNOFILE=32000

Then reload systemd:

sudo systemctl daemon-reload