MySQL on Windows using Docker : A comprehensive setup using Docker and WSL

I am setting up my development machine, because in my experience office laptops ,are not meant for personal code development if you are serious about #codeForSelf. Besides it’s more fun to work from scratch with no restriction. This new machine, is on windows, and while I was impressed with what Microsoft had come up with for developers in past 3-4 years (I use Mac for office work, so have been out of touch since), there still was a need to setup my development machine with all the components that I would be using for application development. One of them being database, like MySQL. But I did not want to setup MySQL the installer way, that way I get stuck with this machine and think about migration trauma if I make an app that needs to be deployed on say, cloud. An attractive option was to use Docker based container with volume that can be backed up and migrated. This blog, serves as a record of this exercise, and probably help folks who are newbies like me for such a setup. Let’s jump right in.

Spin up MySQL using Docker on windows : The complete stuff

Step#1: Install docker on Windows

I am not going to cover this in detail. But I went in using WSL’s (Windows Subsystem for Linux) Ubuntu flavor, and install docker using usual apt package manager.

Step#2: Get the MySQL docker image

Once you have docker installed, download MySql’s official latest, or intended version’s image from MySql’s office docker repo using “docker pull mysql”

Step#: Run image and log in !!

I did this using WSL console, would recommend the same if you are on windows.

“docker run -p 3307:3306 –name={$container_name} -e MYSQL_ROOT_PASSWORD={$root_password} –restart on-failure -d mysql/mysql-server:{$image_tag}

Nothing special about the command above, you are starting MySQL container using image tag of your choice (use latest, in case you are unsure of the tag) and pairing your host system’s port 3307 to docker container’s 3306

docker log {$container_id} should show you, status of the container and logs in case of a rare occurrence of any error

To log into MySQL inside the container use “docker exec” command

docker exec -it {$container_name} mysql -uroot -p

We are executing “mysql” command with root user and password interactively on TTY. A successful, execution would bring up MySQL prompt. You can now execute SQL commands on the container, that you have just spun up. Half way there 🙂

Let’s jot in some commands

create database spike; # this would be our test database

show databases; # this would show all your databases

use spike; #switch to spike database 

#create a new table
create table spike_band_song (
    rowid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    band_name varchar(200) NOT NULL,
    song_name varchar(200) NOT NULL
) ;

#insert some values in the table
insert into spike_band_song (band_name,song_name) VALUES (
  'Guns and Roses',
  'Sweet child of mine
);

select * from spike_band_song

If you are able to see the expected results, you have setup MySQL using dockers on windows. Story does not end here though. There are two things yet to be completed for making this setup application worthy.

Connect using MySQL client

You are not going to always log into your container to execute CRUD queries. For this you would need a client or tool to connect to the docker container. For instance MySQL Workbench. But to do so, you would want to tweak your users a bit.

It’s recommended best practice not to expose your root user, so lets create a new user by logging into our container using the “exec” command

CREATE USER ‘meta’@’%’ IDENTIFIED BY ‘password’;

We create a user meta on % host. When you execute “select host, user from mysql.user” you would see the root user created on localhost as ‘host‘. This causes an issue when you are connecting outside of your docker container. Your container is in essence a machine running over your host machine. Using % as host, ensures access not just to localhost, but other host strings like ‘127.0.0.1’ or the IP of your host machine

As meta user is created, we grant it privilege on our spike database using

GRANT ALL PRIVILEGES on spike.* TO ‘meta’@’%’

FLUSH PRIVILEGES

Now using MySQL workbench, you can create and test a connection to localhost:3307 using user ‘meta‘ and password ‘password‘

The last piece: Volume

Your docker container as long as its not removed, persists the data in its internal space. But what if you delete is somehow ? or if you want to migrate your dev version to cloud for a beta ? or create a database cluster using multiple MySQL container nodes ? you don’t want to delete, spend time on migration or get stuck with a non scalable database and for this you use Docker volumes

You create a docker volume, using “docker volume create {$volume_name}” and mount it using -v option when you are spinning up your docker container using the “docker run” command

But you don’t want to do this every time on the command prompt. There should be a file where all setup steps go in and you just edit and run it for your container to come up. For this, we use “docker-compose“. Docker compose lets your compose your docker container configuration in a .yaml file and then spin up a container reading the specifications from the file.

For our exercise up till now, docker compose file looks like below:

 services:
  mysql:
    image: mysql/mysql-server:latest
    container_name: spikemysql
    ports:
      - 3307:3306
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: password
    volumes:
      - mysql-test-vol:/var/lib/mysql

volumes:
  mysql-test-vol:

I created a volume name “mysql-test-vol” using the “docker volume create”

Go to the directory where you have created the above yaml file and run

docker-compose -f “filename.yaml” up

And now you should have your container up and ready using the compose file.

You might to remove (docker rm) the previously created container spun up from the command line and create the users and tables again once docker-compose has spun up your new container

The final step : Testing your MySQL connection

I do this using python.

download the mysql connector using pip and type in below code to check the connectivity

 import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="tb",
  password="password",
  port=3307,
  database="spike"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM spike_band_song")
myresult = mycursor.fetchall()


for x in myresult:
  print(x)

sql = "INSERT INTO spike_band_song (band_name, song_name) VALUES (%s, %s)"
val=('AC/DC','Highrway To Hell')

mycursor.execute(sql,val)

mydb.commit()

You should be able to see new data from DB both on your py console as well as from Workbench.

Happy Coding !!