Preface

When we deploy a docker application, we often encounter a scenario where the docker application needs to use a database. At this time, we have two options:
1. Use the docker compose command to create a new database that is only used for this docker.
2. Reuse existing databases

I see that many bloggers on the Internet recommend the docker compose method. Of course, this method is indeed simple. Just create a new yml file, paste the content provided on the Internet into it and save it, and finally run it in the directory corresponding to the yml file.docker-compose upYou can pull up all related containers, and the management interface of protainer looks very refreshing, which is very suitable for beginners.

However, as an obsessive-compulsive person who is dedicated to building a home data center, creating a new database container every time I need it would be a shame, so I naturally chose option 2. Then here we need to solve how to create a new library for this docker application in the existing database and create a new user with full management permissions for this library, so this article will solve this problem.

Note: This article assumes that the mariadb database is used and the database client uses DBeaver Community Edition (a good series for those who use it, thanks to Maoer for recommending it). The docker application takes next-terminal as an example (for the construction of this bastion host, see:Docker series builds an open source bastion machine based on next-terminal ).

Initialize the database

Method 1: Use DBeaver to initialize a new library

First, download and install DBeaver for your system:DBeaver Community Edition official website download addressThen, after logging into the corresponding mariadb database, right-click on the database and select Create a new database:

Then create a database. Generally, you only need to specify the database name and use the default values for other things:

In this way, the database used by next-terminal is built, and then right-click on the user and select New User:

Then specify the username and password you want to create, and click Save in the lower right corner:

Note: The host's % means that the user name can connect from any IP address. Of course, you can also use localhost to specify that you can only log in from this local machine, or use a specific IP address. You can adjust these according to the actual situation.

After clicking Save, the following interface will pop up. Click Execute directly:

After creating the new user "next-terminal", the next step is to grant this user access to the "next-terminal" database. Select the newly created user "next-terminal@%", then select "Schema Permissions" in the upper right corner, and from left to right select: jumpserver – % (All) – Select All. Then click "Save" in the lower right corner, as shown in the red box in the image below:

Then the following box will pop up, just click on the execute button in the lower right corner:

Note: Be sure to create the user first and then grant permissions, otherwise an error will be reported.

Method 2: Initialize the new library using the command line

---

This method requires the use of the mysql command, which needs to be run on a host that can connect to the database using the command. There are usually two scenarios:
1. Install the database using source code on the host

Installing the database on the host through the software store of the Baota panel or using the apt method to install the database belongs to this scenario. At this time, you only need to run the mysql command directly on the host.
2. Install the database using docker

In this way, since the database is deployed in docker, you need to enter the docker internal environment to run the mysql command. The command to enter the docker internal environment is as follows:

docker exec -it yourDBcontainer /bin/bash

Note: yourDBcontainer can be either container ID or container name.

---

Run the mysql command to log in to the database as root:

mysql -u root -p -h your-database-ip

Then run the following command to create a new library and the corresponding account:

create database 'next-terminal' default charset 'utf8mb4'; create user 'next-terminal'@'%' identified by 'password'; grant all on next-terminal.* to 'next-terminal'@'%'; flush privileges;

Note: The second lineidentified byBehindpasswordThis is the password of the new account. Please modify it according to the actual situation.

Mission accomplished.

Afterword

Besides using DBeaver (or other similar professional database management tools), you can also use phpMyAdmin to complete the initialization of the MariaDB database. However, considering future maintenance work, DBeaver is more convenient. After all, phpMyAdmin can only be used for MySQL or MariaDB, while DBeaver can also be used for other databases such as PostgreSQL, SQLite, Microsoft SQL Server, and Oracle. And as a man dedicated to building a professional "home data center", how could he only use one database?