This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
You may be pondering the question in the title while using the Azure Database for MySQL service for the first time. This question is frequently asked by developers and/or our customers new to the service. In this blog post, I try to address this question by helping you understand the requirement, and also share a tip if in some scenarios you cannot afford to accommodate this requirement. Let’s get started.
For those new to the Azure Database for MySQL service or unfamiliar with what I am talking about here, let me quickly summarize the requirement. As mentioned in the documentation, when connecting to the service, the username in the connection string for authentication needs to be specified in the format username@servername. It’s also important to note that all popular driver frameworks viz JDBC, PHP, ADO.Net, Nodejs, Python, Golang, etc. support this specification. Further, we have also worked with the community and updated some of the drivers which no longer require you to specify this format. The following is the list of drivers which no longer require this format:
- Java - MariaDB Connector/J: since version 2.2.5+
- C/C++ - MariaDB Connector/C: since version 3.0.5+
- PHP - mysqlnd: since PHP 7.2.23+; PHP 7.3.10+
To understand this requirement for Azure Database for MySQL, you first need to understand the underlying architecture and design of the service. Below is the architecture for Azure Database for MySQL.
The design is optimized for high availability, and elasticity. The architecture separates the compute and storage. The database engine runs on a proprietary compute container (not docker container), while data files reside on Azure premium storage with three locally redundant synchronous copies of the database files. During planned/unplanned failovers or scale up/down events, a new container is provisioned, the storage with data files is mapped to the new container and database is brought online, thereby providing high availability, and elasticity at reduced cost.
In the above architecture, the gateway ensures transparent failover for the client applications. In other words, the gateway will transparently redirect the incoming connections to your active server at any given time to ensure the failover to a new compute server post planned or unplanned events doesn’t require you to change the connection string. The servername in the format <servername>.mysql.database.azure.com resolves to the gateway IP. The gateway is a shared resource in Azure region and every incoming request (new connection or subsequent queries after session is established) to the gateway needs to specify the target server it needs to connect. As the servername in full qualified format (<servername>.mysql.database.azure.com) is primarily meant to reach the regional gateway service, specifying hostname just in the connection string may not be enough to reach the target server. For accurate connection redirection at the gateway, you are required to specify userid in the format username@servername. Hope this addresses the question on why this format is required.
We have seen that most, if not all, connection drivers support this format, and hence it should be straight forward for developers or customers to use this format. In addition, we are also working with the community to make it seamless for developers to connect to Azure Database for MySQL server. However, we have seen in some rare scenarios, using legacy drivers that don’t allow special character such as ‘@’ or some legacy applications that doesn’t allow changing the connection string in this format causes unforeseen errors. For such scenarios, you can use the ProxySQL load balancer proxy solution to broker the incompatibility between the application and Azure Database for MySQL.
ProxySQL is a high-performance MySQL proxy that sits between the application and the Azure Database for MySQL service. Ideally, ProxySQL is designed to provide a transparent read/write split of the workload to allow you to load balance and deploy a scale out architecture with MySQL, but you can also use it with a single server with no replicas between the application and Azure Database for MySQL to bypass the username@servername format requirement. ProxySQL further allows you to leverage the built-in connection pooling benefits of ProxySQL and thereby optimizes the performance of the application running against Azure Database for MySQL.
If you need step-by-step instructions for configuring ProxySQL against Azure Database for MySQL, you can refer to my colleague, Amol Bhatnagar’s previous blog posts. This blog post shows you to configure ProxySQL on Ubuntu VM (assuming your application is running on VM), while this blog post allows you to configure ProxySQL on Kubernetes as a service. You can skip the part of the blog post related to configuring read/write rules, as the detail may not be applicable for single server scenario. However, the rest of the steps are relevant and should just work.
If you have trouble setting up ProxySQL on Azure Database for MySQL, please contact the Azure Database for MySQL team at AskAzureDBforMySQL@service.microsoft.com.