Skip to main content

MySQL® - Connect with CLI

Stephanie R.
Updated

Learn how to connect to a MySQL® database instance with a Command Line Interface (CLI).

Cloud Databases allow you to focus on building and deploying cloud applications while OVHcloud takes care of the database infrastructure and maintenance in operational conditions.

 

Requirements

 

Concept

To interact correctly with the MySQL® instance, we need to first install something called a MySQL® client to connect and control the MySQL® service remotely. It's a Client-Server interaction.

 

Instructions

Installing the MySQL® client

Please note that a MySQL® client is natively integrated in the MySQL® software package, but it does not allow the use of Service URI.

You first need to install software to interact with your MySQL® instance remotely. This official software can be installed on various client machines such as your own computer, a VPS, or a virtual machine. The only rule is to be able to reach the public network (Internet) and have sufficient rights to install it.

To do so, and depending on your configuration, you may need to follow official MySQL® documentation to install MySQL® Shell (mysqlsh), more powerful compared to MySQL® Client (mysql).

MySQL® Shell is not mandatory, but much more convenient to use compared to standard MySQL® Clients, since you can connect with Service URI instead of individual parameters.

Follow the steps here after selecting Windows, macOS, or Linux as the operating system.

We are now ready to learn how to connect to our MySQL® instance.

 

Configuration

We will now follow official MySQL® documentation to perform our first connection.

In your CLI, type mysqlsh --version. The result should look like this:

laptop$mysqlsh --version
/snap/mysql-shell/29/bin/mysqlsh   Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (Source distribution)

It means that mysqlsh is correctly installed and working properly. If you do not see something like this result, please go back to the previous step and reinstall the MySQL® shell.

 

Connect to your MySQL® instance

We will follow the official MySQL® documentation.

To perform a connection, simply type mysqlsh --sql followed by the Service URI:

laptop$mysqlsh --sql "mysql://<username>:<password@<hostname>t<port>/defaultdb?ssl-mode=REQUIRED"

Don't forget you need to modify the username, password, hostname, and port.

This information can be found in the OVHcloud Control Panel in the Connection information section.

manager_database_dashboard_mysql.png

In our example, it will look like this:

laptop$mysqlsh --sql "mysql://avnadmin:Mysup3rs3cur3p4ssw0rd@mysql-ab123456-cd7891011.database.cloud.ovh.us:20184/defaultdb?ssl-mode=REQUIRED"

Once connected correctly, you should see something similar to:

MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to '.(removed)..:20184/defaultdb?ssl-mode=REQUIRED'
Fetching schema names for autocompletion... Press ^C to stop.
Fetching table and column names from `defaultdb` for auto-completion... Press ^C to stop.
Your MySQL connection id is 50811
Server version: 8.0.26 Source distribution
Default schema set to `defaultdb`.
mysql-sql [defaultdb]>

Congratulations! You are now fully able to interact with your MySQL® instance!

 

MySQL® client usage

Once connected, you can manage your MySQL® instance with built-in MySQL® client methods.

Please follow the official MySQL® documentation.

To verify:

  • SHOW DATABASES; will list all the databases;
  • \h CREATE DATABASE will display information about creating a new database.

In our example, it will look like this:

mysql-sql [defaultdb]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| defaultdb          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.0046 sec)
defaultdb=> \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.mysql.org/docs/13/sql-createdatabase.html

 

 

Go further

Visit the GitHub examples repository to find out how to connect to your database with several languages.

For more information and tutorials, please see our other Managed Databases & Analytics or Platform as a Service guides. You can also explore the guides for other OVHcloud products and services.

OVHcloud Managed Databases and Analytics:

  • Grafana® is a registered trademark of Grafana Labs and is used with the permission of Grafana Labs. OVH SAS and its subsidiaries are not affiliated with or endorsed by Grafana Labs.
  • Kafka® is a registered trademark of The Apache Software Foundation and has been licensed for use by OVHcloud, who has no affiliation with and is not endorsed by The Apache Software Foundation.
  • MongoDB® is a registered trademark of MongoDB, Inc.
  • MySQL® is a registered trademark of Oracle and/or its affiliates.
  • PostgreSQL® is a registered trademark of the PostgreSQL Community Association of Canada.

Related articles