Posted on Sep 22 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: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.Pulling the Docker image from the registry with:After finishing pulling, we can verify that the image has been loaded:mysql:8.0.34-debian should be in the available image list:We can save the image to disk with:Later on, if for some reason, we need the image again, we don't need to download it, we can load it up with: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.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.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:The command to run:Now, run the container in the interactive mode with the Bash process to change config file permissions: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:Change permissions to match the container's custom config file. That is, owner has read and write; groups and others have only read:Permissions should now be correct. To verify: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.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:These are MySQL socket files: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:The mounting option is: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:That is:Please note, as an alternative, we can also use bind mount for the database directory:So we have two possible full final commands:🚀 ❶ The first full, final command:🚀 ❷ The second full, final command: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):Re-run the image with either one of the above final commands: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: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:Run the container in the interactive mode with the Bash process, with:Once in the Bash shell, run mysql command line with:We can query default_authentication_plugin's value with:We should get:Then log_bin_trust_function_creators:Up to this point, we've run two (queries), they should be in the general_log.log file:And they are:We're using the default MySQL port 3306. Allow traffic through this port with:The set up is basically complete, mysql:8.0.34-debian Docker container is ready for connection. Let's test it.Remotely connect to the container from Windows 10 Pro using MySQL Workbench 8.0. The connection information is as follow: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.We will use the employees Oracle Corporation MySQL test data database.On Windows 10 Pro, back up the employees database with: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:Next, create the employees database:We can then list available databases with:We are now ready to load up the /home/behai/Public/employees.sql dump file with:-- This can take a while, depending on the machine.We can query the employees database with:-- The departments table should have data.The log files should now have gotten bigger. We can verify with: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.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:● Stored procedure DemoStoredProc1: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:Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well Confirm For further actions, you may consider blocking this person and/or reporting abuse Harsh Makwana - Jul 6 Yasir Rehman - Jul 8 Jack Lin - Jul 7 Nathan Bland - Jul 7 Once suspended, behainguyen will not be able to comment or publish posts until their suspension is removed. Once unsuspended, behainguyen will be able to comment and publish posts again. Once unpublished, all posts by behainguyen will become hidden and only accessible to themselves. If behainguyen is not suspended, they can still re-publish their posts from their dashboard. Note: Once unpublished, this post will become invisible to the public and only accessible to Be Hai Nguyen. They can still re-publish the post if they are not suspended. Thanks for keeping DEV Community safe. Here is what you can do to flag behainguyen: behainguyen consistently posts content that violates DEV Community's code of conduct because it is harassing, offensive or spammy. Unflagging behainguyen will restore default visibility to their posts. DEV Community — A constructive and inclusive social network for software developers. With you every step of your journey. Built on Forem — the open source software that powers DEV and other inclusive communities.Made with love and Ruby on Rails. DEV Community © 2016 - 2023. We're a place where coders share, stay up-to-date and grow their careers.