For the popular database servers like MySQL, MariaDB and Percona, you have to run different commands for the task like displaying the database, showing tables, listing users from tables and so on. With MyCLI, running such commands is easy. 

MyCLI is basically a command line interface (CLI) built using prompt_toolkit library and Python which provides syntax highlighting and auto-completion.

You can also configure MyCLI for the listing of previous commands with the up/down arrow key. So you don't need to worry about typing the previous commands again. 

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related Linux System Database queries.

In this context, we shall look into how to install and use MyCLI  on Ubuntu 20.04 server.

Main Features of MyCLI includes:

• Syntax highlighting using Pygments is supported.
• SSL connections are supported.
• As you type for SQL keywords as well as columns, tables and views in the database, auto-completions are available in this case.
• Multiline queries is supported.
• Automatic creation of config file at ~/.myclirc at the time of installation.
• Timing of Sql statements and table rendering is supported.
• By default, smart-completions are enabled offering suggestions for context-sensitive completion.
• SELECT * FROM and then pressing tab will be showing the table names.
• SELECT * FROM users WHERE and then pressing tab will be showing column names.
• All queries and its results logs are saved in a file. By default, this feature is disabled.
• Colorful printing of tabular data.
• Supports saving of a favorite queries using \fs alias. You can execute the query again with \fs alias.

How to install MyCLI on Ubuntu 20.04 LTS server ?

To install MyCLI on Ubuntu 20.04 LTS server, you can run the below commands:

$ sudo apt update
$ sudo apt install mycli -y

With the above command, mycli will be completely installed and ready to use.

How to use MyCLI ?

Here, we are going to use the MyCLI command for the popular database server MySQL. Lets run a few commands by using MyCLI features which makes it easy to run commands and syntax:

# mycli -u root -p database_name

Here, you will see the options to complete the syntax which makes it easy to choose one of the required ones.

What does the Auto-completion feature of MyCLI do ?

With the MyCLI auto-completions feature, you can see the options to complete the syntax just by typing SELECT * F. This will display the options like FROM which makes it easy to choose one of the required ones.

# mycli -u root -p phpmyadmin
SHOW DATABSES;
USE phpmyadmin;
SELECT * F

How to use the Smart-completion feature of MyCLI ?

The smart completions feature of MyCLI goes like this:

SHOW TABLES;
SELECT * FROM pma__table_info WHERE

Here, we have pressed space after WHERE, it is showing options for related tables. In our case, it is showing column options for table pma__table__info.

About the Alias Support feature of MyCLI ?

This feature is also useful Even after the table names are aliased, column completions will work as illustrated below:

SELECT * FROM pma__table_info WHERE db_name;
SELECT t FROM pma__table_info WHERE db_name;

[Need assistance in fixing MySQL database issues ? We can help you.  ]