MySQL Server on Docker
In this post, we are going to learn how to use MySQL server inside docker.
I use MySQL Server a lot for my pet projects and it is always a pain to configure them for my use again and again. So, I thought of using a Docker to run MySQL server and only keep database on my disk.
By using docker, I don’t have to keep the MySQL server downloaded and installed on my machine and need not to worry about what all files MySQL server has installed and where all it has put them. Using Docker, I just start the server and stop it with one tiny command and it doesn’t spoil my disk.
I searched and tried various articles online to-do this but was not able find a simple article to achieve this. So, I want to share what I did in the simple steps to get docker with mysql server going.
Before proceeding make sure you have Docker installed on your machine and it is running.
Run following command to install MySQL server docker image.
Run command:
docker install mysql/mysql-server:latest
Now, create the following directory in your user home. Like:
mkdir ~/sql/docker
mkdir ~/sql/docker/volume
mkdir ~/sql/docker/volume/data
Go to directory “~/sql/docker/volume” and here we will create a file named my.cnf file for MySQL server.
cd ~/sql/docker/volume
Add the following lines to this file:
[mysqld]
user=mysql
Now, we will create a small shell file to start the MySQL server in docker. Add the following lines a shell file. You can name this file whatever. Remember to assigned execute permission to this file using chmod u+x mysqldocker.sh.
#/usr/bin/env bash
docker run — rm — name=mysql \
— mount type=bind,src=$HOME/src/docker/mysql/volume/my.cnf,dst=/etc/my.cnf \
— mount type=bind,src=$HOME/src/docker/mysql/volume/data,dst=/var/lib/mysql \
-p 3306:3306/tcp \
-p 33060:33060 \
-d mysql/mysql-server:latest
After running above command, MySQL server will start inside the docker and bind to data directory present on your disk. That is: ~/sql/docker/volume/data.
Now the next step is to change the root user password, as the first start of new MySQL server generates a random root user password and print in logs. Using following command, we can find out the generated root user password.
docker logs mysql 2>&1 | grep GENERATED
Above command will print something like:
GENERATED ROOT PASSWORD: Axegh3kAJyDLaRuBemecis&EShOs
Note: You need to run the above command soon after the start of MySQL server. Trying running the docker logs command multiple times, if by chance MySQL start is slow and it has not written the generated password in the logs.
Copy the generated password. Now, we will change the root user password.
Run following command to go in the SQL shell and enter the copied root user password once prompted.
docker exec -it mysql mysql -uroot -p
Now, paste the following command to change the root user’s password.
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘newpassword’
Using ALTER USER SQL command you can change the root user password.
Now, root user password is changed. But still you can’t connect to MySQL server from outside. You need to either create a new user or allow remote connections to root user.
I used following commands to create a new root user which can be accessed from outside of the network.
mysql> CREATE USER ‘root’@’%’ IDENTIFIED BY ‘root’;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;
Once, this is done, now root user can connect from outside.
Note: This setup is not secure as anyone who knows root user password would be able to connect. As I use this docker setup for mostly testing, so it is fine.
Now, you can use the docker MySQL server freely.
