How to Connect to MySQL Database Clusters

Validated on 19 Aug 2020 • Last edited on 8 Jul 2024 ssl certificate | connection status | tcp | ip

MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality.

You can connect to DigitalOcean Managed Databases using command line tools and other third-party clients. This guide explains where to find your MySQL database’s connection details and how to use them to configure tools and clients.

Retrieve Database Connection Details Using the CLI

How to Retrieve Database Connection Details Using the DigitalOcean CLI

  1. Install doctl, the DigitalOcean command-line tool.

  2. Create a personal access token and save it for use with doctl.

  3. Use the token to grant doctl access to your DigitalOcean account.

              doctl auth init

  4. Finally, run doctl databases connection. Basic usage looks like this, but you can read the usage docs for more details:

                doctl databases connection <database-cluster-id> [flags]

    The following example retrieves the connection details for a database cluster with the ID f81d4fae-7dec-11d0-a765-00a0c91e6bf6:

                  doctl databases connection f81d4fae-7dec-11d0-a765-00a0c91e6bf6

Retrieve Database Connection Details Using the API

This API call retrieves the information about your database, including its connection details. The connection details are located in the returned connection JSON object.

How to Retrieve Database Connection Details Using the DigitalOcean API

  1. Create a personal access token and save it for use with the API.

  2. Send a GET request to https://api.digitalocean.com/v2/databases/{database_cluster_uuid}

    cURL

    Using cURL:

                    curl -X GET \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30"

    Go

    Using Godo, the official DigitalOcean V2 API client for Go: 

                    import (
    "context"
    "os"

    "github.com/digitalocean/godo"
)

func main() {
    token := os.Getenv("DIGITALOCEAN_TOKEN")

    client := godo.NewFromToken(token)
    ctx := context.TODO()

    cluster, _, err := client.Databases.Get(ctx, "9cc10173-e9ea-4176-9dbc-a4cee4c4ff30")
}

    Python

                    import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

get_resp = client.databases.get_cluster(database_cluster_uuid="a7a89a")

View MySQL Cluster Connection Details

You use your database’s connection details to configure tools, applications, and resources that connect to the database. To view your database’s connection details, click the name of the cluster on the Databases page to go to its Overview page.

Databases Overview screen showing connection string

You can view customized connection details based on how you want to connect to the database:

  • Public network and Private network (VPC) options generate connection details based on if you want to connect via the cluster’s public hostname or the cluster’s private hostname. Only other resources in the same VPC network as the cluster can access it using its private hostname.

  • The Database field updates the connection details based on which database you want to connect to.

  • The User field updates the connection details with the user credentials that you would like to connect with.

You can also choose to view the connection details in three different formats:

  • Connection parameters: Database information meant for application configuration, such as configuring connections for MySQL Workbench and Sequel Ace.

  • Connection string: A condensed string that you can pass to a client on the command line.

  • Flags: A complete mysql command that supplies the connection variables as individual flags.

We recommend the flags format because the readability can help if you want to customize the way you connect. The only required parameter is sslmode, but MySQL supports many options for customizing connections.

By default, the control panel doesn’t reveal the cluster’s password for security reasons. Click Copy to copy connection details with the password, or click show-password to reveal the password.

Anatomy of the Flags Format

The connection string passes different parameters to MySQL via Flags. Here’s a high-level breakdown of what information those flags correspond to:

  • Hostname and Port: The hostname is specified with the -h flag, and tells your client computer how to reach the cluster. Port is specified with the -P flag (notice it’s upper case). If you do not specify a port, the client will attempt to use the default MySQL port, 3306.

  • Username and Password: The username and password are specified with the -u flag for user and the -p flag for password. By default your database has only one accessible user, doadmin. If you leave the -p flag blank, you will be prompted for the user’s password.

  • Database: The database is specified with the -D flag (notice it’s upper case), this tells the MySQL client which database you want to access.

  • Other Flags: You can find a more comprehensive list of the flags you can pass to the MySQL command on the official documentation for MySQL at mysql Client Options.

Download the SSL Encryption

Each managed database comes with an SSL certificate. You can use this SSL certificate to encrypt connections between your client applications and the database.

To download your database’s SSL certificate, click the name of the cluster on the Databases page to go to its Overview page. In the Connection Details section, click Download CA certificate.

Databases connection details with Download CA Certificate selected

When you configure your client applications, you can use the certificate’s location on your local system. Each client application is configured differently, so check the documentation for the tool you’re using for more detail on setting up SSL connections.

Connect to the Database

You can connect and manage the database using a command line tool or an application that has a graphic user interface (GUI). In this guide, we show you how to connect to the database using the command line tools, mysql and MySQL Shell (mysqlsh), and the visual database management application, MySQL Workbench.

mysql is an SQL shell with input line editing capabilities, while MySQL Shell has more advanced features, like X DevAPI integration and multi-connection support.

To connect to MySQL database clusters using mysql, you need three things:

Note
MySQL 5.x clients, such as Sequel Pro, are not compatible with the higher password encryption requirements of MySQL 8.x. You can change the password encryption of your database’s users if you are receiving authentication errors.

To connect using the flags format with mysql using the doadmin user, paste the entire command from the control panel into your terminal:

    
        
            
mysql -u doadmin -p<your_password> -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 -D defaultdb

You can also leave the password out when using the above commands and you will be prompted to enter it before you can connect.

To connect using the database’s SSL certificate, update the flags commands with the --ssl-ca flag and the path to the certificate on your local system:

mysql -u doadmin -p<your_password> -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 -D defaultdb --ssl-ca=path/to/your-ssl.crt

When you successfully connect, the shell displays some information about the MySQL server and your prompt changes. Successful mysql connections look like this:

    
        
            
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 151986
Server version: 8.0.16 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Once you’re connected, you can manage your MySQL databases via command line. The official MySQL documentation includes a guide to MySQL commands.

If you’re having trouble connecting to the database, you can troubleshoot the connection using our Support page, or you can reference MySQL’s connection documentation.

To connect to MySQL database clusters using MySQL Shell, you need three things:

Note
MySQL 5.x clients, such as Sequel Pro, are not compatible with the higher password encryption requirements of MySQL 8.x. You can change the password encryption of your database’s users if you are receiving authentication errors.

To connect with mysqlsh using the flags format, paste the entire flags command from the control panel into your terminal and replace the first term, mysql, with mysqlsh:

    
        
            
mysqlsh -u doadmin -p<your_password> -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 -D defaultdb

You can also leave the password out when using the above commands and you will be prompted to enter it before you can connect.

To connect using the database’s SSL certificate, update the flags commands with the --ssl-ca flag and the path to the certificate on your local system:

mysqlsh -u doadmin -p<your_password> -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 -D defaultdb --ssl-ca=path/to/your-ssl.crt

When you successfully connect, the shell displays some information about the MySQL server and your prompt changes. Successful mysqlsh connections look like this:

    
        
            
MySQL Shell 8.0.16

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
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 session to 'doadmin@mysql-test-nyc1-do-user-2430004-0.db.ondigitalocean.com:25060/defaultdb'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 152077
Server version: 8.0.16 Source distribution
Default schema set to `defaultdb`.

MySQL  mysql-test-do-user-4915853-0.db.ondigitalocean.com:25060 ssl  defaultdb  JS >

Once you’re connected, you can manage your MySQL databases via command line. The official MySQL documentation includes a guide to MySQL commands.

If you’re having trouble connecting to the database, you can troubleshoot the connection using our Support page, or you can reference MySQL Shell’s connection documentation.

To connect to MySQL database clusters using MySQL Workbench, you need three things:

Note
MySQL 5.x clients, such as Sequel Pro, are not compatible with the higher password encryption requirements of MySQL 8.x. You can change the password encryption of your database’s users if you are receiving authentication errors.

To connect to your MySQL database using a standard TCP/IP connection, open MySQL Workbench, then click the Database drop-down menu and select Manage Connections…. Click New in the Manage Server Connections window.

In the Manage Server Connections window, create a descriptive name for the connection in the Connection Name field, and then select the Standard (TCP/IP) option from the Connection Method drop-down menu.

MySQL Workbench Manage Server Connections window with database credentials

Under the Parameters section, use the information from your database’s Connection Details section in the control panel to fill out the necessary fields.

If you want to connect using SSL encryption, click the SSL tab. In the Use SSL field, select the Require and Verify CA option. In the SSL CA File field, enter the path to your database’s SSL certificate on your local system.

MySQL Workbench Manage Server Connections window with SSL certificate

Once you have entered the details, click Test Connection. If you receive the message Successfully made the MySQL connection, click OK to close the message, and then click Close in the Manage Server Connections window to save the connection configuration. If you receive an error, recheck that you entered your credentials correctly and then reattempt the test.

To connect to the database, select Connect to Database… from the Database drop-down menu. Then select your database connection from the Stored Connection drop-down menu, and then click OK. MySQL Workbench will connect to your database.

If you’re having trouble connecting to the database, you can troubleshoot the connection using our Support page, or you can reference MySQL Workbench’s connection documentation.

Control Panel Blog Pricing Careers Terms Privacy Status API Docs Tutorials Support