How to run multiple mysql versions using docker - Magento 2

If you are working with multiple Magento 2 sites there is a high change that you have to work with different Magento versions and therefore also the requirements for database differ for each project. For example, you have one project where the database version is mysql5.7 while at the same time you have a project where the production database is running mysql8.0. Perhaps for some projects you need to use MariaDB. As a developer you need to be able to easily switch between version locally.

More often than not your OS will not support having multiple database engines installed on the server and you will have to fallback to compiling from source or using some type of workaround.

Luckily a great solution for this is to use Docker. Docker allows us to launch any version we wish in a docker container. We can then easily configure and allow connections to this container - essentially achieving the goal.

In this article we will explore how to use docker containers to run any database version and how to connect the container with your Magento 2 application. Lets get started.

Prerequisites

1. Docker

It is best to follow the official installation guide. It takes ~10 minutes to install the software on Ubuntu 20.

2. Docker compose

Docker compose allows us to define the services we need and set configuration that will automatically be executed when executing the file. You do not need to use docker compose however it is extremely convenient if you do. You will need to install it - follow the official guide.

3. Define docker services

Assuming you have now installed docker and docker compose. Lets define a docker service which will allow us to run mysql5.7 in a docker container.

version: '3.3'
services:
  db:
    image: mysql:5.7
    restart: always
    environment:
      MYSQL_DATABASE: 'db'
      MYSQL_USER: 'db_user'
      MYSQL_PASSWORD: 'db_password'
      MYSQL_ROOT_PASSWORD: 'db_password'
    ports:
      - '3506:3306'
    expose:
      - '3506'
    volumes:
      - mysql5.7:/var/lib/mysql
volumes:
  mysql5.7:

1. mysql5.7 official image will be pulled
2. set up the database user and password, and password for root user
3. expose port 3506 which we will use to connect to docker instance
4. map port 3506 to 3306 on docker instance
5. persist volume data on /var/lib/mysql

4. docker compose

docker-compose up -d 

-d option is used to run the container in background. If you do not use this option then the process will stay attached to the terminal session and once you close the terminal the process will stop. In addition logs and output is put to the terminal if you do not use the -d option - therefore it can be useful for real time debugging and seeing what type of action the container is having.

5. Find container name:

We will need to know the name of the container before we can use it in the following command. Running the below command will show you a list of containers - find the db container and take note of the container name - we will use this later as db_container_name.

docker ps

6. Create database

Next we need to create a database where we will store our Magento data. Take the configuration values you configured earlier, the container name and execute the below command.

 docker exec -i db_container_name mysql -u db_user -pdb_password -e "create database db_name"

7. Import database into your new container

Once the database is created we can import the data into our new database which is running in a docker container. To do this you will need to know the credentials, container name and you will need a database dump (you can get this using mysqldump command or exporting the database using any other software). Once you have all of that execute the below command.

docker exec -i db_container_name mysql -u db_user -pdb_password db_name < database.sqlSetup 
 

8. Magento env.php database configuration

'connection' => [
            'default' => [
                'host' => '127.0.0.1:3506',
                'dbname' => 'db_name',
                'username' => 'db_user',
                'password' => 'db_password',
                'model' => 'mysql4',
                'engine' => 'innodb',
                'initStatements' => 'SET NAMES utf8;',
                'active' => '1'
            ]
        ]

The important bit here is that we are using host 127.0.0.1 and connecting via the container external port 3506. Then just configured the credentials and you should be good to go.

That is it, if you followed everything correctly you should be running magento using mysql 5.7 without ever needing to install anything on your local machine. The same steps could be use to run any other database version, for example,5.6, 8.0 or mariadb.