The command-line client

The code and output examples in this book use the mysql command-line client. Knowing some client commands can greatly increase productivity when this tool is used.

The mysql command-line client knows that a SQL statement is terminated when it finds a semicolon (;), a \g, or a \G terminator. In the first case, the output is printed in a tabular form, shown as follows:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

Tip

Downloading the example code

You can download the example code files for all Packt Publishing books you have purchased from your account at http://www.packtpub.com. If you have purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

The mysql client has a prompt that normally appears at the beginning of a new line, as follows:

MariaDB [none]>

In the prompt, [none] means that no default database is selected. This means that, every time a table is named in a statement, the name of the database where it is located must be specifically specified. The USE statement selects a default database, whose name will appear in the prompt. The following example shows how to use it:

MariaDB [(none)]> USE test;
Database changed
MariaDB [test]>

When a statement spans on more lines, the lines begin with a different prompt, as shown in the following example:

MariaDB [test]> SELECT 1
    -> FROM DUAL;

If we forget to type a statement terminator, the modified prompt helps us notice the problem, shown as follows:

MariaDB [test]> SHOW TABLES
    ->

Here, the mysql client does not know that the statement is finished because a terminator (similar to a semicolon) is missing.

If a quote is open at the end of a line, the quoting character is shown in the prompt of the new line. While one could include a newline character in a string by pressing the Enter key, more often this happens by mistake. As we can see in the following example, the prompt helps us notice the problem:

MariaDB [test]> SELECT 'hello world FROM DUAL;
    '>

The problem here is that the end quote for the "hello world" string is missing. Note that the second line's prompt starts with a single quote.

Sometimes the tabular output is very difficult to read, particularly when output rows are longer than the command-line rows. When this is the case, the \G terminator is more convenient, as shown in the following example:

MariaDB [(none)]> SHOW VARIABLES LIKE 'char%' \G
*************************** 1. row ***************************
Variable_name: character_set_client
        Value: utf8
*************************** 2. row ***************************
Variable_name: character_set_connection
        Value: utf8
*************************** 3. row ***************************
Variable_name: character_set_database
        Value: latin1
…

On Linux systems, it is possible to use a pager program to read long outputs. Pagers provide the ability to scroll the output using the keyboard, or the mouse wheel, or any other method supported by the selected pager. Examples of good pagers are less, more, and lv (not installed by default on many distributions). To use less, run the following command:

MariaDB [(none)]> \P less
PAGER set to 'less'

The following queries will be seen with less. To disable the pager, run the following command:

MariaDB [(none)]> \P
Default pager wasn't set, using stdout.

Sometimes an output is long, but the user is only interested in a few rows, or even one row. In this case, it is possible to use the grep command as a pager with an option. The following example shows how to run the SHOW ENGINE InnoDB STATUS administrative statement, and get the rows that show the thread's status (the ones containing the string 'I/O thread'):

MariaDB [performance_schema]> \P grep 'I/O thread'
PAGER set to 'grep 'I/O thread''
MariaDB [performance_schema]> SHOW ENGINE InnoDB STATUS \G
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
…

Another interesting option is to set the md5sum program as a pager. As a result, when a query is executed, the MD5 hash of the query will be shown. This is useful to compare the results of two queries, for example, to check that two tables are identical, shown as follows:

MariaDB [(none)]> \P md5sum
PAGER set to 'md5sum'
MariaDB [(none)]> SELECT * FROM test.t1;
3ec930f74d6ec7d7bdd7aa8544440835  -
MariaDB [(none)]> SELECT * FROM test.t2;
3ec930f74d6ec7d7bdd7aa8544440835  -

In the preceding example, the queries are passed to md5sum, and their MD5 values appear in the command line. Since the values are identical, we can be reasonably sure that the queried tables (t1 and t2) are identical.

The \tee command can be used to log the current client session into a text file. On Windows, this can be used to save long outputs to a file and open it with a text editor, since the \P command does not work. To stop the logging, the \notee command can be used.

SQL warnings are not printed on the command prompt by default; only a warning count is showed. This can be a problem because warnings often indicate that a statement did not work as expected. To see all the warnings, the \W (uppercase) client command can be used. To suppress all the warnings and obtain a cleaner output, the \w (lowercase) command is used:

MariaDB [(none)]> \W
Show warnings enabled.
MariaDB [(none)]> SELECT 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
Warning (Code 1365): Division by 0
MariaDB [(none)]> \w
Show warnings disabled.
MariaDB [(none)]> SELECT 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

In the preceding example, we first enable the printing of warnings. The following SELECT query generates a warning. Then, we disable the printing of warnings. The same query does not show a warning anymore, but a warning count is still seen.

Sometimes, while using the command line, one needs to write a complex query. Using a good editor will be convenient. On Linux, it is possible to switch to an editor such as vi or Emacs by typing the edit command. The editor specified in the EDITOR environment variable is used. When the user exits the editor, the statement he/she wrote will appear in the command line.

In MariaDB 10.0, it is possible to stop the server from the command line without exiting or opening a new console to call mysqladmin. The SQL command to stop the server is SHUTDOWN. Unlike most administrative statements that require the SUPER privilege, this command requires the SHUTDOWN privilege. Normally, only the root user has these privileges. The client command to exit the client is \q. The following example shows how to terminate both the server and the client:

MariaDB [(none)]> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q
Bye

The mysql client can also be used to execute a batch file, that is, a text file containing a list of SQL statements. This can be done to restore a logical backup or to create a database required by an application. The results of the execution can be written into a text file. This is done using a Unix-like syntax, which works on all systems (including Windows) for the mysql client, shown as follows:

mysql < input_file > output_file

To quickly execute a single statement and see the results, it is not necessary to run the entire program. It is possible to use only one simple invocation:

federico@this:/usr/local/mysql/bin$ ./mysql -e "SELECT version();"	
+--------------------+
| version()          |
+--------------------+
| 10.0.5-MariaDB-log |
+--------------------+