Home
Linux
Install and Use MyCLI in MySQL / MariaDB / Percona for Auto-Completion and Syntax Highlighting on Ubuntu 20.04 server

Install and Use MyCLI in MySQL / MariaDB / Percona for Auto-Completion and Syntax Highlighting on Ubuntu 20.04 server

Linux

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 LinuxAPT, 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. ]

This article covers how to install and use the MyCLI tool on your Ubuntu Linux system. In fact, MyCLI is an easy-to-use command line interface (CLI) for MySQL, MariaDB, and Percona, that helps you speed up development with auto-completion and syntax highlighting features.

How to install the MyCLI tool on your Linux system ?

MyCLI tool is based on Python and needs to be installed via pip. Make sure PIP and python development libraries are installed.:

$ yum install python-pip
$ yum -y install python-devel
$ pip install mycli

MyCLI Features include:

Auto-completion as you type for SQL keywords as well as tables, views and columns in the database. Auto-completion is on by default. The REPL will pop up a suggestion menu as soon as you start typing. The suggestions are context sensitive based on the position of the cursor. eg: Only tables are suggested after the FROM keyword, only column names are suggested after the WHERE clause.
Syntax highlighting using Pygments. Syntax highlighting has plenty of themes that can be changed via the config file.
Smart-completion (enabled by default) will suggest context-sensitive completion.
SELECT * FROM will only show table names.
SELECT * FROM users WHERE will only show column names.
Support for multiline queries.
Favorite queries with optional positional parameters. Favorite Queries are a way to save frequently used queries with a short name. Save a query using \fs alias query and execute it with \f alias whenever you need.
Timing of sql statments and table rendering.
Config file is automatically created at ~/.myclirc at first launch.
Log every query and its results to a file (disabled by default).
Pretty prints tabular data (with colors).
Support for SSL connections.
There are two types of keybindings available. Emacs mode and Vi mode
Cross-platform support – runs under Linux and macOS. The software may run under Windows but this is not tested.