MariaDB Shell

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), the warm command, which populates the database's in-memory cache with a table or schema, and the pre-slice command, which pre-slices tables in an Xpand cluster

  • MariaDB Shell supports commands from other MariaDB tools, including commands from mariadb client and mariadb-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 in 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

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)

Configure MariaDB Enterprise Repository (Linux)

To install MariaDB Shell with a package manager on Linux, the MariaDB Enterprise Repository must be configured.

MariaDB Corporation provides the MariaDB Enterprise Repository for customers of MariaDB Corporation to install MariaDB database products using the operating system's package manager.

The MariaDB Enterprise Repository is configured using the mariadb_es_repo_setup script, which requires the Customer Download TokenCustomer Download Token to be provided via the --token option.

Configure the MariaDB Enterprise Repository using the following procedure:

  1. Retrieve your Customer Download TokenRetrieve your Customer Download Token.

  2. Install the prerequisites for downloading the software from the Web by executing the specific command for your operating system shown below.

    • On CentOS, RHEL, and Rocky Linux, install the following prerequisites with YUM:

      $ sudo yum install wget
      
    • On Debian and Ubuntu, install the following prerequisites with APT:

      $ sudo apt install wget apt-transport-https
      
    • On SLES, install the following prerequisites with ZYpp:

      $ sudo zypper install wget
      
  3. Configure the MariaDB Enterprise Repository using the mariadb_es_repo_setup script and provide the Customer Download Token using the --token option:

    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "3f4a9d1c507a846a598e95d6223871aade69a9955276455324e7cc5f54a87021  mariadb_es_repo_setup" \
           | sha256sum -c -
    
    $ chmod +x mariadb_es_repo_setup
    
    $ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply
    

    To learn how to configure your system to install a specific product or version from the MariaDB Enterprise Repository, see the available mariadb_es_repo_setup command-line options.

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.

  1. Configure the MariaDB Enterprise Repository

  2. Install MariaDB Shell using 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
      
    • On SLES, install MariaDB Shell with ZYpp:

      $ sudo zypper install mariadb-shell
      
  3. 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) »
    

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""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 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: SKYSQL_DEFAULT_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

Execute a Query

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 │
└─────────────────────┘
1 row in set (0.000800).

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.

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 Commands".

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.

output

The output sub-command is used to change the output format.

For additional information, see "Output Formats".

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.

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.

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 example, to execute MariaDB Shell's re-implementation of the mariadb-admin status sub-command:

MariaDB (No Database) » system status
Uptime:			0:18:08
Threads Created:	2
Questions:		5
Opened Tables:		16
Open Tables:		10
Queries per second ave:	0.004596

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.

MariaDB Tools Commands

Some commands require MariaDB Tools to be installed.

MariaDB Tools commands can be executed from an interactive shell:

MariaDB (No Database) » summary

MariaDB Tools commands can also be executed using CLI commands:

$ mariadb-shell \
   --host HOSTNAME --port TCP_PORT \
   --user DATABASE_USER --password USER_PASSWORD \
   summary \
   > ~/mariadb-summary-output.log

summary

If MariaDB Tools is installed, MariaDB Shell can call the the mariadb-summary tool using the summary command:

$ mariadb-shell \
   --host HOSTNAME --port TCP_PORT \
   --user DATABASE_USER --password USER_PASSWORD \
   summary \
   > ~/mariadb-summary-output.log

system-summary

If MariaDB Tools is installed, MariaDB Shell can call the the mariadb-system-summary tool using the system-summary command:

$ mariadb-shell \
   --host HOSTNAME --port TCP_PORT \
   --user DATABASE_USER --password USER_PASSWORD \
   system-summary \
   > ~/mariadb-system-summary-output.log

FISH Commands

Fully Integrated System Histogram (FISH) commands can be executed from an interactive shell:

MariaDB (No Database) » fish report

Fully Integrated System Histogram (FISH) commands can also be executed using CLI commands:

$ mariadb-shell \
   --host HOSTNAME --port TCP_PORT \
   --user DATABASE_USER --password USER_PASSWORD \
   fish report \
   > ~/mariadb-fish-report.log

collection

The fish collection command provides a set of options for collecting metrics to produce a FISH report.

dump

The fish dump command creates a JSON file containing the metrics used to produce FISH reports:

MariaDB (No Database) » fish dump

report

The fish report command can create FISH reports:

MariaDB (No Database) » fish report

Output Formats

MariaDB Shell supports multiple output formats.

The output format can be set from an interactive shell:

MariaDB (No Database) » output modern
Output format changed to modern

The output format can also be set using the -o or --output CLI options:

$ mariadb-shell \
   --host HOSTNAME --port TCP_PORT \
   --user DATABASE_USER --password USER_PASSWORD \
   --output modern

The following output formats are supported:

  • classic

  • modern

  • json

  • sqlplus

  • csv

  • xml

  • html