DEV Community

Cover image for Docker on Ubuntu 22.10: running mysql:8.0.34-debian with custom config, socket, database and log files reside on host machine.
Be Hai Nguyen
Be Hai Nguyen

Posted on

Docker on Ubuntu 22.10: running mysql:8.0.34-debian with custom config, socket, database and log files reside on host machine.

#docker #mysql #hy000 #ubuntu

We'll look at how to run the official mysql:8.0.34-debian image on Ubuntu 22.10, which we'll store most of the run-time files on our own Ubuntu 22.10 host machine, in locations of our own dictation. These include the custom config file, the database files, the MySQL socket files, and the log files. Finally, we verify that the setup works.

I've previously written about mysql:8.0.30-debian Docker Official Image on Windows 10 Pro:

  1. Docker on Windows 10: running mysql:8.0.30-debian with a custom config file.
  2. Docker on Windows 10: mysql:8.0.30-debian log files

Afterward, I'd also set it up to run on Ubuntu 22.10, but without log files written to the host machine. I've reinstalled Ubuntu 22.10 a fair while back, but only attempted to set up mysql:8.0.30-debian Docker image container last week. It took me about an hour to consolidate the instructions. I'm writing this post so that in the future, I've something to go back to should the need arise.

In this post, we are using a newer image mysql:8.0.34-debian.

Please note: docker commands in this post are run with the --rm flag, that means when we stop the docker container, we expect it to be removed as well.

Also, the post's longer than I've anticipated, but after the final draft's been completed, I have re-exercised all the steps twice, on average, it takes around 20 (twenty) minutes to complete all the steps.

Table of contents

Prepare the mysql:8.0.34-debian Image

Pulling the Docker image from the registry with:

$ sudo docker image pull mysql:8.0.34-debian
Enter fullscreen mode Exit fullscreen mode

After finishing pulling, we can verify that the image has been loaded:

$ sudo docker images
Enter fullscreen mode Exit fullscreen mode

mysql:8.0.34-debian should be in the available image list:

REPOSITORY         TAG             IMAGE ID       CREATED        SIZE
mysql              8.0.34-debian   beb1bec24656   12 days ago    601MB
bh-aust-postcode   latest          90d2986553e0   2 months ago   980MB
postgres           latest          a26eb6069868   9 months ago   379MB
Enter fullscreen mode Exit fullscreen mode

We can save the image to disk with:

$ sudo docker save mysql:8.0.34-debian --output /home/behai/Public/mysql-8-0-34-debian.tar
Enter fullscreen mode Exit fullscreen mode

Later on, if for some reason, we need the image again, we don't need to download it, we can load it up with:

$ sudo docker load --input /home/behai/Public/mysql-8-0-34-debian.tar
Enter fullscreen mode Exit fullscreen mode

The Custom Config File on Ubuntu 22.10

The Location and Content

The custom config file is /home/behai/Public/database/mysql-config/mysql-docker.cnf. mysql-docker.cnf is the only file I have under /home/behai/Public/database/mysql-config/.

Please note, the above directory is just my personal preference. Manually create the directories and create mysql-docker.cnf using either nano, vi or vim, etc.

Content of /home/behai/Public/database/mysql-config/mysql-docker.cnf:
Enter fullscreen mode Exit fullscreen mode 
[mysqld]
default_authentication_plugin=mysql_native_password
log_bin_trust_function_creators=1

# Below contents are updated contents.

# General and slow logging.
log_output=FILE

general_log=1
general_log_file="/var/lib/mysql/general_log.log"

slow_query_log=1
slow_query_log_file="/var/lib/mysql/slow_query.log"
long_query_time=10
log_queries_not_using_indexes=1

# Error Logging.
log_error="/var/lib/mysql/error.err"
Enter fullscreen mode Exit fullscreen mode

Please note that /var/lib/mysql/ is the database directory, which later on, we'll mount to the directory /home/behai/Public/database/mysql on the host Ubuntu 22.10 machine. That is, all database files and log files will reside in the directory /home/behai/Public/database/mysql on the host machine.

Permissions and Mounting

The required permissions are: owner has read and write; groups and others have only read. Our /home/behai/Public/database/mysql-config/mysql-docker.cnf must have the same permissions.

The permissions must be set from a running container. We need to mount it so that docker can recognise this external custom config file. We need only the directory, the mounting option is:

--mount type=bind,source=/home/behai/Public/database/mysql-config,target=/etc/mysql/conf.d
Enter fullscreen mode Exit fullscreen mode

The command to run:

$ sudo docker run -d -it --rm --name mysql-docker --mount type=bind,source=/home/behai/Public/database/mysql-config,target=/etc/mysql/conf.d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pcb.2176310315865259 mysql:8.0.34-debian
Enter fullscreen mode Exit fullscreen mode

Now, run the container in the interactive mode with the Bash process to change config file permissions:

$ sudo docker exec -it mysql-docker bash
Enter fullscreen mode Exit fullscreen mode

Once in the interactive Bash mode, verify that we’re looking at the Ubuntu 22.10 directory, which has only this config file, also note its permissions:

# ls -l /etc/mysql/conf.d
Enter fullscreen mode Exit fullscreen mode

Change permissions to match the container's custom config file. That is, owner has read and write; groups and others have only read:

# cd /etc/mysql/conf.d/
# chmod u+rw-x mysql-docker.cnf
# chmod g+r-wx mysql-docker.cnf
# chmod o+r-wx mysql-docker.cnf
Enter fullscreen mode Exit fullscreen mode

Permissions should now be correct. To verify:

# ls -l
Enter fullscreen mode Exit fullscreen mode 
root@1e7273c76a2a:/etc/mysql/conf.d# ls -l
total 4
-rw-r--r-- 1 1000 1000 425 Sep 18 12:06 mysql-docker.cnf
root@1e7273c76a2a:/etc/mysql/conf.d#
Enter fullscreen mode Exit fullscreen mode

The Socket and the Database Directories

In this section, we'll briefly discuss why we need MySQL socket files on the host machine, how to mount this directory. And then we'll discuss mounting the database directory.

Again, please note that all directories created are my own personal preference. You can create them wherever based on your own liking.

MySQL Socket and Mounting the Socket Directory to Host Machine

I've carried out this set up process over several iterations. The second time I set it up with mysql:8.0.30-debian is (yes, present tense, since I'm still writing 😂 this post) smooth sailing. When I try mysql:8.0.34-debian, approximately at this point, I could not connect to the container. The error message I get is:


MySQL server is running but I cannot connect : ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

I spend sometimes troubleshoot it, I understand what the problem is, but get nowhere fixing it. I switch back to mysql:8.0.30-debian -- and the same error persists! This does not happen during the second time as mentioned above.

The documentation on the official page does not mention about MySQL socket, but I thought, might be the socket files should be on the host machine also, I try that. It seems to work. I am not at all certain if this is the cause of the problem, or that it is the correct solution. So, please keep that in mind.

-- I would like to point out that, after completing the final draft of this post, I carry all the steps twice, and everything still works.

Run the container in the interactive Bash mode, then look in /var/run/mysqld/ directory:

root@df87897c3326:/etc/mysql/conf.d# ls -l /var/run/mysqld/
Enter fullscreen mode Exit fullscreen mode

These are MySQL socket files:

total 12
-rw-r----- 1 mysql mysql 2 Sep 20 12:20 mysqld.pid
srwxrwxrwx 1 mysql mysql 0 Sep 20 12:20 mysqld.sock
-rw------- 1 mysql mysql 2 Sep 20 12:20 mysqld.sock.lock
srwxrwxrwx 1 mysql mysql 0 Sep 20 12:20 mysqlx.sock
-rw------- 1 mysql mysql 2 Sep 20 12:20 mysqlx.sock.lock
Enter fullscreen mode Exit fullscreen mode

I choose to bind mount container directory /var/run/mysqld/ to the same directory on the Ubuntu 22.10 host machine, it does not exist, we need to create it:

$ sudo mkdir /var/run/mysqld
Enter fullscreen mode Exit fullscreen mode

The mounting option is:

--mount type=bind,source=/var/run/mysqld,target=/var/run/mysqld/
Enter fullscreen mode Exit fullscreen mode

Mounting the Database Directory to Host Machine

For the purpose of this post, I'm assuming that the database directory on the host machine does not yet exist. Create the database directory: /home/behai/Public/database/mysql.

Based on the official documentation Where to Store Data, the volume mount option for the database directory takes the form:

-v /my/own/datadir:/var/lib/mysql
Enter fullscreen mode Exit fullscreen mode

That is:

-v /home/behai/Public/database/mysql:/var/lib/mysql
Enter fullscreen mode Exit fullscreen mode

Please note, as an alternative, we can also use bind mount for the database directory:

--mount type=bind,source=/home/behai/Public/database/mysql,target=/var/lib/mysql
Enter fullscreen mode Exit fullscreen mode

So we have two possible full final commands:

🚀 ❶ The first full, final command:

$ sudo docker run -d -it --rm --name mysql-docker
    --mount type=bind,source=/home/behai/Public/database/mysql-config,target=/etc/mysql/conf.d
    -v /home/behai/Public/database/mysql:/var/lib/mysql
    --mount type=bind,source=/var/run/mysqld,target=/var/run/mysqld/
    -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pcb.2176310315865259 mysql:8.0.34-debian
Enter fullscreen mode Exit fullscreen mode

🚀 ❷ The second full, final command:

$ sudo docker run -d -it --rm --name mysql-docker
    --mount type=bind,source=/home/behai/Public/database/mysql-config,target=/etc/mysql/conf.d
    --mount type=bind,source=/home/behai/Public/database/mysql,target=/var/lib/mysql
    --mount type=bind,source=/var/run/mysqld,target=/var/run/mysqld/
    -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pcb.2176310315865259 mysql:8.0.34-debian
Enter fullscreen mode Exit fullscreen mode

Verify Custom Config Items, and Log Files Created

We run the mysql:8.0.34-debian image with one of the final commands above and verify that the items specified in the custom config file have taken effect: ⓵ default_authentication_plugin and log_bin_trust_function_creators have been set; ⓶ MySQL socket files, as well as ⓷ all database files and log files get created in the respective directories on the host machine.

To recap, at this point, both /var/run/mysqld and /home/behai/Public/database/mysql on the host machine are empty.

Stop the container with (note, mysql-docker container also gets removed):

$ sudo docker stop mysql-docker
Enter fullscreen mode Exit fullscreen mode

Re-run the image with either one of the above final commands:

$ sudo docker run -d -it --rm --name mysql-docker --mount type=bind,source=/home/behai/Public/database/mysql-config,target=/etc/mysql/conf.d -v /home/behai/Public/database/mysql:/var/lib/mysql --mount type=bind,source=/var/run/mysqld,target=/var/run/mysqld/ -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pcb.2176310315865259 mysql:8.0.34-debian
Enter fullscreen mode Exit fullscreen mode 
$ sudo docker run -d -it --rm --name mysql-docker --mount type=bind,source=/home/behai/Public/database/mysql-config,target=/etc/mysql/conf.d --mount type=bind,source=/home/behai/Public/database/mysql,target=/var/lib/mysql --mount type=bind,source=/var/run/mysqld,target=/var/run/mysqld/ -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pcb.2176310315865259 mysql:8.0.34-debian
Enter fullscreen mode Exit fullscreen mode

Verify MySQL Socket Files

After running one of the final commands above, after a little while, MySQL socket files should be created in /var/run/mysqld. Please see the screenshot below:

084-01-1.png

Verify Database Files and Log Files

Similar to MySQL socket files, after running one of the final commands above, initial database and log files are created in /home/behai/Public/database/mysql. Please see the screenshot below:

084-02.png

Verify default_authentication_plugin and log_bin_trust_function_creators

Run the container in the interactive mode with the Bash process, with:

$ sudo docker exec -it mysql-docker bash
Enter fullscreen mode Exit fullscreen mode

Once in the Bash shell, run mysql command line with:

# mysql -uroot -ppcb.2176310315865259
Enter fullscreen mode Exit fullscreen mode

We can query default_authentication_plugin's value with:

mysql> show variables like 'default_authentication_plugin';
Enter fullscreen mode Exit fullscreen mode

We should get:

+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

Then log_bin_trust_function_creators:

mysql> show variables like 'log_bin_trust_function_creators';
Enter fullscreen mode Exit fullscreen mode 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.01 sec)
Enter fullscreen mode Exit fullscreen mode

Checking Out general_log.log File

Up to this point, we've run two (queries), they should be in the general_log.log file:

$ sudo cat /home/behai/Public/database/mysql/general_log.log
Enter fullscreen mode Exit fullscreen mode

And they are:

...
2023-09-21T03:12:01.424052Z         8 Connect   root@localhost on  using Socket
2023-09-21T03:12:01.424277Z         8 Query     select @@version_comment limit 1
2023-09-21T03:12:10.402121Z         8 Query     show variables like 'default_authentication_plugin'
2023-09-21T03:12:27.396645Z         8 Query     show variables like 'log_bin_trust_function_creators'
2023-09-21T03:12:48.420061Z         8 Quit
behai@hp-pavilion-15:~/Public/database$
Enter fullscreen mode Exit fullscreen mode

Allow Traffic Through MySQL port 3306

We're using the default MySQL port 3306. Allow traffic through this port with:

$ sudo ufw allow from any to any port 3306 proto tcp
Enter fullscreen mode Exit fullscreen mode

The set up is basically complete, mysql:8.0.34-debian Docker container is ready for connection. Let's test it.

Test With a Remote Connection

Remotely connect to the container from Windows 10 Pro using MySQL Workbench 8.0. The connection information is as follow:

Host: 192.168.0.16 -- Ubuntu 22.10 IP address on local network.
Port: 3306
Username: root
Password: pcb.2176310315865259
Enter fullscreen mode Exit fullscreen mode

MySQL Workbench version is 8.0.30 build 2054668 C4 (64 bits) Community, older versions might not work.

I'm able to connect with no problem. It's an empty server, the only item appears under SCHEMAS is sys. Let's set up a database.

Further Test Via Setting Up a Complete Database

We will use the employees Oracle Corporation MySQL test data database.

Database Creation and Population

On Windows 10 Pro, back up the employees database with:

docker exec mysql-docker /usr/bin/mysqldump -u root --password=pcb.2176310315865259 employees > E:\employees.sql
Enter fullscreen mode Exit fullscreen mode

E:\employees.sql is around 170 MB, and it does not have the use employees statement in the content. Copy E:\employees.sql to Ubuntu's /home/behai/Public/ directory. I.e. /home/behai/Public/employees.sql.

Next, restore /home/behai/Public/employees.sql on Ubuntu 22.10 using the newly set up container for mysql:8.0.34-debian image.

The following commands issue warning about password being insecure. For the purpose of this post, we can ignore this warning.

First, drop any existing employees database. We know there is not any, but this is a step I always follow:

$ sudo docker exec mysql-docker /usr/bin/mysql -u root --password=pcb.2176310315865259 -e "drop database if exists employees"
Enter fullscreen mode Exit fullscreen mode

Next, create the employees database:

$ sudo docker exec mysql-docker /usr/bin/mysql -u root --password=pcb.2176310315865259 -e "create database employees default CHARSET=utf8mb4 collate=utf8mb4_unicode_ci"
Enter fullscreen mode Exit fullscreen mode

We can then list available databases with:

$ sudo docker exec mysql-docker /usr/bin/mysql -u root --password=pcb.2176310315865259 -e "show databases"
Enter fullscreen mode Exit fullscreen mode 
mysql: [Warning] Using a password on the command line interface can be insecure.
Database
employees
information_schema
mysql
performance_schema
sys
Enter fullscreen mode Exit fullscreen mode

We are now ready to load up the /home/behai/Public/employees.sql dump file with:

$ sudo docker exec -i mysql-docker /usr/bin/mysql -u root --password=pcb.2176310315865259 --database employees < /home/behai/Public/employees.sql
Enter fullscreen mode Exit fullscreen mode

-- This can take a while, depending on the machine.

We can query the employees database with:

$ sudo docker exec mysql-docker /usr/bin/mysql -u root --password=pcb.2176310315865259 --database employees -e "select * from departments"
Enter fullscreen mode Exit fullscreen mode

-- The departments table should have data.

The log files should now have gotten bigger. We can verify with:

behai@hp-pavilion-15:~/Public/database$ cd mysql
behai@hp-pavilion-15:~/Public/database/mysql$ pwd
/home/behai/Public/database/mysql
behai@hp-pavilion-15:~/Public/database/mysql$ ls -l general_log.log slow_query.log error.err
-rw-r----- 1 999 gamemode      6606 Sep 21 22:17 error.err
-rw-r----- 1 999 gamemode 181679518 Sep 21 22:17 general_log.log
-rw-r----- 1 999 gamemode       537 Sep 21 22:04 slow_query.log
behai@hp-pavilion-15:~/Public/database/mysql$
Enter fullscreen mode Exit fullscreen mode

general_log.log is 181,679,518 bytes. This is to be expected, considering that /home/behai/Public/employees.sql is around 170 MB. To delete these log files, first stop the container, delete the log file(s), then re-run the image again.

Remotely Create Stored Procedures on the Just Setup Database

Connect to the container from Windows 10 Pro using MySQL Workbench (as above). The employees database should be there. Select it, and create the following two (2) stored procedures:

● Stored procedure get_employees:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `get_employees`( pmLastName varchar(16), pmFirstName varchar(14) )
    READS SQL DATA
begin
  select * from employees e where (e.last_name like pmLastName)
    and (e.first_name like pmFirstName) order by e.emp_no;
end$$
DELIMITER ;
Enter fullscreen mode Exit fullscreen mode

● Stored procedure DemoStoredProc1:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `DemoStoredProc1`( pm_dept_no varchar(4) )
    READS SQL DATA
begin
  select * from departments where dept_no = pm_dept_no;
  select * from dept_manager where dept_no = pm_dept_no;
end$$
DELIMITER ;
Enter fullscreen mode Exit fullscreen mode

My naming convention is not consistent. I'm aware of this. I wrote these two (2) stored procedures times apart for different posts, so I just kept the original names.

Since we connect using user root, they should be created successfully.

With that, we conclude this post. I do apologise that this post is a bit long. I did not expect it to be this long, the case of the devil is in the details, I think. I do hope you find it useful. Thank you for reading and stay safe as always.

✿✿✿

Feature image source:

Other Docker Posts Which I've Written

  1. Synology DS218: sudo password and unsupported Docker problems update... -- I have been updating the DSM without running sudo or docker. I have just tried both recently, both failed. I'm describing how I've managed to fix these two problems.
  2. Python, Flask: Railway.app deployment and Railway's Nixpacks Docker image build tool. -- I've successfully deployed my Australian postcodes API project to https://railway.app. I did have some problem during deployment. I'm describing how I've addressed this problem. In the process, we're also covering the following: ⓵ running Railway's own Nixpacks Docker build tool locally on Ubuntu 22.10. ⓶ Override the Nixpacks-built Docker image's CMD: we look at three (3) ways to run the Flask CLI command venv/bin/flask update-postcode, and similarly, we look at how to override the start command gunicorn wsgi:app --preload specified in the Nixpacks required Procfile.
  3. Using PostgreSQL Official Docker image on Windows 10 and Ubuntu 22.10 kinetic. -- Discussing a basic set up process to use the PostgreSQL Official Docker image on Windows 10 Pro, and Ubuntu 22.10 kinetic running on an older HP laptop. Then backup a PostgreSQL database on Windows 10 Pro machine, and restore this backup database to the newly set up Docker PostgreSQL Server 15.1 on the Ubuntu 22.10 machine.
  4. Docker Compose: how to wait for the MySQL server container to be ready? -- Waiting for a database server to be ready before starting our own application, such as a middle-tier server, is a familiar issue. Docker Compose is no exception. Our own application container must also wait for their own database server container ready to accept requests before sending requests over. I've tried two ( 2 ) “wait for” tools which are officially recommended by Docker. I'm discussing my attempts in this post, and describing some of the pending issues I still have.
  5. pgloader Docker: migrating from Docker & localhost MySQL to localhost PostgreSQL. -- Using the latest dimitri/pgloader Docker image build, I've migrated a Docker MySQL server 8.0.30 database, and a locally installed MySQL server 5.5 database to a locally installed PostgreSQL server 14.3 databases. I am discussing how I did it in this post.
  6. Docker on Windows 10: mysql:8.0.30-debian log files -- Running the Docker Official Image mysql:8.0.30-debian on my Windows 10 Pro host machine, I want to log all queries, slow queries and errors to files on the host machine. In this article, we're discussing how to go about achieving this.
  7. Docker on Windows 10: running mysql:8.0.30-debian with a custom config file. -- Steps required to run the official mysql:8.0.30-debian image on Windows 10 with custom config file E:\mysql-config\mysql-docker.cnf.
  8. Python: Docker volumes -- where is my SQLite database file? -- The Python application in a Docker image writes some data to a SQLite database. Stop the container, and re-run again, the data are no longer there! A volume must be specified when running an image to persist the data. But where is the SQLite database file, in both Windows 10 and Linux? We're discussing volumes and where volumes are on disks for both operating systems.
  9. Python: Docker image build -- save to and load from *.tar files. -- We can save Docker images to local *.tar files, and later load and run those Docker images from local *.tar files. I'm documenting my learning experimentations in this post.
  10. Python: Docker image build -- “the Werkzeug” problem 🤖! -- I've experienced Docker image build installed a different version of the Werkzeug dependency package than the development editable install process. And this caused the Python project in the Docker image failed to run. Development editable install means running the “pip3 install -e .” command within an active virtual environment. I'm describing the problem and how to address it in this post.
  11. Python: Docker image build -- install required packages via requirements.txt vs editable install. -- Install via requirements.txt means using this image build step command “RUN pip3 install -r requirements.txt”. Editable install means using the “RUN pip3 install -e .” command. I've experienced that install via requirements.txt resulted in images that do not run, whereas using editable install resulted in images that do work as expected. I'm presenting my findings in this post.
  12. Synology DS218: unsupported Docker installation and usage... -- Synology does not have Docker support for AArch64 NAS models. DS218 is an AArch64 NAS model. In this post, we're looking at how to install Docker for unsupported Synology DS218, and we're also conducting tests to prove that the installation works.

Top comments (0)

Read next

kalunda profile image

How to create a python virtual environment on Ubuntu 22.04.

Agnes -

wandealves profile image

Desvendando o Poder do Docker, Nginx e dotnet 8: Configurando um Proxy Reverso com Facilidade

Wanderson Alves Rodrigues -

ietxaniz profile image

Practical PostgreSQL Logical Replication: Setting Up an Experimentation Environment Using Docker

Iñigo Etxaniz -

hasanelsherbiny profile image

Docker With ASP.net

Hasan Elsherbiny -