How to install MySQL on Docker

MySQL is one of the most popular SQL Databases and maybe the most used database on the web. Sometimes, we need to create a test environment or maybe we want to create our webpage on our own cloud server. One of the best ways to achieve this is using Docker containers. Today we will learn how to install MySQL server on a Docker Container.

Pre-requisites

If you are using windows, you should have Docker and WSL installed on the machine you are going to make the implementation.

We have already written about this in a previous post. I recommend you to read it and install your environment in the platform you would like to use.

Installing MySQL image

First of all, we are going to create a file called deploy_mysql.yml.

The file should be allocated in its own folder and contain the next code:

version: '3.8'
services:
  db:
    image: mysql:8.0
    cap_add:
      - SYS_NICE
    restart: always
    environment:
      - MYSQL_DATABASE=main_database
      - MYSQL_ROOT_PASSWORD=[YOUR PASSWORD]
    ports:
      - '3306:3306'
    volumes:
      - db:/var/lib/mysql
      - ./db/init.sql:/docker-entrypoint-initdb.d/init.sql
    container_name: mysql-server
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
volumes:
  db:
    driver: local

Understanding our docker-compose file

Let’s analyze line per line what we are doing here (The tabs are very important for Docker-Compose):

Docker-compose versioning

version: ‘3.8’: First of all we are declaring the docker-compose file version we are going to use.

Docker-compose services

services: We are declaring what services will the container have

db: Our container will contain a database
image: The image we are going to use for our database

cap_add: We are going to add the next container capabilities
SYS_NICE: Grants the container the CAP_SYS_NICE capability, which allows the container to raise process nice values, set real-time scheduling policies, set CPU affinity, and other operations.
restart: always: Will restart our database and container every time we execute the Docker Compose.
environment: From here on, we are going to declare predefined variables for our MySQL environment.
– MYSQL_DATABASE=main_database: We are declaring our container will have a database at start called main_database
– MYSQL_ROOT_PASSWORD=[YOUR PASSWORD]: We declare our root password.
ports: What will be the ports that our database will use?
– ‘3306:3306’: We are defining here that our database will be externally (First port value) in 3306 and internally in 3306. If you want to change the listening port of your implementation, you could use, for instance, 3307:3306 and your database will listen in the 3307 port.
volumes: Where our DB will be stored
– db:/var/lib/mysql: Where our DB storage will be inside our container.
– ./db/init.sql:/docker-entrypoint-initdb.d/init.sql: This will create a subfolder and a file /db/init.sql in our folder (Outside the environment) that will be on the root of our container (We can modify this file and it will be modified in our container)
container_name: mysql-server: The name we will put to the container.
adminer: Administration console for our database
image: adminer: Will use the image called adminer
restart: always: As stated before, will restart our implementation every time we execute the Docker Compose, in this case for the adminer.
ports: Ports where the adminer will listen
– 8080:8080: Same as 3306:3306 but for adminer now

Docker-compose volumes

volumes: Volume where the information will be stored. For more information about this here is some documentation.
db: Volume for the db
driver: local: This means “inside the container”

Starting our container with Docker-Compose

That’s it! We are now ready to run our docker container with docker-compose.

Just start your favourite console, navigate to the folder where the YML is stored and run:

docker-compose -f deploy_mysql.yml -d up

This will execute our file in docker-compose and -d means it will be in detach mode (It will run on background)

You have now your docker container up and running!

This implementation is a ready-to-use instance for docker, however, there are other ways to install our MySQL on Docker.

Other ways to install our container

Let’s start by creating a bridged network

docker network create my-network-1

Now let’s pull the image

docker pull mysql/mysql-server:latest

Now we are going to run our container with this pulled image

docker run --network my-network-1 --publish 3306:3306 --name=mysql-server -d mysql/mysql-server:latest

Once the image is ready, we can see the logs, and we need to find this line:

docker logs mysql-server
GENERATED ROOT PASSWORD: Axegh3kAJyDLa1NRuBemecis&EShOs1sabr

Connecting to our instance

Now we are going to connect to our instance and change the root password

docker exec -it mysql-server mysql -uroot -p
# This will ask you for the password that you get from the log file.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '[YOUR_NEW_PASSWORD]';

And you are done! You can now connect to your docker new instance!

Upgrading your MySQL docker instance

If you need to upgrade your SQL docker instance you have to run this little command (With the docker container running).

docker exec -it mysql-server mysql_upgrade -uroot -p

After doing this you should restart your container

docker restart mysql-server

Starting and Stopping MySQL docker instance

To start your MySQL docker instance:

docker start mysql-server

To stop your MySQL docker instance:

docker stop mysql-server

Deleting your docker instance

You can either delete your instance from the Docker Desktop GUI after you stop it or by the following command

docker rm mysql-server

Conclusion

Installing a MySQL instance for development environments or dockerized servers is a beautiful way to have your data organized without the need of installing a VM that will require more resources and will bring you more problems.

If you need to do some troubleshooting, here are some MySQL docs that will help you.

See you at the next one!

Share on Facebook