The slow query log is the most useful file on your database server and the least likely to be read.
It sits at /var/log/mysql/mysql-slow.log, it grows, logrotate deletes it, and nobody looks. Meanwhile the query that has been taking three seconds since March is still taking three seconds.
This guide ships MySQL and MariaDB logs somewhere you will actually look at them. It also deals with the thing that makes database logs harder than web server logs: the slow query log is not one line per event.
Here is one entry from the slow query log:
# Time: 2026-08-07T09:14:02.123456Z
# User@Host: app[app] @ localhost [] Id: 12
# Query_time: 3.221000 Lock_time: 0.000100 Rows_sent: 1 Rows_examined: 4820391
SET timestamp=1754557442;
SELECT * FROM orders WHERE customer_id = 42;
That is one slow query across five lines.
The ingest endpoint splits on newlines. One line in, one row out. Ship that file raw and you get five rows, four of which are useless on their own. The query time is in one row and the query is in another, and no join puts them back together.
So do not ship the file. Get the database to give you one record per query instead.
MySQL and MariaDB can write the slow query log to a table instead of a file. The table has columns, which is exactly what you want.
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
Make it survive a restart. In /etc/mysql/my.cnf:
[mysqld]
log_output = TABLE
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
Now every slow query lands in mysql.slow_log:
SELECT start_time, query_time, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC LIMIT 5;
Set long_query_time with intent. The default is 10 seconds, which means you only ever see disasters. One second catches the queries that are actually costing you. Below 0.1 on a busy server, the log becomes the load.
log_queries_not_using_indexes is the setting people regret leaving on. It logs every full scan regardless of duration, including the trivial ones on ten-row lookup tables. Turn it on for a week while you fix things, then turn it off.
Now a small script reads new rows and posts them, one line each:
#!/bin/bash
# /usr/local/bin/ship-slow-queries.sh
set -euo pipefail
STATE=/var/lib/mysql-shipper/last_seen
ENDPOINT="https://logs.example.com/api/v1/ingest_logs/YOUR-SOURCE-TOKEN"
mkdir -p "$(dirname "$STATE")"
since=$(cat "$STATE" 2>/dev/null || echo "1970-01-01 00:00:00")
rows=$(mysql --defaults-file=/etc/mysql/shipper.cnf --batch --raw --skip-column-names -e "
SELECT JSON_OBJECT(
'source', 'mysql-slow',
'start_time', start_time,
'user_host', user_host,
'query_seconds', TIME_TO_SEC(query_time) + MICROSECOND(query_time) / 1000000,
'lock_seconds', TIME_TO_SEC(lock_time) + MICROSECOND(lock_time) / 1000000,
'rows_sent', rows_sent,
'rows_examined', rows_examined,
'db', db,
'sql_text', CONVERT(sql_text USING utf8mb4)
)
FROM mysql.slow_log
WHERE start_time > '$since'
ORDER BY start_time;
")
[ -z "$rows" ] && exit 0
printf '%s\n' "$rows" | curl -sf -X POST "$ENDPOINT" --data-binary @-
printf '%s\n' "$rows" | tail -n 1 \
| python3 -c "import sys,json; print(json.load(sys.stdin)['start_time'])" > "$STATE"
Run it every minute:
* * * * * /usr/local/bin/ship-slow-queries.sh
Four things in that script matter.
--raw is not optional. Batch mode normally escapes backslashes in output. JSON_OBJECT has already escaped them correctly, and escaping them a second time produces JSON that parses into the wrong string.
JSON_OBJECT handles the newlines for you. A multi-line SELECT becomes \n inside the JSON string, so the whole query still arrives as one line and one row. That is the entire reason this approach works and shipping the file does not.
sql_text is a blob. CONVERT(... USING utf8mb4) turns it into text. Without that you get a hex dump.
The offset advances only after the POST succeeds, because of set -e. A failed upload retries next minute rather than skipping an hour of queries.
Give the shipper its own read-only account rather than root:
CREATE USER 'logshipper'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT ON mysql.slow_log TO 'logshipper'@'localhost';
Put the credentials in /etc/mysql/shipper.cnf, mode 0600, so the password never appears in a command line or in ps:
[client]
user = logshipper
password = a-long-random-password
Then monitor the shipper with a cron ping. A log pipeline that quietly stopped looks exactly like a database with no slow queries.
mysql.slow_log has no automatic cleanup. On a busy server it grows until it is the biggest table you own.
Once the rows are shipped you do not need them locally:
5 * * * * /usr/bin/mysql --defaults-file=/etc/mysql/root.cnf -e "TRUNCATE TABLE mysql.slow_log" >/dev/null
Run the shipper first and the truncate on the hour, and give the shipper enough headroom that it never races the truncate. Losing a few slow queries to a race is survivable; losing the disk is not.
The error log is where crashes, failed connections and InnoDB complaints go. It is one line per event already, so it ships without any of the work above.
MySQL 8.0 can write the error log as JSON:
INSTALL COMPONENT 'file://component_log_sink_json';
SET PERSIST log_error_services = 'log_filter_internal; log_sink_json';
That writes to your error log path with .00.json appended. Ship it with an offset script — the same one from the Apache guide with the path changed.
The error log is plain text:
2026-08-07 09:14:02 0 [Warning] Aborted connection 4821 to db: 'app' user: 'app' host: 'localhost' (Got timeout reading communication packets)
Ship it as-is and search it with full-text search. These are the lines worth an alert rule:
[ERROR]
Aborted connection
Too many connections
InnoDB: Operating system error
Table ... is marked as crashed
Aborted connection deserves special attention. A handful is normal. A sudden run of them is a connection pool that has stopped recycling, and it usually appears an hour before the outage does.
The general query log records every statement. It is a debugging tool, not a logging strategy.
On a production database it will write more data than your application does, and it will slow the server down while doing it. If you need it, turn it on for a few minutes and turn it off again:
SET GLOBAL general_log = 'ON';
-- reproduce the problem
SET GLOBAL general_log = 'OFF';
Do not ship it continuously.
Central Logging stores logs in SQLite and runs SQL over them, so json_extract reaches each field.
The queries costing you the most, ranked by total time rather than worst case:
SELECT
substr(json_extract(msg, '$.sql_text'), 1, 120) AS query,
COUNT(*) AS runs,
ROUND(SUM(json_extract(msg, '$.query_seconds')), 1) AS total_seconds,
ROUND(AVG(json_extract(msg, '$.query_seconds')), 3) AS avg_seconds
FROM logs
WHERE json_extract(msg, '$.source') = 'mysql-slow'
GROUP BY query
ORDER BY total_seconds DESC
LIMIT 20;
Total time is the right ranking. A 4-second report that runs twice a day matters less than a 200ms query running forty times a second, and only this ordering shows you that.
Queries reading far more rows than they return, which is the signature of a missing index:
SELECT
"timestamp",
json_extract(msg, '$.rows_examined') AS examined,
json_extract(msg, '$.rows_sent') AS sent,
json_extract(msg, '$.sql_text') AS query
FROM logs
WHERE json_extract(msg, '$.source') = 'mysql-slow'
AND json_extract(msg, '$.rows_examined') > 10000
AND json_extract(msg, '$.rows_sent') < 100
ORDER BY examined DESC
LIMIT 50;
A query examining 4 million rows to return one is not slow because the database is busy. It is slow because it has no index to use.
Lock waits, which look like slow queries but are not:
SELECT
"timestamp",
json_extract(msg, '$.lock_seconds') AS lock_seconds,
json_extract(msg, '$.query_seconds') AS query_seconds,
json_extract(msg, '$.sql_text') AS query
FROM logs
WHERE json_extract(msg, '$.source') = 'mysql-slow'
AND json_extract(msg, '$.lock_seconds') > 0.1
ORDER BY lock_seconds DESC
LIMIT 50;
If lock_seconds is most of query_seconds, the query is fine and something else is holding a lock. Optimising the query would waste your afternoon.
The timestamp column is when the log server received the line, not when the query ran. Shipping every minute makes them close, but a batch that arrives after a network outage sorts as if it all just happened.
Sort by the database’s own clock when order matters:
ORDER BY json_extract(msg, '$.start_time') DESC
Turn the useful queries into alert rules. This one tells you a query has crossed from slow to broken:
SELECT COUNT(*) AS bad
FROM logs
WHERE json_extract(msg, '$.source') = 'mysql-slow'
AND json_extract(msg, '$.query_seconds') > 10
HAVING bad > 0;
Rules run every five minutes and notify through Slack, Telegram or Pushover. Set a max frequency, or one pathological query will message you all night.
The slow query log is multi-line, so shipping the file gives you fragments. Log to mysql.slow_log instead, ship it as one JSON object per query, and truncate the table on a schedule.
Rank by total time, not worst case. Watch rows_examined against rows_sent for missing indexes. Ship the error log as plain text and alert on Aborted connection.
Central Logging is a single binary you install on a server you already own. See Deploy to get an instance running in about five minutes.
💌 Get notified on new features and updates