How to export/import database from Azure Flexible server to MySQL In App?

[Tony_Ju]

MySQL In App allow us import and export data to and from an external MySQL database directly.

There are two version of Azure MySQL server :  Azure MySQL single Server and Azure MySQL Flexible Server.

There is no issue when export from MySQL in APP to Azure MySQL single Server as the external server.

But if we try to export from MySQL in APP to Azure MySQL Flexible Server, we will encounter below error. 

The error message is not useful for us to troubleshooting. Even when I go to the activity logs to check the detailed error message. I still cannot get the cause. 

Error occurred while connecting to the database  on server tonyserver.mysql.database.azure.com: Unable to connect to any of the specified MySQL hosts.. Please make sure the database connection string is up to date.

Then I tried to connect to Azure MySQL Flexible Server from the application code level. I got some accurate error. 

Failed to connect to MySQL: Connections using insecure transport are prohibited while --require_secure_transport=ON.

This is the PHP code I used to test the connection.

<?php
$conn = mysqli_init();
mysqli_real_connect($conn, 'XX.mysql.database.azure.com', 'userID', 'password', 'database', 3306);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}else{
    die('connected successfully');
}
?>

After changing require_secure_transport setting on the MySQL server to OFF. The PHP code can connect to the server successfully.

However, when trying to connect from MySQL In App, I still encounter the same error as we saw before. Luckily, we can change the Input Format to connection string. Just note that we need to add the SslMode=None in the connection. Now we can import/export from and to Azure MySQL Flexible server.

Note:  please consider the security issue when disabling the SSL mode.

Conclusion:

If you want to use the export/import feature of MySQL In App and external server is Azure MySQL Flexible server, we need to change require_secure_transport setting on the MySQL server to OFF and use connection string method to connect. If you require secure transport, you could consider downloading the sql file from the phpMyAdmin portal and importing the SQL file.

Note:

MySQL In App runs a local MySQL instance with your app and shares resources from the App Service plan. Note that apps using MySQL In App are not intended for production environments, and they will not scale beyond a single instance.