mysql server client in docker containers

Run MySQL Server in a Docker Container

docker run --rm \
   -v/tmp/test1:/var/lib/mysql \
   --name mysql01 \
   -e MYSQL_ROOT_PASSWORD=root \
   -e MYSQL_ROOT_HOST='%' \
   -p 3306:3306 \
   -d \
   mysql/mysql-server

• the -v option ensures that the database is saved on the host. The default directory where MySQL saves the database is /var/lib/mysql. This directory can be configured in /etc/mysql/my.cnf, as explained in this post. By overlaying the directory /tmp/test1 on the host to /var/lib/mysql, we "cheat" MySQL to save databases on the host. So, we can kill the container and restart it, and the database is still there.

  Please be aware that the directory on the host must be empty the first time we run the above command; otherwise, MySQL would fail to initialize. I figured out this problem after several failures using docker logs.

• the -e option sets the root password of MySQL to "root". Feel free to set it to any password you like.

• the second -e options sets MYSQL_ROOT_HOST to a wildcard so that clients running on any host could connect to the server as the user "root". This trick is supposed to work; however, it doesn't work with me. I had to follow this trick to connect to the MySQL server from a client running inside the server container, or another container on the same host, to enable remote TCP/IP connections:

  CREATE USER 'root'@'%' IDENTIFIED BY 'root';
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
  FLUSH PRIVILEGES;

• the --name option names the container to mysql01, which can be used to refer to this container.

• the -p option maps the port 3306, on which the MySQL server listens, to the same port on the host, so that clients could connect to the server via TCP/IP.

Run MySQL Client in the Server Container

docker exec -it mysql01 mysql -uroot -p

This command executes the command mysql, which is the command line tool of MySQL, in the container named mysql01.

• The command line flags of mysql include -u, which specifies the username of MySQL, and -p, which makes MySQL prompts for the password. For this example, we should type the password "root", which was set in the previous command.

• Please wait for a few seconds after the starting of the MySQL server container before we execute the client; otherwise, the startup of the client might fail.

• Once we get into the MySQL client, we can type SQL commands, e.g.,

  show databases;
  create database yi;

Run Client in a Different Container on the Same Host

docker run --rm -it \
   -v /tmp/test1:/var/lib/mysql \
   mysql/mysql-server \
   mysql -uroot -p

• The -v option maps the database directory on the host to the client container. This mapping is necessary because, by default, the client talks to the server via Unix socket /var/lib/mysql/mysql.sock, which is /tmp/test1/mysql.sock on the host.

Run Client in a Container on a Remote Host

docker run \
   --rm -it \
   mysql/mysql-server \
   mysql -h 192.168.1.3 -p 3306 -uroot -p

• the -h option tells the client where the server is running on. In this example, the given IP is the one of the host where I ran the MySQL server container.

Please be aware that the above command works only if the server allows remote connections.