Uncategorized

Getting MySql Running on Azure

Leave a comment

azure[1] There are a few applications that I’m playing with in Windows Azure that are reliant on MySql for various reason. For example, I’m working with a group that is doing Joomla development and it’s completely dependent on MySql. Mostly this is due to using MySql native drivers rather than using a database independent layer such as ADO.NET in .NET or PDO in PHP or leveraging database specific features that are only found in MySql. Regardless of the reason, for me to run these applications in Windows Azure, I have to get MySql running in Azure. I found that it wasn’t as hard as I initially thought it would be and it’s a technique that I can reuse for a lot of binary executables.

Thoughts and Recommendations

Before I go into the technical details of getting MySql working, I thought I should share what I think of running MySql in Windows Azure to save you the time of reading through all of this before I try to talk you out of it.

The reality is that even though you can run MySql in Windows Azure, it’s not overly practical to do so. My recommendation is to use this as a last resort or stopgap until Sql Server support for your application is ready to go. There are multiple reasons why this is my recommendation, some technical, some financial. None of them have to do with MySql itself but rather the process that you have to go through to run and manage it in Windows Azure.

First, let’s dig into the technical. The way that hosting MySql in Windows Azure working is that you create a worker role that will actually host the binary. You have to copy up the executable and then have just a little bit of Windows Azure specific code to actually execute the application and pipe requests to it. You can actually do that with just about any type of executable as long as it can be XCopy deployed and run headless. This could be especially good for things like doing distributed calculations.

With regards to mySql, however, one issue that I see with this is that you have to pay $0.12 an hour per web role that you have live which means that on average (at 0.12 * 24 * 30) you’re paying $86.40 a month for a single instance of MySql. That’s prior to having any type of failover or durability. SqlAzure is only $9.99 a month for a gig of storage.

If you want failover and durability, you need to run at least one extra instance of MySql in another worker role and configure them in a master/slave configuration. The reality is that you can get some fairly high durability with this configuration and it’s out of the box part of the Windows Azure MySQL PHP Solution Accelerator that we’re going to download in a moment.

Now, if you want to manage the MySql instances, you’ve got a couple of options. One of those is to run PHPMyAdmin which runs in a web role. Or you can connect to your MySql databases with a remote admin tool of some sort. Contrast that to being able to log into the Windows Azure admin page and administer your SQL Azure databases.

Getting MySql Running on Azure

Now that I’ve convinced you not to do this unless you absolutely have to, let’s dig into the technical steps of getting MySql running.

There’s a PDC talk that covers some of this material at Developing PHP and MySQL Applications with Windows Azure..

First, I wanted to get MySql running in Windows Azure.

  1. First, I downloaded the Windows Azure MySQL PHP Solution Accelerator. There’s a manual for that accelerator that lays out the rest of the requirements for getting MySql running in Windows Azure.
  2. The first requirement is MySql itself. I downloaded the XCopy deploy version of MySQL (http://dev.mysql.com/downloads/mysql/5.1.html#winx64).
  3. Next, since we are going to use PHPMyAdmin to administer the MySql databases, I downloaded PHP – http://windows.php.net/download
  4. And then, obviously, PHPMyAdmin – http://www.phpmyadmin.net/home_page/downloads.php.

Now that I had the basic requirements down for MySql running in Azure, there are a couple of steps that I had to take.

First, I installed the Windows Azure MySQL PHP Solution Accelerator and extracted MySql, PHP and PHPMyAdmin.

Then I ran the “Buildme.cmd” script that comes with the accelerator. That checked to see if the MySQL binaries were in the right place. Since I hadn’t done anything other than download and unzip MySQL, the Buildme.cmd script fixed that for me and copied them to the right directory (.\MySql_WorkerRole\mysql). BTW, this could take a little while. Then it asked me for the

Next thing is to open up the .\, I renamed php.ini-recommended to be php.ini and did a few edits.

  1. Change the extension_dir to be as follow:
    • extension_dir = “./ext”
  2. Uncomment (remove the ; from in front of the following:
    • extension=php_mbstring.dll
    • extension=php_mcrypt.dll
    • extension=php_mysql.dll

Second, I extracted PHPMyAdmin to a holding directory and made a few edits to /libraries/config.default.php.

  1. Search for ‘auth_type’ and ensure that it’s as follow:
    • $cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
  2. Search for ‘AllowArbitraryServer’ and ensure that it’s as follows:
    • $cfg[‘AllowArbitraryServer’] = true;

Now that those edits are done we are almost ready to try running the MySql portion of this exercise in the DevFabric.  Last this is that I *highly* recommend changing the username/password from mysqluser/#ms123 to something else. Now we’re ready to test.

Before we do that, let’s take a quick peek at what the solution accelerator comes with.

First, there are 4 projects that are (or at least can be) deployed and a couple of helper folders.

  • MySql_WorkerRole – this is the primary application that we’re concerned about as it is the one that deploys MySql for us.
    • MySqlPHP – Windows Azure configuration bits for the projects
  • PHPMyAdminWebRole – this role packages up PHPMyAdmin and runs it in Azure.
  • InstanceManagerClient – this is an admin application that you can use to get command line access to the worker roles running MySql. This can be very useful for a lot of reasons. It connects to the InstanceManagerServer
    • InstanceManagerServer – this runs in each of the MySql_WorkerRole instances listening for connections from the InstanceManagerClient.
  • WorkerRole – Logging and maintenance worker role
  • Roles.Common – helper project
  • Test.Common – test project

Really, for this exercise we just care about the top three of those projects.

In the config project is the ServiceConfiguration.cscfg which has a worker role configured for MySql:

<?xml version="1.0"?>



<ServiceConfiguration . . .>



  <Role name="MySql_WorkerRole">



    <Instances count="3" />



    <ConfigurationSettings>



      <!--<Setting name="DataConnectionString" value="UseDevelopmentStorage=true" />-->



      <Setting name="TableStorageEndpoint" value="http://table.core.windows.net" />



      <Setting name="BlobStorageEndpoint" value="http://blob.core.windows.net" />



      <Setting name="DataConnectionString" value="DefaultEndpointsProtocol=https;AccountName=azuresolaccstorage;AccountKey=yQai0qLjF7lUTPy3pcf+j2xyjAKQnf7nCv2IYMIPPBWsqkGiOZqD/KMb9aolc9/kh41pm6XB5Zt0boBje7MXlg==" />



      <Setting name="DiagnosticsConnectionString" value="DefaultEndpointsProtocol=https;AccountName=azuresolaccstorage;AccountKey=yQai0qLjF7lUTPy3pcf+j2xyjAKQnf7nCv2IYMIPPBWsqkGiOZqD/KMb9aolc9/kh41pm6XB5Zt0boBje7MXlg==" />



      <Setting name="ContainerName" value="mysqlphp11" />



      <Setting name="FullBackupHour" value="06:00" />



      <Setting name="IncrementalBackupDuration" value="10" />



      <Setting name="RecycleThreadSleepTime" value="300" />



      <Setting name="EnableWindowsAzureDrive" value="False" />



      <Setting name="EnableBackup" value="True"/>



    </ConfigurationSettings>



  </Role>



. . .



</ServiceConfiguration>


Here’s where you configure if you want to use more than one instance (for failover and the like) and whether or not you want to actually write out to blob storage and all. 


The second thing is a MySql_WorkerRole project. This is a C# project that does a little bit of very important work for us. To sum up, it copies the MySql bits out to Windows Azure, starts the process running and then monitors the health of the MySql server. 


All we have to do now is deploy. To do that, run the Runme.cmd. This will package things up, copy them out to the development fabric, starts the various roles and then launches the browser. 


Side note on the development experience – I’ll also warn you now that playing with the firewall on your dev box is a little tricky because every time that you redeploy it creates a new package and copies the MySql executables to a new location. That means that you pretty much have to open a incoming port rather than just authorizing the program unless you want to reauthorize the with your firewall every time. 


Conclusions


Although I recommend using MySql on Windows Azure for stopgap and last resort, the awesome news is that with the Windows Azure MySQL PHP Solution Accelerator it’s actually not that hard to setup and works pretty well.

Leave a Reply

Your email address will not be published. Required fields are marked *