mariadb-client

MariaDB-specific behavior of the `mariadb` command-line client — batch mode auto-enables silent/tab output, `\G`/`\g` and client-side `DELIMITER` for stored-routine bodies, `--safe-updates` sets `max_

SKILL.md

GitHub

mariadb Command-Line Client

Last updated: 2026-07-20

mariadb is the interactive/batch SQL shell for MariaDB, built on GNU readline. The binary is mariadb (since 10.5); a symlink named mysql is kept on Unix for compatibility, and mysql.exe ships as an alternate binary on Windows — both invoke the identical client.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Features available across current LTS branches (10.6, 10.11, 11.4, 11.8) are shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

If the agent writes / suggests… …prefer the MariaDB form
Pipes a script and expects the usual box-drawn table output Non-interactive input (piped stdin, < file.sql, -e, --batch) auto-switches to tab-separated, unboxed output and disables the history file — this is --batch's effect, triggered automatically, not just via the flag. Force table output back with --table/-t
Sets --safe-updates and then writes SET sql_max_join_size=... to raise the join-size cap That variable name doesn't exist — the session variable is max_join_size (no sql_ prefix). --safe-updates itself sends SET SQL_SAFE_UPDATES=1, SQL_SELECT_LIMIT=n, MAX_JOIN_SIZE=n as an init command; only two of the three carry the sql_ prefix
Assumes the client defaults to latin1 because older docs said so The client auto-detects the character set from the OS locale (LC_CTYPE on Unix) at connect time; if that fails, it falls back to the server's compiled-in default, utf8mb4 — not latin1. Force one explicitly with --default-character-set=name
Writes a stored routine body over the CLI and lets each internal ; end the statement The client, not the server, splits statements on the delimiter. Wrap BEGIN…END blocks: DELIMITER // … body ending END // … DELIMITER ;. DELIMITER is a client-side command, invisible to the server and to other clients
Ends every statement the same way and expects table output for one long row Terminate that one statement with \G (or ego) instead of ; for vertical (one-line-per-column) output — no flag needed, and it works even under a custom DELIMITER
Puts -p mypassword (space before the password) on the command line -p takes no separated argument — -p mypassword is parsed as -p (prompts) plus a positional mypassword database name. Use -pmypassword (no space) or, better, --password with no value to be prompted; a password on the command line is visible in shell history and briefly in the process list
Assumes -e "sql" behaves like interactive mode, just non-interactive -e/--execute forces batch output, disables --force, and skips the history file — same code path as --batch (status.batch=1)
Writes UPDATE/DELETE scripts for an agent to run unsupervised and worries about a missing WHERE --safe-updates (alias --i-am-a-dummy, -U) blocks UPDATE/DELETE unless the WHERE uses a key or a LIMIT is given, caps SELECT at select_limit rows (default 1000) and joins at max_join_size row combinations (default 1,000,000)
Assumes TLS server-certificate verification is opt-in like older MariaDB versions (since 11.4) --ssl is on by default, and --ssl-verify-server-cert is now on by default too (previously off) — use --skip-ssl-verify-server-cert to opt out
Reaches for mysql_history env var name to disable the history file The variable is MYSQL_HISTFILE (default ~/.mysql_history); point it at /dev/null or symlink the file there

Invocation & Naming

mariadb db_name
mariadb --user=user_name --password db_name   # prompts for the password
mariadb db_name < script.sql > output.tab      # batch: tab-separated output

Companion option groups read from option files: [mysql], [mariadb-client], [client], [client-server], [client-mariadb] — in that order, so a mariadb-client group entry can override a broader [client] one for this binary specifically.

Connection Options

Flag Short Notes
--host= -h Connect to a host; localhost triggers the Unix socket, 127.0.0.1 forces TCP
--port= -P Default 3306, resolved via option file / $MYSQL_TCP_PORT / built-in default
--socket= -S Unix socket path (or named-pipe name on Windows)
--user= -u Defaults to the current OS user
--password[=] -p No value → prompt. -p (short form) must not have a space before the value
--protocol= tcp, socket, pipe, or memory — forces the transport
--ssl / --ssl-ca= / --ssl-cert= / --ssl-key= TLS is enabled automatically once any --ssl-* option is set
--ssl-verify-server-cert On by default (since 11.4); --skip-ssl-verify-server-cert to disable
--default-character-set= Force a charset instead of auto-detecting from the locale
--defaults-file= / --no-defaults / --defaults-extra-file= / --defaults-group-suffix= Must be the first argument if given
--max-allowed-packet= Default 16 MiB, min 4 KiB, max 2 GiB
--reconnect On by default: auto-reconnects once on a dropped connection, silently losing session state (temp tables, @vars, current transaction). Disable with --skip-reconnect to fail loudly instead
--connect-timeout= Seconds before giving up; default 0 (no timeout)

Batch vs. Interactive

Interactive use (a TTY) defaults to boxed table output and keeps a history file (~/.mysql_history, override via $MYSQL_HISTFILE). Batch mode — triggered by --batch/-B, -e/--execute, or simply piping/redirecting input — switches to tab-separated output, disables the history file, and enables --silent. Force table-style output back in batch mode with --table/-t; force vertical with --vertical/-E or a trailing \G.

mariadb appdb -e "SELECT id, name FROM users LIMIT 5"   # batch-style output, no TTY needed
mariadb appdb < migration.sql                            # runs a script, non-interactively

--force/-f continues past SQL errors in a script instead of aborting (also flips --abort-source-on-error off for the source command); --verbose/-v (repeatable, -vvv = table format) and --unbuffered/-n (flush after every query) are the other common batch-script flags.

Client Commands

These run inside the shell, not sent to the server; each has a backslash shortcut and, unless --binary-mode is set, must start the line:

Command Shortcut Effect
go \g Send the statement (default terminator, same as ;)
ego \G Send the statement, display the result vertically
delimiter \d Change the statement terminator — client-side only, needed for BEGIN…END bodies
source \. Run a .sql script file
tee \T Append all output to a file (notee/no-arg \t to stop)
pager \P Pipe result output through a pager (Unix only; falls back to $PAGER)
edit \e Open the current statement in $EDITOR
status \s Show connection info, including which protocol was actually used
system \! Run a shell command (Unix only)
prompt \R Change the prompt (see sequences below)
charset \C Switch the client's session character set
use \u Switch the default database
connect \r Reconnect, optionally to a different db/host
clear \c Discard the statement typed so far
warnings/nowarning \W/\w Toggle auto-SHOW WARNINGS after every statement
help \h In-client help; help <topic> also queries server-side HELP

prompt sequences worth knowing: \d current database, \h host, \u user, \p port/socket, \R/\r time (24h/12h), \v server version, \_ space, \n newline. Set a persistent one with --prompt= or the PROMPT environment variable.

(since 12.0) --script-dir= scopes where source looks for script files, instead of the working directory. (since 10.6.18, backported across all current branches) --sandbox / the \- command disable filesystem-touching commands (system, tee, source, pager with an argument) for the rest of the session — useful when piping untrusted SQL through the client.

Output Formats

Flag Short Effect
--batch -B Tab-separated, one row per line; implies --silent, skips history
--table -t Boxed ASCII table (interactive default)
--vertical -E One column per line, per row — same as ending a statement with \G
--xml -X XML output
--html -H HTML <table> output
--raw -r In batch/silent output, stop escaping \n \t \0 \\ — needed for exact-bytes piping
--skip-column-names -N Omit the header row
--silent -s Less chatty; repeatable for progressively less output

Safety for Scripted/Unattended Use

--safe-updates (aka --i-am-a-dummy, both map to -U) is the guardrail for agent-generated UPDATE/DELETE: it refuses to run either statement unless the WHERE clause references a key or a LIMIT is present, and it issues SET SQL_SAFE_UPDATES=1, SQL_SELECT_LIMIT=<n>, MAX_JOIN_SIZE=<n> as the connection's init command. Tune the caps with --select-limit= (default 1000) and --max-join-size= (default 1,000,000) — note the session variable behind the second one is max_join_size, without the sql_ prefix that sql_safe_updates/sql_select_limit carry.

mariadb --safe-updates --select-limit=500 --max-join-size=10000 appdb

--local-infile[=0|1] toggles whether LOAD DATA LOCAL INFILE is even offered to the server (it also needs server-side local_infile=1 to actually work) — leave it off for scripts that ingest untrusted file paths.

Character Set

The client does not hardcode latin1. At connect time it resolves the charset from, in order: an explicit --default-character-set=, then the OS locale (LC_CTYPE on Unix, the console code page on Windows), then the compiled-in server default (utf8mb4). If terminal output looks mangled, set --default-character-set=utf8mb4 (or auto) explicitly rather than assuming a latin1 default that no longer applies.

Examples

# One-shot query, script-friendly output:
mariadb appdb -e "SELECT COUNT(*) FROM orders"

# Run a migration, stop on the first error, log everything:
mariadb --force appdb < migration.sql | tee migration.log

# Safe interactive session for ad hoc fixes on production:
mariadb --safe-updates -h prod-db -u app_ro appdb

# Vertical output for a wide row, custom delimiter for a routine body:
mariadb appdb <<'SQL'
DELIMITER //
CREATE PROCEDURE demo() BEGIN SELECT 1; END //
DELIMITER ;
SELECT * FROM orders WHERE id = 42\G
SQL

See Also

<sub>This page is: Copyright © 2026 MariaDB. All rights reserved.</sub>