Announcing MySQL in-app for Web Apps (Windows)
mksunitha
MySQL in-app feature enables running MySql natively on Azure App Service platform.You don’t need to provision a database explicitly as during the creation of the web app when using this feature, we take care of enabling it if you select “MySQL in-app ” during creation or if the feature is turned ON for existing web app. To understand what MySQL in-app means , I have highlighted core functionality supported with the preview release of the feature:
You may also create a WordPress application with MySQL in-app from Azure marketplace. Test drive our demo site here using MySQL in-app.You can login with username : demo and password: demopassword .
The database enabled with your web app is "localdb". You are now ready to import your database schema or create a new one for your web app.
Browse your web app and Check the process explorer to verify if mysqld.exe is running. If the process is running, then MySQL server is ready to use. Remember to open the MySQL connection to your MySQL in-app database , localdb in your web app.
- Support PHP , MYSQL applications like WordPress, Joomla , Drupal etc .
- MySQL server running on the same instance side by side with your web server hosting the site. This will boost performance of your application
- Storage is shared between both MySQL and your web app files. Note with Free and Shared plans you may hit our quota limits when using the site based on the actions you perform . Check out quota limitations for Free and Shared plans.
- You can turn on Slow query logging and general logging for MySQL . Note that this can impact the site performance and should NOT always be turned ON . The logging feature will help investigation any application issues .
Create new Web App with MySQL in-app
Login to Azure portal and launch Web App + MySQL template by clicking here .Enter the Site name and select MySQL in-app as the database provider. Click on Create to deploy a web app using MySQL in-app.
You may also create a WordPress application with MySQL in-app from Azure marketplace. Test drive our demo site here using MySQL in-app.You can login with username : demo and password: demopassword .
Limitations
For preview release the feature has some limitations that to keep in mind.- Auto scale feature is not supported since MySQL currently runs on on a single instance .
- Enabling Local cache is not supported.
- MySQL database cannot be accessed remotely. You can only access your database content using PHPMyadmin or using MySQL utilities in KUDU debug console. This is described in detail below.
- WordPress and Web App + MySQL templates currently support MySQL in-app in the create experience.We are working on bringing this feature in for other MySQL based applications in Web category for Azure marketplace.
Manage MySQL in-app
Go to your web app and select MySQL in-app in the Menu blade on the right. You can use the setting here to manage your MySQL in-app feature , turn on logging , access PHPmyadmin etc.
Access database content
The database is protected by our sandbox environment and hence cannot be accessed remotely through MySQL workbench or MySQL command line tools (running on remote machine) . There are two ways you can manage your database content :-
Using PHPMyAdmin: With MySQL in-app , the MySQL process ( mysqld.exe) must be ready for connections before using PHPmyadmin tool to access the database. This means your web app has to open the mysql connection. You can use the sample code mentioned in section Get database connection string to open mysql connection. Go to your web app and select MySQL in-app in the Menu blade on the right . Click on the Browse button to open PHPmyadmin.
The database enabled with your web app is "localdb". You are now ready to import your database schema or create a new one for your web app.
-
Using Kudu Debug console: Access your Kudu debug console from the portal , go to your web app and Select Advanced Tools or use use a URL in this format https://sitename.scm.azurewebsites.net/debugconsole (replace sitename with your web app name).
Run the following command to run your query (remember to update the port number to your web app’s MySQL port since this feature does not use 3306 port)
D:\Program Files (x86)\mysql\5.7.9.0\bin\mysql.exe -e "ENTER SQL STATEMENTS" --user=azure --password=password --port=49175 --bind-address=127.0.0.1Example:
D:\Program Files (x86)\mysql\5.7.9.0\bin\mysql.exe -e "USE localdb;Select * from tasks;" --user=azure --password=password --port=49175 --bind-address=127.0.0.1 mysql: [Warning] Using a password on the command line interface can be insecure. mysql: Unknown OS character set 'cp0'. mysql: Switching to the default character set 'latin1'. task_id subject start_date end_date description 1 task1 2016-02-18 2016-02-19 sample taskThe mysql warning statements can be ignored in the output. You can use mysqladmin.exe and mysqld.exe as well , in the similar format as above .Please review the password and database information from D:\home\data\mysql\MYSQLCONNSTR_localdb.ini before entering the connection information in the commands below. Here is an example to flush logs
mysqladmin.exe flush-logs --user=azure --password=password --port=49175 --bind-address=127.0.0.1
Logging
Turn on slow query logs or general logs for MySQL . Once turned on , you can find these logs in D:\home\logfiles\mysql folder. Note if these settings are always on , then this can impact your web app performance
How to deploy your web app to using MySQL in-app
Get the database connection string
Before you deploy your web app code , the key thing to note when using this feature is to use ENVIRONMENT VARIABLES since the database connection information is not accessible directly. You can get the database connection information using MYSQLCONNSTR_localdb environment variable . You can also get the connection string from D:\home\data\mysql\MYSQLCONNSTR_localdb.ini .Here is a sample code snippet that you can use in your application to get the database host, port, database name , database user, database password.
$connectstr_dbhost = '';
$connectstr_dbname = '';
$connectstr_dbusername = '';
$connectstr_dbpassword = '';
foreach ($_SERVER as $key => $value) {
if (strpos($key, "MYSQLCONNSTR_localdb") !== 0) {
continue;
}
$connectstr_dbhost = preg_replace("/^.*Data Source=(.+?);.*$/", "\\1", $value);
$connectstr_dbname = preg_replace("/^.*Database=(.+?);.*$/", "\\1", $value);
$connectstr_dbusername = preg_replace("/^.*User Id=(.+?);.*$/", "\\1", $value);
$connectstr_dbpassword = preg_replace("/^.*Password=(.+?)$/", "\\1", $value);
}
$link = mysqli_connect($connectstr_dbhost, $connectstr_dbusername, $connectstr_dbpassword,$connectstr_dbname);
if (!$link) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
}
echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL;
echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;
mysqli_close($link);
As an example , if you are running a WordPress site you need to update wp-config.php such that it reads the connection string from the environment variable .
/*Add at the begining of the file*/
$connectstr_dbhost = '';
$connectstr_dbname = '';
$connectstr_dbusername = '';
$connectstr_dbpassword = '';
foreach ($_SERVER as $key => $value) {
if (strpos($key, "MYSQLCONNSTR_localdb") !== 0) {
continue;
}
$connectstr_dbhost = preg_replace("/^.*Data Source=(.+?);.*$/", "\\1", $value);
$connectstr_dbname = preg_replace("/^.*Database=(.+?);.*$/", "\\1", $value);
$connectstr_dbusername = preg_replace("/^.*User Id=(.+?);.*$/", "\\1", $value);
$connectstr_dbpassword = preg_replace("/^.*Password=(.+?)$/", "\\1", $value);
}
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', $connectstr_dbname);
/** MySQL database username */
define('DB_USER', $connectstr_dbusername);
/** MySQL database password */
define('DB_PASSWORD', $connectstr_dbpassword);
/** MySQL hostname : this contains the port number in this format host:port . Port is not 3306 when using this feature*/
define('DB_HOST', $connectstr_dbhost);
As a BEST PRACTICE when using MySQL in-app, we recommend to ALWAYS use Environment variables for the database information to prevent database connection issues with your web app. If your application requires a separate variable for port , you can use WEBSITE_MYSQL_PORT environment variable. The port number selected can vary if the instance is recycled and hence ALWAYS use environment variables.
Deploy you code
Deploy your web app code using GIT or FTP or any one of the supported deployment processes with Azure Web Apps . Refer Deploy to Azure app service web apps for details .Deploy your database
You cannot directly deploy you database . Hence , export your local database into a SQL script . Access your web app MySQL in-app database using PHPmyAdmin ( https://sitename.scm.azurewebsites.net/phpmyadmin) and click on the IMPORT tab to import your script into the localdbdatabase . For example:USE localdb; CREATE TABLE IF NOT EXISTS tasks ( task_id INT(11) NOT NULL AUTO_INCREMENT, subject VARCHAR(45) DEFAULT NULL, start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL, description VARCHAR(200) DEFAULT NULL, PRIMARY KEY (task_id) ) ENGINE=InnoDBRemember to include USE localdb; statement in your script so that the your schema and data are imported into the correct database.
How to turn on MySQL for existing Web Apps
Go to your web app and select MySQL in-app in the Menu blade on the right . Turn ON MySQL in-app feature.
Browse your web app and Check the process explorer to verify if mysqld.exe is running. If the process is running, then MySQL server is ready to use. Remember to open the MySQL connection to your MySQL in-app database , localdb in your web app.
Migration to production database
You can easily migrate this database when ready for production to- Azure database for MySQL(Preview)
- ClearDB database
- MySQL on virtual machine on Linux or Windows OS
Best practices
- When using Web app with MySQL in-app provider with Basic, Standard or Premium app service plans, turn on ALWAYS ON setting as described here. By default, web apps are unloaded if they are idle for some period of time which means both the web app and MySQL in-app server will be take a longer time to load from an idle state. PHPMyadmin may not be accessible during the idle state. With ALWAYS ON feature, you can keep your web app from getting into an idle state.
- When using this feature with Free and Shared Web App pricing plans, add the app setting WEBSITE_FASTCGI_MAXINSTANCES and set its value to 3 if your web app is likely to get traffic from a few users say between 10-20. This setting will prevent creating too many PHP FastCGI instances which will consume all the memory causing your web app to hit the quota too early. Checkout benchmarking blog post for more information.
