MARIADB(1)

💖 MARIADB(1) — the MariaDB command-line tool 💖

MariaDB Database System · MARIADB(1)

okay bestie, sit down, hydrate, because this is the manual page for mariadb, and she is iconic. 💅

---

✨ NAME ✨

mariadb — the MariaDB command-line tool (mysql is now a symlink to mariadb) 🫶

---

🌸 SYNOPSIS 🌸

mariadb [options] db_name

---

🦋 DESCRIPTION 🦋

mariadb is a simple SQL shell (with GNU readline capabilities, so yes, she has ✨line editing✨). She supports both interactive and non-interactive use, like a true multi-hyphenate. When you use her interactively, query results come out in an ASCII-table format. When you use her non-interactively (say, as a filter), the result shows up in tab-separated format. Want a different vibe? The output format can be changed using command options. 🔥

If you're having problems because of insufficient memory for large result sets, use the --quick option. That forces mariadb to retrieve results from the server a row at a time, instead of pulling the entire result set and buffering it in memory before displaying it. It does this by returning the result set using the mariadb_use_result() C API function in the client/server library rather than mysql_store_result(). No cap, that's the whole trick.

Using mariadb is very easy. Invoke her from the prompt of your command interpreter like this:

shell> mariadb db_name

Or:

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

Then type an SQL statement, end it with “;”, \g, or \G and press Enter. That's it, that's the ritual. 🎀

Typing Control-C causes mariadb to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mariadb exits. So like… first Ctrl-C is a polite request, second one is a breakup. 😭

You can execute SQL statements in a script file (batch file) like this:

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

---

👑 MARIADB OPTIONS 👑

mariadb supports the following options, which can be specified on the command line or in the [mariadb], [client], [client-server] or [client-mariadb] option file groups. mariadb also supports the options for processing option files. Let's go through every single one, no skipping, we don't do that here. 💖

• --help, -?, -I — Display a help message and exit. Your emotional support flag.
• --abort-source-on-error — Abort source filename operations in case of errors.
• --auto-rehash — Enable automatic rehashing. This option is on by default, which enables database, table, and column name completion. Use --disable-auto-rehash, --no-auto-rehash, or --skip-auto-rehash to disable rehashing. That causes mariadb to start faster, but you must issue the rehash command if you want to use name completion.

To complete a name, enter the first part and press Tab. If the name is unambiguous, mariadb completes it. Otherwise, you can press Tab again to see the possible names that begin with what you have typed so far. Completion does not occur if there is no default database. (Rude, honestly, but okay. 🙈)

• --auto-vertical-output — Automatically switch to vertical output mode if the result is wider than the terminal width.
• --batch, -B — Print results using tab as the column separator, with each row on a new line. With this option, mariadb does not use the history file.

Batch mode results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

• --binary-mode — Binary mode allows certain character sequences to be processed as data that would otherwise be treated with a special meaning by the parser. Specifically, this switch turns off parsing of all client commands except \C and DELIMITER in non-interactive mode (i.e., 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 within strings, whereas by default, '\r\n' is 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.
• --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 (discard comments), enable with --comments (preserve comments).
• --compress, -C — Compress all information sent between the client and the server if both support compression.
• --connect-timeout=seconds — Set the number of seconds before connection timeout. (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 and memory and CPU usage statistics when the program exits.
• --default-auth=name — Default authentication client-side plugin to use.
• --default-character-set=charset_name — Use charset_name as the default character set for the client and connection.

A common issue that can occur when the operating system uses utf8 or another multi-byte character set is that output from the mariadb client is formatted incorrectly, due to the fact that the MariaDB client uses the latin1 character set by default. You can usually fix such issues 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 has been read. Must be given as first option.
• --defaults-file=filename — Set filename as the file to read default options from, override global defaults files. Must be given as 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. Exists only for MySQL compatibility. 💀 She's retired but she's still on the roster.
• --execute=statement, -e statement — Execute the statement and quit. Disables --force and history file. The default output format is like that produced with --batch.
• --force, -f — Continue 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. Allows one to have spaces (including tab characters and new line characters) between function name and '('. The drawback is that this causes built in functions to become reserved words.
• --init-command=str — SQL Command to execute when connecting to the MariaDB server. Will automatically be re-executed when reconnecting. She remembers. 🧚
• --line-numbers — Write line numbers for errors. Disable this with --skip-line-numbers.
• --local-infile[={0|1}] — Enable or disable LOCAL capability for LOAD DATA INFILE. With no value, the option enables LOCAL. The option may be given as --local-infile=0 or --local-infile=1 to explicitly disable or enable LOCAL. Enabling LOCAL has no effect if the server does not 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 1GB.)
• --max-join-size=num — Set the automatic limit for rows in a join when 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 commands. For example, quit and \q both are 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 — This has the same effect as --skip-auto-rehash. See the description for --auto-rehash.
• --no-beep, -b — Do not beep when errors occur. (The beep was NOT serving anyway. 🙈)
• --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 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. This is useful for skipping updates to other databases in the binary log.
• --pager[=command] — Use the given command for paging query output. If the command is omitted, 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”, discusses output paging further.
• --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 omit the password value following the --password or -p option on the command line, mariadb prompts for one.

⚠️ Specifying a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line.

• --pipe, -W — On Windows, connect to the server via a named pipe. This option applies only 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 default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). Forces --protocol=tcp when specified on the command line without other connection properties.
• --print-defaults — Print the program argument list and exit. This must be given as the first argument.
• --progress-reports — bestie this is your little ✨progress bar✨ for the long-haul commands (think a big dramatic ALTER TABLE). It's on by default, so if you're over it, hit it with --skip-progress-reports to turn it off.
• --prompt=format_str — sets your prompt to whatever format you want. All the special little sequences you can stuff into that format are listed over in the section called “MARIADB COMMANDS”. go look, it's cute.
• --protocol={TCP|SOCKET|PIPE|MEMORY} — this picks the connection protocol for talking to the server. Super handy when your other connection params would normally make it pick a protocol that isn't the one you actually want. you're in charge, not the defaults.
• --quick, -q — do not cache each query result, just print every row the second it arrives. Heads up though: this can slow the server down if the output gets suspended. And with this option on, mariadb doesn't touch the history file at all. 🙈
• --raw, -r — okay so for tabular output, the “boxing” around columns is what lets you tell one column value apart from another. For nontabular output (like what you get in batch mode, or when --batch or --silent is given), special characters get escaped so you can still spot them easily — newline, tab, NUL and backslash come out as \n, \t, \0 and \\. The --raw option kills that escaping entirely.

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

% 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. It's enabled by default, and to disable it you use --skip-reconnect or --disable-reconnect.
• --safe-updates, --i-am-a-dummy, -U — only lets through those UPDATE and DELETE statements that say which rows they're modifying by using key values. If you set this in an option file, you can override it by using --safe-updates on the command line. The section called “MARIADB TIPS” has more tea on this one. 🫶
• --secure-auth — do not send passwords to the server in the old (pre-4.1.1) format. This blocks connections except for servers that use the newer password format. old format? we don't know her.
• --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 pop up after every statement, if there are any. Works in both interactive and batch mode. 👑
• --sigint-ignore — ignores SIGINT signals (which is typically what happens when you type Control-C). no more accidental rage-quitting mid-query.
• --silent, -s — silent mode, i.e. produce less output. You can give this option multiple times to produce less and less and less output. This one gives you nontabular output format plus escaping of special characters — and that escaping can be turned off with raw mode, see the --raw description above. 💅
• --skip-auto-rehash — disables automatic rehashing. It's a synonym for --disable-auto-rehash.
• --skip-column-names, -N — don't write column names in results. just the data, bestie.
• --skip-line-numbers, -L — don't write line numbers for errors. So useful when you're comparing 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. It forces --protocol=socket when you specify it on the command line without other connection properties; on Windows, it forces --protocol=pipe.
• --ssl — enables SSL for the connection (it gets automatically enabled with other flags anyway). Disable it with --skip-ssl.
• --ssl-ca=name — CA file in PEM format (check the OpenSSL docs, and yes it 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, so turn it on yourself if you want that security moment. 🔥
• --table, -t — displays output in table format. That's the default for interactive use, but you can also use it to get table output in batch mode.
• --tee=file_name — appends a copy of the output to the given file. Only works in interactive mode, just so you know. The section called “MARIADB COMMANDS” spills more tea on tee files.
• --unbuffered, -n — flushes the buffer after each query. instant gratification only. ✨
• --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. You can give this multiple times for more and more and MORE output. (For example, -v -v -v produces table output format even in batch mode. iconic.)
• --version, -V — displays version information and exits.
• --vertical, -E — prints query output rows vertically (one line per column value). Without this option 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. it's giving patience.
• --xml, -X — produces XML output. When --xml is used with mariadb, the output matches that of 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.)

---

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. To pick a different file, set the value of the MARIADB_HISTFILE environment variable. The environment variable MYSQL_HISTFILE will be used if MARIADB_HISTFILE isn't present.

okay real talk 💀 the .mariadb_history file should be protected with a restrictive access mode, because sensitive information might get written into it — like the text of SQL statements that contain passwords. protect your girl.

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

• Set the MARIADB_HISTFILE variable to /dev/null. To make that setting kick in every 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 only need to do this once. 🎀

---

🌈 MARIADB COMMANDS 🌈

mariadb sends every SQL statement you issue off to the server to be executed. But there's also a whole set of commands that mariadb itself interprets. For the full list of those commands, type help or \h at the mariadb> prompt:

okay so you type help and this whole menu drops 💅 here's literally what it prints, no edits:

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'

notable detail bestie: all text commands have to be first on the line and end with ;. That's the whole rule. Don't fight it.

Every command comes in a long form and a short form. The long form is not case sensitive. The short form is. The long form can be followed by an optional semicolon terminator — the short form should not be. And using short-form commands inside multi-line /* ... */ comments? Not supported. Nope. Don't do it. 🙈

---

✨ the commands, one by one ✨

• help [arg], \h [arg], \? [arg], ? [arg] — Drops a help message listing all the available mariadb commands. Give it an argument and mariadb uses that as a search string to go grab server-side help. More on that in the section called “MARIADB SERVER-SIDE HELP”.
• charset charset_name, \C charset_name — Switches the default character set and fires off a SET NAMES statement. Why do we care? Because it keeps the character set synchronized between client and server if mariadb is running with auto-reconnect enabled (which, btw, is not recommended 🔥) — the charset you picked is what gets used for reconnects.
• clear, \c — Clears your current input. Use it when you change your mind about the statement you were typing. The digital equivalent of deleting the paragraph. 💀
• connect [db_name host_name]], \r [db_name host_name]] — Reconnects to the server. The optional database name and host name arguments let you specify the default database or the host where the server's running. Leave them out and the current values get used instead.
• delimiter str, \d str — Changes the string mariadb treats as the separator between SQL statements. The default is the semicolon character (;), yes, that little guy.

The delimiter can be handed over as an unquoted or a quoted argument. Quoting works with either a single quote (') or a double quote ("). Wanna put a quote inside a quoted string? Either quote the string with the other quote character, or escape the quote with a backslash (\). Avoid backslashes outside of quoted strings though, because backslash is the escape character for MariaDB. For an unquoted argument, the delimiter gets read up to the first space or the end of the line. For a quoted argument, it's read up to the matching quote on the line.

Now the plot twist: when the delimiter mariadb recognizes 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. That server-side behaviour matters for multiple-statement execution, and for parsing the body of stored procedures and functions, triggers, and events.

• edit, \e — Edits the current input statement. mariadb peeks at the values of the EDITOR and VISUAL environment variables to figure out which editor to use. If neither variable is set, the default editor is vi. edit works only in Unix.
• ego, \G — Sends the current statement to the server to be executed, and shows the result in vertical format. (Yes this is the one you spam when a wide table looks like a crime scene.)
• exit, \q — Exits mariadb.
• go, \g — Sends the current statement to the server to be executed.
• nopager, \n — Disables output paging. See the description for pager. Works only in Unix.
• notee, \t — Disables copying output into the tee file. See the description for tee.
• nowarning, \w — Enables display of warnings after each statement. (That is word for word what the page says, bestie. I'm just the messenger. 🙈)
• pager [command], \P [command] — Enables output paging. Use the --pager option when you launch mariadb and you can browse or search query results in interactive mode using Unix programs like less, more, or any other similar program. Give the option no value and mariadb checks the PAGER environment variable and sets the pager to that. Pager functionality works only in interactive mode.

You can flip output paging on interactively with the pager command and off with nopager. The command takes an optional argument; hand it one and the paging program gets set to that. With no argument, the pager is set to the pager that was set on the command line, or to stdout if no pager was specified.

Output paging works only in Unix, because it uses the popen() function, which does not exist on Windows. On Windows you can use the tee option instead to save query output — although it's not as convenient as pager for browsing output in some situations.

• print, \p — Prints the current input statement without executing it.
• prompt [str], \R [str] — Reconfigures the mariadb prompt to the given string. The special character sequences you can use in the prompt are described later in this section. Give prompt no argument and mariadb resets the prompt to the default, mariadb>.
• quit, \q — Exits mariadb.
• rehash, \# — Rebuilds the completion hash, which is the thing enabling 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 — Gives you status information about the connection and the server you're using. And 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. Works only in Unix.
• tee [file_name], \T [file_name] — Use the --tee option when you launch mariadb and you can log statements and their output. All the data displayed on the 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 enable this interactively with the tee command. No parameter? The previous file gets used. The tee file can be turned off with the notee command. Run tee again and logging comes right back on.

• use db_name, \u db_name — Uses db_name as the default database.
• warnings, \W — Enables display of warnings after each statement (if there are any).

---

Here are a few tips about the pager command, because this thing is lowkey a superpower:

• You can use it to write to a file, and the results go only to the file: mariadb> pager cat > /tmp/log.txt

You can also pass any options for the program you want to use as your pager: mariadb> pager less -n -i -S

• In that example above, clock the -S option. It's so useful for browsing wide query results. Sometimes a super wide result set is a nightmare to read on screen. The -S option to less makes the result set way more readable, because you can scroll it horizontally with the left-arrow and right-arrow keys. You can also use -S interactively inside less to switch horizontal-browse mode on and off. For more info, read the less manual page:

shell> man less

• The -F and -X options can be used with less to make it exit if the 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 sends query results to two files in two different directories on two different file systems mounted on /dr1 and /dr2 — and still displays the results onscreen via less. Iconic behaviour honestly. 👑

You can also combine the tee and pager functions. Have a tee file enabled, set the pager to less, and boom: you can browse results with less and 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 don't have the Unix tee available. The built-in tee also logs everything that gets printed on the screen, whereas the Unix tee used with pager does not log quite that much. Plus, tee file logging can be turned on and off interactively from inside mariadb — which is exactly what you want when you wanna log some queries to a file but not others. 🫶

Okay bestie, real talk: the prompt command reconfigures the default mariadb> prompt. The string for defining the prompt can contain these special sequences:

• \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 backslash followed by 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

You can set the prompt in several ways:

• Use an environment variable. You can set the MYSQL_PS1 environment variable to a prompt string. For example:

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

• Use a command-line option. You can 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. You can set the prompt option in the [mariadb] group of any MariaDB option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For example:

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

In this example, note that the backslashes are doubled. If you set the prompt using the prompt option in an option file, it is advisable to double the backslashes when using the special prompt options. There is some overlap in the set of allowable prompt options and the set of special escape sequences that are recognized in option files. The overlap may 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 interactively 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 provide an argument to the help command, mariadb uses it as a search string to access server-side help. The proper operation of this command requires that the help tables in the mysql database be initialized with help topic information.

If there is no match for the search string, the search fails (sad, I know):

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 (the whole menu, slay):

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 the search string matches multiple items, mariadb shows a list of matching topics (pick your fighter):

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

Use a topic as the search string to see the help entry for that topic (main character energy):

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 |
+---------------+-----------+

🌸 EXECUTING SQL STATEMENTS FROM A TEXT FILE 🌸

okay so normally you just vibe with mariadb interactively, like:

shell> mariadb db_name

but bestie, you can also just... put all your SQL statements in a file and tell mariadb to read from it instead. iconic. so: make a text file text_file with the statements you wanna run, then invoke it like this:

shell> mariadb db_name < text_file

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

shell> mariadb < text_file

already in mariadb and wanna run a script file? use the source command or the \. command:

mariadb> source file_name
mariadb> \. file_name

sometimes you want your script to show progress info to the user 🫶 and for that you can drop in statements like this:

SELECT '<info_to_display>' AS ' ';

that statement outputs <info_to_display>. slay.

you can also invoke mariadb with the --verbose option, which makes every statement get displayed before its result shows up.

also bestie, mariadb ignores Unicode byte order mark (BOM) characters at the beginning of input files. a BOM being there does not make mariadb change its default character set. to change that, invoke mariadb with an option like --default-character-set=utf8.

---

💅 MARIADB TIPS 💅

this section is basically me handing you my tips & tricks so you can use mariadb way more effectively. you're welcome.

🦋 Displaying Query Results Vertically 🦋

some query results are just so much more readable when they're shown vertically instead of the usual horizontal table format. you display a query vertically by ending it 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 😭). it saves you in those cases where you might have typed a DELETE FROM tbl_name statement and forgotten the WHERE clause. normally that statement deletes every row from the table. with --safe-updates, you can only delete rows by specifying the key values that identify them. it helps prevent accidents. we love a safety net.

when you use the --safe-updates option, mariadb issues this 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're 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 different from 1,000 and 1,000,000? you can override the defaults using the --select-limit and --max-join-size options:

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

🎀 Disabling mariadb Auto-Reconnect 🎀

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 that statement again. but here's the tea ☕ — even if mariadb succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: 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 was shut down and restarted between the first and second statements without you even knowing:

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 with the connection, and after the reconnection it is undefined. if it's important to you that mariadb terminates with an error when the connection has been lost, you can start the mariadb client with the --skip-reconnect option.

---

📜 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 info, please refer to the MariaDB Documentation, available online at https://mariadb.com/docs/

---

💖 AUTHOR 💖

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

---

and that's the whole page bestie, you made it to the end 🫶 this was MariaDB 11.8, dated 5 March 2025, and it's MARIADB(1) — go forth and query, no cap 💅✨