Azure Database for MySQL is a fully managed database service, based on the MySQL Community Edition, that runs in the Microsoft cloud. While the service provides features such as patching, backups, high availability, and security, there are some requirements and best practices to consider when you're using the service.
This article lists the most common connection issues that users may encounter, together with suggestions and recommended solutions for addressing those issues.
Important: This blog post applies to both Azure Database for MySQL deployment modes (Single Server and Flexible Server).
Scenario: After provisioning an Azure Database for MySQL server, the connection fails
There can be multiple potential causes for this, so be sure to review the following guidance.
Connection string or password
- Symptom: ERROR 1045 (28000): Access denied for user 'username'@'IP address' (using password: YES)”
Recommended solution
- Be sure that the "username" exists as a valid user on the server, as it may have been inadvertently deleted.
- Use the admin account to check Users and Privileges with any MySQL client (e.g., MySQL Workbench) or run one of the following queries:
select user, host from mysql.user;
- or -
show grant for ‘username’@’ip addr’;
This will provide a list of all users that can connect so that you can verify that the host IP address for the user in question is allowed to connect. You may be able to connect by using another account from the list.
- Symptom: Invalid username specified.
Recommended solution: Confirm that the username you're using is passed in the format listed below:
- Single Server: username@servername
- Flexible Server: username
- Symptom: Failed to log in to Azure Database for MySQL to run the query using the admin account.
Recommended solution: You can reset the admin password by using the Azure portal, which will recreate the user, reset the password, and restore the admin permissions. You can then log in using the server admin account and perform further operations.
- Verify the connection string and other connection settings. See How to connect applications to Azure Database for MySQL.
- If you're using a non-admin user for your database, ensure the user has the correct. See How to create non-admin users.
Firewall or Network
Start by testing the connection from the Azure Cloud shell in the portal to see if you can connect. This can also help determine whether it’s a database availability issue or an issue with your client network.
Server firewall configuration
- Symptom: Client with IP address 'XXX.XX.XXX.X' is not allowed to connect to this MySQL server
Recommended solution: Ensure that you’re using the correct username format and password. Also verify that the server Firewall rule allows the IP address. For temporary testing purposes only, set up a firewall rule using the range 0.0.0.0 to 255.255.255.255, which opens the server to all IP addresses. If this resolves your connectivity issue, remove this rule, and create a firewall rule for an appropriately limited IP address or address range. Regardless, when your testing is complete, be sure to remove the temporary rule.
Client firewall configuration
The firewall on your client must allow connections to your database server.
- If you’re connecting from Azure VMs (virtual machines), verify that network security group (NSG) rules are not blocking the connection. Also review the route table to determine if there are any VPN devices that require configuration.
- If you’re using VNet rules, ensure that the service endpoints and VNet integration (Private access) setup are configured correctly. For details about related limitations see the following resources:
- For Single Server: Use Virtual Network service endpoints and rules
- For Flexible Server: Connectivity and networking concepts for Flexible Server
For Single Server specifically, consider the following points:
- Confirm that your network allows outbound connections on port 3306 (this port number cannot be changed). You can try to telnet to your server.
- Confirm that your network/firewall does not block the connection to IP address of the regional Azure Database for MySQL Gateway. Use psping to ping the FQDN of your server to verify that it resolves correctly to the Gateway IP. If you’re using the private endpoint, it should resolve to your private IP address for the private endpoint. For more information, see Connectivity architecture.
- If you’re using the Private link and deny public access is on, you can only connect from the VNet of a private endpoint or peered VNet or VPN/express route.
For Flexible Server specifically, there are two networking options for connecting: private access (VNet integration) or public access (allowed IP addresses). If your flexible server is deployed using private access (VNet integration), the client or application connecting to your database instance should be in the same VNet as your flexible server instance. If the client or application is deployed in a different VNet, then you must enable VNet peering between the two VNets. For more information, see Private Network Access overview.
- Symptom: Server is not configured to allow IPv6 connections.
Recommended solution: Note that the Basic tier does not support VNet service endpoints. You must remove the endpoint Microsoft.Sql from the subnet attempting to connect to the Basic tier server.
With the Single Server deployment model, a gateway is used to redirect the connections to server instances. After the connection is established, the MySQL client displays the version of MySQL set in the gateway, not the actual version running on your MySQL server instance. To determine the version of your MySQL server instance, use the SELECT VERSION(); command at the MySQL prompt. For more information, see Supported MySQL server versions.
TLS/SSL
Ensure that you’re using the correct SSL configuration and the right certificate. Also be sure that you have configured TLS correctly. To configure TLS for Single Server, see TLS configuration.
If you use Flexible Server, TLS/SSL is enabled by default and can’t be disabled. The minimum TLS version supported on the server is TLS1.2. All incoming connections with TLS 1.0 and TLS 1.1 will be denied. You cannot disable or change the TLS version.
Driver
It is highly recommended to use only the supported drivers and tools and latest client version. Note that you can’t connect to Azure Database for MySQL using SSMS.
Scenario: It takes longer to establish connections than before
- Symptom: You notice a higher connection time after migrating from on-premises to Azure Database for MySQL - Single Server, which is a result of the Single Server architecture. This can impact your workload performance if there are large volume of short-lived connections, such as when a user creates a connection, runs a simple query, and closes the connection.
Recommended solution: It's highly recommend connection pooling if you have not done it yet and exam your pool configuration. For more information, see the blog post Connecting efficiently to Azure Database for MySQL with ProxySQL.
- Symptom: There is a sudden increase in the connection latency.
Recommended solution: Determine whether you recently increased workload or made any changes to application code or the server parameters. Each of these can contribute to higher connection latency.
Scenario: Connection dropped
- Symptom: ERROR 1184 (08S01): Aborted connection 22 to db: 'db-name' user: 'user' host: 'hostIP' (init_connect command failed)
Recommended solution: Reset init_connect value in the Azure portal, on the Server Parameters tab, using only the supported server parameter. For more information, see the article Troubleshoot errors commonly encountered during or post migration and the blog post Azure Database for MySQL server has gone away.
- Symptom: Error Code: 2013. Lost connection to MySQL server during query error or receive MySQL server has gone away intermittently.
Recommended solution
- The server dropped an incorrect or too large packet. In the Azure portal, on the Server Parameters tab, increase the value of the max_allowed_packet parameter.
- Timeout from the TCP/IP connection. On the server side, consider increasing the value of the packet connect_timeout, net_write_timeout, net_read_timeout, and wait_timeout parameters. On the client side, check your client timeout value.
- The running thread was stopped manually or the query was run after closing the connection. In this case, determine who may have stopped the thread and check your application logic.
For more information, see the blog posts Azure Database for MySQL server has gone away and Performance Troubleshooting Basics.
- Symptom: ERROR 1040 (08004): Too many connections
Recommended solution: Check the value of the max_connnections parameter and adjust if necessary. You can find the limit for each tier in the max_connections section of the article Server parameters. For the best experience, it's recommended that you use a connection pooler such as ProxySQL to efficiently manage connections as mentioned above. If you see an unexpectedly high number of connections, check your application code and retry the configuration as well.
Important: If you have a replica server, it’s strongly recommended to use ProxySQL to load balance traffic between the primary and replica servers.
It’s possible that you may experience either connection failures or timeouts during peak hours. Please check your active connections as well as CPU/memory/IO usage percentage in the portal metrics tab. Consider upgrading your server if any resource is hitting 100%. For more information, see performance troubleshooting basics.
You can also enable slow query logs to capture queries that are candidates for performance tuning and rewrite as necessary. For more information, see Slow query logs.
- Symptom: The last packet sent successfully to the server was X milliseconds ago. The driver has not received any packets from the server.
Recommended solution: Consider either expiring and/or testing connection validity before using it in your application. To avoid this issue, increase the server configured values for client timeouts, or use the Connector/J connection property autoReconnect=true to avoid this problem.
Retry logic is recommended to handle transient errors. However, you may want to limit the total retry count and delay between each retry to avoid excessive retry causing further issues. For more information, see Handle transient errors and connect efficiently.
Scenario: All connections are failing
This could be because of issue in Azure infra or maintenance activities.
- There may be a planned maintenance activity going on your database server. Check your Resource Health for the status. You can set up planned maintenance notifications to get notified of any planned activities.
- If you think there's a regional outage, see Overview of business continuity with Azure Database for MySQL for steps to recover to a new region.
- Consider set up alert when Azure database is not accessible.
- Set up database level monitoring for all your Azure Database for MySQL servers using open source tools such as PMM. For more information, see the blog post Monitor Azure Database for MySQL using Percona Monitoring and Management (PMM).
Conclusion
Hopefully, if you've been through all the guidance above, you've identified the cause of the connectivity issue. If you have any questions, please feel free to let me know.
Reference