Build a Docker Mysql 8.0.28 pre-configured for node

by

In this “tutorial” I will explain how to fix some issues you can face trying to connect “node” to a MySQL 8.0.28 docker container.

Using composer is faster and better, how to? click here

Create a customized MySQL image with users tables and data (no composer)

Note: This image will have users and grand inside, should be not shared in the cloud nor contain sensible data but only used for test in a development environment

We are going to create a customize MySQL image that contains pre-installed database/table/users

Creating SQL scripts

Inside the main folder, let’s make a new folder called dump where we put the SQL statements that must execute in the building process.
Inside the folder, we can put any .SQL file we want, remember those files will be executed in alphabetical order.
table.sql

CREATE TABLE testable (
first varchar(250),
second  varchar(25),
);

INSERT INTO testable (first , second) 
VALUES ('primo', 'secondo')

Now, we may face some trouble connecting node using a root user in MySQL 8.0.28.
The root user is saved with the plugin caching_sha2_password instead of the classic mysql_native_password and node does not support it yet.

So we have to make a second file called users.sql

CREATE USER 'newadmin'@'localhost' IDENTIFIED BY 'testpassword';
ALTER USER 'newadmin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newadmin';
RENAME USER 'newadmin'@'localhost' TO 'newadmin'@'%';
GRANT ALL PRIVILEGES ON testdb.* TO 'newadmin'@'%';

We created a user called newadmin and password testpassword
We changed the plugin to mysql_native_password for node connection
We change localhost to % host (as the docker will assign an IP)
We grant newadmin (%) all privileges to the db testdb

Building the image

Dockerfile

FROM mysql
RUN sed -i '/skip-name-resolve/d' /etc/mysql/conf.d/docker.cnf
ENV MYSQL_DATABASE=fjitest
#COPY my.cnf /etc/mysql/my.cnf
COPY ./dump/ /docker-entrypoint-initdb.d/

RUN sed -i ‘/skip-name-resolve/d’ /etc/mysql/conf.d/docker.cnf
Mysql 8.0.28 has the skip-name-resolve set on TRUE by default.
With skip-name-resolve on TRUE the command GRANT ALL PRIVILEGES will not work.
My idea was to add a custom my.cnf with the skip-name-resolve = FALSE but is not working so I forced it using the command RUN sed -i ‘/skip-name-resolve/d’ /etc/mysql/conf.d/docker.cnf

Building

$ docker build -t my-mysql .

Now we have our MySQL build with our database and our user.

The container

$ docker run –rm -d -p 3306:3306 –name my-mysql -e MYSQL_ROOT_PASSWORD=mypassword my-mysql

Now we should be able to connect with the node mysql pool to the testdb on 127.0,0,1 on the port 3306 using the newadmin user.
Workbench is compatible with the caching_sha2_password so you should be able to connect it with root.

Note: At this point every time the container is deleted ( I add the option –rm so every time is stopped) the new data will be deleted. If you want to add newer data to the image, you have to update the SQL files and build the image again

Some useful commands

mysql > SELECT user FROM mysql.user; //show all users
docker exec -it my-mysql bash //connecting to mysql from bash
mysql -uroot -p //mysql login as root
docker run -it –rm my-mysql –verbose –help <– create a temp container that shows the mysql variables
docker cp my-mysql:/etc/mysql/my.cnf . <– copy the my.cnf file in the container to the host

Better and faster way, use composer

docker-composer.yaml

database:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: mypassword
      MYSQL_DATABASE: dbtest
    restart: always  
    ports: 
      - 3306:3306
    volumes: 
      - ./dump:/docker-entrypoint-initdb.d/
#to connect on phpmyadmin in ther server put database instead the localhost/127.0.0.1
  phpmyadmin:
    image: phpmyadmin
    restart: always
    ports:
      - 8888:80
    environment:
      PMA_ARBITRARY: 1

docker-composer up <– will create 2 container (one for phpmyadmin)
./dump:/docker-entrypoint-initdb.d/ <– in the dump folder need to be the SQL file with the table data and the users, those will be added to the container. If, for some strange reason you need to have this in a custom image you can build in the composer.
In the following compose file the image is build reading the docker

  primodb:
    build: 
      context: .
      dockerfile: dockerfile      
    environment:
      MYSQL_ROOT_PASSWORD: mypassword
    restart: always
    ports: 
      - 3306:3306

Questions? Suggestions? Please leave a comment below.

Leave a comment