In this article, learn how to start MySQL containers using Docker CLI and Docker compose.

If you are developing applications that require data storage, MySQL is a popular SQL database. It doesn’t matter that you’ve never used MySQL or a database before – this tutorial will learn and become familiar with the process of starting a Docker container. These concepts can be applied to any dependencies required by the application.

1. Find the appropriate MySQL image on Docker Hub

Visit the Docker Hub and type in the search box to find the page that contains the official image: MySQL

The Docker repository provides one or more supported tags that are variations of images. In general, if the distribution uses semantic versioning, the tag will match the application’s major.minor.patch number.

For MySQL, you can choose from various versions of 5.6, 5.7, or 8.0:

If you choose an exact version, such as 8.0.22, that version of MySQL will always be installed. Selecting major and minor releases, such as 8.0, will install the latest version of minor. It could be 8.0.22 now, 8.0.23 tomorrow. Selecting only major versions, such as 8, will install the latest major version. It could be 8.0.22 now, but next week it could be 8.1.0. Selecting Latest (or no label specified) will install the latest version, regardless of the version. It could be 8.0.22 now, but next month it could be 9.0.0 or higher. For this example, Latest is a good choice, but you should try to match the version on the production server or choose an exact version so that every developer uses the same dependency. Docker makes it easy to test database upgrades at any time.

2. Start the MySQL container

Start the MySQL container by typing the following command in the terminal (Windows users: remove backslashes and newlines) :

docker run 
  -it --rm --name mysql 
  -p 3306:3306 
  --mount "src=mysqldata,target=/var/lib/mysql" 
  -e MYSQL_ROOT_PASSWORD=websecret 
  mysql
Copy the code

All Docker CLI commands start with a Docker and an instruction such as run followed by options. Docker Run creates a container from a given image (mysql) and starts it. If the image is not available on the host, the image is downloaded.

It can take a few minutes to download the image, start the container, and initialize MySQL on the first run. The subsequent run is almost instantaneous. The database is ready to use when you see the following:

[System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections.
Copy the code

Docker Run provides A number of options (see Appendix A), but the main options you will use are:

-d: Run a container as a background process (exit at the end of the application) it: Keep the container running in the foreground (even after the application ends) and display an activity log –rm: fetch the container after stopping –name: name the container (otherwise use a random GUID) -p: Map the host port to the container port –mount: Create a persistent Docker management volume to hold data. String Specifies a SRC volume name and a target to mount the volume name in the container’s file system -v: mounts the host folder using the symbol: -e: defines environment variables –env-file: reads environment variables from a file, where each line defines a VAR=value — NET: connects to a specific Docker network — Entrypoint: overrides the default startup application

If –rm is not specified, the container will remain available even after it is stopped. Although it is possible to restart it, there is little benefit because it is easier to execute the Docker run command again.

MySQL credentials

It is a good practice to create a MySQL user that is granted limited privileges for a specific database, that is, it can access the data but cannot change the table structure or check other databases. This can be done by setting environment variables (see the MySQL image documentation).

Production environments can start their own MySQL containers, so using root with all permissions seems less risky. However, containers are not sandbox applications. Locked users are always more secure.

For brevity, the following example uses root, but more secure credentials are recommended during development and deployment.

3. Connect to the database using MySQL client

Once the database container is started, you can connect to localhost:3306 using any MySQL client application installed on the host with user ID root and password websecret.

If you don’t have a MySQL client handy, Admineris is a lightweight PHP-based option. It can also be used as a Docker image, running the following command on another terminal:

docker run 
  -it --rm --name adminer 
  -p 8080:8080 
  adminer
Copy the code

After startup, open http://localhost:8080 in your browser and enter your MySQL login credentials:

Note that you cannot use localhost as the server name because Adminer will resolve to its own container! Instead, you can:

  1. Enter the host. The docker. Internal.

    Docker Desktop routes this domain to your PC’s network IP address, but it may not be available on all systems.

  2. Use the actual network IP address.

    This can be obtained from the ifconfig command on macOS and Linux or the ipconfig command on Windows.

  3. Or use the docker-assigned container IP address.

Docker created its own virtual network. Docker inspect mysql returns container information in JSON format. You can find the IPAddress value using the -f format option:

docker inspect 
  -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 
  mysql
Copy the code

4. Connect to the container shell

Each Docker container runs an isolated Linux environment. You can connect to its shell to run commands, check logs, or perform any other activity.

Remember that containers are stateless! Any changes made are lost as soon as the container is restarted.

Assuming the MySQL container is still running, open another terminal and type:

docker exec -it mysql bash
Copy the code

Some lightweight images using Alpine Linux do not provide bash shells. If this command fails, try using docker exec it mysql sh instead.

For example, you can access the MySQL command line and list the database:

> mysql -u root -pwebsecret Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or 'h' for help. Type 'c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 5 rows in the set (0.00 SEC) mysql >Copy the code

Enter exit to exit

5. View, stop, and restart the container

Container run in interactive mode (switch) start with – it can usually stop press Ctrl | Cmd + C.

To view a list of running containers, type the following:

docker container ls
docker container ls -a 
Copy the code

Or shorter:

docker ps
Copy the code

Displays a list of active containers. This can be quite wide – the truncated version is shown here:

CONTAINER ID  IMAGE    STATUS      PORTS           NAMES
ef3bab04fc8f  adminer  Up 16 mins  8080->8080/tcp  adminer
793003e459e6  mysql    Up 17 mins  3306->3306/tcp  mysql
Copy the code

Each container is assigned a hexadecimal ID that can be used as a reference in Docker commands. However, specifying the container –name can greatly simplify administration.

You can restart the container docker container restart by providing a list of one or more names. This can be useful if you want to delete browser sessions or temporary data:

docker container restart adminer mysql
Copy the code

You can also pause and unpause for example, if you want to test the application’s response to a database failure, run the container:

docker container pause mysql
docker container unpause mysql
Copy the code

Similarly, containers can be stopped by docker container:

docker container stop adminer mysql
Copy the code

These containers are started with the -rm option, so they are deleted when stopped. If you don’t use this option (or are experiencing Docker problems), list all containers: Docker ps-a stops container removal can be used: Docker Container Prune

6. Define the Docker network

Using IP addresses to communicate between containers can be difficult. Once the container is started, it is assigned an IP address on the default Docker network, but it can be changed at a later startup.

Docker Run has — IP and — IP6 options, so you can define a fixed IP, but it’s usually easier to refer to another container using –name. This can be done by creating your own Docker network.

Stop all running containers and create a new network, for example named mysqlnet here:

docker network create --driver bridge mysqlnet
Copy the code

At startup, any container can connect to the network using the — NET option. The MySQL example:

docker run 
  -d --rm --name mysql 
  -p 3306:3306 
  --mount "src=mysqldata,target=/var/lib/mysql" 
  -e MYSQL_ROOT_PASSWORD=websecret 
  --net mysqlnet 
  mysql
Copy the code

Adminer:

docker run 
  -d --rm --name adminer 
  -p 8080:8080 
  --net mysqlnet 
  adminer
Copy the code

The name of each container is now resolved on the Docker mysqlNet network. Therefore, you can enter mysql as the server name on the Adminer login screen.

7, clear

Docker takes up a lot of disk space! Horrible usage statistics can be obtained by entering the following information

docker system df
Copy the code

To view all running and stopped containers, type:

docker container ls -a
Copy the code

Note that containers are usually small because they are stateless and start from a specific image.

To view all live images and dangling images (container independent images), type:

docker image ls -a
Copy the code

To view all docker-managed disk volumes, enter:

docker volume ls
Copy the code

To view all Docker networks, enter:

docker network ls
Copy the code

Using MySQL and Adminer alone will use nearly 1GB of space. This will improve further as you start using other dependencies and creating your own images.

Stop the container by adding the container name to the command: stop

docker container stop adminer mysql
Copy the code

You can remove all stopped containers, unused networks, and suspended images using the following methods:

docker system prune
Copy the code

It’s safe, and running often can be a good idea.

The following command will do the same and erase any images that are not related to the running container:

docker system prune -a
Copy the code

7.1 Deleting a Disk Volume

Deleting a Docker volume permanently erases its data! There’s no going back.

If you are developing database-driven applications, you can usually keep one or more data dumps that can be used to recreate the database in various states. Most MySQL client tools provide dump or Export capabilities, such as the Export link in Adminer.

Alternatively, the mysqldump utility that comes with MySQL can be run by attaching to the container shell or using the Docker exec command.

Use root to backup the database named mydb to a file named backup.sql.

MySQL credentials on Linux or macOS, please use:

docker exec mysql /usr/bin/mysqldump -u root -pwebsecret mydb 
  > backup.sql
Copy the code

Windows PowerShell:

docker exec mysql /usr/bin/mysqldump -u root -pwebsecret -r mydb | 
  Set-Content backup.sql
Copy the code

Assuming you wish to continue, you can view the Docker volume using the following methods:

docker volume ls
Copy the code

Then delete anything by ID or name:

docker volume rm 
Copy the code

Unused Docker volumes – those that are not currently connected to a running container – can be removed using the following methods:

docker volume prune
Copy the code

7.2 Completely clean startup

Each unused container, image, volume, and network can be erased using the following methods:

docker system prune -a --volumes
Copy the code

If you want to force erasure without a confirmation prompt, add -f.

8. Start multiple containers with Docker Compose

Launching Docker containers alone is not fun — especially if the commands are long and hard to remember. Fortunately, Docker Compose provides a way to build and start containers, networks, and volumes from a single configuration file called docker-comemage.yml.

YAML is not a markup language: it is a common, compact data format, often used for configuration purposes. It uses new lines and tabs instead of the quotes and parentheses JSON prefers.

Docker Compose provides a number of configuration options ([see Appendix C]), but an example is the best way to illustrate common Settings. Here’s an example: docker-comemage.yml

version: '3'
services:
  mysql:
    image: mysql
    container_name: mysql
    environment:
      - MYSQL_ROOT_PASSWORD=websecret
    volumes:
      - mysqldata:/var/lib/mysql
    ports:
      - "3306:3306"
    networks:
      - mysqlnet
    restart: on-failure
  adminer:
    image: adminer
    container_name: adminer
    depends_on:
      - mysql
    ports:
      - "8080:8080"
    networks:
      - mysqlnet
    restart: on-failure
volumes:
  mysqldata:
networks:
  mysqlnet:
Copy the code

This will create the same container as the Docker run command used above and list volumes and networks at the bottom. It also defines two new Settings:

  • Depends_on Startup dependency: Adminer will start after MySQL starts

  • Restart on-failure: The container restarts automatically when stopped with exit code.

If you have not stopped an existing container, do the following immediately:

docker container stop adminer mysql
docker system prune
Copy the code

Start docker compose from the same directory as the docker-comemess. yml file using the following command:

docker-compose up
Copy the code

If necessary, the mysql and Adminer images will be downloaded and both containers will be started. By default, Docker-compose runs as a foreground task and displays a log of all container activities. Use MySQL when you see the following:

mysql | ... [Server] X Plugin ready for connections.
Copy the code

You can now start Adminer at http://localhost:8080/ and connect to the MySQL server using the user ID root and password websecret.

Specifying a single port value exposes only ports in the network, for example

ports:
      - "3306"
Copy the code

In this case, although Adminer can still communicate with mysql:3306 on the internal network, the host cannot access mysql.

Services can be used and connected to their shells as before. Container will continue to run until you press Ctrl in the start-up docker – compose of the terminal | Cmd + C.

From now on, docker-compose will be used whenever possible. It is rarely necessary to enter individual Docker commands, but it is useful to know that they are available, especially since Docker Compose may not be available on every system.