Create a MariaDB container and manage databases

Creating the MariaDB container

docker create \
  --name=mariadb \
  -e PUID=1027 \
  -e PGID=100 \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -e TZ=Europe/Stockholm \
  -e MYSQL_DATABASE=db_name \		
  -e MYSQL_USER=db_username \
  -e MYSQL_PASSWORD=db_password \
  -p 3306:3306 \
  -v /volume1/docker/mariadb:/config \
  --restart unless-stopped \
  linuxserver/mariadb

--name: Name of the Docker container
-e PUID: User ID
-e PGID: User Group ID
-e MYSQL_ROOT_PASSWORD: Password needed to access the container
-e TZ: Timezone. Consult this list to find yours
-e MYSQL_DATABASE: In case you want to immediately create a database, this is the name you want to give to the database
-e MYSQL_USER: Every database needs to be assigned to a user. You can create one here
-e MYSQL_PASSWORD: Password of the database
-p 3306:3306: The port you want to map to the database. Remember to open the port from the router admin panel

Accessing MariaDB

1. Connect via SSH as root
2. Type docker exec -it mariadb bash
3. Access as root user using the command mysql -u root -p and inputting the password

Creating a new database and user

1. CREATE DATABASE database_name;
2. CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

Giving permission to the new user

1. GRANT USAGE ON database_name.* TO 'user_name'@localhost IDENTIFIED BY 'password';
2. SHOW GRANTS FOR 'user_name'@localhost;
3. FLUSH PRIVILEGES;

Showing list of users

1. SELECT user FROM mysql.user;

Showing user's permissions

1. SHOW GRANTS FOR 'username'@'localhost';

Deleting user and database

1. DROP USER 'user'@'localhost';
2. DROP DATABASE database_name;