MARIADB(1)

🌸 MARIADB(1) — the MariaDB Database System's command-line tool 🌸

okay bestie, buckle up, this is the mariadb manual page (it lives in the MariaDB Database System section of your man pages) and it's a whole journey. we're doing this together. 🫶

---

💖 NAME 💖

mariadb — the MariaDB command-line tool. and yes, plot twist: mysql is now a symlink to mariadb. iconic behavior honestly.

---

✨ SYNOPSIS ✨

mariadb [options] db_name

---

🦋 DESCRIPTION 🦋

so mariadb is a simple SQL shell, but make it GNU readline-capable. she does interactive and non-interactive use, she contains multitudes.

• when you use her interactively, query results show up in an ASCII-table format. very demure, very mindful.
• when you use her non-interactively (like, as a filter, slay), the result comes out in tab-separated format.

and guess what — the output format can be changed with command options. you're not locked in, bestie.

if you're having problems because there isn't enough memory for big result sets, use the --quick option. that forces mariadb to grab results from the server one row at a time instead of pulling the entire result set and buffering it all in memory before showing it. how does it do that? by returning the result set using the mariadb_use_result() C API function in the client/server library instead of mysql_store_result(). no cap.

using mariadb is genuinely so easy. invoke her from your command interpreter's prompt like this:

shell> mariadb db_name

or:

shell> mariadb --user=user_name --password=your_password db_name

then just type an SQL statement, end it with ;, \g, or \G, and hit Enter. periodt.

typing Control-C makes mariadb try to kill the current statement. if that can't be done, or if you type Control-C again before the statement is killed, mariadb exits. 💀

you can also run SQL statements in a script file (batch file) like this:

shell> mariadb db_name < script.sql > output.tab

---

👑 MARIADB OPTIONS 👑

mariadb supports all these options, and you can specify them on the command line or in the [mariadb], [client], [client-server] or [client-mariadb] option file groups. she also supports the options for processing option files. let's go, one by one, no skipping. 💅

• --help, -?, -I — show a help message and exit. that's it, that's the whole thing.
• --abort-source-on-error — abort source filename operations if errors happen.
• --auto-rehash — turn on automatic rehashing. this one is on by default, and it enables database, table, and column name completion. wanna turn it off? use --disable-auto-rehash, --no-auto-rehash, or --skip-auto-rehash. that makes mariadb start faster, but then you gotta issue the rehash command if you want name completion back.

to complete a name, type the first part and press Tab. if the name is unambiguous, mariadb finishes it for you. otherwise, press Tab again to see all the possible names starting with what you've typed so far. heads up: completion does not happen if there's no default database. 🙈

• --auto-vertical-output — automatically switch to vertical output mode when the result is wider than your terminal width. she knows her limits, respectfully.
• --batch, -B — print results using tab as the column separator, one row per new line. with this option, mariadb does not use the history file.

batch mode gives you nontabular output format plus escaping of special characters. you can disable that escaping with raw mode — go read the description for the --raw option.

• --binary-mode — binary mode lets certain character sequences be processed as data that the parser would otherwise treat as having a special meaning. specifically: this switch turns off parsing of all client commands except \C and DELIMITER in non-interactive mode (aka when binary mode is combined with either 1) piped input, 2) the --batch mariadb option, or 3) the source command). also, in binary mode, occurrences of '\r\n' and ASCII '\0' are preserved inside strings — whereas by default, '\r\n' gets translated to '\n' and '\0' is disallowed in user input.
• --character-sets-dir=path — the directory where character sets are installed.
• --column-names — write column names in results. names! in your results!
• --column-type-info, -m — display result set metadata.
• --comments, -c — whether to preserve comments in statements sent to the server. the default is --skip-comments (which discards comments); enable with --comments (which preserves them).
• --compress, -C — compress all info sent between the client and the server, if both of them support compression.
• --connect-timeout=seconds — set the number of seconds before the connection times out. (default value is 0.)
• --database=db_name, -D db_name — the database to use.
• --debug[=debug_options], -# [debug_options] — write a debugging log. a typical debug_options string is 'd:t:o,file_name'. the default is 'd:t:o,/tmp/mysql.trace'.
• --debug-check — print some debugging information when the program exits.
• --debug-info, -T — prints debugging information plus memory and CPU usage statistics when the program exits.
• --default-auth=name — the default authentication client-side plugin to use.
• --default-character-set=charset_name — use charset_name as the default character set for the client and the connection.

a common issue, and honestly a classic: when your operating system uses utf8 or another multi-byte character set, output from the mariadb client can be formatted incorrectly, because the MariaDB client uses the latin1 character set by default. usually you can fix this by using this option to force the client to use the system character set instead. 💜

• --defaults-extra-file=filename — set filename as the file to read default options from, after the global defaults files have been read. must be given as the first option.
• --defaults-file=filename — set filename as the file to read default options from, and override the global defaults files. must be given as the first option.
• --defaults-group-suffix=suffix — in addition to the groups named on the command line, read groups that have the given suffix.
• --delimiter=str — set the statement delimiter. the default is the semicolon character (;).
• --disable-named-commands — disable named commands. use the \* form only, or use named commands only at the beginning of a line ending with a semicolon (;). mariadb starts with this option enabled by default. however, even with this option, long-format commands still work from the first line. see the section called "MARIADB COMMANDS".
• --enable-cleartext-plugin — obsolete option. she only exists for MySQL compatibility. a little nostalgic queen. 🎀
• --execute=statement, -e statement — execute the statement and quit. this disables --force and the history file. the default output format is like the one you'd get with --batch.
• --force, -f — keep going even if an SQL error occurs. sets --abort-source-on-error to 0.
• --host=host_name, -h host_name — connect to the MariaDB server on the given host.
• --html, -H — produce HTML output.
• --ignore-spaces, -i — ignore spaces after function names. this lets you have spaces (including tab characters and new line characters) between a function name and '('. the drawback? this causes built-in functions to become reserved words. trade-offs, bestie. 😭
• --init-command=str — SQL command to execute when connecting to the MariaDB server. it'll automatically be re-executed whenever you reconnect.
• --line-numbers — write line numbers for errors. disable it with --skip-line-numbers.
• --local-infile[={0|1}] — enable or disable LOCAL capability for LOAD DATA INFILE. with no value, the option enables LOCAL. you can also give it as --local-infile=0 or --local-infile=1 to explicitly disable or enable LOCAL. heads up though: enabling LOCAL has no effect if the server doesn't also support it.
• --max-allowed-packet=num — set the maximum packet length to send to or receive from the server. (default value is 16MB, largest is 1GB.)
• --max-join-size=num — set the automatic limit for rows in a join when you're using --safe-updates. (default value is 1,000,000.)
• --named-commands, -G — enable named mariadb commands. long-format commands are allowed, not just short-format ones. for example, quit and \q are both recognized. use --skip-named-commands to disable named commands. see the section called "MARIADB COMMANDS". disabled by default.
• --net-buffer-length=size — set the buffer size for TCP/IP and socket communication. (default value is 16KB.)
• --no-auto-rehash, -A — same effect as --skip-auto-rehash. see the description for --auto-rehash.
• --no-beep, -b — do not beep when errors occur. silence is golden. 🔇
• --no-defaults — do not read default options from any option file. this must be given as the first argument.
• --one-database, -o — ignore statements except those that occur while the default database is the one named on the command line. this filtering is limited, and based only on USE statements. useful for skipping updates to other databases in the binary log.
• --pager[=command] — use the given command for paging query output. if you omit the command, the default pager is the value of your PAGER environment variable. valid pagers are less, more, cat [> filename], and so forth. this option works only on Unix and only in interactive mode. to disable paging, use --skip-pager. the section called "MARIADB COMMANDS" talks about output paging even more.
• --password[=password], -p[password] — the password to use when connecting to the server. if you use the short option form (-p), you cannot have a space between the option and the password. if you leave out the password value after the --password or -p option on the command line, mariadb prompts you for one.

⚠️ specifying a password on the command line should be considered insecure. you can use an option file to avoid putting the password on the command line. protect yourself, bestie. 💅

• --pipe, -W — on Windows, connect to the server via a named pipe. this option only applies if the server supports named-pipe connections.
• --plugin-dir=dir_name — directory for client-side plugins.
• --port=port_num, -P port_num — the TCP/IP port number to use for the connection, or 0 for the default. the default is chosen in this order of preference: my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). specifying this on the command line without other connection properties forces --protocol=tcp.
• --print-defaults — print the program argument list and exit. this must be given as the first argument.

---

more of this manual is coming, bestie, don't go anywhere 🧚

• --progress-reports — get progress reports for long-running commands (looking at you, ALTER TABLE, you absolute unit of a slowpoke). Defaults to on, so if you want it gone, hit it with --skip-progress-reports. 👑
• --prompt=format_str — sets your prompt to whatever format_str you want ✨. All the special little sequences you can sneak in there are listed over in the section called “MARIADB COMMANDS” — go peek. 💅
• --protocol={TCP|SOCKET|PIPE|MEMORY} — the connection protocol for talking to the server, bestie. It's the move when your other connection params would lowkey pick a protocol that isn't the one you actually wanted. 🙈
• --quick, -q — do not cache each query result; print each row the second it arrives 🏃♀️. Heads up: this can slow the server down if your output gets suspended. Also, with this option mariadb doesn't use the history file. No cap.
• --raw, -r — okay so for tabular output, the “boxing” around columns is what lets you tell one column value from another 🎁. But for nontabular output (the batch-mode kind, or whenever you passed --batch or --silent), special characters get escaped so you can actually spot them: newline, tab, NUL and backslash get written as \n, \t, \0, and \\. --raw turns that escaping off. Periodt.

Here's the glow-up, tabular vs nontabular vs raw mode with no escaping:

% mariadb
mariadb> SELECT CHAR(92);
+----------+
| CHAR(92) |
+----------+
| \        |
+----------+
% mariadb -s
mariadb> SELECT CHAR(92);
CHAR(92)
\\
% mariadb -s -r
mariadb> SELECT CHAR(92);
CHAR(92)
\

• --reconnect — if the connection to the server dips, it automatically tries to reconnect 🫶. One single reconnect attempt happens each time the connection is lost. Enabled by default, so to shut it off use --skip-reconnect or --disable-reconnect.
• --safe-updates, --i-am-a-dummy, -U — only lets through UPDATE and DELETE statements that say which rows they're touching, using key values 🔒. If you already set this in an option file, you can override it by throwing --safe-updates on the command line. More tea on this one lives in the section called “MARIADB TIPS”.
• --secure-auth — do not send passwords to the server in the old (pre-4.1.1) format 🚫. That means no connections except to servers that use the newer password format. It's giving secure.
• --select-limit=limit — sets the automatic limit for SELECT when you're using --safe-updates. Default value is 1,000. ✨
• --server-arg=name — sends name as a parameter to the embedded server. 📨
• --show-warnings — makes warnings show up after each statement, if there are any 🚨. Works in both interactive and batch mode.
• --sigint-ignore — ignores SIGINT signals (which is usually just you mashing Control-C 😭).
• --silent, -s — silent mode: produce less output 🤫. You can pass this one multiple times to get less and less and less output.

This option gives you nontabular output format plus escaping of special characters. Escaping can be turned off with raw mode — go read the --raw description above, iykyk.

• --skip-auto-rehash — disables automatic rehashing. It's a synonym for --disable-auto-rehash. 💫
• --skip-column-names, -N — don't write column names in your results. Just the data, bestie. 🙈
• --skip-line-numbers, -L — don't write line numbers for errors. Chef's kiss when you wanna compare result files that include error messages. 🍓
• --socket=path, -S path — for connections to localhost, this is the Unix socket file to use, or, on Windows, the name of the named pipe to use 🔌. If you specify it on the command line without any other connection properties, it forces --protocol=socket; on Windows, it forces --protocol=pipe.
• --ssl — enable SSL for the connection (it gets automatically enabled alongside other flags anyway). Disable it with --skip-ssl 🔐.
• --ssl-ca=name — CA file in PEM format (check the OpenSSL docs; this implies --ssl). 📜
• --ssl-capath=name — CA directory (check the OpenSSL docs; implies --ssl). 📁
• --ssl-cert=name — X509 cert in PEM format (check the OpenSSL docs; implies --ssl). 🎫
• --ssl-cipher=name — the SSL cipher to use (check the OpenSSL docs; implies --ssl). 🔡
• --ssl-key=name — X509 key in PEM format (check the OpenSSL docs; implies --ssl). 🗝️
• --ssl-crl=name — certificate revocation list (check the OpenSSL docs; implies --ssl). 📋
• --ssl-crlpath=name — certificate revocation list path (check the OpenSSL docs; implies --ssl). 🗂️
• --ssl-verify-server-cert — verifies the server's "Common Name" in its cert against the hostname used when connecting 🕵️♀️. This option is disabled by default.
• --table, -t — display output in table format ✨. This is the default for interactive use, but you can also use it to get table output while in batch mode.
• --tee=file_name — append a copy of the output to the given file 📝. Heads up: this option works only in interactive mode. The section called “MARIADB COMMANDS” discusses tee files further.
• --unbuffered, -n — flush the buffer after each query 🚿.
• --user=user_name, -u user_name — the MariaDB user name to use when connecting to the server. 👤
• --verbose, -v — verbose mode: produce more output about what the program is doing 🗣️. Pass it multiple times to get more and more output. (Like, -v -v -v gives you table output format even in batch mode. Iconic.)
• --version, -V — display version information and exit. Bye bestie 👋.
• --vertical, -E — print query output rows vertically, one line per column value 📏. Don't want the flag? You can still get vertical output for individual statements by ending them with \G.
• --wait, -w — if the connection can't be established, wait and retry instead of just aborting 🫶.
• --xml, -X — produce XML output 🏷️. When --xml is used with mariadb, the output matches mariadb-dump --xml. See mariadb-dump(1) for the details.

The XML output also uses an XML namespace, like this:

shell> mariadb --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
<?xml version="1.0"?>
<resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">version</field>
<field name="Value">5.0.40-debug</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">Source distribution</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">i686</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">suse-linux-gnu</field>
</row>
</resultset>

You can also set the following variables by using --var_name=value. 👇

• connect_timeout — the number of seconds before connection timeout. (Default value is 0.)
• max_allowed_packet — the maximum packet length to send to or receive from the server. (Default value is 16MB.)
• max_join_size — the automatic limit for rows in a join when using --safe-updates. (Default value is 1,000,000.)
• net_buffer_length — the buffer size for TCP/IP and socket communication. (Default value is 16KB.)
• select_limit — the automatic limit for SELECT statements when using --safe-updates. (Default value is 1,000.)

---

Okay so on Unix, the mariadb client writes a record of executed statements to a history file 📚. By default this file is named .mariadb_history and it gets created in your home directory. For backwards compatibility .mariadb_history will be used if present and .mariadb_history is missing. Wanna point it somewhere else? Set the value of the MARIADB_HISTFILE environment variable. And the environment variable MYSQL_HISTFILE will be used if MARIADB_HISTFILE isn't present.

Bestie, protect .mariadb_history with a restrictive access mode. Sensitive information might get written to it, like the text of SQL statements that contain passwords. Do not leave that door open. 🔒😭

If you do not want to maintain a history file, first remove .mariadb_history if it exists, and then use either of these techniques:

• Set the MARIADB_HISTFILE variable to /dev/null. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files.
• Create .mariadb_history as a symbolic link to /dev/null:

shell> ln -s /dev/null $HOME/.mariadb_history

You need do this only once. ✨

---

🎀 MARIADB COMMANDS 🎀

mariadb sends each SQL statement that you issue to the server to be executed 🚀. But there's also a whole set of commands that mariadb itself interprets — main character energy, honestly. Wanna see the list? Type help or \h at the mariadb> prompt:

💖

mariadb> help
List of all MariaDB commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for 'help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mariadb server, display result vertically.
exit      (\q) Exit mariadb. Same as quit.
go        (\g) Send command to mariadb server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mariadb prompt.
quit      (\q) Quit mariadb.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given
               outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing
               binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'

okay so that's the whole command menu, bestie 👑 — every single one of them has a long form and a short form, and here's the tea: the long form is not case sensitive, but the short form is. The long form can be followed by an optional semicolon terminator, but the short form should not be. (Rude of it, honestly, but that's the rules.)

🚨 also important: using short-form commands inside multi-line /* ... */ comments is not supported. Don't do it, she will not parse that for you, no cap.

---

• help [arg], \h [arg], \? [arg], ? [arg] — shows you a help message with all the available mariadb commands listed out 💖

If you actually hand it an argument, mariadb treats that as a search string to go fetch server-side help. More deets in the section called “MARIADB SERVER-SIDE HELP”.

• charset charset_name, \C charset_name — changes the default character set and fires off a SET NAMES statement. ✨

This is the moment where the charset stays synced up between client and server if mariadb is running with auto-reconnect enabled (which — heads up — is not recommended, bestie), because that specified character set is what gets used for reconnects.

• clear, \c — wipes the current input clean. Use this when you've typed a whole statement and then had the realisation that you do not, in fact, want to send it 🙈
• connect [db_name host_name]], \r [db_name host_name]] — reconnects to the server. You can pass the optional database name and host name arguments to pick the default database or the host where the server lives. Leave them out and it just uses the current values. 🔁
• delimiter str, \d str — changes the string mariadb reads as the separator between SQL statements. The default is the semicolon character (“;”).

The delimiter can be given as an unquoted or a quoted argument. Quoting works with either single quote (') or double quote (") characters. To sneak a quote inside a quoted string, either wrap the string in the other quote character, or escape the quote with a backslash (“\”). Backslash should be avoided outside of quoted strings because it's the escape character for MariaDB, so like… don't. For an unquoted argument, the delimiter gets read up to the first space or end of line. For a quoted argument, it's read up to the matching quote on the line.

Now the spicy part: when the delimiter mariadb recognises is set to anything other than the default “;”, instances of that character get sent to the server without interpretation. BUT — the server itself still reads “;” as a statement delimiter and processes statements accordingly. This server-side behaviour matters for multiple-statement execution, and for parsing the body of stored procedures and functions, triggers, and events. iykyk 🧚

• edit, \e — lets you edit the current input statement. mariadb checks the values of the EDITOR and VISUAL environment variables to figure out which editor to use. The default editor is vi if neither variable is set.

this one works only in Unix, just so you know 💅

• ego, \G — sends the current statement off to the server to be executed and displays the result using vertical format. It's giving organised. It's giving readable. Obsessed. ⭐
• exit, \q — exits mariadb. bye bestie 🦋
• go, \g — sends the current statement to the server to be executed.
• nopager, \n — disables output paging. See the description for pager.

also Unix-only, sorry to the Windows girlies 😭

• notee, \t — disables output copying to the tee file. See the description for tee.
• nowarning, \w — enables display of warnings after each statement.
• pager [command], \P [command] — enables output paging. By using the --pager option when you invoke mariadb, you can browse or search query results in interactive mode with Unix programs like less, more, or literally any other similar program. If you give the option no value, mariadb checks the value of the PAGER environment variable and sets the pager to that. Pager functionality works only in interactive mode.

Output paging can be switched on interactively with the pager command and switched off with nopager. The command takes an optional argument; if you give one, the paging program is set to that. With no argument, the pager is set to the pager that was set on the command line, or stdout if no pager was specified.

Output paging works only in Unix because it uses the popen() function, which does not exist on Windows. For Windows, the tee option can be used instead to save query output, although it is not as convenient as pager for browsing output in some situations. 🫶

• print, \p — prints the current input statement without executing it. Peek, don't yeet.
• prompt [str], \R [str] — reconfigures the mariadb prompt to whatever string you give it. The special character sequences you can use in the prompt are described later in this section. ✨

If you run the prompt command with no argument at all, mariadb resets the prompt to the default of mariadb>.

• quit, \q — exits mariadb.
• rehash, \# — rebuilds the completion hash, the thing that enables database, table, and column name completion while you're typing statements. (See the description for the --auto-rehash option.) 👑
• source file_name, \. file_name — reads the named file and executes the statements inside it. On Windows, you can specify path name separators as / or \\.
• status, \s — hands you status information about the connection and the server you're using. If you're running in --safe-updates mode, status also prints the values for the mariadb variables that affect your queries. 💖
• system command, \! command — executes the given command using your default command interpreter.

Unix only, once again 🌸

• tee [file_name], \T [file_name] — by using the --tee option when you invoke mariadb, you can log statements and their output. All the data shown on screen gets appended into a given file. Genuinely so useful for debugging too. mariadb flushes results to the file after each statement, right before it prints its next prompt. Tee functionality works only in interactive mode.

You can turn this feature on interactively with the tee command. Without a parameter, the previous file is used. The tee file can be disabled with the notee command. Running tee again re-enables logging. 🔥

• use db_name, \u db_name — uses db_name as the default database. Slay, now you're in.
• warnings, \W — enables display of warnings after each statement (if there are any).

---

okay so here are a few tips about the pager command, because this thing is lowkey iconic once you get it 🎀

• You can use it to write to a file, and then the results go only to the file:

mariadb> pager cat > /tmp/log.txt

You can also pass any options for the program that you want to use as your pager:

mariadb> pager less -n -i -S

• In the example above, note the -S option. You may find it very useful for browsing wide query results. Sometimes a very wide result set is difficult to read on the screen. The -S option to less can make the result set much more readable because you can scroll it horizontally using the left-arrow and right-arrow keys. You can also use -S interactively within less to switch the horizontal-browse mode on and off. For more information, read the less manual page:

shell> man less

• The -F and -X options may be used with less to cause it to exit if output fits on one screen, which is convenient when no scrolling is necessary:

mariadb> pager less -n -i -S -F -X

• You can specify very complex pager commands for handling query output:

mariadb> pager cat | tee /dr1/tmp/res.txt \
          | tee /dr2/tmp/res2.txt | less -n -i -S

In this example, the command would send query results to two files in two different directories on two different file systems mounted on /dr1 and /dr2, yet still display the results onscreen via less. it's giving multitasking main character energy 💅

---

You can also combine the tee and pager functions. Have a tee file enabled and pager set to less, and you are able to browse the results using the less program and still have everything appended into a file at the same time. The difference between the Unix tee used with the pager command and the mariadb built-in tee command is that the built-in tee works even if you do not have the Unix tee available. The built-in tee also logs everything that is printed on the screen, whereas the Unix tee used with pager does not log quite that much. Additionally, tee file logging can be turned on and off interactively from within mariadb. This is useful when you want to log some queries to a file, but not others. ✨

okay so real talk, the prompt command is the one that reconfigures your default mariadb> prompt, and bestie, it is SO customizable. 🎀 The string you give it can contain the following special sequences — and yes, we're doing all of them, no skipping:

• \c — a counter that increments for each statement you issue 🔢
• \D — the full current date 📅
• \d — the default database
• \h — the server host
• \l — the current delimiter (new in 5.1.12) ✨
• \m — minutes of the current time
• \n — a newline character
• \O — the current month in three-letter format (Jan, Feb, ...)
• \o — the current month in numeric format
• \P — am/pm
• \p — the current TCP/IP port or socket file
• \R — the current time, in 24-hour military time (0–23) ⏰
• \r — the current time, standard 12-hour time (1–12)
• \S — semicolon
• \s — seconds of the current time
• \t — a tab character
• \U — your full user_name@host_name account name 🫶
• \u — your user name
• \v — the server version
• \w — the current day of the week in three-letter format (Mon, Tue, ...)
• \Y — the current year, four digits
• \y — the current year, two digits
• \_ — a space
• \ — a space (a space follows the backslash)
• \' — single quote
• \" — double quote
• \\ — a literal "" backslash character
• \x — x, for any "x" not listed above

---

And there are, like, four whole ways to set this thing. Pick your fighter:

• Use an environment variable. Set the MYSQL_PS1 environment variable to a prompt string. For example:

shell> export MYSQL_PS1="(\u@\h) [\d]> "

• Use a command-line option. Set the --prompt option on the command line to mariadb. For example:

shell> mariadb --prompt="(\u@\h) [\d]> "
(user@host) [database]>

• Use an option file. Set the prompt option in the [mariadb] group of any MariaDB option file, like /etc/my.cnf or the .my.cnf file in your home directory. For example:

[mariadb]
prompt=(\\u@\\h) [\\d]>\\_

In this example, notice that the backslashes are doubled. If you set the prompt using the prompt option in an option file, it's advisable to double the backslashes when you're using the special prompt options. There's some overlap in the set of allowable prompt options and the set of special escape sequences that are recognized in option files. And that overlap can cause you problems if you use single backslashes. For example, \s is interpreted as a space rather than as the current seconds value. 😭 The following example shows how to define a prompt within an option file to include the current time in HH:MM:SS> format:

[mariadb]
prompt="\\r:\\m:\\s> "

• Set the prompt interactively. You can change your prompt on the fly by using the prompt (or \R) command. For example:

mariadb> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]>
(user@host) [database]> prompt
Returning to default PROMPT of mariadb>
mariadb>

---

🌸 MARIADB SERVER-SIDE HELP 🌸

mariadb> help search_string

If you hand an argument to the help command, mariadb uses it as a search string to access server-side help. For this to actually work properly, the help tables in the mysql database need to be initialized with help topic information. (iykyk)

If there's no match for your search string, the search just... fails:

mariadb> help me
Nothing found
Please try to run 'help contents' for a list of all accessible topics

Use help contents to see a list of the help categories:

mariadb> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the
following categories:
   Account Management
   Administration
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Language Structure
   Plugins
   Storage Engines
   Stored Routines
   Table Maintenance
   Transactions
   Triggers

If your search string matches multiple items, mariadb shows you a list of the matching topics:

mariadb> help logs
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following topics:
   SHOW
   SHOW BINARY LOGS
   SHOW ENGINE
   SHOW LOGS

And if you use a topic as the search string, you get the help entry for that topic:

mariadb> help show binary logs
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server. This statement is used as
part of the procedure described in [purge-binary-logs], that shows how
to determine which logs can be purged.
mariadb> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

like. the way that it just tells you things. we love a self-documenting queen 👑

💾 EXECUTING SQL STATEMENTS FROM A TEXT FILE 💾

So normally you're just vibing in the mariadb client interactively, like this:

shell> mariadb db_name

But bestie, you can also just yeet your SQL statements into a file and tell mariadb to read its input from there. To do that, make a text file called text_file with all the statements you wanna run. Then call mariadb like this:

shell> mariadb db_name < text_file

And if you drop a USE db_name statement as the very first line in that file, you don't even need to name the database on the command line:

shell> mariadb < text_file

Already inside mariadb? No problem, you can run an SQL script file with the source command or the \. command:

mariadb> source file_name
mariadb> \. file_name

Sometimes you want your script to show the user some progress info, and for that you can sneak in statements like this:

SELECT '<info_to_display>' AS ' ';

That statement right there outputs <info_to_display>. ✨

You can also launch mariadb with the --verbose option, which makes every statement get displayed before the result it produces. So you see the receipts. 💅

Oh, and heads up: mariadb ignores Unicode byte order mark (BOM) characters at the beginning of input files. Having a BOM does not make mariadb switch up its default character set. If you actually want to change that, invoke mariadb with an option like --default-character-set=utf8.

---

🌸 MARIADB TIPS 🌸

This section is basically the little bag of tricks that'll help you use mariadb way more effectively. Let's go. 🫶

📊 Displaying Query Results Vertically 📊

Some query results are just so much more readable when you show them vertically instead of in the usual horizontal table format. You get vertical output by ending the query with \G instead of a semicolon. For example, longer text values that include newlines are often way easier to read with vertical output:

mariadb> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
     date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: monty@no.spam.com
  mail_to: "Thimble Smith" <tim@no.spam.com>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi.  I think this is a good idea.  Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
Thimble> TODO list and see what happens.
Yes, please do that.
Regards,
Monty
     file: inbox-jani-1
     hash: 190402944
1 row in set (0.09 sec)

🛡️ Using the --safe-updates Option 🛡️

If you're a beginner, a genuinely useful startup option is --safe-updates (or --i-am-a-dummy, which does the exact same thing — no judgement, the name is just iconic 💀). It's a lifesaver for those moments where you might have typed a DELETE FROM tbl_name statement and forgotten the WHERE clause. Normally that statement nukes all the rows from the table. With --safe-updates, you can only delete rows by specifying the key values that identify them. It's literally there to prevent accidents.

When you use the --safe-updates option, mariadb issues the following statement when it connects to the MariaDB server:

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

That SET statement has these effects:

• You are not allowed to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both). For example:

UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;

• The server limits all large SELECT results to 1,000 rows unless the statement includes a LIMIT clause.
• The server aborts multiple-table SELECT statements that probably need to examine more than 1,000,000 row combinations.

Wanna set limits that aren't 1,000 and 1,000,000? You can override the defaults with the --select-limit and --max-join-size options:

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

🔌 Disabling mariadb Auto-Reconnect 🔌

Okay so here's the tea. If the mariadb client loses its connection to the server while it's sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. But even if mariadb manages to reconnect, your first connection has ended and all your previous session objects and settings are gone: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you — like in this example, where the server got shut down and restarted between the first and second statements without you knowing it:

mariadb> SET @a=1;
Query OK, 0 rows affected (0.05 sec)
mariadb> INSERT INTO t VALUES(@a);
ERROR 2006: MariaDB server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test
Query OK, 1 row affected (1.30 sec)
mariadb> SELECT * FROM t;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.05 sec)

The @a user variable has been lost along with the connection, and after the reconnection it is undefined. 😭 So if it's important to you that mariadb terminates with an error when the connection has been lost, start the mariadb client with the --skip-reconnect option. Protect your peace.

---

📜 COPYRIGHT 📜

Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc., 2010-2025 MariaDB Foundation

This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA or see http://www.gnu.org/licenses/.

---

🐛 NOTES 🐛

1. Bug#25946 — http://bugs.mysql.com/bug.php?id=25946

---

👀 SEE ALSO 👀

For more information, please refer to the MariaDB Documentation, available online at https://mariadb.com/docs/

---

👑 AUTHOR 👑

MariaDB Foundation (http://www.mariadb.org/).

---

And that's a wrap, bestie! This whole page is from MariaDB 11.8, dated 5 March 2025, and it's the MARIADB(1) manual page — so if anyone asks where you learned all this, you know exactly what to cite. Slay, go run some queries. 💖✨