Creating Azure Database for MySQL and using Azure Cloud Shell

After using Microsoft SQL Server for over 10 years going back to MySQL feels weird BUT with Azure it is really easy to build a MySQL database and I have a feeling that this might be the start of an incoming change for some. For this post I want to show you what Azure currently offers I then create a MySQL database and connect to it via Azure Cloud Shell.

Azure Database for MySQL provides a managed database service for app development and deployment that allows you to stand up a MySQL database in minutes. It is based on the community edition of MySQL and uses the InnoDB Storage Engine.

Here are some features on the InnoDB Storage Engine.

innodb

https://dev.mysql.com/doc/refman/5.5/en/innodb-introduction.html

When you create a MySQL server think of it as a central point where you access your databases just like you would when using “on-prem”. Microsoft states that it is “a container with strong lifetime semantics – delete a server and it deletes the contained databases”, makes perfect sense.

It is time to get practical. The first step is to create a MySQL server, you can select version 5.6 or 5.7.  Notice that I can only build a Basic tier at this moment in time.

mysqlserver

prices

Here you have the concept of compute units. No such thing as DTUs here but just as confusing.

Compute Units are a measure of CPU processing throughput guaranteed to be available to a single Azure Database for MySQL server. A Compute Unit is a blended measure of CPU and memory resources. In general, 50 Compute Units equate to half-core, 100 Compute Units equate to one core, and 2000 Compute Units equate to twenty cores of guaranteed processing throughput available to your server. I am not going to rehash official documentation on these concepts so I recommend reading https://docs.microsoft.com/en-gb/azure/mysql/concepts-compute-unit-and-storage

Assume I have created the server I now want to connect to the MySQL server via Azure Cloud Shell which is the weird looking symbol next to the notification bell below.

cloudshells

When using this tool for the first time you will be asked about where to create files i.e. the need for a storage account. It needs a storage account to hold persistent files.

clouddrive

By the way there is no PowerShell yet! (Sorry PowerShell fans – those on my twitter friends list being Rob Sewell, Shane O’Neill and Chrissy LeMaire).

First step is to enter the connection details for your MySQL server which is the information from the first screen of this blog post. You then enter your server password last.

The connection string being:

 mysql -h servername.mysql.database.azure.com -u username@servername -p

welcomes

Whoops, we need to do client IP address access because I received the below message.

ipaddress

To do this you go to the connection security setting within the portal for your MySQL server.

firewalls

Add a firewall rule for the relevant IP address range.

rules

Let’s re-connect via Azure Cloud Shell, by the way the circular arrows (shown below) within the shell basically starts a new session.

Bash

I am now connected to the server as you can see it is MySQL Community server, I am sure I wanted version 5.7 though?

oraclemysql

Let’s issue some VERY basic commands, just to prove how easy it is to start issuing commands.

Below shows basic server details.

 mysql> status

In case you are wondering being a SQL Server pro, MySQL’s default port is 3306. – All basic information.

loggedin

Next a list of databases on the MySQL server host.

mysql> SHOW DATABASES;

showdatabases

I then create a database, which I hope to see in the portal too.

 mysql> CREATE DATABASE DogMVP;

dogdb

Once you have created your dummy database you would need to issue USE to get into it where then you can start writing code to create your tables etc.

Regarding the Azure portal it takes less than 30 seconds for it to be updated.

azureportal

I finally run the below command because I fancied finding out bytes sent vs received.

 mysql> SHOW GLOBAL STATUS;

globalstatus

I then quit. (How polite of MySQL)

QUIT

There are currently some limitations, the following stand out for me.

  • Dynamic scaling of servers across service tiers is currently not supported. That is, switching between Basic and Standard service tiers.
  • Dynamic on-demand increase of storage on pre-created server is currently not supported.
  • Decreasing server storage size is not supported.

Overall what do I think? I think this is great. Not only is it easy to create a MySQL environment (Just as easy as Azure SQL Database) but having the Azure Cloud Shell combined with it makes the experience of connecting and issuing commands smooth and simple. It has great synergy.

Even though I dislocated couple of my fingers over the weekend I really enjoyed writing this post up, hopefully you have enjoyed reading it too.

One thought on “Creating Azure Database for MySQL and using Azure Cloud Shell

  1. Pingback: Creating Azure Database for MySQL and using Azure Cloud Shell — All About SQL – SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s