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

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.


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

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

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.

Prepare the mysql:8.0.34-debian Image
The Custom Config File on Ubuntu 22.10
- The Location and Content
- Permissions and Mounting
The Socket and the Database Directories
- MySQL Socket and Mounting the Socket Directory to Host Machine
- Mounting the Database Directory to Host Machine
Verify Custom Config Items, and Log Files Created
Allow Traffic Through MySQL port 3306
Test With a Remote Connection
Further Test Via Setting Up a Complete Database
- Database Creation and Population
- Remotely Create Stored Procedures on the Just Setup Database
Other Docker Posts Which I've Written

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

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

$ sudo docker images

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

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

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

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:

[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"

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.

For default_authentication_plugin, see caching_sha2_password Compatibility Issues and Solutions. For log_bin_trust_function_creators, see 25.7 Stored Program Binary Logging.
For log files, see 5.4.1 Selecting General Query Log and Slow Query Log Output Destinations. For the purpose of this post, I turn on a lot of logs. This would not be appropriate in a production environment. I'm aware of that.

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

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

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

$ sudo docker exec -it mysql-docker bash

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

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

Permissions should now be correct. To verify:

# ls -l

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#

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/

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

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

The mounting option is:

--mount type=bind,source=/var/run/mysqld,target=/var/run/mysqld/

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

That is:

-v /home/behai/Public/database/mysql:/var/lib/mysql

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

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

🚀 ❷ 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

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

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

$ 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

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:

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:

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

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

# mysql -uroot -ppcb.2176310315865259

We can query default_authentication_plugin’s value with:

mysql> show variables like 'default_authentication_plugin';

We should get:

+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.02 sec)

Then log_bin_trust_function_creators:

mysql> show variables like 'log_bin_trust_function_creators';

+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.01 sec)

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

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$

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

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

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

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"

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"

We can then list available databases with:

$ sudo docker exec mysql-docker /usr/bin/mysql -u root --password=pcb.2176310315865259 -e "show databases"

mysql: [Warning] Using a password on the command line interface can be insecure.
Database
employees
information_schema
mysql
performance_schema
sys

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

– 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"

– 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$

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 ;

● 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 ;

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:

Table of contents