Check and Administer MariaDB Safely with mysqladmin

Is the database actually up, or has it just stopped answering mysqladmin's ping? That single check is the start of a small, repeatable workflow for checking a MariaDB server, reading useful status information, and carrying out a few controlled administrative tasks. The command is the MariaDB client name mariadb-admin exposed through a compatibility symlink. On this machine it comes from MariaDB 10.11.14, package mariadb-client.

Allow about fifteen minutes. You need a shell, the client package, a MariaDB account, and permission to perform any administrative operation you choose. Most checks are ordinary commands. Database deletion, password changes, log flushing, replication actions and shutdown are service or security changes: read the warning beside each example before running it.

1. Confirm the installed client

Start with a local, read-only check. Use --no-defaults as the first option when you want to rule out unexpected settings from option files:

$ command -v mysqladmin
/usr/bin/mysqladmin
$ readlink -f "$(command -v mysqladmin)"
/usr/bin/mariadb-admin
$ mysqladmin --no-defaults --version
mysqladmin  Ver 10.0 Distrib 10.11.14-MariaDB, for debian-linux-gnu on x86_64

The executable reports its client build as version 10.0 while the distribution identifies it as MariaDB 10.11.14. That is the installed program's own output, not the version of a server it may later contact. The version command, covered below, asks the server and therefore needs a working connection.

Checkpoint: If the path is not the client you expect, stop here. Check the package and your PATH before troubleshooting authentication.

2. Make the connection explicit

The general form is one or more commands after the options:

$ mysqladmin [OPTIONS] command [command-arg] [command [command-arg]] ...

Connection settings can come from the command line or option-file groups such as [mysqladmin] and [client]. For a one-off test, make the host, protocol and account visible:

$ mysqladmin --no-defaults \
    --protocol=socket \
    --socket=/run/mysqld/mysqld.sock \
    --user=DB_ADMIN \
    --password \
    ping

Because --password has no value, the client prompts without putting the secret in the shell history or process list. Avoid --password=SECRET and the compact -pSECRET form; a password on the command line is considered insecure. Replace the socket and account with values for your host. If the server listens over TCP, use --host=DB_HOST --port=3306 --protocol=tcp instead.

Option-file selection has a distracting ordering rule: --no-defaults, --defaults-file=FILE and --defaults-extra-file=FILE must be first options. A small private file can keep routine connection details out of repeated commands:

[mysqladmin]
user=DB_ADMIN
socket=/run/mysqld/mysqld.sock
$ mysqladmin --defaults-extra-file=/path/to/admin.cnf --password ping

Protect a file containing connection credentials with normal filesystem permissions and do not paste its contents into tickets or chat.

3. Check whether the server is alive

Run ping for a simple liveness check:

$ mysqladmin --no-defaults --user=DB_ADMIN --password ping
mysqld is alive

On a healthy server, the command normally prints mysqld is alive and returns status 0. Capture the status immediately if a script needs it:

if mysqladmin --no-defaults --user=DB_ADMIN --password ping; then
    printf '%s\n' 'MariaDB is responding'
else
    status=$?
    printf 'MariaDB ping failed with status %s\n' "$status" >&2
    exit "$status"
fi

A non-zero result normally means the server is not reachable. The manpage makes a subtle distinction: an access-denied response still proves that the server is running, so ping can return 0 even when the account cannot authenticate. Treat authentication errors as a separate operational problem rather than declaring the service down.

On this machine there is no local server socket, so the safe probe below returns 1 and reports that /run/mysqld/mysqld.sock does not exist:

$ mysqladmin --no-defaults --protocol=socket ping
mysqladmin: connect to server at 'localhost' failed
...
$ printf 'ping exit: %s\n' "$?"
ping exit: 1

4. Read status and active work

Once the connection works, status gives a compact snapshot including uptime, active threads, questions, slow queries, open tables and average queries per second:

$ mysqladmin --no-defaults --user=DB_ADMIN --password status
Uptime: ...  Threads: ...  Questions: ...
Slow queries: ...  Opens: ...  Flush tables: ...
Open tables: ...  Queries per second avg: ...

The values are server counters, so they change between runs. Use processlist to see active server threads:

$ mysqladmin --no-defaults --user=DB_ADMIN --password processlist

Add --verbose for the equivalent of a fuller process list. Treat query text as sensitive operational data and avoid copying it into public logs. To inspect server variables, use variables; use extended-status for status variables and their values. The shorter stat and proc prefixes work here because they are unique, but full command names are clearer in scripts.

For a repeated view, combine --sleep=SECONDS with --count=N:

$ mysqladmin --no-defaults --user=DB_ADMIN --password \
    --sleep=5 --count=6 status

This executes the command six times with a five-second delay. Without --count, do not assume a repeated command will stop when you look away. The --relative option is documented for differences between repeated extended-status results only.

5. Create a database, then verify it

Creating an empty database changes server state but does not require a shell-level elevated privilege. The MariaDB account needs the relevant database privilege:

$ mysqladmin --no-defaults --user=DB_ADMIN --password create REPORTING_DB
$ mysqladmin --no-defaults --user=DB_ADMIN --password status

mysqladmin does not provide a database listing command. Verify the result with the SQL client, if installed:

$ mariadb --no-defaults --user=DB_ADMIN --password \
    --execute='SHOW DATABASES LIKE "REPORTING_DB";'
Database (REPORTING_DB)
REPORTING_DB

If creation was accidental and the database is still empty, remove it with the command in the next section. Do not treat that as a general undo operation: deleting a database removes its tables and data.

6. Handle destructive and service-disrupting commands

Warning: drop deletes a database and all its tables. It is irreversible unless you have a tested backup. The normal command asks for confirmation:

$ mysqladmin --no-defaults --user=DB_ADMIN --password drop REPORTING_DB
Dropping the database is potentially a very destructive action.
Type "yes" to confirm, anything else to cancel: yes

Do not add --force in a recovery script unless you have separately confirmed the exact database name. It suppresses this confirmation and also tells the client to continue after an error when multiple commands are supplied.

Warning: shutdown stops the server and interrupts clients. Use it only during an approved maintenance window, and confirm the target host before pressing enter:

$ mysqladmin --no-defaults --host=DB_HOST --user=DB_ADMIN --password shutdown

When a local socket connection is used, the client waits for the server process ID file to disappear. That is a useful completion check, but it does not make shutdown safe to run on the wrong host. To recover, start MariaDB through the service manager used by your distribution, for example the documented unit for that host, and then rerun mysqladmin ping.

Other commands also change live behaviour. flush-logs, flush-tables, refresh, replication start or stop commands, kill, and password should be treated as maintenance actions. A password change affects the account used by the client, so update its secure option file or secret store afterwards. Never use the password command with --skip-grant-tables; the manpage warns that no password change is applied in that mode.

Done means