Getting Auto-completion For MySQL With mycli
One of the more helpful features of working with the Linux shell is the ability to auto-complete many commands, paths, and the flags and options of commands when working. This feature saves time and helps ensure that you’ve got the right option for auto-completion. In fact, it’s so useful that when working in situations where it’s absent, it can feel like working with a hand tied behind your back. Auto-completion is also great for beginners as it can get them acquainted with the commands and options they are using.
One such situation is when working with the MySQL/MariaDB command line interface. As an interface, it provides no hinting or completion features, and an error message helpfully prompts you to read the manual for the MySQL version you are using to determine which command to use.
This is where mycli comes in. It’s a Python script that interfaces with the database at the command line, and offers not only auto-completion, but also hinting. This is done by providing relevant lists of options for the command you may wish to use based on valid syntax for that command. mycli can not only help speed up working with the MySQL command line, but can also help beginners remember the right options to use with a command.
Unfortunately, mycli doesn’t come in the normal distribution repositories for most Linux systems, but it is available with the Python package manager PIP.
So we’ll start by installing this.
On Debian, Ubuntu and other related distributions:
sudo apt-get update
sudo apt-get install python-pip
On CentOS/Red Hat and related distributions:
sudo yum install python-pip
Once the installation of pip is complete, you can then install mycli with the following command:
sudo pip install mycli
The install will run through and then you’ll be able to run mycli as follows:
mycli -u root
Note that unlike the MySQL command, you don’t need to provide the -p flag to prompt you for a password as this will happen automatically.
The first thing you’ll notice is that at the bottom of the screen is a status bar. This shows the current status of multiline mode which is toggled with the F3 key. By default, multiline is off, which means that whenever you press the Enter key, the command you are typing is run. Activating multiline means that commands are entered similarly to in the normal MySQL client, requiring a semicolon to signify the end of the line.
The next thing you’ll notice once you start typing is that mycli will pop up a little menu with a number of applicable options for what you were typing. These can be navigated with the up and down arrows, and the tab or right arrow keys for completion. Once you move to the next word, it will offer options that are relevant to what you previously typed in a similar popup. For example, typing the command “USE” will cause the dropdown to show a list of available databases you can use. Also note that it will also use color to show the options and typed commands for easy viewing.
One last great feature is that once you run a select query that runs over multiple pages, mycli will helpfully display the output with the ability to scroll forward and backward. This can make for a much easier read than with the normal MySQL client.
All in all, mycli makes a great client to introduce beginners to working with MySQL at the command line. It is also a helpful time-saving addition for experienced MySQL users.