Posted inMySQL

MySQL Error Log and General Query Log: Production Troubleshooting Techniques for Sysadmins

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

When PagerDuty Screams at 2 AM

The system crashes right when you’re sound asleep after a stressful release day. You SSH into the server, see low CPU usage and plenty of free RAM, yet the application keeps throwing <a href="https://itfromzero.com/en/mysql-en/fixing-mysql-connection-errors-from-1045-to-too-many-connections-real-world-stories-and-solutions.html">Connection Refused</a> errors. It’s a feeling of utter helplessness if you don’t know where to start.

I once faced a tough case: the database was refusing all new connections for no apparent reason. By checking the Error Log, I discovered MySQL had exhausted its open_files_limit (file descriptors). It took only 5 minutes to increase the limit and restart, instead of guessing all night. Experience shows that managing MySQL without mastering logs is like driving through fog without headlights.

In this article, I’ll focus on two “critical” log types: the Error Log (for system troubleshooting) and the General Query Log (for monitoring every statement).

1. MySQL Error Log: The System Incident Journal

The Error Log is the first place you should look when MySQL crashes or fails to start. It records every event from the moment the engine begins running until it encounters a critical error.

Configuring the Error Log Correctly

On production, don’t leave logs to their default settings. Specify a dedicated path for easier management and backups. Open your my.cnf file (usually located in /etc/mysql/) and add the following lines:

[mysqld]
# Store logs in a dedicated directory
log_error = /var/log/mysql/error.log

# For MySQL 8.0+, use verbosity to control detail level
# 1: Errors only, 2: Errors + Warnings, 3: Errors + Warnings + Notes
log_error_verbosity = 3

Note: Restarting MySQL on production is risky. You should use the following command to check the current log location without stopping the service:

SHOW VARIABLES LIKE 'log_error';

Tips for Reading Logs Without Getting Overwhelmed

Instead of opening multi-gigabyte files, use tail to monitor the latest entries in real-time:

tail -f /var/log/mysql/error.log | grep -i "error"

Errors such as Out of memory, <a href="https://itfromzero.com/en/mysql-en/a-guide-to-using-mysqlcheck-for-mysql-database-maintenance-optimization-and-table-repair.html">Table is marked as crashed</a>, or Too many connections will appear immediately. If you see an [ERROR] tag, it’s a signal that you need to act right away.

2. General Query Log: Your 24/7 Surveillance Camera

While the Error Log tells you what broke, the General Query Log reveals who did what. It records every connection and every SQL statement the server receives.

I once discovered an old module running a SELECT 1 loop tens of thousands of times per second thanks to this log. The query wasn’t syntactically wrong, but it was eating up all the server’s IOPS.

Enabling the General Query Log (Use with Caution!)

Warning: The General Log can reduce server performance by 10% to 15%. It records 100% of activity, causing log files to bloat very quickly. Only enable it when debugging a specific issue and disable it immediately afterward.

You can enable the log directly in the MySQL shell without a restart:

-- Enable logging
SET GLOBAL general_log = 'ON';

-- Save to a traditional text file
SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = '/var/log/mysql/query_debug.log';

If you prefer using SQL to query the logs for convenience, change the output to TABLE. The data will be stored in the mysql.general_log table. However, logging to a table puts more pressure on the storage engine than logging to a file.

When Do You Actually Need It?

  • Checking if the application is sending redundant queries (N+1 queries).
  • Auditing access when you suspect unauthorized users are accessing sensitive tables.
  • Debugging business logic errors that the Error Log cannot catch.

3. A Hard-Learned Lesson: Don’t Let Logs Fill Up Your Disk

A colleague of mine once forgot to disable the General Log on a server with 5,000 requests/s traffic. As a result, a 200GB hard drive filled up completely overnight, causing the database to lock up. It’s a basic but extremely common mistake.

The Solution: Configuring Log Rotation

Utilize logrotate on Linux to automatically compress and delete old logs. Create a file at /etc/logrotate.d/mysql-server with the following content:

/var/log/mysql/*.log {
    daily
    rotate 7
    missingok
    compress
    postrotate
        mysqladmin flush-logs
    endscript
}

This configuration keeps the 7 most recent log files. The system will automatically compress them to save space, allowing you to sleep soundly.

4. A Quick Comparison of Log Types

Don’t confuse their purposes. Remember these three rules:

  1. Error Log: Use when MySQL won’t start or has crashed.
  2. General Log: Use to audit all actions (enable during logic debugging).
  3. Slow Query Log: Use to find slow-running statements (> 1s) for index optimization.

In practice, the Error Log should always be ON. The General Log should only be enabled for short periods (e.g., 15-30 minutes) to sample data.

Conclusion

Mastering log files is the first step toward becoming a Senior Database Engineer. Don’t wait for an incident to occur before looking for your configuration files. Check today to see if your Error Log is recording to the right place and if logrotate is active. Wishing you stable systems and fewer late nights!

Share:
Tags: