-
Notifications
You must be signed in to change notification settings - Fork 653
MySQL in app
The feature enables running MySql within the site itself. One does not need to provision database explicitly and the data is always backed up along with the site contents. The key benefit is ease of use/setup and network performance (MySql running locally within the site). The limitation is it does not support multiple instances, auto scale nor slots.
Any sites using MySql as database namely WordPress, Joomla, etc.
To enable the feature, see this. To verify if MySql is actually accessible by the site, create the following file d:\home\site\wwwroot\phpmysql.php with below content and browse to http://<sitename>.azurewebsites.net/phpmysql.php.
<?php
$servername = "";
$username = "";
$password = "";
$dbname = "";
// Parsing connnection string
foreach ($_SERVER as $key => $value) {
if (strpos($key, "MYSQLCONNSTR_") !== 0) {
continue;
}
$servername = preg_replace("/^.*Data Source=(.+?);.*$/", "\\1", $value);
$dbname = preg_replace("/^.*Database=(.+?);.*$/", "\\1", $value);
$username = preg_replace("/^.*User Id=(.+?);.*$/", "\\1", $value);
$password = preg_replace("/^.*Password=(.+?)$/", "\\1", $value);
}
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} else {
echo "connection successful<br/>";
}
$conn->close();
?>The connection string flows to your application as an env variable MYSQLCONNSTR_localdb. The env is added to the main site process. For SCM/Kudu site, one may read the same info from D:\home\data\mysql\MYSQLCONNSTR_localdb.txt file. Beware that we are not using the default MySql port (3306). In fact, the port number may vary for each application life cycle depending on its availability at startup time. The port info is also available as an env variable WEBSITE_MYSQL_PORT to your site.
phpMyAdmin is enabled by default with the feature. You can access it thru https://<sitename>.scm.azurewebsites.net/phpMyAdmin/. Since MySql is only started with the main site, do make sure that the main site is running (simplest way is to turn on AlwaysOn) before using phpMyAdmin. Unlike phpMyAdmin from SiteExtenions gallery, this phpMyAdmin is aware of MySql credentials and will connect automatically.
Important: If you previously have phpMyAdmin installed via SiteExtension gallery, you will have to uninstall it. Since this phpMyAdmin from SiteExtension gallery will take precedent and it is not MySql In-App aware, it will not work with MySql In-App.
Among other things, MySql.exe or MySqlAdmin.exe tools are available at d:\program files (x86)\MySql\5.7.9.0\bin. Like phpMyAdmin above, make sure the main site is running before using the tool in SCM Kudu Console. The credential is available at D:\home\data\mysql\MYSQLCONNSTR_localdb.txt. If you are running WebJobs and need to get connection string programmatically, this sample shows how to do so. Do make sure you set AlwaysOn for your site to ensure MySql is up and running.
Due to limited spaces, we may not have all the complete MySql tool set. Let us know (via forum) if any other tools would be useful.
MySql data is stored at d:\home\data\mysql directory.
MySql server log is stored at d:\home\logfiles\mysql directory. This will be where you start investigating any MySql issues. Beware that the logfile names are dated and, if the number exceeds the limit (50 log files), old ones will be removed to free up spaces.
To turn on general log, simply add appSettings WEBSITE_MYSQL_GENERAL_LOG = 1. The log file is also stored at d:\home\logfiles\mysql directory (look for log file with general suffix).
To turn on slow query log, simply add appSettings WEBSITE_MYSQL_SLOW_QUERY_LOG = 1. The log file is also stored at d:\home\logfiles\mysql directory (look for log file with slowquery suffix).
You can specify extra arguments for MySqld.exe via WEBSITE_MYSQL_ARGUMENTS appSettings.
Yes. During site backup, we simply run mysqldump.exe tool against In-App MySql. The output script LocalMySqlDatabase.sql was saved along with the site content - you can see it in the backup zip file. During restore, we simply run mysql.exe on the LocalMySqlDatabase.sql script.
Yes. You could simply use phpMyAdmin (see how to access above). Programatically, you could POST to https://<sitename>.scm.azurewebsites.net/mysqlutils/dump (with empty body) and https://<sitename>.scm.azurewebsites.net/mysqlutils/execute to export and import sql scripts respectively. Note that, this is SCM of your site, you will need to provide publishing credentials.
Yes. The connection string is stored at D:\home\data\mysql\MYSQLCONNSTR_localdb.txt. The application (such as wordpress) reads from this file for what database, username and password to use. This also applies to what to backup and restore provided by Azure WebApps. If you want to customize the database, username and password, after you have created a new database, add new username or update password, simply modify D:\home\data\mysql\MYSQLCONNSTR_localdb.ini
, remove D:\home\data\mysql\MYSQLCONNSTR_localdb.txt and restart the WebApps.
Try delete D:\home\data\mysql\MYSQLCONNSTR_localdb.txt and restart the WebApps.
Forum is a good starting point. Do title it with MySql in-app keyword for faster references.