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:
- Docker on Windows 10: running mysql:8.0.30-debian with a custom config file.
- 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 - The Custom Config File on Ubuntu 22.10
- The Socket and the Database Directories
- 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
- 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. Forlog_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:
- https://www.omgubuntu.co.uk/2022/09/ubuntu-2210-kinetic-kudu-default-wallpaper
- https://www.stickpng.com/img/icons-logos-emojis/tech-companies/jenkins-logo-landscape
- https://www.freepnglogos.com/uploads/logo-mysql-png/logo-mysql-mysql-logo-png-images-are-download-crazypng-21.png
Other Docker Posts Which I've Written
-
Synology DS218: sudo password and unsupported Docker problems update... --
I have been updating the DSM without running
sudo
ordocker
. I have just tried both recently, both failed. I'm describing how I've managed to fix these two problems. -
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 commandvenv/bin/flask update-postcode
, and similarly, we look at how to override the start commandgunicorn wsgi:app --preload
specified in the Nixpacks required Procfile. - 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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)