How to Install and Use Mycli on Linux

MyCLI is a command line interface for MySQL, MariaDB, and Percona with auto-completion and syntax highlighting. The auto-completion will help you write queries faster and with ease. The REPL (Read, Eval, Print, Loop) 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.

MyCLI uses BPython which is a Python REPL that has auto-completion, syntax highlighting and displays the doc string of functions as you type. This article covers installation and usages of MyCLI in any Linux flavors.

Installation of MyCLI

Use python package installer (PIP) to install My-CLI. You need to have Python PIP installed in your system already. Install PIP using following command in the terminal.

 # apt-get install python-pip (Ubuntu)
 # yum -y install python-pip (CentOS)

Check the version of pip and then install MyCLI.

 # pip -V
 # pip install -U mycli

You can also use apt-get in Ubuntu/Debian to install it.

 # sudo apt-get install mycli

Find the version of MyCLI and usages using following two commands.

 # mycli --version 
 Version: 1.10.

 # mycli --help

MyCLI Features

The primary feature is auto-completion as you type for SQL keywords as well as tables, views and columns in the database apart from syntax highlighting that uses Pygments. Smart-completion (enabled by default) will suggest context-sensitive completion. It supports multi-line queries and SSL connections. You can save your frequently used query using \fs alias query and execute it with \f alias whenever you need and time sql statements and render a table.

Three configuration files are generated in the home directory. They are ~/.mycli-history, ~/.mycli.log and ~/.myclirc. The history file contains all the sql statements and the timestamp that you have executed. The log files contain the logs of My-CLI. Default log level is INFO. The other possible values: "CRITICAL", "ERROR", "WARNING",  and "DEBUG". "NONE" disables logging. The configuration file is ~/.myclirc. These files get created when you launch mycli for the first time. Another cool feature is its ability to log every query and its results to a file. This is disabled by default. Enable this by uncommenting the following line in ~/.myclirc.

 # vi ~/.myclirc

 audit_log = ~/.mycli-audit.log

The default format for table output is 'psql'. Change the format that suits you. The other options are psql, plain, simple, grid, fancy_grid, pipe, orgtbl, rst, mediawiki, html, latex, latex_booktabs, tsv.

 table_format = fancy_grid

How to Use MyCLI

Before using MyCli, create a test database. Although this is optional and is used in this article solely for demonstrating the features of mycli. You can skip this step since you will have MySQL database already in your system. Download the employee's database schema, unzip it and import it.

 # wget https://github.com/datacharmer/test_db/archive/master.zip
 # unzip master.zip
 # cd test_db-master
 # mysql -u root -p < employees.sql

Now invoke mycli by using the following command from the terminal. If the authentication is successful then you will get the my-cli prompt. You can customize the prompt in ~/.myclirc

 # mycli -h localhost -u root employees

 mysql root@localhost:employees> select * from

Once you have type 'select * from' then only table names from the current database are suggested after the FROM keyword. The column names from the current table are suggested after the WHERE clause. Also, Insert statement will suggest the column names. Aliases in the query are resolved and the columns from the table aliases are suggested.

You can use my-cli for connecting to remote server using the following command.

 # mycli mysql://root@MySQL-Server-IP:3306/employees

Conclusion

My-Cli is a nice client side tool that will shorten the time of writing the query in the terminal by suggesting tables and columns names as you write a query. There is also postgres equivalent of this tool by the name pgcli developed by Amjith. If you want to add a new feature to mycli, you will get guidance from him. According to him, my-cli is compatible with Windows also but this has not been tested yet.