Extract Matching SQL Statements from MariaDB Logs

Digging one bad statement out of an old-style MySQL update log by eye is slow, and mysql_find_rows pulls it out for you instead. You finish with a repeatable way to select useful statements from an old-style MariaDB or MySQL update log without running those statements. The command reads SQL text and writes selected statements to standard output. It does not connect to a database, replay a log, or change data.

Allow about ten minutes. You need a shell, the mariadb-client package, and a log or other text file containing SQL statements terminated by semicolons. This guide uses MariaDB 10.11.14 from Ubuntu package mariadb-client 1:10.11.14-0ubuntu0.24.04.1. On this installation, mysql_find_rows is a symlink to mariadb-find-rows. No command here needs elevated privileges.

1. Check the installed command

Start by confirming which executable the shell will run and reading its built-in help:

$ command -v mariadb-find-rows
/usr/bin/mariadb-find-rows
$ mariadb-find-rows --help
/usr/bin/mariadb-find-rows takes the following options:

--help or --Information
  Shows this help

--regexp=#
  Print queries that matches this.

--start_row=#
  Start output from this row (first row = 1)

--skip-use-db
  Don't include 'use database' commands in the output.

--rows=#
  Quit after this many rows.

The utility has no useful --version option on this installation. Record the package version instead when a support note needs to be reproducible:

$ dpkg-query -W -f='${Package} ${Version}\n' mariadb-client
mariadb-client 1:10.11.14-0ubuntu0.24.04.1

Checkpoint: you have confirmed the binary and package. If the package query reports that the package is not installed, stop here and install it through your normal operating system change process rather than copying a different binary into place.

2. Inspect a log without a pattern

Pass one or more file names after the options. The command expects each statement to end with a semicolon:

$ mariadb-find-rows /var/log/mysql/update.log
USE shop;
SET sql_mode='STRICT_TRANS_TABLES';
UPDATE orders SET status='paid' WHERE id=42;

With no file names, it reads standard input. This is useful for a compressed or filtered stream, provided the upstream command produces the complete semicolon-terminated statements:

$ zcat /path/to/update.log.gz | mariadb-find-rows
USE shop;
SET sql_mode='STRICT_TRANS_TABLES';
UPDATE orders SET status='paid' WHERE id=42;

The default output includes USE database and SET statements, as well as statements that match the selected query condition. That default can surprise you when you are looking for one table. The output is text only; it is not a preview of a command that the utility will execute.

3. Select statements containing a pattern

Use --regexp with the text you need to find. The manpage calls this a regular expression, so quote it to keep the shell from interpreting special characters:

$ mariadb-find-rows --regexp='problem_table' /path/to/update.log
USE demo;
SET @trace_id = 17;
INSERT INTO problem_table VALUES (1);

On the installed command, matching output still includes the relevant USE and SET statements. This preserves context for a group of statements, but it means a regexp search is not necessarily a one-line or one-statement result. A multiline SQL statement is printed as one block, so redirect the result to a file if you need to review it carefully:

$ mariadb-find-rows --regexp='problem_table' /path/to/update.log > /tmp/problem-table.sql
$ less /tmp/problem-table.sql

Writing to /tmp changes only the temporary review file. Do not pipe the result into mariadb unless you have deliberately reviewed every statement and understand its effect. This utility extracts SQL; it does not make extracted SQL safe to execute.

4. Remove database switches from the result

Add --skip-use-db when the database-selection statements are noise or when another review process supplies the database context:

$ mariadb-find-rows --regexp='problem_table' --skip-use-db /path/to/update.log
SET @trace_id = 17;
INSERT INTO problem_table VALUES (1);

This option removes USE statements, not SET statements. Keep that distinction visible during incident review. A later SQL consumer may depend on a session setting, and removing database selection can also make a file ambiguous if it is ever replayed.

Checkpoint: compare the first few lines of the normal and skipped output. If a database name is needed to interpret an unqualified table, keep the original output or record the database separately.

5. Bound the amount of output

Use --rows to stop after a fixed number of output rows. In this tool, the count applies to statements printed, including default USE and SET output:

$ mariadb-find-rows --skip-use-db --rows=2 /path/to/update.log
SET @trace_id = 17;
INSERT INTO problem_table VALUES (1);

This is a review aid, not a guarantee that the first two statements are representative. A large log can contain a matching statement later, and a context statement can consume one of the available rows. Start with a small limit when testing a new regexp, then increase it deliberately.

To skip the first statement rows, use --start_row. The first row is numbered 1:

$ mariadb-find-rows --start_row=2 /path/to/update.log
SET @trace_id = 17;
INSERT INTO problem_table VALUES (1);
SELECT 2;

Do not confuse --start_row=2 with a line number or a byte offset. It starts output from the second statement as understood by the utility.

6. Diagnose empty or surprising output

First verify the input path and whether the file really contains semicolon-terminated SQL:

$ test -r /path/to/update.log && printf '%s\n' 'log is readable'
log is readable
$ sed -n '1,20p' /path/to/update.log

If there are no file names, check the pipe. A command that produces no data gives mariadb-find-rows nothing to print. If the output contains only USE or SET, the input was read successfully but no other statement matched the pattern, or the selected row range excluded the statements you wanted.

Check the exact spelling and quoting of the regexp. A shell expansion can change an unquoted pattern before the utility sees it. Test the simplest distinctive fragment first, such as problem_table, then add regexp detail. Keep the original log unchanged while investigating.

Malformed or non-SQL input is not repaired by this command. It was written for update logs used before MySQL 5.0 and expects semicolon terminators. For modern general-purpose logs, confirm the logging format before treating missing output as evidence that no matching query occurred.

Done means