Wednesday, October 6, 2021

Install mysql with docker in your local machine (ubuntu 20.04)

I had to use another laptop for some days and I didnt want to install packages that left garbage behind when moving away from this machine.

These are the two guides I followed for reference : 

 Preparation

In my case docker was already installed. docker can run from my user, so no need to append `sudo` to any `docker` command

Install mysql server via docker

The key here is to use the official mysql package from docker hub

# https://hub.docker.com/_/mysql
docker image ls
docker pull mysql:latest
docker image ls

# create a volume in local machine
docker volume create mysql-data
docker volume ls
docker volume inspect mysql-data

# map a local directory to host the config
mkdir -p /home/jesus/projects/docker/mysql-volume/conf.d
echo -e '[mysqld]\nmax_connections=100\nmysqlx= 0' > /home/jesus/projects/docker/mysql-volume/conf.d/my-custom.cnf

then create the container

docker run \
--detach \
--name=mysql_server \
--publish 3306:3306 \
--volume=/home/jesus/projects/docker/mysql-volume/conf.d:/etc/mysql/conf.d \
--volume=mysql-data:/var/lib/mysql \
--env MYSQL_ROOT_PASSWORD=root \
--env MYSQL_ALLOW_EMPTY_PASSWORD=yes \
mysql:latest

docker ps
docker logs mysql_server

change root password

# access to the container in interactive mode
docker exec -it mysql_server bash


#supply initial root password (to enter mysql inside the container)
mysql -uroot -proot

# SET PASSWORD FOR 'root' = 'something-super-secret';
SET PASSWORD FOR 'root' = '';
FLUSH PRIVILEGES;

# enable docker access from anywhere (probably the image has this already)
UPDATE mysql.user SET host = '%' WHERE user='root';
FLUSH PRIVILEGES;

Install mysql client

Install the client in your machine that accesses the mysql server running in the docker container. For other distributions adjust the method to install the mysql client (only the client is needed). The mysql site provides that information.

First, add the mysql repo. Go to https://dev.mysql.com/downloads/repo/apt/ and get the latest repository available. In my case (@2021-10-01). Adjust the .deb package accordingly.

# add the repo
wget https://dev.mysql.com/get/mysql-apt-config_0.8.19-1_all.deb
#Click OK with the default options or select which mysql you want
sudo dpkg -i mysql-apt-config_0.8.19-1_all.deb
rm mysql-apt-config_0.8.19-1_all.deb

# install the client from the repo
sudo apt-get update
sudo apt-get install mysql-client
# sudo apt-get install mysql-server

ensure it works

mysql -uroot -h127.0.0.1 -P3306
# or with password
# mysql -uroot -p -h127.0.0.1 -P3306

😄 Fin!

P.S. Useful commands

docker start mysql_server
docker stop mysql_server
docker restart mysql_server
docker ps
docker volume inspect mysql-data
docker container ls --all

mysql -uroot -p -h127.0.0.1 -P3306

# dangerous!
docker rm mysql_server
docker volume rm mysql-data