MySQL Setup Configuration

Database Container Launch

The container is started using the docker command with the appropriate settings:

docker run --name mysql-5.7.33 --volume /opt/mysql-5.7.33/dbdata:/var/lib/mysql --volume /opt/mysql-5.7.33/config:/etc/mysql -p 3310:3306 -p 33100:33060 -d mysql/mysql-server:5.7.33

Let's consider each of these parameters:

--name mysql-5.7.33 - container name. Consists of the server-dev prefix, database type mysql and version 5.7.33

--volume /opt/mysql-5.7.33/dbdata:/var/lib/mysql - container volume. You can read more about this on the corresponding docker manual page. But, at the moment, it is enough for us to know that the volume is used to save our data if the container is restarted or rebuilt. /opt/mysql-5.7.33/dbdata - the folder in the external system where our data will be stored. /var/lib/mysql - the folder inside the container, which we want to save.

--volume /opt/mysql-5.7.33/config:/etc/mysql - another container volumes for saving MySQL config files.

-p 3310:3306 - using this parameter, we set the port that will provide access to the container. 3310 - external port on which this container will be available.

-p 33100:33060 - port for X Protocol (mysqlx_port), supported by clients such as MySQL Shell, MySQL Connectors and MySQL Router, is calculated by multiplying the port used for classic MySQL protocol by 10. More details can be found at the link.

WARNING Before deploying a container to a specific port, you need to make sure that this port is free and not used by other containers. You can use docker ps -a to view all running and stopped containers.

3306 - the port on which the database is accessible inside the container. Thus, we forward requests from an external port to an open port inside the container.

-d - mean “detach”, we run container in background.

mysql/mysql-server:5.7.33 - indicate the MySQL image that exists on the docker hub with the corresponding version.

As a result of execution, we will see the following console picture:

Database Console Launch

After starting the container, we need to find out the password for the root. This can be done using the appropriate command:

docker logs mysql-5.7.33 | grep GENERATED

For Windows users:

docker logs mysql-5.7.33 | findstr GENERATED

Logging into the mysql console of a docker container is done using the command:

docker exec -it mysql-5.7.33 mysql -uroot -p{GENERATED_PASSWORD}

As a result, we will observe the following picture in the console:

Setting Up The Database and Access Mode

The database is configured using sequential execution of SQL queries.

Let's change the root password to our own (this is an optional step):

ALTER USER 'root'@'localhost' IDENTIFIED BY '{STRONG_PASSWORD}';

Let's create a database that will be used to store Jira's data:

CREATE DATABASE exportdb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

These database settings are recommended in the corresponding Jira manual.

Create a user for the database that will use the login we created:

GRANT SELECT, UPDATE, DROP, CREATE, INSERT, DELETE, ALTER on exportdb.* TO 'exportuser'@'%' IDENTIFIED BY '{STRONG_USER_PASSWORD}'; flush privileges;

{STRONG_USER_PASSWORD} - the password that we will later use to configure the Jira instance.

'exportuser'@'%' - at the moment, we only support working with users whose access is granted to any host (using the %).

flush privileges; - when we grant some privileges for a user, running the command flush privileges will reloads the grant tables in the mysql database enabling the changes to take effect without reloading or restarting mysql service.

As a result, we will see the following picture in the console:

You can exit the console with exit.

Let's check if we have configured our database correctly and try to connect to it.


Read our step-by-step guide on how to export Jira data to MySQL in our blog

https://www.alphaservesp.com/blog/connect-jira-to-mysql-with-sql-connector-for-jira-cloud-data-center