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 on DigitalOcean Managed Databases, you will need two things:
A MySQL client installed on your local computer. You can install 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.
The database's connection parameters. For both of the above tools, you will use the Flags format which supplies the variables as individual flags that are easier to read and customize.
To get the database's connection parameters from your control panel, visit the Databases page, open the database's More menu, then select Connection details and click Flags.
Before troubleshooting connection problems, check the DigitalOcean status page for ongoing issues in your database's region.
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,
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.
The database is specified with the
-D flag (notice it's upper case), this tells the MySQL client which database you want to access.
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.
Below are some common MySQL database connectivity errors and error codes along with likely causes and solutions. Learn more about specific error codes in the Server Error Message Reference.
Access denied errors can look different depending on the root cause.
For example, this error means that you're using the wrong password:
ERROR 1045 (28000): Access denied for user 'sammy'@'203.0.113.0' (using password: YES)
This access denied error means the connection string didn't contain the
-p flag but a password is required:
ERROR 1045 (28000): Access denied for user 'sammy'@'203.0.113.0'' (using password: NO)
This access denied error means the user does not have privileges on the database specified in the connection string:
ERROR 1044 (42000): Access denied for user 'sammy'@'%' to database 'defaultdb'
To troubleshoot the issue, ensure you have the correct username, password, and database name in your connection string. You should also ensure the user has privileges on the database you want to connect to. Learn more about privileges in Privileges Provided by MySQL.
The following error message means you are attempting to connect to MySQL using an application or a version 5.x client that does not support the
ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
If you are getting this error using a PHP based application to connect to the database, this solution only works for applications using PHP 7.2 or higher. DigitalOcean Managed Databases using MySQL 8+ are automatically configured to use
caching_sha2_password authentication by default.
caching_sha2_password uses a stronger password encryption than prior versions of MySQL and PHP based applications using PHP 7.1 or older do not support MySQL 8+ password encryption. If your applications are experiencing authentication issues, you can use the Password Encryption option in the control panel to set a user’s password encryption settings to MySQL 5.x encryption settings.
Alternatively, you can connect using MySQL 8.x and issue the following statement, which will change the password type the server will accept for the current user. Replace
use_your_user with your MySQL username, and replace
use_your_password with the user's current password. After successfully executing the statement, you should be able to connect to MySQL with a 5.x client or an application that does not support
ALTER USER use_your_user IDENTIFIED WITH mysql_native_password BY 'your_password';
This error means the client you're using to connect does not support TLS/SSL:
ERROR 2026 (HY000): SSL connection error: unknown error number
It's most common on MySQl/MariaDB version 5.7.x, which is the default version in the Ubuntu repository. To troubleshoot this issue, ensure you're using the correct client version (8.x) to connect to the MySQL node. See How To Install the Latest MySQL on Ubuntu 18.04 for more information.
ERROR 2005 (HY000): Unknown MySQL server host 'myqsl--do-user-example-0.db.ondigitalocean.com' (0)
This error means that you are using an incorrect hostname, or your local computer cannot resolve the hostname. To troubleshoot this error, ensure you have the correct hostname for the database server, and that your local computer has a connection to the Internet. You may also need to look into your local DNS configuration.
ERROR 2013 (HY000) at line xx: Lost connection to MySQL server during query
This error means either network connectivity issues or the query being issued is taking too long to complete. If you get this error message often, check your network connection to ensure its stability. If the query is taking longer than 30 seconds to complete (the default value for the
net_read_timeout variable), consider revising the query so it's not sending as much data, or splitting the query into multiple queries.
ERROR 1049 (42000): Unknown database 'default'
This error means that the database specified in the connection string does not exist. To troubleshoot, check your spelling, and ensure the database exists.
ERROR 1129 (HY000): Host 'host_name' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
This error means that the MySQL server received too many interrupted connection requests from your location. The default value of the
max_connect_errors system variable is 100, so if you get a lot of disconnects in a short period of time, this could happen. To troubleshoot this issue, make sure your queries are not timing out and that your network connection is stable.