MariaDB - GitzJoey/DCSLab Wiki

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