Start using CLI more and more with mycli

Jul/18/2018 4 min read by Marco Monteiro

Ever since I moved into Linux I started using the CLI even more. Don't get me wrong, I used it quite a lot on Mac. However if there was one thing that I normally didn't use it for was for mySQL. However a few days ago I discovered mycli. Since then I gotta say. Love it!

Here's a bit more about the tool.

Install the thing first:

$ sudo apt-get install mycli    


$ mycli --help
Usage: mycli [OPTIONS] [DATABASE]
A MySQL terminal client with auto-completion and syntax highlighting.

    - mycli my_database
    - mycli -u my_user -h my_host.com my_database
    - mycli mysql://my_user@my_host.com:3306/my_database

    -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.
    --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.    


Example 2


mycli is written using prompt_toolkit.

  • Auto-completion as you type for SQL keywords as well as tables, views and columns in the database.
  • Syntax highlighting using Pygments.
  • 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. 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

Like it? Tweet it.

"Start using CLI more and more with mycli" via @marcogmonteiro