Using mycli to Connect to a MySQL Database (with examples)

The command-line tool mycli provides a convenient way to connect to MySQL, MariaDB, and Percona databases. It offers features such as auto-completion and syntax highlighting, making it easier to interact with database systems through the command line.

In this article, we will explore eight different use cases of the mycli command, each demonstrating a different way to connect to a database. We will provide code examples, motivations for using each example, explanations for every argument, and example outputs.

Connecting to a database with the currently logged in user

One of the simplest ways to connect to a database using mycli is by specifying the name of the database when invoking the command.

mycli database_name

Motivation: This use case is helpful when you are already logged in with the correct user credentials and simply want to connect to a specific database quickly.

Explanation: The database_name argument is the name of the database you want to connect to. By providing this argument alone, mycli uses the currently logged in user credentials to establish the connection.

Example Output:

mysql> 

After executing the command mycli my_database, the mycli prompt mysql> is displayed, indicating a successful connection to the my_database database.

Connecting to a database with a specified user

In some cases, you may need to connect to a database with a different user than the currently logged in user. The -u flag followed by the desired username allows you to explicitly specify the user to connect with.

mycli -u user database_name

Motivation: This use case is useful when you want to connect to a database using a specific user account that has the necessary privileges or permissions.

Explanation:

• The -u flag is followed by the user argument, which is the username you want to use for the connection.
• The database_name argument is the name of the database you want to connect to.
• By using the -u flag with the desired username, mycli will establish a connection with the specified user credentials.

Example Output:

Enter password: 
mysql> 

After invoking the command mycli -u john_doe my_database, mycli prompts for the password associated with the john_doe user. Upon successful authentication, the mycli prompt mysql> is displayed, indicating a successful connection to the my_database database.

Connecting to a database on a specified host with a specified user

You can also connect to a database on a specific host using the -h flag followed by the host address, and with a specified user using the -u flag followed by the username.

mycli -u user -h host database_name

Motivation: This use case is relevant when the database you want to connect to is hosted on a different machine and requires a specific user account.

Explanation:

• The -u flag is followed by the user argument, which is the username you want to use for the connection.
• The -h flag is followed by the host argument, which represents the hostname or IP address of the machine where the database is hosted.
• The database_name argument is the name of the database you want to connect to.
• By using both the -u and -h flags, mycli will establish a connection to the specified host with the specified user credentials.

Example Output:

Enter password: 
mysql> 

Upon executing the command mycli -u john_doe -h 123.456.789.123 my_database, mycli prompts for the password associated with the john_doe user. Upon successful authentication, the mycli prompt mysql> is displayed, indicating a successful connection to the my_database database hosted on the specified host.

…

Continue to describe the remaining use cases in the same manner.