Tutorial on MySQL CLI (mycli) on Linux
MySQL is a powerful and widely used relational database management system. While many users interact with their databases using graphical user interfaces (GUIs), command-line interfaces (CLIs) can offer a more powerful and flexible way to manage MySQL databases. In this article, we will explore mycli, a popular CLI tool for MySQL, and how it can be used to perform a variety of database management tasks on a Linux system.
What is mycli?
mycli is a Python-based command-line interface (CLI) for MySQL that provides enhanced features and usability over the default MySQL CLI tool. mycli offers an intuitive, color-coded interface that provides syntax highlighting, auto-completion, and other usability enhancements that can help users navigate and manage their MySQL databases more efficiently. Some of the features offered by mycli include:
- Syntax highlighting: mycli highlights SQL syntax and keywords, making it easier to read and write SQL statements.
- Auto-completion: mycli can suggest and complete SQL statements based on user input, reducing the likelihood of typos and syntax errors.
- Database introspection: mycli can inspect the database schema and provide useful information about tables, columns, and indexes.
- Improved pager: mycli can display query results in a more user-friendly format, including support for horizontal scrolling and filtering.
- Customizable key bindings: mycli allows users to customize the key bindings and shortcuts for their workflow.
Using mycli To use mycli, you will need to have a MySQL instance running and accessible from your Linux system. Once you have a MySQL instance set up, you can launch mycli by simply typing “mycli” in the terminal. mycli will attempt to connect to the MySQL instance using default settings, but you can specify the connection details as command-line arguments if necessary.
MySQL, MariaDB, and Percona are all supported by mycli, along with the following features:
- Automatically completes commands, and cases sensitively, as soon as we begin typing.
- Prints tabular data in an attractive manner.
- SSL connection support.
- Highlighting of syntax.
- Multi-theme support.
- It is able to handle Unicode input and output.
So let’s start the installation,
Step 1: Set up a VPSie cloud server
Start by logging into your VPSie account Make a new server, There are several Linux distributions available on vpsie and after creating one use the credentials that we emailed you to connect to your Cloud Server via SSH.
Step 2: Install MySQL CLI – mycli
Mycli packages are available for Debian/Ubuntu and Fedora within their respective system package manager repositories, using the following commands to install them,
# sudo apt-get install mycli
# sudo dnf install mycli
RedHat & CentOS do not have rpm packages, and we will need to use pip to do so. Installing mycli with pip is possible on Ubuntu, Fedora, or CentOS/RHEL.
Install Pip on your system by running the following command.
# sudo yum install python-pip
After installing Pip, follow these instructions to install MyCLI.
# sudo pip install mycli
Use the following command to find your MyCLI version.Use the following command to find your MyCLI version,
# mycli --version Version: 1.20.1
To learn more, roam around. Enter this command,
# mycli --help
Usage: mycli [OPTIONS] [DATABASE] A MySQL terminal client with auto-completion and syntax highlighting. Options: -h, --host TEXT Host address of the database. -P, --port INTEGER Port number to use for connection. Honors $MYSQL_TCP_PORT. -u, --user TEXT User name to connect to the database. -S, --socket TEXT The socket file to use for connection. -p, --password TEXT Password to connect to the database. --pass TEXT Password to connect to the database. --ssh-user TEXT User name to connect to ssh server. --ssh-host TEXT Host name to connect to ssh server. --ssh-port INTEGER Port to connect to ssh server. --ssh-password TEXT Password to connect to ssh server. --ssh-key-filename TEXT Private key filename (identify file) for the ssh connection. --ssl-ca PATH CA file in PEM format. --ssl-capath TEXT CA directory. --ssl-cert PATH X509 cert in PEM format. --ssl-key PATH X509 key in PEM format. --ssl-cipher TEXT SSL cipher to use. --ssl-verify-server-cert Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. -V, --version Output mycli's version. -v, --verbose Verbose output. -D, --database TEXT Database to use. -d, --dsn TEXT Use DSN configured into the [alias_dsn] section of myclirc file. --list-dsn list of DSN configured into the [alias_dsn] section of myclirc file. -R, --prompt TEXT Prompt format (Default: "\t \u@\h:\d> "). -l, --logfile FILENAME Log every query and its results to a file. --defaults-group-suffix TEXT Read MySQL config groups with the specified suffix. --defaults-file PATH Only read MySQL options from the given file. --myclirc PATH Location of myclirc file. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -t, --table Display batch output in table format. --csv Display batch output in CSV format. --warn / --no-warn Warn before running a destructive query. --local-infile BOOLEAN Enable/disable LOAD DATA LOCAL INFILE. --login-path TEXT Read this path from the login file. -e, --execute TEXT Execute command and quit. --help Show this message and exit.
Thanks for reading! I hope it was informative for you!
To launch mycli, type “mycli” in the terminal. mycli will try to connect to the MySQL instance using the default settings, but you can change the details of the connection on the command line if you need to.
Some common mycli commands and features include introspection commands, query history, paged output, auto-completion, and customizable key bindings.
Paged output is a feature that allows you to display query results in pages to view and interact with large datasets more efficiently. To control paged output, you can use the “–pager” option or press “Enter” to continue paging or “q” to quit.
You can customize key bindings and shortcuts using the “\bind” command, followed by the desired key combination and action. For example, to create a shortcut to execute the current query using “ctrl+enter,” you can use the command “\bind ctrl+enter execute.”
mycli’s features, including syntax highlighting, auto-completion, and database introspection, can help you write and execute SQL statements more quickly and accurately. Paged output and customizable key bindings can also improve your workflow and efficiency.
No, mycli is a command-line tool that can be used without installation. Launch mycli in the terminal to start using it.
Yes, mycli is open-source software and is available under the MIT license. It can be freely used, modified, and distributed.