MariaDB Shell

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:

1. Configure the MariaDB Community Repository, which is required to install MariaDB Shell's client library (MariaDB Connector/C).

2. 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
     

3. 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.

4. 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
     

5. 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:

1. Access the MariaDB downloads page.

2. Choose the following:

   • For "Product", choose "MariaDB Shell"

   • For "OS", choose the package for your Linux distribution

3. Click the "Download" button.

4. When the download is complete, install the downloaded package.

5. 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:

1. Access the MariaDB downloads page.

2. Choose the following:

   • For "Product", choose "MariaDB Tools"

   • For "OS", choose the package for your Linux distribution

3. Click the "Download" button.

4. When the download is complete, install the downloaded package.

macOS

To install MariaDB Shell on macOS:

1. Access the MariaDB downloads page.

2. Choose the following:

   • For "Product", choose "MariaDB Shell"

   • For "OS", choose "macOS"

3. Click the "Download" button.

4. When the download is complete, double click on the MariaDB Shell.pkg file to start the install wizard.

5. 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:

1. Access the MariaDB downloads page.

2. Choose the following:

   • For "Product", choose "MariaDB Shell"

   • For "OS", choose "MS Windows (64-bit)"

3. Click the "Download" button.

4. When the MSI package download is complete, click on the file to run the installation wizard.

5. Click the "Finish" button.

6. 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) »
   

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" "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, replace HOSTNAME with the IP address or Fully Qualified Domain Name of your database.

• For the --port command-line option, replace TCP_PORT with the TCP port of your database.

• For the --user command-line option, replace DATABASE_USER with the username for your database user account.

• For the --password command-line option, replace USER_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, 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" "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 to 127.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

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

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.