Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 40 ยท Getting Started

The mariadb Client and Other Tools

Use the mariadb command-line client, run scripts, read SHOW and DESCRIBE output, change the prompt and output format, and pick a graphical tool such as HeidiSQL or DBeaver.

Once the server runs, you need a way to talk to it. The most important tool is the command-line client, mariadb (older installs call it mysql; both names work). Learning it pays off because it exists on every server, works over SSH and is scriptable. Graphical tools are a comfortable addition for exploring data.

Connecting

Terminal
mariadb -u app -p shop              # prompt for the password, select database shop
mariadb -u app -p -h 10.0.0.5 -P 3306 shop
mariadb -u root                      # socket login as the OS root user (Linux)
  • -u is the user, -p asks for the password (do not type it on the command line, it ends up in the shell history), -h the host and -P the port.
  • The last word is the default database, the same as typing USE shop; later.

You then see a prompt such as MariaDB [shop]>. A statement ends with a semicolon, and it may span several lines. Type \c to cancel a half-typed statement, and exit or Ctrl+D to leave.

Ending statements: ; and \G

\G replaces the semicolon and prints each row vertically, which is perfect for wide tables:

SQL
SELECT * FROM customers WHERE id = 1\G
TEXT
*************************** 1. row ***************************
        id: 1
first_name: Ada
...

Running scripts and one-liners

Terminal
mariadb -u app -p shop < schema.sql                 # run a file
mariadb -u app -p shop -e "SELECT COUNT(*) FROM orders"   # run one statement
mariadb -u app -p -N -B -e "SELECT id, name FROM products" shop > products.tsv

-N drops the column header and -B prints tab-separated "batch" output, which is easy to feed into other programs. Inside the client, SOURCE schema.sql; runs a file too. If one statement in a script fails, the client stops unless you add --force.

Handy client commands

Command What it does
\h or help List client commands
\s or status Show connection, version and character set
USE name; Switch database
\T file.txt / \t Start or stop copying output to a file (tee)
\P less Pipe output through a pager; \n turns it off
\e Edit the current statement in $EDITOR
\q Quit

You can set the prompt in the option file so you never forget where you are:

INI
[mariadb]
prompt = "\u@\h [\d]> "
safe-updates

safe-updates (also --i-am-a-dummy) refuses UPDATE and DELETE statements without a key in the WHERE clause or a LIMIT, a good habit on a production console.

Looking around: SHOW and DESCRIBE

These statements are MariaDB's way to inspect the server. They are read-only here:

SQL
SHOW DATABASES;
SHOW TABLES;                       -- tables in the current database
SHOW FULL TABLES WHERE Table_type = 'VIEW';
DESCRIBE products;                 -- same as SHOW COLUMNS FROM products
SHOW CREATE TABLE products\G       -- the exact DDL
SHOW INDEX FROM products;
SHOW TABLE STATUS LIKE 'orders'\G
SHOW VARIABLES LIKE 'innodb%';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;
SHOW WARNINGS;                     -- messages from the last statement

DESCRIBE products prints a table of column name, type, whether NULL is allowed, key membership, default and extras:

TEXT
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100)  | NO   |     | NULL    |                |
| price       | decimal(10,2) | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

All of this comes from the system catalog, information_schema, which you can also query with ordinary SELECT. The runnable equivalent in SQLite is PRAGMA table_info, which returns the same facts for a table:

SQL A DESCRIBE stand-in ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The same idea works for a whole database. Count the columns per table:

SQL Columns per table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

On MariaDB you would write:

SQL
SELECT table_name, COUNT(*) AS column_count
FROM information_schema.columns
WHERE table_schema = DATABASE()
GROUP BY table_name
ORDER BY column_count DESC;

Comments and delimiters

MariaDB accepts three comment styles:

SQL
-- double dash needs a space after it
# hash comment to end of line
/* block
   comment */
/*M! SEQUENCE_ONLY_IN_MARIADB */   -- executable comment that only MariaDB runs

Because ; ends a statement, writing a stored procedure with several statements inside confuses the client. You change the terminator with DELIMITER // before the body and back with DELIMITER ; afterwards. The stored programs chapter returns to this.

Graphical tools

Tool Best for
HeidiSQL Light Windows client with a good table editor (ships with the MariaDB installer)
DBeaver Cross-platform, many databases, ER diagrams
phpMyAdmin Browser-based, common on shared hosting
MySQL Workbench Modeling and administration (works with MariaDB for basics)
VS Code extensions Run queries beside your code

GUIs are friendly for browsing, but they can hide what SQL they send. Keep the command line for scripts, migrations and servers without a desktop.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should you avoid typing the password after -p on the command line?

    Typing only -p makes the client prompt for the password, which keeps it out of history and process lists.
  2. 2. What does ending a statement with \G do?

    \G is an alternative terminator that shows one column per line, which makes wide rows readable.
  3. 3. Which statement shows the exact CREATE TABLE text of an existing table?

    SHOW CREATE TABLE returns the full definition including engine, charset and keys.
  4. 4. What does the safe-updates option do?

    It protects you from forgetting a WHERE clause on a console session.

Summary

  • mariadb -u user -p database connects; -e runs one statement and < runs a script file.
  • \G prints rows vertically, \s shows status, and safe-updates guards against sloppy UPDATE and DELETE.
  • SHOW, DESCRIBE and information_schema reveal the structure of the server.
  • Graphical tools such as HeidiSQL and DBeaver are good for browsing; the command line is best for automation.

Next: Databases in MariaDB