MySQL tutorial with Docker

Raising a database with Docker is the fastest way to have a programming environment ready to start coding on a home PC, without performing installations that leave remnants of files and configurations throughout the hard drive, greatly simplifying development and production environments.

And how is it done? In short, Docker is an open-source platform in the form of a virtual machine that manages and runs containers that package applications. What? Let's see the details...

What is Docker

Docker is a application virtualization service within containers. This allows applications to run independently of the host that houses them. When an application is virtualized, orchestrators monitor the state and load of the applications; they can easily start or stop instances in the cloud to tolerate high workloads.

Docker is available at no cost for major desktop platforms and is one of the fundamental tools for any modern programmer. If you want to expand your knowledge about its use, review our Docker tutorial.

Requirements for MySQL with Docker

If you are a Windows or Mac user, you need to have Docker Desktop installed on your PC or laptop, and it should have a certain capacity and power. Additionally, if you are a Windows user, you need to have the Professional version to install Docker. If not, visit the tutorial on installing Ubuntu, the best free and open-source operating system for programming. If you are a Ubuntu or Debian user, make sure you have Docker Compose on your system, as explained in the Docker installation tutorial.

Install MySQL with Docker

Starting a MySQL database with Docker cannot be simpler. Just take the template of the Docker Compose configuration file provided below and invoke the command that will handle the entire process.

version: '2'
services:
    mysql:
        image: 'mysql:latest'
        restart: always
        volumes:
        - './mysql_data:/var/lib/mysql'
        environment:
        - MYSQL_ROOT_PASSWORD=secure_pass_here
        ports:
        - '3306:3306'

Save the file locally with the name docker-compose.yml in a directory named <home_user>/docker/mysql if you are on Unix, or <home_user>\docker\mysql if you are on Windows. The configuration saves the data in the subdirectory ./mysql/data permanently because, otherwise, each time the container is restarted, the hosted databases would be lost. Then, open a command terminal in the same directory as the file and run:

docker-compose up -d

The system will first download the image from the public Docker repositories when it sees that it's not available locally, and then it will proceed to start and open the port specified in the configuration file. The -d option instructs Docker to run the service in the background.

From that point, use your preferred graphical interface to connect to the database. If you don't have one, you can get the free and multi-database client DBeaver.

Verify that the MySQL service is active

To verify that the service is active and the container is running normally, try running the following docker command that shows the running containers:

docker ps

You should see at least one entry corresponding to the active MySQL container, as shown below:

CONTAINER ID  IMAGE    COMMAND                 CREATED       STATUS       PORTS                               NAMES
327fd50443a7  mysql:5  "docker-entrypoint.s…"  29 hours ago  Up 6 hours   0.0.0.0:3306->3306/tcp, 33060/tcp   docker_mysql_1

If so, you can proceed with the rest of the activities, as mentioned in the previous text fragment, the container is running (status up) and listening on port 3306.

Connect MySQL with DBeaver

If you have the SQL client DBeaver, once the container is running, you can connect with this multi-platform client by entering the following information:

Select new connection for MySQL 8+
Server host: localhost
Port: 3306
User name: root
Password: secure_pass_here

Next, click "Test Connection," and if everything goes well, click "Finish." From that moment on, the SQL graphical client can establish a connection with the container as long as it is running.

If you don't have much experience in managing databases like MySQL, we recommend reading the SQL tutorial for beginners.

Create MySQL User

Creating a user, other than the system administrator (root), is an important step, as it ensures that if we make a mistake executing SQL statements or if the application behaves unexpectedly, the impact will be limited to the scope of this user. Therefore, it's usually recommended to create a separate user for each database managed by the MySQL system. This way, all applications can operate independently without interfering with each other. This approach is highly recommended.

To create a user in MySQL, we need to know the username, password, and the IP address from which access will be allowed (or if it should be open from any address). Once we have these parameters clear, we'll open a connection using our preferred method and execute the following statement:

create user 'pepito'@'%' identified by 'pepitoPass';

The '%' character indicates that the 'pepito' user can connect from any IP address. If not, the '%' should be replaced with the corresponding IP address, keeping the quotes, or 'localhost' can be used as the host, allowing only local connections.

Change Password in MySQL

To change the password of a user in MySQL, we need to know which user we're referring to and from where they will be connecting, or from where it's allowed to connect. The user@host tuple is necessary to identify the line that needs to be modified.

ALTER USER 'pepito'@'%' IDENTIFIED BY 'securePepitoPass';

This statement assumes that the user previously had access from any internet address.

Delete a User in MySQL

To delete a user, once again we need the user-host pair. The operation cannot be undone; however, if a mistake is made, the user can be recreated. If done in this manner, neither the database nor the permissions associated with it would be affected.

drop user 'pepito'@'%';

Create MySQL Database

Creating a database in MySQL is one of the simplest operations to perform; it only requires the name of the database:

create database pepito_app;

Grant Permissions to a User in MySQL

Once the user and the database are created within the container running the SQL management system, the next step is to grant permissions to the user on that database. There is a wide range of permissions that users can have, allowing precise control over what each user can and cannot do. This granularity provides a high level of control and security over the stored data. For example, some commonly used permissions are:

• ALL: Grants all permissions on the database
• ALTER: Allows modification of tables, sequences, etc...
• CREATE: Allows creation of tables, sequences, etc...
• DROP: Allows deletion of tables, sequences, etc...
• DELETE: Allows deletion of records in tables
• INSERT: Allows insertion of records into tables
• UPDATE: Allows updating records in tables
• SELECT: Allows querying tables, sequences, etc...

In most cases, if we have one user per database, granting the ALL permission will be sufficient, or at least the necessary permissions to manage the records in the database. However, to grant permissions to a user on a database, we should write the following SQL statement:

grant all privileges on pepito_app.\* to 'pepito'@'%';
flush privileges;

These two statements first grant all permissions on all objects (.*) in the pepito_app database, and then update the MySQL caches to reload the permissions configuration. Permissions are cached to speed up access resolution without having to read the configuration tables every time.

Revoke Permissions from a User in MySQL

Revoking permissions from a user is done similarly to granting access, but in this case, the revoke keyword is used as follows:

revoke insert on pepito_app.\* from 'pepito'@'%';

Remember that after each privilege change, the cache should be updated with:

flush privileges;

Delete MySQL Database

If creation is one of the simplest operations to perform, deletion will be similar:

drop database pepito_app;

All tables, sequences, indexes, and data in the database will be permanently and irrevocably deleted from the management system. Make sure you enter the correct name of the database!

Conclusions

As we have learned, if you want to start programming quickly and efficiently without dealing with complicated installations, Docker is the solution. Setting up a database with Docker allows you to have a programming environment ready in minutes, without worrying about leaving traces of files and configurations on your hard drive. Visit Arteco consulting SL and discover this and much more content related to the world of web programming.