Command-Line Scripting

Overview

Command-line scripts can connect to MariaDB database products database services in MariaDB SkySQL using the mariadb client:

  • MariaDB Client (mariadb) enables non-interactive connections from command-line scripts

  • MariaDB Client is available on Linux, macOS, and Microsoft Windows

  • Command-line options define client behavior

Alternatively, you can connect interactively or from your application.

Use Cases

  • Execute queries from the command-line

  • Execute queries from a script, such as a bash shell script

  • Execute queries from a file or piped in from another program

  • Save query output to a file

Compatibility

MariaDB Client (mariadb) has been tested by MariaDB to properly connect and execute queries with:

  • MariaDB Enterprise ColumnStore

  • MariaDB Enterprise Server

  • MariaDB MaxScale

  • MariaDB Xpand

  • Distributed Transactions

  • Multi-Node Analytics

  • Replicated Transactions

  • Single Node Analytics

  • Single Node Transactions

Connection Parameters

The connection parameters connection parameters must be provided to the mariadb client using command-line options.

When connecting to MariaDB Xpand a Distributed Transactions service, the --no-auto-rehash command-line option must also be set.

The command-line options can be specified in multiple ways.

Command-Line Arguments

The database SkySQL service's connection parameters can be specified as command-line arguments:

mariadb --host mariadb1.example.com \
    --port 5001 \
    --user DB_USER_NAME \
    --password 'DB_USER_PASSWORD' \
    --ssl-verify-server-cert \
    --ssl-ca ca_chain.pem

mariadb --host doc-test-tx-single.mdb0000001.db.skysql.net \
    --port 5001 \
    --user DB00000001 \
    --password 'SKYSQL_DEFAULT_PASSWORD' \
    --ssl-verify-server-cert \
    --ssl-ca skysql_chain.pem

In the example above, the database user's password is hard-coded. Passwords are ideally stored in a shared secret system. Please adapt the example to satisfy your security requirements.

Configuration File

The database SkySQL service's connection parameters can be specified in a configuration file in the [client] or [mariadb-client] groups:

[client]
host=mariadb1.example.com
port=5001
user=DB_USER_NAME
password=DB_USER_PASSWORD
ssl-verify-server-cert
ssl-ca=/path/to/ca_chain.pem

[client]
host=doc-test-tx-single.mdb0000001.db.skysql.net
port=5001
user=DB00000001
password=SKYSQL_DEFAULT_PASSWORD
ssl-verify-server-cert
ssl-ca=/path/to/skysql_chain.pem

On Linux and macOS, the mariadb client reads options from ~/.my.cnf by default.

On any OS, the mariadb client can read options from a custom configuration file by specifying the --defaults-file or the --defaults-extra-file options:

mariadb --defaults-file ~/connection_parameters.cnf

If a configuration file contains sensitive information, such as a password, then access to the file should be restricted by changing the file's permissions:

$ chmod 0600 ~/connection_parameters.cnf

Execute queries from a command-line argument

The mariadb client can execute queries from a command-line argument by specifying the --execute option:

mariadb --defaults-file ~/connection_parameters.cnf \
    --execute 'CALL run_daily_reports();'

Execute queries from a file

The mariadb client can execute queries from a file using a shell redirection for standard input:

mariadb --defaults-file ~/connection_parameters.cnf \
   < run_daily_reports.sql

Execute queries output by another command-line utility

The mariadb client can execute queries output by another command-line utility by piping the utility's output to the client:

generate_report_sql.sh | mariadb --defaults-file ~/connection_parameters.cnf

Write query output to a file

The mariadb client can write query output to a file using a shell redirection for standard output:

mariadb --defaults-file ~/connection_parameters.cnf \
   < run_daily_reports.sql \
   > daily_report_output.log

The output can be customized with command-line options:

  • To reduce output, specify --silent. Errors can still be caught by inspecting the command-line client's exit code.

  • To output data in a tab-separated values (TSV) format, specify --batch. The --batch option implicitly enables --silent.

  • To remove the column name header from the output, specify --skip-column-names.

  • To prevent special characters from being escaped in the output, specify --raw.