MySQL Error Log Table Explained: Using performance_schema for Error Analysis

It is 2:47 a.m. and your on-call rotation just fired. A MySQL replica is throwing intermittent connection errors, and your instinct is to SSH into the box and run tail -f /var/log/mysql/error.log. You scroll through thousands of timestamped lines, squinting at plain text, trying to correlate the flood of InnoDB warnings with the exact window when replication lag spiked. You grep for ERROR, pipe through awk, and still cannot tell whether this is an authentication failure, a tablespace issue, or a network blip — because the log format gives you no structured field to filter on. If you are running MySQL 8.0.22 or later, there is a dramatically better way: query the error log with SQL, directly from the server that generated it.

Why MySQL 8.0 Error Logging Is Different

Before MySQL 8.0, the error log was a monolithic, server-controlled text file. Its format was fixed, its destination was fixed, and consuming it programmatically meant parsing free-form strings that could change across patch releases. MySQL 8.0 redesigned error logging around a composable pipeline of log components: filters that decide which events pass through, and sinks that write those events to a destination.

The component architecture is controlled by the log_error_services system variable, which accepts a semicolon-delimited list of component names loaded in order:

-- View the current logging pipeline
SHOW VARIABLES LIKE 'log_error_services';
-- log_error_services: log_filter_internal; log_sink_internal

-- The default pipeline:
--   log_filter_internal  →  applies verbosity filtering (log_error_verbosity)
--   log_sink_internal    →  writes to the traditional error log file

Each event in the pipeline carries a structured set of fields: timestamp, priority (ERROR, WARNING, INFORMATION), error code, subsystem (e.g., InnoDB, Repl, Server), and the human-readable message. These fields are what performance_schema.error_log surfaces as columns.

The performance_schema.error_log Table: Columns and Semantics

Available since MySQL 8.0.22, performance_schema.error_log is an in-memory ring buffer of recent error log events. It holds up to 1,000 events by default and is populated regardless of which log sinks are configured. Here is the full column set:

DESCRIBE performance_schema.error_log;

-- Field          Type              Null  Key  Default
-- LOGGED         timestamp(6)      NO         NULL
-- THREAD_ID      bigint unsigned   YES        NULL
-- PRIO           enum(...)         NO         NULL    -- 'ERROR','WARNING','INFORMATION','SYSTEM'
-- ERROR_CODE     varchar(10)       YES        NULL    -- e.g. 'MY-010116'
-- SUBSYSTEM      varchar(7)        YES        NULL    -- e.g. 'Server','InnoDB','Repl'
-- DATA           text              YES        NULL    -- the full log message

A few important notes on these columns: LOGGED is a microsecond-precision timestamp in the server's local timezone. PRIO uses an enumeration — ERROR is the most severe, SYSTEM is used for start/stop lifecycle events. ERROR_CODE follows the MY-NNNNNN format introduced in MySQL 8.0. SUBSYSTEM identifies the server component that raised the event. DATA contains the full message text, which is also what gets written to the file-based log sinks.

Querying the Error Log: Practical SQL Patterns

Filtering by Recency

The most common operational query: show everything that happened in the last hour, ordered newest first.

SELECT
    LOGGED,
    PRIO,
    ERROR_CODE,
    SUBSYSTEM,
    LEFT(DATA, 200) AS message
FROM performance_schema.error_log
WHERE LOGGED > NOW() - INTERVAL 1 HOUR
ORDER BY LOGGED DESC;

Filtering by Severity

During an incident, you want only hard errors, not the constant stream of informational messages about connections opening and closing.

-- Show only ERROR-level events from the last 24 hours
SELECT
    LOGGED,
    ERROR_CODE,
    SUBSYSTEM,
    DATA
FROM performance_schema.error_log
WHERE PRIO = 'ERROR'
  AND LOGGED > NOW() - INTERVAL 24 HOUR
ORDER BY LOGGED DESC;

-- Count errors by subsystem to identify the noisiest component
SELECT
    SUBSYSTEM,
    COUNT(*) AS error_count
FROM performance_schema.error_log
WHERE PRIO = 'ERROR'
  AND LOGGED > NOW() - INTERVAL 6 HOUR
GROUP BY SUBSYSTEM
ORDER BY error_count DESC;

Filtering by Error Code

MySQL 8.0 assigns a unique code to every error event. This is far more reliable than grepping for text fragments that can vary across versions. To find all occurrences of the "Access denied" authentication failure error:

-- MY-000031 = Can't connect to MySQL server (client-side network error signal)
-- MY-010116 = Aborted connection (authentication, timeout, or network drop)
-- MY-013360 = Plugin 'sha256_password' reported: Authentication requires secure connection

SELECT LOGGED, THREAD_ID, SUBSYSTEM, DATA
FROM performance_schema.error_log
WHERE error_code = 'MY-010116'
ORDER BY LOGGED DESC
LIMIT 50;

Replication and InnoDB Focused Queries

-- Replication errors only
SELECT LOGGED, ERROR_CODE, DATA
FROM performance_schema.error_log
WHERE SUBSYSTEM = 'Repl'
  AND PRIO IN ('ERROR', 'WARNING')
  AND LOGGED > NOW() - INTERVAL 3 HOUR
ORDER BY LOGGED DESC;

-- InnoDB warnings in the last 30 minutes
SELECT LOGGED, ERROR_CODE, DATA
FROM performance_schema.error_log
WHERE SUBSYSTEM = 'InnoDB'
  AND PRIO = 'WARNING'
  AND LOGGED > NOW() - INTERVAL 30 MINUTE
ORDER BY LOGGED DESC;

Configuring Error Log Verbosity

The log_error_verbosity system variable controls how much the log_filter_internal component lets through before events reach any sink. It accepts values 1, 2, or 3:

-- 1 = ERROR only (minimal, production default for quiet systems)
-- 2 = ERROR + WARNING (recommended for most production deployments)
-- 3 = ERROR + WARNING + INFORMATION (verbose; use during debugging only)

SET GLOBAL log_error_verbosity = 2;

-- Persist across restarts (add to my.cnf / my.ini):
-- [mysqld]
-- log_error_verbosity = 2

Enabling JSON Format: log_sink_json

The traditional text log is useful for human reading but painful for machines. The log_sink_json component writes each log event as a JSON object on its own line — a format that Elasticsearch, Datadog, Splunk, Loki, and Fluent Bit all ingest natively without custom grok patterns.

-- Step 1: Install the JSON sink component (requires SYSTEM_VARIABLES_ADMIN privilege)
INSTALL COMPONENT 'file://component_log_sink_json';

-- Step 2: Update the log_error_services pipeline to include the JSON sink
-- The filter runs first, then both sinks receive filtered events in parallel
SET PERSIST log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';

-- The JSON log file is written to the same directory as log_error,
-- with a .json extension appended: e.g., /var/log/mysql/error.log.json

A sample JSON log event looks like this:

{
  "prio": 2,
  "err_symbol": "ER_ACCESS_DENIED_ERROR",
  "label": "Warning",
  "msg": "Access denied for user 'app_user'@'10.0.1.45' (using password: YES)",
  "time": "2026-02-23T02:47:13.482310Z",
  "ts": 1740272833,
  "thread": 412,
  "err_code": 1045,
  "subsystem": "Server",
  "source_file": "sql_acl.cc",
  "source_line": 7891
}

Feed this file to a Filebeat or Fluent Bit agent with the JSON codec, and every field maps directly to a structured index in Elasticsearch or a Datadog log attribute — no parsing configuration required.

Sending Errors to the System Event Log

On Linux, log_sink_syseventlog sends events to the syslog facility, where they can be collected by journald, rsyslog, or any syslog-compatible aggregator. On Windows, it writes to the Windows Event Log.

INSTALL COMPONENT 'file://component_log_sink_syseventlog';

-- Add syseventlog to the pipeline after your filter
SET PERSIST log_error_services = 'log_filter_internal; log_sink_internal; log_sink_syseventlog';

-- View in journald on systemd-based Linux:
-- journalctl -u mysql --since "1 hour ago" -p err

Comparing SQL Queries to tail -f

The traditional approach to MySQL error log analysis works like this: SSH to the database host, locate the error log file path from SHOW VARIABLES LIKE 'log_error', then run tail -f, grep, and awk pipelines to extract what you need. This works, but it has real production drawbacks.

With performance_schema.error_log, you query the log from any MySQL client, without host access, using the same credentials and audit trail as any other database operation. You can join the log against other performance_schema tables, aggregate by subsystem, correlate timestamps with slow query log events from performance_schema.events_statements_history_long, and write monitoring queries that fire alerts through your existing SQL-based tooling.

-- Example: correlate error log spikes with high-latency statements
-- Find threads that generated errors and check their recent query history
SELECT
    el.LOGGED,
    el.ERROR_CODE,
    el.DATA AS error_message,
    esh.SQL_TEXT,
    esh.TIMER_WAIT / 1e9 AS query_duration_ms
FROM performance_schema.error_log el
JOIN performance_schema.events_statements_history_long esh
    ON esh.THREAD_ID = el.THREAD_ID
WHERE el.PRIO = 'ERROR'
  AND el.LOGGED > NOW() - INTERVAL 1 HOUR
ORDER BY el.LOGGED DESC
LIMIT 20;

RDS and Aurora: Accessing Error Logs

On Amazon RDS for MySQL and Aurora MySQL, you cannot read the error log file directly from the filesystem — the underlying host is managed by AWS. You have three options:

Option 1: performance_schema.error_log (recommended for real-time queries). The table is fully available on RDS MySQL 8.0.22+ and Aurora MySQL 3.x. All SQL queries shown above work without modification.

Option 2: AWS Console. In the RDS console, navigate to your instance, select the Logs & events tab, and choose the error/mysql-error.log file. You can view and download the last portion of the log file directly from the browser.

Option 3: AWS CLI. For scripting and automation, use the RDS download-db-log-file-portion API:

# Download the last 10,000 lines of the error log
aws rds download-db-log-file-portion \
    --db-instance-identifier my-mysql-instance \
    --log-file-name error/mysql-error.log \
    --starting-token 0 \
    --output text

# List all available log files for an instance
aws rds describe-db-log-files \
    --db-instance-identifier my-mysql-instance \
    --query 'DescribeDBLogFiles[*].LogFileName'

A Production Monitoring Query Worth Bookmarking

This query gives you a high-level health snapshot of your MySQL error log in one pass. Run it from your monitoring system every 5 minutes and alert when any ERROR count exceeds your threshold:

SELECT
    DATE_FORMAT(LOGGED, '%Y-%m-%d %H:%i:00') AS minute_bucket,
    PRIO,
    SUBSYSTEM,
    ERROR_CODE,
    COUNT(*)                                 AS event_count,
    LEFT(MIN(DATA), 150)                     AS sample_message
FROM performance_schema.error_log
WHERE LOGGED > NOW() - INTERVAL 15 MINUTE
GROUP BY
    DATE_FORMAT(LOGGED, '%Y-%m-%d %H:%i:00'),
    PRIO,
    SUBSYSTEM,
    ERROR_CODE
HAVING PRIO = 'ERROR'
ORDER BY minute_bucket DESC, event_count DESC;

Structured error log access is one of many areas where MySQL 8.0 closes the operational gap with enterprise database platforms. If your team is still grep-parsing plain-text log files in production, migrating to a proper log pipeline — JSON sink, external aggregation, and SQL-based alerting queries against performance_schema.error_log — is a high-leverage change that pays back the first time you debug a 3 a.m. incident in minutes instead of hours. For hands-on help designing your MySQL observability stack, log component configuration, or migrating to MySQL 8.0 from an older release, the JusDB team is available at jusdb.com.