Azure-docs: User name format does not work with ProxySQL

[chainhead]

Our current set-up on AWS uses ProxySQL (https://proxysql.com/) in front of a MySQL on AWS RDS. Refer https://github.com/chainhead/mysql-demo for an example. However, on Azure, the following command does not work:
mysql -h 127.0.0.1 -P6032 -u dbuser@server -p It returns an error as 'dbuser'@'server'@'localhost' not having access.

Note that, the host and port point to the ProxySQL server that will route the request to the back-end MySQL database.

Can you please suggest a workaround? Not being able to use ProxySQL is a big show stopper for us!

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 1f766765-03b0-2d76-7fe0-42b207afd9f9
• Version Independent ID: cbd009b3-ba92-2207-d843-77c2268eb7a5
• Content: Quickstart: Create Azure Database for MySQL server - Azure portal
• Content Source: articles/mysql/quickstart-create-mysql-server-database-using-azure-portal.md
• Service: mysql
• GitHub Login: @ajlam
• Microsoft Alias: andrela

[NavtejSaini-MSFT]

@chainhead Thanks for the question. We are looking into this and will get back to you regarding this.

[NavtejSaini-MSFT]

@chainhead The Connectivity in Azure for MySQL works differently from AWS. MySQL works with proxy by default, so would need to add each public IP and publish it in MySQL Server for any allowed IP to access the service which means public IP would need to be white listed under the MySQL server’s Firewall rules

Additionally you can check this document for MySql connectivity architecture:
https://docs.microsoft.com/en-us/azure/mysql/concepts-connectivity-architecture

[chainhead]

MySQL works with proxy by default, so would need to add each public IP and publish it in MySQL Server for any allowed IP to access the service which means public IP would need to be white listed under the MySQL server’s Firewall rules

Long-winded sentence - not clear!

I did define a user in MySQL as 'demo1'@'%' (close to what is shown here https://docs.microsoft.com/en-us/azure/mysql/howto-create-users). The firewall for MySQL is set to take connections in the range of 10.0.0.0 to 10.0.0.255 because I have added a Microsoft.Sql service end-point. The addition of a service end-point works despite the end-point being named (deceptively) as Micorsoft.Sql - https://docs.microsoft.com/en-us/azure/mysql/concepts-data-access-and-security-vnet#terminology-and-description

While all this discussion is around connectivity, the larger problem is the inability to use ProxySQL for its intended purposes e.g. connection pooling, query rules and so on (https://www.percona.com/resources/technical-presentations/proxysql-use-case-scenarios-percona-mysql-webinar). Or, are these things also managed by Azure?

[Mike-Ubezzi-MSFT]

@chainhead No need for proxysql.com as the Azure Database for MySQL (PaaS) service provides a GW service the brokers all connections to your MySQL instance. If there is functionality you are specifically seeking that proxysql.com provides, can you detail that? If you look at the server params for your MySQL deployment, you will find those parameters that are available.

How to configure server parameters in Azure Database for MySQL by using the Azure portal (link)

If you desire the use of proxysql.com, you can deploy an Azure Ubuntu VM with a MySQL image and set-up a solution exactly as detailed in the below diagram. There are some limitations with the Azure Database for MySQL (PaaS) as you don't have access to the my.cnf file and not all server properties are exposed through the Azure Portal. In this case, you could deploy a MySQL VM and set up a solution similar to the image below as an IaaS solution. Please see the Azure Marketplace (link) for available apps.

One caveat is that the load balancer is a separate service that you would deploy (link).

I hope this information is helpful and please do ask additional questions as you need to understand more. The PaaS service will not accommodate your desire to leverage proxysql.com.

Some additional information to help you understand some key Azure concepts:

• The Microsoft.sql service end-point is a common service across all Azure Database PaaS services (Azure SQL Database single instance and managed pools, MySQL, MariaDB, and PostgreSQL).
• A VNet service end-point allows for private address space and a firewall allow/deny list is still required with or without the service end-point.
• The Azure Database for MySQL is a PaaS offering and deploying a MySQL instance in an Azure VM is IaaS, where you have more control...specifically to the core *.cnf files and access to the superuser account.

Again, please let us know if you have additional questions.

[chainhead]

If there is functionality you are specifically seeking that proxysql.com provides, can you detail that?

I would request you to refer this link for ProxySQL features (e.g. read/write splits among others) and critique on how Azure MySQL achieves them natively.

As of now, I have successfully developed an Azure equivalent solution that uses Azure Database for MySQL and VMs for hosting the load balancer and APIs. I couldn't use ProxySQL; hence this question. (I know, I can use an API Gateway with serverless compute that will eliminate the load balancer and API VMs - but, one step at a time. :-) ).

[NavtejSaini-MSFT]

@chainhead We understand your need completely and it may not be available right now but you can definitely add your idea for use of proxysql to the following user voice link for MySql so that product team can look into this further.
https://feedback.azure.com/forums/597982-azure-database-for-mysql

@ajlam Adding you to this conversation, so that you can check if the document can be enhanced to add a link or topic for open source/3rd party tools. or the functionality mentioned in the proxy sql link can be incorporated.

[chainhead]

While I was going through the feedback page, I noticed this idea Single alias for all db server login names that can work for all master and replica servers. This idea is similar to the issue discussed here. I have up-voted that idea and also added mine.

Thanks for the support so far!

[ambhatna]

@chainhead
We now supports ProxySQL. For more information, you can refer to the blog Load balance read replicas using ProxySQL in Azure Database for MySQL.

Please let us know if you have any other question.

[chainhead]

@ambhatna Truly appreciate you/your team/Microsoft for so promptly addressing my need!