TIL: how to create and work with a MySQL database on Azure


Arthur Turrell


April 24, 2024

In this TIL, I find out how to create a new MySQL database on Microsoft Azure. This is a place to store structured, tabular data. Note that the instructions below assume you are using a bash-like terminal, for example zsh, rather than Powershell.


You’ll need to sign up for a Microsoft Azure account for this, and create a “resource group”. You’ll also need the Azure Command Line Interface (CLI), which you can find information on here. (Alternatively, you can do this through the Azure Portal, but it’s often useful to have the reproducible commands.)

It’s also helpful to understand the lingo and how different bits of the cloud-based SQL setup relate to one another:

  • there is the resource group, which many Azure services could live under
  • there is a server, which co-ordinates resources
  • there are individual databases, which live on particular services

These three are hierarchical: a database lives on a server that sits under a particular resource group.

You may be wondering why MySQL rather than another type of SQL database? The pricing information is quite difficult to parse, but for hobby projects, it looks like a MySQL database is a lot cheaper than an Azure SQL (MSSQL) database. A basic MySQL database (burstable, b1ms model) is around US$13.00 per month whereas a provisioned Azure SQL database is hundreds of dollars a month. The latter option has a more complex set of options too, though, so there’s more to make a mistake with. So we’ll be going with the open source MySQL in this case.


Creating a MySQL server

First set and load your configs. You’ll need to decide on a login and password for your SQL server.

location=<>  # eg "uksouth"

Then to create the server (with conservative cost settings), it’s

az mysql flexible-server create --location $location --resource-group $resourcegroup --name $server --admin-user $login --admin-password $password --sku-name Standard_B1ms --tier Burstable --public-access Enabled --version 5.7 --high-availability Disabled --storage-auto-grow Disabled

Configure permissions to use the SQL server

The default options are cautious on two sets of permissions, the certificate and the IP addresses that are allowed to connect. We need to open these up for your computer.

Go on to Azure portal page for the newly created server and click “configure networking”. Then enable connections from your specific IP address through the firewall (“add current client IP address”). On the same page, there’s an option to download the SSL certificate. Do this, and put it in a secrets folder that is not under version control.

Connect to the SQL server using the command line

Let’s check that it worked! Assuming you saved the SSL certificate as sql_cert.crt.pem in the same directory in which you are working, you should be able to run the following on the command line:

mysql -h $server.mysql.database.azure.com -u $login -p "--ssl-ca=sql_cert.crt.pem"

If you saved the certificate in secrets/, change the path in the above command appropriately.

Note that you may need to regenerate the .pem file from time to time.

Note also that if you are accessing this resource through a VPN or other managed network, this is where you may have difficulties. To circumvent these, you can use a normal internet connection or connect from a virtual machine.

Working with the SQL database from Python

Python has a number of great tools for working with SQL databases. For this, we’ll look at just two: MySQL Connector and the wonderful Ibis project, which translates across a wide number of data analysis backends.

Just as we did before, we need to plug in our credentials. The syntax is slightly different from bash and it’s recommended to use a secrets manager like python-dotenv to manage these and ensure they do not get mistakenly committed to version control.

username = <>
password = <>
hostname = "<>.mysql.database.azure.com"

Connecting and creating a database with MySQL Connector

Next, we start up the connection.

mydb = mysql.connector.connect(

where it’s assumed that the certificate is in a folder called secrets.

If running this works, you won’t see an error message or a confirmation message, but you can check all is as it should be by running:


which should result in something like:

<mysql.connector.connection_cext.CMySQLConnection object at 0x3213r4g90>

Now it’s not very exciting to have a SQL server with no databases in. So, next, we’re going to create a database.

create_db_query = "CREATE DATABASE online_movie_rating"
mycursor = mydb.cursor()

If you then run

mycursor.execute("Show databases;")
for db in mycursor:

you’ll get a list of databases that should include online_movie_rating.

But an empty database is no fun—let’s populate it. First, the table we wish to create:

create_movies_table_query = """
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT

And then the execution of that create table command:

mydb = mysql.connector.connect(
mycursor = mydb.cursor()

Note the difference with the earlier command—this one has a database= keyword argument in it.

With the table created, we can also populate it with some data:

insert_movies_query = """
INSERT INTO movies (title, release_year, genre, collection_in_mil)
    ("Forrest Gump", 1994, "Drama", 330.2),
    ("3 Idiots", 2009, "Drama", 2.4),
    ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5),
    ("Good Will Hunting", 1997, "Drama", 138.1),
    ("Skyfall", 2012, "Action", 304.6),
    ("Gladiator", 2000, "Action", 188.7),
    ("Black", 2005, "Drama", 3.0),
    ("Titanic", 1997, "Romance", 659.2),
    ("The Shawshank Redemption", 1994, "Drama",28.4),
    ("Udaan", 2010, "Drama", 1.5),
    ("Home Alone", 1990, "Comedy", 286.9),
    ("Casablanca", 1942, "Romance", 1.0),
    ("Avengers: Endgame", 2019, "Action", 858.8),
    ("Night of the Living Dead", 1968, "Horror", 2.5),
    ("The Godfather", 1972, "Crime", 135.6),
    ("Haider", 2014, "Action", 4.2),
    ("Inception", 2010, "Adventure", 293.7),
    ("Evil", 2003, "Horror", 1.3),
    ("Toy Story 4", 2019, "Animation", 434.9),
    ("Air Force One", 1997, "Drama", 138.1),
    ("The Dark Knight", 2008, "Action",535.4),
    ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1),
    ("The Lion King", 1994, "Animation", 423.6),
    ("Pulp Fiction", 1994, "Crime", 108.8),
    ("Kai Po Che", 2013, "Sport", 6.0),
    ("Beasts of No Nation", 2015, "War", 1.4),
    ("Andadhun", 2018, "Thriller", 2.9),
    ("The Silence of the Lambs", 1991, "Crime", 68.2),
    ("Deadpool", 2016, "Action", 363.6),
    ("Drishyam", 2015, "Mystery", 3.0)

Let’s see if we can retrieve some of this:

select_movies_query = "SELECT * FROM movies LIMIT 5"
result = mycursor.fetchall()
for row in result:
(1, 'Forrest Gump', 1994, 'Drama', 330)
(2, '3 Idiots', 2009, 'Drama', 2)
(3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', 35)
(4, 'Good Will Hunting', 1997, 'Drama', 138)
(5, 'Skyfall', 2012, 'Action', 305)

Finally, to close the connection, use mydb.close().

Using Ibis to work with a cloud-based SQL database

The Ibis project makes working with a ton of different back-end data tools easy by making the syntax consistent. Examples are: MySQL, BigQuery, DuckDB, pandas, MSSQL, and much, much more!

Once you’ve imported Ibis (import ibis), you may find it more fun and useful to use it in interactive mode, which looks more like how pandas works:

ibis.options.interactive = True

To see some data, we need to create a connection:

con = ibis.mysql.connect(

And to grab our “movies” table again, but as a pandas dataframe:

t = con.table("movies")
id title release_year genre collection_in_mil
0 1 Forrest Gump 1994 Drama 330
1 2 3 Idiots 2009 Drama 2
2 3 Eternal Sunshine of the Spotless Mind 2004 Drama 35
3 4 Good Will Hunting 1997 Drama 138
4 5 Skyfall 2012 Action 305

Ibis is great to explore data. It has fully fledged pandas-style and SQL-style APIs. So you can do:


Returned result from filter using Ibis

But you can also get this as SQL:

FROM movies AS t0
  t0.release_year < 1996

It’s a really powerful downstream analysis tool.

And that’s it! Most other SQL commands are as you’d expect and can be looked up in the MySQL documentation.