Running MySQL database server with Docker

banner made in canva.

Recently I have started upgrading self with some docker skills. So, here is how, you can run and use a MySQL server on your computer.

The first step is to go ahead and create a folder. I tend to create a separate folder for every new container I wish to run. it seems to work better for me.

Then, create a file called, docker-compose.yml. The file name is important for the command to work as per my steps.

version: "3.8"

services:
  mysql:
    image: mysql:latest
    container_name: mysql-container
    environment:
      MYSQL_ROOT_PASSWORD: my-secret-pw
      # uncomment below if you want to automatically create and select a database
      # MYSQL_DATABASE: my_database
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql

volumes:
  mysql-data:

What is happening here? we are setting up a service with the name, ‘mysql’. the image ‘mysql:latest’ will be pulled from the Docker container registry.

Now, time for some additional commands and settings. we need to supply the password. then, of course, the default ports. finally, I want the mysql data to be preserved between different container runs.

Now, I simply need to run the following command in the terminal, and it will get the container pulled and up and running on the local machine.

docker-compose up -d

and this is how it looks.

screenshot from my PC.

Now, you know, I thought, I could use the mysql workbench. but, it kept crashing on me. So, I am not able to use that GUI.

So, we do it old school, from the terminal.

docker exec -it mysql-container mysql -u root -p

and, now, you should see something like this.

PS D:\ToDeleteOctober202024\mysqllatest> docker exec -it mysql-container mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE my_database;
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_database        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> 
mysql> USE my_database;
Database changed
mysql> 

If you want to try some queries, here you go.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL
);

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Amit', 'Sharma', 'amit.sharma@example.com', '2023-01-15'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-02-20'),
('Rahul', 'Verma', 'rahul.verma@example.com', '2023-03-25'),
('Anjali', 'Patel', 'anjali.patel@example.com', '2023-04-10'),
('Vikram', 'Gupta', 'vikram.gupta@example.com', '2023-05-05');

SELECT * FROM employees;

SELECT first_name, last_name, email FROM employees;

SELECT * FROM employees WHERE hire_date > '2023-01-01';

SELECT * FROM employees ORDER BY last_name ASC;

SELECT * FROM employees LIMIT 5;

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Ravi', 'Kumar', 'ravi.kumar@example.com', '2023-06-15');


UPDATE employees
SET email = 'ravi.kumar123@example.com'
WHERE first_name = 'Ravi' AND last_name = 'Kumar';

DELETE FROM employees
WHERE first_name = 'Ravi' AND last_name = 'Kumar';

Okay, so, that is good. the full code is available on my github.

Now, let’s talk about this issue.

command: — default-authentication-plugin=mysql_native_password’

Or another related issue, you might get if you are working on a node js project.

sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client'

I am not a mysql expert or even a database expert. I just wish to run the database so I can build some simple node js express js backend api. my guess is, this is some kind of an authentication related breaking change introduced in the ‘latest’ mysql.

So, one possible solution is to use the older mysql. So, now, your docker compose file will be like this.

version: "3.8"

services:
  mysql:
    image: mysql:8.0.19
    container_name: mysql-container
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: my-secret-pw
      # uncomment below if you want to automatically create and select a database
      # MYSQL_DATABASE: my_database
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql

volumes:
  mysql-data:

So, here, focus on this line.

image: mysql:8.0.19

and this line.

command: --default-authentication-plugin=mysql_native_password

These two things, solve the authentication errors you might get when you try to connect to the mysql database from your node js app.

As usual, full code is available on my GitHub.

That’s all there is to it.

I work as a coding tutor. You can hire me on Upwork, Fiverr and Codementor. You can also book a session on calendly, and visit my website. Also, video tutorials on my YouTube Channel.