Monitor MariaDB Safely with innotop

You will finish with a read-only innotop session for checking MariaDB queries, InnoDB transactions and lock waits, plus a repeatable non-interactive snapshot for scripts. The examples use innotop 1.11.4 from the installed mariadb-client package version 1:10.11.14-0ubuntu0.24.04.1. Allow about fifteen minutes if your database credentials already work.

You need a shell, the mariadb-client package and an account that can connect to the target server. Most monitoring is unprivileged at the Linux level. Database privileges still control what innotop can see: the manual specifically calls out PROCESS for the live query list, and some administration actions need more privilege.

1. Check the installed command

Confirm which binary will run and record its version. This is an ordinary read-only check:

$ command -v innotop
/usr/bin/innotop
$ innotop --version
innotop  Ver 1.11.4

Checkpoint: if the command is missing, stop here and install the client through your normal package-management process. Do not copy a configuration from another host yet; innotop's format and defaults are version-sensitive.

2. Start the local interactive monitor

Run innotop without a filename:

$ innotop

With no options, the installed manpage says it attempts a local connection and uses the MariaDB client option group for other connection parameters. If the login succeeds, the first view is the query list. Press ? to see keys active in the current mode and press q to quit.

Uppercase keys switch modes. Press T for InnoDB transactions, K for lock waits, D for the last InnoDB deadlock, M for replication status, and B for InnoDB buffer information. The exact tables depend on the server and the selected mode. A blank view is not automatically a fault: some modes only show rows when the relevant condition exists.

Do not treat the display as a full audit. The query list depends on database permissions, and the values are sampled at refresh ticks. Press ? before trying an action so you know whether a key only changes the display or sends SQL to the server.

3. Connect to a named server without putting a password in shell history

Use the host, user and port options when the target is not the local default:

$ innotop --host db.example.test --port 3306 --user monitor

The program can prompt for a password. Prefer that prompt or a protected MariaDB client option file. The manpage documents --password, but a password written directly on a command line can be exposed through shell history or process inspection, so do not use this form with a real secret:

$ innotop --host db.example.test --user monitor --password 'REPLACE_ME'

Replace the placeholder only in a private test, then remove it from history. A safer operational pattern is to configure the client credentials using your normal MariaDB option-file controls and let innotop read the client group.

Checkpoint: if authentication fails, test the same host, port and account with your regular MariaDB client. If the query list is empty but the connection works, investigate database privileges rather than adding sudo; Linux root access does not grant a MariaDB account the PROCESS privilege.

4. Inspect an InnoDB status file without connecting to a server

Pass a filename when you need to examine captured InnoDB status output or a server error log containing it:

$ innotop /var/log/mysql/mysqld.err

This changes the data source completely. innotop does not connect to any server in file-monitoring mode; it watches the named file and presents one connection called file. Its displayed uptime is the time since innotop started, not the database server's real uptime. Check the file before launching:

$ test -r /path/to/status-file && echo readable
readable
$ innotop /path/to/status-file

Reading a protected log may require elevated privileges, but use the smallest safe access change available. Do not make a database log world-readable just to avoid a permission error.

5. Capture a finite, tab-separated snapshot

For a script or a quick comparison, use non-interactive mode with a finite count and delay:

$ innotop --nonint --count 5 --delay 1 --host db.example.test --user monitor

--nonint prevents screen clearing and emits tab-separated table data. --count 5 requests five refresh ticks and --delay 1 pauses one second between ticks. The first output may look sparse when incremental measurements need a previous sample. In incremental mode, the manual says the first tick can be suppressed and an extra refresh can be performed, so do not assume that no immediate output means a hung process.

Save output only after deciding whether it can contain query text or other sensitive data:

$ umask 077
$ innotop --nonint --count 5 --delay 1 --mode Q --host db.example.test --user monitor > innotop-query.tsv
$ test -s innotop-query.tsv && echo snapshot-created
snapshot-created

The --mode option selects the starting mode; Q is the query list. The file can contain SQL and connection details, so keep it in a directory with appropriate permissions and remove it according to your retention policy. The command itself does not alter the database.

6. Keep configuration changes deliberate

innotop looks for $HOME/.innotop first and then /etc/innotop/innotop.conf. You can select another file with --config:

$ innotop --config /path/to/innotop.conf --nonint --count 1 --delay 1

The configuration is read-only by default. During an interactive session, $ opens the configuration editor and g reaches general settings. The command-line --write option changes that safety boundary: if no configuration file was loaded, innotop can write the running configuration to ~/.innotop/innotop.conf when it exits.

Warn before using --write: it changes state in your home directory and can save connection details. In particular, passwords stored in the innotop configuration are plain text, not encrypted. If you accidentally saved a password there, stop innotop, remove the secret from the configuration using your normal credential-rotation procedure, restrict the file permissions, and rotate the database password if exposure is possible.

To undo an experimental configuration, quit without --write and restore the previous file from your backup. Do not edit a configuration while innotop is running if it is writable; the manual warns that the running process can write its own view over those edits on exit.

7. Treat administration keys as production actions

innotop can do more than observe. In query or transaction views, k can issue KILL and x can issue KILL QUERY. In replication mode, other keys can start or stop replication. These actions can terminate work or disrupt service.

Before confirming any such prompt, identify the server, connection ID and query, check the maintenance context, and confirm the action with the requested y. If you only need evidence, quit and collect it without pressing an administration key. Do not use the deadlock-wipe feature or create/drop monitoring tables as a first diagnostic step: those operations change database state and require suitable privileges.