MariaDB Shell
This page is part of MariaDB's MariaDB Documentation.
The parent of this page is: Interactive Clients
Topics on this page:
Overview
MariaDB Shell, mariadb-shell
, is an administrative tool for managing your MariaDB deployments:
MariaDB Shell implements commands to perform advanced administrative operations, such as the
fish
command, which outputs a Full Internal System Histogram (FISH), thewarm
command, which populates the database's in-memory cache with a table or schema, and thepre-slice
command, which pre-slices tables in an Xpand clusterMariaDB Shell supports commands from other MariaDB tools, including commands from
mariadb
client andmariadb-admin
A range of output formats are supported, including: classic, modern, JSON, SQL*Plus, CSV, XML, and HTML
MariaDB Shell is available for Linux, Windows, and macOS
MariaDB Shell is a Technical Preview and should not be used for production environments.
Compatibility
MariaDB Shell (mariadb-shell
) has been tested by MariaDB to properly connect and execute commands with:
MariaDB Enterprise ColumnStore 6
MariaDB Enterprise ColumnStore 22.08
MariaDB Enterprise Server 10.4
MariaDB Enterprise Server 10.5
MariaDB Enterprise Server 10.6
MariaDB MaxScale 2.4
MariaDB MaxScale 2.5
MariaDB MaxScale 6
MariaDB MaxScale 22.08
MariaDB Xpand 5
MariaDB Xpand 6.0
MariaDB Xpand 6.1
Supported Operating Systems
Apple macOS (x86_
64 / M1) CentOS 7 (x86_
64) Debian 10 (x86_
64) Debian 11 (x86_
64) Microsoft Windows (x86_
64) Red Hat Enterprise Linux 7 (x86_
64) Red Hat Enterprise Linux 8 (x86_
64) Red Hat Enterprise Linux 9 (x86_
64) Rocky Linux 8 (x86_
64) Rocky Linux 9 (x86_
64) Ubuntu 18.04 (x86_
64) Ubuntu 20.04 (x86_
64) Ubuntu 22.04 (x86_
64)
Install MariaDB Shell
Installation of MariaDB Shell varies by operating system.
Linux (via Repository)
If the MariaDB Enterprise Repository is configured, MariaDB Shell can be installed on Linux using the operating system's package manager.
MariaDB Tools is recommended to provide additional administrative capabilities. When MariaDB Shell is installed from the MariaDB Enterprise Repository, MariaDB Tools will be installed automatically.
To install MariaDB Shell, perform the following steps in the order shown below:
Configure the MariaDB Community Repository, which is required to install MariaDB Shell's client library (MariaDB Connector/C).
Install MariaDB Connector/C from the MariaDB Community Repository by executing the specific command for your operating system shown below.
On CentOS, RHEL, and Rocky Linux, install MariaDB Connector/C with YUM:
$ sudo yum install MariaDB-shared
On Debian and Ubuntu, install MariaDB Connector/C with APT:
$ sudo apt install libmariadb3
Configure the MariaDB Enterprise Repository, which is required to install MariaDB Shell.
Configuring the MariaDB Enterprise Repository overwrites the configuration of the MariaDB Community Repository.
Install MariaDB Shell from the MariaDB Enterprise Repository by executing the specific command for your operating system shown below.
On CentOS, RHEL, and Rocky Linux, install MariaDB Shell with YUM:
$ sudo yum install mariadb-shell
On CentOS 7, the
openssl11
package must be installed.On Debian and Ubuntu, install MariaDB Shell with APT:
$ sudo apt install mariadb-shell
When the installation is complete, open a terminal window and confirm that MariaDB Shell can be executed:
$ mariadb-shell
SLDB (0101) - Error connecting to MariaDB Service: Access denied for user 'USERNAME'@'localhost' For more information see: https://docs.mariadb.com/mariadb-shell/SLDB0101 Welcome to MariaDB Shell! SQL statements end with semicolons (;) or \g. Copyright (c) 2022 MariaDB Corporation Ab. Type 'help' for help, 'commands' for shell commands, 'bugs' to report errors. For connection information, use 'sys status'. Type 'connect' for connection wizard. MariaDB Shell is in technical preview and should not be used in production environments. Use at your own risk. MariaDB (No Database) »
Linux (via Browser)
MariaDB Shell can be downloaded using a web browser:
Access the MariaDB downloads page.
Choose the following:
For "Product", choose "MariaDB Shell"
For "OS", choose the package for your Linux distribution
Click the "Download" button.
When the download is complete, install the downloaded package.
When the installation is complete, open a terminal window and confirm that MariaDB Shell can be executed:
$ mariadb-shell
SLDB (0101) - Error connecting to MariaDB Service: Access denied for user 'USERNAME'@'localhost' For more information see: https://docs.mariadb.com/mariadb-shell/SLDB0101 Welcome to MariaDB Shell! SQL statements end with semicolons (;) or \g. Copyright (c) 2022 MariaDB Corporation Ab. Type 'help' for help, 'commands' for shell commands, 'bugs' to report errors. For connection information, use 'sys status'. Type 'connect' for connection wizard. MariaDB Shell is in technical preview and should not be used in production environments. Use at your own risk. MariaDB (No Database) »
MariaDB Tools is recommended to provide additional administrative capabilities. MariaDB Tools can also be downloaded using a web browser:
Access the MariaDB downloads page.
Choose the following:
For "Product", choose "MariaDB Tools"
For "OS", choose the package for your Linux distribution
Click the "Download" button.
When the download is complete, install the downloaded package.
macOS
To install MariaDB Shell on macOS:
Access the MariaDB downloads page.
Choose the following:
For "Product", choose "MariaDB Shell"
For "OS", choose "macOS"
Click the "Download" button.
When the download is complete, double click on the
MariaDB Shell.pkg
file to start the install wizard.When the installation is complete, open a terminal window and confirm that MariaDB Shell can be executed:
$ mariadb-shell
SLDB (0101) - Error connecting to MariaDB Service: Access denied for user 'USERNAME'@'localhost' For more information see: https://docs.mariadb.com/mariadb-shell/SLDB0101 Welcome to MariaDB Shell! SQL statements end with semicolons (;) or \g. Copyright (c) 2022 MariaDB Corporation Ab. Type 'help' for help, 'commands' for shell commands, 'bugs' to report errors. For connection information, use 'sys status'. Type 'connect' for connection wizard. MariaDB Shell is in technical preview and should not be used in production environments. Use at your own risk. MariaDB (No Database) »
Microsoft Windows
MariaDB Shell is supported on Microsoft Windows.
To install MariaDB Shell on Microsoft Windows:
Access the MariaDB downloads page.
Choose the following:
For "Product", choose "MariaDB Shell"
For "OS", choose "MS Windows (64-bit)"
Click the "Download" button.
When the MSI package download is complete, click on the file to run the installation wizard.
Click the "Finish" button.
When the installation is complete, open a command prompt and confirm that MariaDB Shell can be executed:
C:\>"C:\Program Files\MariaDB Shell\mariadb-shell.exe"
SLDB (0101) - Error connecting to MariaDB Service: Access denied for user 'USERNAME'@'localhost' For more information see: https://docs.mariadb.com/mariadb-shell/SLDB0101 Welcome to MariaDB Shell! SQL statements end with semicolons (;) or \g. Copyright (c) 2022 MariaDB Corporation Ab. Type 'help' for help, 'commands' for shell commands, 'bugs' to report errors. For connection information, use 'sys status'. Type 'connect' for connection wizard. MariaDB Shell is in technical preview and should not be used in production environments. Use at your own risk. MariaDB (No Database) »
Connect
MariaDB Shell uses the same connection syntax on all operating systems.
There are multiple ways to specify connection parameters.
DSN Option
MariaDB Shell provides the --dsn
command-line option that can be used to specify connection parameters:
$ mariadb-shell --dsn mariadb://DATABASE_USER:USER_PASSWORD@HOSTNAME:TCP_PORT/?ssl-ca=CA_CHAIN_PATH
Replace
HOSTNAME
with the IP address or Fully Qualified Domain Name of your database.Replace
TCP_PORT
with the TCP port of your database.Replace
DATABASE_USER
with the username for your database user account.Replace
USER_PASSWORD
with the password for your database user account or leave it blank and type it when prompted.Replace
CA_CHAIN_PATH
with the path to the CA chain certificate when TLS is used.
For information about how to obtain the connection parameters, see "Connection Parameters" .
Compatibility Options
For compatibility, MariaDB Shell provides command-line options that are similar to the connection-related options provided by tools like mariadb
client:
$ mariadb-shell \
--host HOSTNAME --port TCP_PORT \
--user DATABASE_USER --password USER_PASSWORD \
--ssl-ca CA_CHAIN_PATH
For the
--host
command-line option, replaceHOSTNAME
with the IP address or Fully Qualified Domain Name of your database.For the
--port
command-line option, replaceTCP_PORT
with the TCP port of your database.For the
--user
command-line option, replaceDATABASE_USER
with the username for your database user account.For the
--password
command-line option, replaceUSER_PASSWORD
with the password for your database user account or leave it blank and type it when prompted.For the
--ssl-ca
command-line option, replaceCA_CHAIN_PATH
with the path to the CA chain certificate when TLS is used.
For information about how to obtain the connection parameters, see "Connection Parameters" .
Connection Wizard
MariaDB Shell provides a connection wizard that can be used to save a set of connection parameters in a profile.
To run the connection wizard, use the connect
or reconnect
command from an interactive shell:
MariaDB (No Database) » connect
You must provide the connection settings for the new connection.
Host: myservice.mdb0000001.db.skysql.net
Username: DB00000001
Password:
Schema: db1
Are you using a socket? [n]|y: n
Port: 5001
Do you require SSL? [n]|y: y
SSL.pem Path: ~/skysql-chain.pem
SSL Certificate Path:
SSL Key Path:
SSL Certificate Authority Path:
Database changed to db1
Do you want to create a connection profile with current setup? [y]|n: y
Profile Name:dev
Persisted configuration {'host': 'myservice.mdb0000001.db.skysql.net', 'user': 'DB00000001', 'password': 'SKYSQL_DEFAULT_PASSWORD', 'database': 'db1', 'port': 5001, 'ssl_ca': '/home/mariadb-user/skysql-chain.pem'} for profile dev
For security reasons, when MariaDB Shell prompts for the password, the provided password is not printed to the screen.
SSH Tunnel
MariaDB Shell can connect to database servers using a remote SSH tunnel.
When the --ssh-user
and --ssh-host
command-line options are specified, a remote SSH tunnel is created between the client and the remote host:
On the client, the tunnel uses a random ephemeral TCP port. The client initiates the tunnel by connecting to the remote server on the standard SSH port. If the remote server uses a non-standard SSH port, it can be specified using the
--ssh-port
command-line option.On the remote server, the traffic at the end of the tunnel is forwarded to the IP address
127.0.0.1
on the database port. The database port is specified in the normal way using the--port
command-line option. Since the traffic is forwarded to127.0.0.1
, the SSH tunnel must terminate at the database server itself. Jump hosts are not currently supported.
MariaDB Shell supports password authentication and public key authentication for SSH tunnels:
The
--ssh-pass
command-line option can be used to enable password authentication. If an argument is provided, the argument is interpreted as the password. If no argument is provided, the user is prompted for the password.The
--ssh-key
command-line option can be used to enable public key authentication. The argument must be the absolute path to the private key file. If the private key file is encrypted, the--ssh-key-pass
command-line option can be used to specify the encryption password.
For example, to use a remote SSH tunnel with password authentication:
$ mariadb-shell \
--ssh-host HOSTNAME --ssh-port SSH_PORT \
--ssh-user SSH_USER --ssh-password SSH_PASSWORD \
--port TCP_PORT \
--user DATABASE_USER --password DATABASE_PASSWORD
Or to use a remote SSH tunnel with public key authentication:
$ mariadb-shell \
--ssh-host HOSTNAME --ssh-port SSH_PORT \
--ssh-user SSH_USER --ssh-key /home/MY_USER/.ssh/id_rsa --ssh-key-pass SSH_KEY_PASSWORD \
--port TCP_PORT \
--user DATABASE_USER --password DATABASE_PASSWORD
Execute a Query
MariaDB Shell provides multiple ways to execute queries.
Interactive Shell
To execute queries in an interactive shell, type the queries and press enter:
MariaDB (No Database) » SELECT NOW();
┌─────────────────────┐
│ NOW() │
├─────────────────────┤
│ 2022-10-06 23:20:54 │
└─────────────────────┘
Command-Line
To execute queries using the command-line, use the --execute
command-line option:
$ mariadb-shell \
--host HOSTNAME --port TCP_PORT \
--user DATABASE_USER --password USER_PASSWORD \
--execute='SELECT NOW();'
Piped from Command
To execute queries piped from another command:
$ echo 'SELECT NOW();' | \
mariadb-shell \
--host HOSTNAME --port TCP_PORT \
--user DATABASE_USER --password USER_PASSWORD
SQL Script
To execute queries from a SQL script, use standard input redirection:
$ mariadb-shell \
--host HOSTNAME --port TCP_PORT \
--user DATABASE_USER --password USER_PASSWORD \
< run_report.sql
View Commands And Options
MariaDB Shell provides many commands that support extensive capabilities.
To see a full list of the available commands and their options, use the help
command from an interactive shell:
MariaDB (No Database) » help
Or use the --help
option from the CLI:
$ mariadb-shell --help
Interactive Shell Commands
Some commands require an interactive shell.
To open an interactive shell, use MariaDB Shell to connect to a database without specifying a command:
$ mariadb-shell \
--host HOSTNAME --port TCP_PORT \
--user DATABASE_USER --password USER_PASSWORD
Welcome to MariaDB Shell! SQL statements end with semicolons (;) or \g.
Connected to 10.6.9-5-MariaDB-enterprise-log.
Copyright (c) 2022 MariaDB Corporation Ab & others.
Type 'help' for help, 'commands' for shell commands, 'bugs' to report errors. For connection information, use 'sys status'.
MariaDB Shell is in technical preview and should not be used in production environments. Use at your own risk.
MariaDB (No Database) »
The interactive shell can be used to execute certain types of commands.
The interactive shell can also be used to execute queries.
bugs
The bugs
sub-command is used to obtain details on how to file a bug report.
clear
The clear
sub-command is used to clear the contents of the terminal screen.
commands
The commands
sub-command is used to print the supported commands.
connect
The connect
sub-command is used to reconnect to the database with established credentials or connect to a new host.
dir
The dir
sub-command is used to print the available databases and tables.
If no database is selected and no database name is provided as an argument, it prints the databases that can be used.
If a database is selected or if a database name is provided as an argument, it prints the tables that can be queried in the database.
The dir
sub-command is an alias for the ls
sub-command.
disconnect
The disconnect
sub-command is used to exit the interactive shell.
exit
The exit
sub-command is used to exit the interactive shell.
fish
The fish
sub-command is used to run operations involving the Full Internal System Histogram.
For additional information, see "FISH Sub-commands for MariaDB Shell".
grants
The grants
sub-command is used to obtain user account privileges.
When the sub-command is executed without any arguments, the sub-command prints the privileges in a tabular form for the user account that is currently connected:
MariaDB (No Database) » grants
Grants for root
┌───────────┬────────┬────────┬────────────────┬────────────────────┐
│ Host │ Schema │ Object │ Grants │ With │
├───────────┼────────┼────────┼────────────────┼────────────────────┤
│ % │ * │ * │ ALL PRIVILEGES │ grant_option: True │
│ localhost │ * │ * │ ALL PRIVILEGES │ grant_option: True │
│ localhost │ │ % │ PROXY │ grant_option: True │
└───────────┴────────┴────────┴────────────────┴────────────────────┘
When the sub-command is executed with for <USER_SPECIFICATION>
as an argument, the sub-command prints the privileges in a tabular form for the specified user account:
MariaDB (No Database) » grants for 'root'@'localhost'
Grants for root
┌───────────┬────────┬────────┬────────────────┬────────────────────┐
│ Host │ Schema │ Object │ Grants │ With │
├───────────┼────────┼────────┼────────────────┼────────────────────┤
│ % │ * │ * │ ALL PRIVILEGES │ grant_option: True │
│ localhost │ * │ * │ ALL PRIVILEGES │ grant_option: True │
│ localhost │ │ % │ PROXY │ grant_option: True │
└───────────┴────────┴────────┴────────────────┴────────────────────┘
To obtain the raw GRANT
statements in a tabular form, use the grants raw
sub-command:
MariaDB (No Database) » grants raw
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Grants for root@% │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ CREATE USER `root`@`%` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476'; │
│ GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476' WITH GRANT OPTION; │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Grants for root@localhost │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ CREATE USER `root`@`localhost` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476'; │
│ GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476' WITH GRANT OPTION; │
│ GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
To dump the raw GRANT
statement in text format, use the grants dump
or grants dump all
sub-command:
MariaDB (No Database) » grants dump all
-- MariaDB Shell grants dump
-- Dumped at 2022-10-24 12:09:58.719842
-- Host: 127.0.0.1
-- ------------------------------------------------------
-- Server version 10.6.9-5-MariaDB-enterprise-log
-- Grants for mariadb.sys@localhost
CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE;
GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`;
GRANT DELETE, SELECT ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`;
-- Grants for root@%
CREATE USER `root`@`%` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476';
GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476' WITH GRANT OPTION;
-- Grants for root@localhost
CREATE USER `root`@`localhost` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476';
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476' WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;
-- Grants for test_user@%
CREATE USER `test_user`@`%` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476';
GRANT USAGE ON *.* TO `test_user`@`%` IDENTIFIED BY PASSWORD '*4F56EF3FCEF3F995F03D1E37E2D692D420111476';
GRANT ALTER ON `hq_sales`.* TO `test_user`@`%`;
help
The help
sub-command is used to print the help message.
leave
The leave
sub-command is used to exit the interactive shell.
ls
The ls
sub-command is used to print the available databases and tables.
If no database is selected and no database name is provided as an argument, it prints the databases that can be used.
If a database is selected or if a database name is provided as an argument, it prints the tables that can be queried in the database.
The dir
sub-command is an alias for the ls
sub-command.
max
The max
sub-command is used to perform MaxScale operations.
For additional information about the max
sub-command, see "MaxScale Sub-commands for MariaDB Shell".
output
The output
sub-command is used to change the output format.
For additional information, see "Output Formats for MariaDB Shell".
pager
The pager
sub-command is used to set the pager used to show results.
pre-slice
The pre-slice
sub-command is used to pre-slice a table in an Xpand cluster.
profile
The profile
sub-command is used to print the current profile and a list of available ones (if no parameter is passed), or switch to a different profile (if a name is passed).
quit
The quit
sub-command is used to exit the interactive shell.
reconnect
The reconnect
sub-command is used to reconnect to the database with established credentials or connect to a new host.
ros
The ros
sub-command is used to interface with the Remote Observability Service.
For additional information about the ros
sub-command, see "Remote Observability Service Sub-commands for MariaDB Shell".
run
The run
sub-command can be used to execute commands in your default OS shell.
The \!
alias is also supported.
sys
The sys
sub-command is used to execute MariaDB Shell's re-implementations of the mariadb-admin
sub-commands.
The sys
sub-command is an alias for the system
sub-command.
For additional information about the sys
sub-command, see "System Sub-commands for MariaDB Shell".
system
The system
sub-command is used to execute MariaDB Shell's re-implementations of the mariadb-admin
sub-commands.
The sys
sub-command is an alias for the system
sub-command.
For additional information about the sys
sub-command, see "System Sub-commands for MariaDB Shell".
tail
The tail
sub-command is used to read and display the current logs of your MariaDB products installed on the current host.
warm
The warm
sub-command is used to populate the database's in-memory cache with a table or schema. This operation works for any in-memory page cache, including the InnoDB Buffer Pool, Aria's page cache, and Xpand's cache.
Interactive Backslash Commands
MariaDB Shell supports some backslash commands from MariaDB Client:
Command | Description |
---|---|
| Execute a command in your default OS shell. For additional information, see " |
| Get status information from the server. |
| Show warnings after every statement. |
| Don't show warnings after every statement. |
| Print current command. |
| Disable pager and print to standard output ( |