How to Install and Use MyCLI in MySQL / MariaDB / Percona for Auto-Completion and Syntax Highlighting

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 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. MyCLI has many such features to be used.

Features of MyCLI

• Syntax highlighting using Pygments is supported.
Sponsored
• 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.

In this article, we are going to install MyCLI on Ubuntu 20.04 server and discuss the use cases of it with some examples. We are using a mysql server, however it works on MariaDB or Percona automatically.

Installation of MyCLI on Ubuntu 20.04 LTS server

To install MyCLI on Ubuntu 20.04 LTS server, you can run the command as shown below.

$ sudo apt update

$ sudo apt install mycli -y

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

Use Cases of 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 can see the options to complete the syntax which makes it easy to choose one of the required ones.

Auto-completion feature of MyCLI

Another example of running the command to show such a feature is as shown below.

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

Smart-completion feature of MyCLI

Also, to show its smart completions feature, we are going to show another example as shown below.

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.

Alias Support feature of MyCLI

This feature is also useful in many ways. Even after the table names are aliased, column completions will work. You can see the below example for further details.

Conclusion

You can enjoy such auto-completion, smart-completion, alias support, and many more such features of MyCLI by simply installing with the above instructions. Thank you!