Preface

When we deploy a docker application, a scenario we often encounter is that the docker application needs to use a database. At this time, we have no more than two choices:
Use the docker compose command to associatively create a new database solely for this docker's use
Reuse an existing database

I see many bloggers online recommending the docker compose method. Of course, this method is indeed simple: just create a new yml file, paste the content provided online, save it, and finally run it in the directory corresponding to the yml filedocker-compose upto pull up all related containers, and it also looks very clean in the Portainer management interface, which is very suitable for beginners.

However, for an obsessive-compulsive person dedicated to building a home data center, having to create a new database container every time is worse than death, so I naturally chose the second option. Therefore, we need to solve how to create a new database for this docker application in an existing database and create a new user with full administrative permissions for this database, so this article is here to solve this problem.

Note: This article assumes that the mariadb database is used, and the database client uses the DBeaver Community Edition (a "you'll know how good it is once you use it" series, thanks to Mao'er for the recommendation). The docker application will take next-terminal as an example (for the setup of this bastion host, see:docker series: Building an open-source bastion host based on next-terminal ）。

Initialize the database

Method 1: Use DBeaver to complete new database initialization

First, download and install DBeaver suitable for your system:DBeaver Community Edition Official Website Download Address. Then, after logging into the corresponding mariadb database, right-click on the database and select New Database:

Then specify the username and password to be created, and click Save in the bottom right corner:

Note: The host's % indicates that the username can connect from any IP address. Of course, you can also use localhost to specify that login is only allowed from the local machine, or use a specific IP address. You can adjust these according to your actual situation.

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

After creating the next-terminal user, the next step is to grant the operation permissions of the next-terminal database to this user. Select the newly created next-terminal@% in Users, then select Schema Privileges in the top right, and select from left to right: jumpserver–%(All)–Select All, then click Save in the bottom right, as shown in the red box below:

Then the following box will pop up, just click Execute in the bottom right corner:

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

Method 2: Use the command line to complete new database initialization

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

Installing the database on the host through the BT Panel App Store or installing it via apt belongs to this scenario. In this case, you only need to run the mysql command directly on the host.
Install the database using Docker

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

docker exec -it yourDBcontainer /bin/bash

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

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

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

Then run the following commands to complete the creation of the new database 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;

create user 'next-terminal'@'%' identified by 'password';grant all on next-terminal.* to 'next-terminal'@'%';flush privileges;Note: The second lineidentified by

All done.

Afterword

after