Wiki/Guides/Docker/02Mariadb.md

title, description, published, date, tags, editor, dateCreated

title	description	published	date	tags	editor	dateCreated
02 Mariadb		true	2023-07-14T17:31:21.541Z		markdown	2023-05-03T01:58:45.617Z

MariaDB

MariaDB is a drop in replacement for MySQL, which is a database used by many services.

First we create some folders

mkdir -p ~/docker/mariadb

First we will create a docker compose file

vim ~/docker/mariadb/docker-compose.yml

Add in the following text

version: '3'

services:
  mariadb:
    image: mariadb:latest
    container_name: mariadb
    restart: always
    command: --transaction-isolation=READ-COMMITTED --binlog-format=ROW --skip-innodb-read-only-compressed
    volumes:
      - /data/mariadb/data:/var/lib/mysql
      - /data/mariadb/config:/etc/mysql/conf.d
      - /data/mariadb/logs:/var/log/mysql
      - /etc/localtime:/etc/localtime:ro

    environment:
      - MYSQL_ROOT_PASSWORD=SETAMYSQLROOTPASSWORDHERE

Be sure to set your mysql root password

Mariadb should be good now, lets start the container

sudo docker-compose -f ~/docker/mariadb/docker-compose.yml up -d

Now we only need to initialize the database

sudo docker exec -it mariadb mariadb-secure-installation

Answer the first 3 questions with No and the rest with Yes

You can get into the database with the following command (only if mariadb is running)

sudo docker exec -it maridb mysql -p

Enter the Mysql root password you provided during the creation of the mariadb container and you should be in.

Here you can create databases with the following command

create database DATABASENAME;

Create a user with

create user USERNAME@'LOCALIP' identified by 'USERPASSWORD';

Give privileges to a user on a database with

grant all privileges on DATABASENAME.* to USERNAME@'LOCALIP';

And Flush the privileges with

flush privileges;

You can exit the mysql prompt with exit; and then pressing enter.

PHPMyAdmin

Is a database manager for Mysql/MariaDB, it can be handy to manage the database with a GUI, Only install it if you need it, It is not needed for MariaDB to function at all.

First we start with a project folder

mkdir -p ~/docker/phpmyadmin

Now we create a docker-compose file

vim ~/docker/phpmyadmin/docker-compose.yml

Paste in the following text

version: '3'

networks:
  phpmyadmin:
    external: true
    name: phpmyadmin

services:
  phpmyadmin:
    image: phpmyadmin
    container_name: phpmyadmin
    restart: always
    volumes:
      - /data/phpmyadmin/config.inc.php:/var/www/html/config.inc.php:ro
    networks:
      phpmyadmin:
        ipv4_address: 172.20.72.10
    environment:
      - PMA_ARBITRARY=1

Now we are going to create the phpmyadmin config folder.

sudo mkdir /data/phpmyadmin

Next we are going to create a config file

sudo vim /data/phpmyadmin/config.inc.php

Paste in the following text

<?php
declare(strict_types=1);
$cfg['blowfish_secret'] = 'PUT YOUR BLOWFISH SECRET HERE';
$i = 0;
$i++;
$cfg['Servers'][$i]['auth_type'] = 'HTTP';
$cfg['Servers'][$i]['host'] = '0.0.0.0';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$cfg['AuthLog'] = '/var/log/phpmyadmin.log';
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

Now we will create a network

sudo docker network create --subnet=172.20.72.0/24 phpmyadmin

And start the container

sudo docker-compose -f ~/docker/phpmyadmin/docker-compose.yml up -d

PHPmyAdmin ofcourse needs access to the mariadb container so lets add mariadb to the network

vim ~/docker/mariadb/docker-compose.yml

Paste in the following lines

services:
  mariadb:
    networks:
      phpmyadmin:
        ipv4_address: 172.20.72.30

networks:
  phpmyadmin:
    external: true
    name: phpmyadmin

Restart MariaDB to add it to the PHPmyAdmin network

sudo docker-compose -f ~/docker/mariadb/docker-compose.yml down && sudo docker-compose -f ~/docker/mariadb/docker-compose.yml up -d

Now we need to add nginx to the phpmyadmin network

vim ~/docker/nginx/docker-compose.yml

Paste in the following lines

services:
  nginx:
    networks:
      phpmyadmin:
        ipv4_address: 172.20.72.20

networks:
  phpmyadmin:
    external: true
    name: phpmyadmin

Now we finally need to create a nginx config file

sudo vim /data/nginx/config/services/phpmyadmin.active

Paste in the following lines

server {
  server_name example.com;
  listen      443 ssl;

# Settings
  autoindex off;
  client_max_body_size 5000M;

# Locations
  location / {
    auth_basic "Restricted Content";
    auth_basic_user_file /etc/nginx/auth/.phpmyadmin;
    proxy_pass http://phpmyadmin:80;
    proxy_http_version                 1.1;
    proxy_cache_bypass                 $http_upgrade;
    proxy_ssl_server_name              on;
    proxy_set_header Upgrade           $http_upgrade;
    proxy_set_header X-Real-IP         $remote_addr;
    proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header X-Forwarded-Host  $host;
    proxy_set_header X-Forwarded-Port  $server_port;
    proxy_set_header Connection "upgrade";
    proxy_set_header Host $host;
    proxy_connect_timeout              6000s;
    proxy_send_timeout                 6000s;
    proxy_read_timeout                 6000s;
  }

  location ~ /\.(?!well-known) {
    deny all;
  }

  location = /favicon.ico {
    log_not_found off;
  }

  location = /robots.txt {
    log_not_found off;
  }

# GZip
  gzip            on;
  gzip_vary       on;
  gzip_proxied    any;
  gzip_comp_level 6;
  gzip_types      text/plain text/css text/xml application/json application/javascript application/rss+xml application/atom+xml image/svg+xml;

# Headers
  add_header X-XSS-Protection          "1; mode=block" always;
  add_header X-Content-Type-Options    "nosniff" always;
  add_header X-Frame-Options "SAMEORIGIN";
  add_header Referrer-Policy           "no-referrer-when-downgrade" always;
  add_header Content-Security-Policy   "default-src 'self' http: https: ws: wss: data: blob: 'unsafe-inline'; frame-ancestors 'self';" always;
  add_header Permissions-Policy        "interest-cohort=()" always;
  add_header Strict-Transport-Security "max-age=31536000; includeSubDomains; preload" always;

# SSL
  ssl_certificate     /etc/letsencrypt/live/example.com/fullchain.pem;
  ssl_certificate_key /etc/letsencrypt/live/example.com/privkey.pem;
  ssl_trusted_certificate /etc/letsencrypt/live/example.com/chain.pem;
}

# Redirect
server {
  listen 80;
  server_name example.com;
  return 301 https://example.com$request_uri;
 }

Generate a auth file for phpmyadmin, be sure to replace USERNAME

sudo htpasswd -c /data/nginx/config/auth/.phpmyadmin USERNAME

now it will ask for a password, give it one and store it well.

Finally restart nginx

sudo docker-compose -f ~/docker/nginx/docker-compose.yml down && sudo docker-compose -f ~/docker/nginx/docker-compose.yml up -d

Fail2ban check on atempted logins Eventhough there are two secuirity layers it is a good practice to keep track of failed logins on the landing page. WIP!