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.
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
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
$ docker build -t my-mysql .
Now we have our MySQL build with our database and our user.
$ 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
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.