MariaDB

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS).
This project is using version 10.6 or Latest

Installation

• Adding a repo

  $ curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
  $ bash mariadb_repo_setup
  

  Check the repo, it will be added in /etc/yum.repos.d/mariadb.repo

• Reset the repo
  This to refresh the repo list

  $ dnf module reset mariadb -y
  

• Install

  $ dnf install MariaDB-server MariaDB-client MariaDB-backup
  

• Enable and start the MariaDB services

  $ systemctl enable --now mariadb
  $ systemctl status mariadb
  

• Securing your MariaDB (after installation)

  $ mariadb-secure-installation
  

Some settings for server.cnf

[mysqld]
# For accessing MariaDB from internet
skip-networking=0    
skip-bind-address

# For low spec VPS
performance_schema=off

Firewall Setting (Optional)

If u want to access mysql remotely

firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --reload
firewall-cmd --list-all

Create non root user

• Login local to mysql

  $ mysql -u root -p
  

• Create schema, user, and privileges

  MariaDB [(none)] > CREATE DATABASE dcslab;
  MariaDB [(none)] > CREATE USER 'user1'@localhost IDENTIFIED BY 'password';
  MariaDB [(none)] > GRANT ALL PRIVILEGES ON dcslab.* TO 'user1'@localhost;
  MariaDB [(none)] > FLUSH PRIVILEGES;
  

• Verify user

  MariaDB [(none)] > SHOW GRANTS FOR 'user1'@localhost;
  

Set require SSL for MariaDB

• Create directory to store the MariaDB SSL .pem file

  $ mkdir /etc/mariadb_ssl/
  $ cd /etc/mariadb_ssl/
  

• Create new CA key

  $ openssl genrsa 4096 > ca-key.pem
  $ openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
  

• Creating the SSL Certificates
  Upon filling up the form, make sure the Common Name value is unique.

  $ openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
  $ openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
  

• Create the Client Certificate
  Upon filling up the form, make sure the Common Name value is unique.

  $ openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
  $ openssl rsa -in client-key.pem -out client-key.pem
  $ openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
  

• Adding the certificates to the MariaDB server There's 2 part of MariaDB section that required to be configure
  • [server] section

    ssl-ca=/etc/mariadb_ssl/ca-cert.pem
    ssl-cert=/etc/mariadb_ssl/server-cert.pem
    ssl-key=/etc/mariadb_ssl/server-key.pem
    
    tls_version=TLSv1.2,TLSv1.3
    

  • [client] section

    ssl-ca=/etc/mysql/mariadb_ssl/ca-cert.pem
    ssl-cert=/etc/mariadb_ssl/client-cert.pem
    ssl-key=/etc/mariadb_ssl/client-key.pem
    

• Change the file owner to mysql

  $ chown -R mysql:root /etc/mariadb_ssl/
  

• Apply the changes

  $ systemctl restart mysqld
  

• Check the SSL is configured properly

  MariaDB [(none)] > SHOW VARIABLES LIKE'%ssl%';
  

Set require SSL to user

Specially for user with '%' host

• For new user

  MariaDB [(none)] > 
  

• For existing user

  MariaDB [(none)] > GRANT ALL PRIVILEGES ON dcslab.* TO 'gitzjoey'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
  

• Check user configuration

  MariaDB [(none)] > show create user 'user1';
  

Client setup

We're using DBeaver as sample
Make sure upon create connection check the Use SSL in SSL tab

• Require cert file
  • ca-cert.pem
  • client-cert.pem
  • client-key.pem
• Advanced
  • Require SSL (checked)
  • Verify server certificate (unchecked)
  • Allow public key retrieval (unchecked)

In server client

$ mysql -u SSL_USER -–ssl-ca=/etc/mariadb_ssl/ca-cert.pem -–ssl-cert=/etc/mariadb_ssl/client-cert.pem -–ssl-key=/etc/mariadb_ssl/client-key.pem