How to Connect to MySQL Database Clusters with mysql and mysqlsh

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.

To connect to MySQL database clusters from the command line, you need two things:

  • A MySQL client on your local computer. We recommend either MySQL version 8 or greater, which lets you use the mysql command in a terminal, or MySQL Secure Shell, which lets you use the mysqlsh command in a terminal.

    Note

    MySQL 5.x clients are not compatible with the higher password encryption requirements of MySQL 8.x, so to ensure compatibility with DigitalOcean Managed Databases, check the version of your MySQL client:

        
            
    mysql --version
    
        
    

    Output like this indicates a compatible version:

    mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)
    

    Output like this indicates a client for MySQL 5.x, which (despite the higher version number) will not work with MySQL 8.x:

    mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper
    
  • The database cluster's connection details, which tells your client how to connect to the cluster.

MySQL Cluster Connection Details

You can find a database cluster's connection details in the control panel. From the Databases page, click the name of the cluster to go to its Overview page.

Databases Overview screen showing connection string

In the Connection Details section, the drop-down menu has three options:

  • Connection parameters, which is meant for application configuration and is incompatible with clients like mysql and mysqlsh.

  • Connection string, which is a condensed string that you can pass to a client on the command line.

  • Flags, which is 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.

User and Database

Beneath each set of connection details, the User and Database drop-down menus allow you to select the user you want to connect with and the database you want to connect to. Selecting a different user or database updates the details for each connection option.

Connection details for a database

When you select a different user or database, use the updated connection details to connect to the database.

Connecting with mysql and mysqlsh

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

    

To connect using the flags format with mysqlsh, paste the entire 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 either of the above commands and you will be prompted to enter it before you can connect. When you connect successfully with mysql or mysqlsh, 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>

    

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.