Tuesday, April 15, 2025

HOMELAB

Just for fun...

Numerous options exist for testing MySQL and general database instances.

This is just an example of how you can use Proxmox to have a simple fast setup for repeatable testing and access.

This example is a nice and cheap option to have a home lab for MySQL and anything else you want.

The homelab virtualization I picked was Proxmox and Openmediavaul for extended NFS storage (not 100% sold on Openmediavault, Debian alone could do it)

Hardware I picked a simple and compact setup:

This virtualization also allows you to have an environment that can run as needed, shutdown, or even walk away and come back to whenever wanted. 

Allows you to also clone each instance for additional instances and testing if desired once set up. 

This is going to be a very simple direct setup across all of these.

You can create block devices for each data directory or NFS if you prefer as well but this will all be local direct installs for demo.

Goal to test and demo:

  • Install
  • Monitoring
  • Vault Password rotation

Debian BASE 

Set up a Debian 12 instance with 4GB and 4 CPUs.
Then I converted this as a template so I can link all other instances from this.
This makes it very fast to set up other instances as well as have the same base to start with.


┌──(root㉿debian12-server)-[~]
└─# uname -a
Linux debian12-server 6.1.0-32-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.129-1 (2025-03-06) x86_64 GNU/Linux

apt install unzip
cd /usr/local/src/
wget https://releases.hashicorp.com/vault/1.4.2/vault_1.4.2_linux_amd64.zip
unzip vault_1.4.2_linux_amd64.zip
mv vault /usr/bin/
setcap cap_ipc_lock=+ep /usr/bin/vault
# vault -v
Vault v1.4.2

MariaDB 11 Rolling 

vi /etc/network/interfaces
auto ens18
iface ens18 inet static
    address 192.168.3.100
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

# hostname
mariadb1.sqlhjalp.com

sudo apt-get install apt-transport-https curl
sudo mkdir -p /etc/apt/keyrings
sudo curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp'
vi /etc/apt/sources.list.d/mariadb.sources

┌──(root㉿mariadb1)-[~]
└─# cat /etc/apt/sources.list.d/mariadb.sources
# MariaDB 11 Rolling repository list - created 2025-04-01 15:13 UTC
# https://mariadb.org/download/
X-Repolib-Name: MariaDB
Types: deb
# deb.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# URIs: https://deb.mariadb.org/11/debian
URIs: https://mirror.its.dal.ca/mariadb/repo/11.rolling/debian
Suites: bookworm
Components: main
Signed-By: /etc/apt/keyrings/mariadb-keyring.pgp

┌──(root㉿mariadb1)-[~]
└─# apt-get update

┌──(root㉿mariadb1)-[~]
└─# apt-get install mariadb-server -y

┌──(root㉿mariadb1)-[~]
└─# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 11.7.2-MariaDB-deb12 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]> exit
Bye

┌──(root㉿mariadb1)-[~]
└─# systemctl stop mariadb.service

Just to be clear. Yes with a proxmox linked server you can restart and it keeps all your values and setup

┌──(root㉿mariadb1)-[~]
└─# uptime
 10:27:29 up 1 min,  2 users,  load average: 0.15, 0.11, 0.04

┌──(root㉿mariadb1)-[~]
└─# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 11.7.2-MariaDB-deb12 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status
--------------
mariadb from 11.7.2-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:		33
Current database:
Current user:		root@localhost
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384, cert is OK
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		11.7.2-MariaDB-deb12 mariadb.org binary distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb3
Conn.  characterset:	utf8mb3
UNIX socket:		/run/mysqld/mysqld.sock
Uptime:			1 min 45 sec

Threads: 1  Questions: 61  Slow queries: 0  Opens: 33  Open tables: 26  Queries per second avg: 0.580
--------------

MySQL Innovation 

vi /etc/network/interfaces
auto ens18
iface ens18 inet static
    address 192.168.3.101
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4


# apt install gnupg -y
# cd /usr/local/src/
# wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
# dpkg -i mysql-apt-config_0.8.33-1_all.deb

│ Which server version do you wish to receive?                                                                                                                           │
  mysql-8.0  
  mysql-innovation    <-- Picked this 
  mysql-8.4-lts    
  mysql-cluster-8.0   
  mysql-cluster-innovation    
  mysql-cluster-8.4-lts    
  None

 Which MySQL product do you wish to configure?                                                                                                                           
 MySQL Server & Cluster (Currently selected: mysql-innovation) 
 MySQL Connectors (Currently selected: Enabled) 
 Ok

# apt-get update
# apt-get install mysql-server -y 

─# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 9.2.0 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		9
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		9.2.0 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Binary data as:		Hexadecimal
Uptime:			47 sec

Threads: 2  Questions: 6  Slow queries: 0  Opens: 119  Flush tables: 3  Open tables: 38  Queries per second avg: 0.127

MySQL Innovation NDB CLuster 

cat /etc/network/interfaces
auto ens18
iface ens18 inet static
    address 192.168.3.102
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

auto ens18
iface ens18 inet static
    address 192.168.3.103
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

auto ens18
iface ens18 inet static
    address 192.168.3.103
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

# apt install gnupg -y
# cd /usr/local/src/
# wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
# dpkg -i mysql-apt-config_0.8.33-1_all.deb

│ Which server version do you wish to receive?                                                                                                                            
 mysql-8.0  
 mysql-innovation     
 mysql-8.4-lts        
 mysql-cluster-8.0       
 mysql-cluster-innovation   <-- Picked this    
 mysql-cluster-8.4-lts   
 None

 Which MySQL product do you wish to configure?                                                                             
  MySQL Server & Cluster (Currently selected: mysql-cluster-innovation)  
  MySQL Connectors (Currently selected: Enabled)     
  Ok

# apt-get update
# apt-get install mysql-cluster-community-server -y 
# apt-get install mysql-cluster-community-management-server   <-- we can pick and choose later which to use
# apt-get install mysql-cluster-community-data-node  -y 

# vi /etc/mysql/conf.d/mysql.cnf

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine

[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.3.102  # location of management server

# mkdir /var/lib/mysql-cluster
# cd /var/lib/mysql-cluster
# vi config.ini
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2    # Number of fragment replicas
DataMemory=98M    # How much memory to allocate for data storage

[ndb_mgmd]
# Management process options:
HostName=192.168.3.102          # Hostname or IP address of management node
DataDir=/var/lib/mysql-cluster  # Directory for management node log files

[ndbd]
# Options for data node "A":
                                # (one [ndbd] section per data node)
HostName=192.168.3.103          # Hostname or IP address
NodeId=2                        # Node ID for this data node
DataDir=/var/lib/mysql/data   # Directory for this data node's data files

[ndbd]
# Options for data node "B":
HostName=192.168.3.104          # Hostname or IP address
NodeId=3                        # Node ID for this data node
DataDir=/var/lib/mysql/data   # Directory for this data node's data files

[mysqld]
# SQL node options:
HostName=192.168.3.102          # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)


┌──(root㉿ndb1)-[/var/lib/mysql-cluster]
└─# ndb_mgmd --initial -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-9.2.0 ndb-9.2.0
WARNING: --ndb-connectstring is ignored when mgmd is started with -f or config-file.

┌──(root㉿ndb2)-[/var/lib/mysql-cluster]
└─# ndbd
2025-04-01 11:53:11 [ndbd] INFO     -- Angel connected to '192.168.3.102:1186'
2025-04-01 11:53:12 [ndbd] INFO     -- Angel allocated nodeid: 2

┌──(root㉿ndb3)-[/var/lib/mysql-cluster]
└─# ndbd
2025-04-01 11:53:19 [ndbd] INFO     -- Angel connected to '192.168.3.102:1186'
2025-04-01 11:53:20 [ndbd] INFO     -- Angel allocated nodeid: 3


┌──(root㉿ndb1)-[/var/lib/mysql-cluster]
└─# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to management server at 192.168.3.102 port 1186 (using cleartext)
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2	@192.168.3.103  (mysql-9.2.0 ndb-9.2.0, Nodegroup: 0, *)
id=3	@192.168.3.104  (mysql-9.2.0 ndb-9.2.0, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@192.168.3.102  (mysql-9.2.0 ndb-9.2.0)

[mysqld(API)]	1 node(s)
id=4 (not connected, accepting connect from 192.168.3.102)

┌──(root㉿ndb1)-[/var/lib/mysql-cluster]
└─# mysql -u root -p -e "select @@hostname"
Enter password:
+-------------------+
| @@hostname        |
+-------------------+
| ndb1.sqlhjalp.com |
+-------------------+

┌──(root㉿ndb2)-[/var/lib/mysql-cluster]
└─# mysql -u root -p -e "select @@hostname"
Enter password:
+-------------------+
| @@hostname        |
+-------------------+
| ndb2.sqlhjalp.com |
+-------------------+

┌──(root㉿ndb3)-[/var/lib/mysql-cluster]
└─# mysql -u root -p -e "select @@hostname"
Enter password:
+-------------------+
| @@hostname        |
+-------------------+
| ndb3.sqlhjalp.com |
+-------------------+

Percona Server 

# cat /etc/network/interfaces
 
auto ens18
iface ens18 inet static
    address 192.168.3.105
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

# apt install curl  gnupg gnupg2 lsb-release -y
# cd /usr/local/src/
# curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
# dpkg -i  percona-release_latest.generic_all.deb
# percona-release enable-only ps-84-lts release
# percona-release enable tools release
# apt install percona-server-server

┌──(root㉿ps1)-[/usr/local/src]
└─# ps -ef |grep mysql
mysql       5832       1  0 11:58 ?        00:00:03 /usr/sbin/mysqld
root        5924     501  0 12:04 pts/1    00:00:00 grep --color=auto mysql

┌──(root㉿ps1)-[/usr/local/src]
└─# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.4-4 Percona Server (GPL), Release '4', Revision '844fde07'

Copyright (c) 2009-2025 Percona LLC and/or its affiliates
Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Percona Cluster 

# cat /etc/network/interfaces
iface ens18 inet static
    address 192.168.3.106
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

iface ens18 inet static
    address 192.168.3.106
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

iface ens18 inet static
    address 192.168.3.106
    netmask 255.255.255.0
    gateway 192.168.3.1
    dns-nameservers 8.8.8.8 8.8.4.4

# apt install curl  gnupg gnupg2 lsb-release -y
# cd /usr/local/src/
# curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
# dpkg -i  percona-release_latest.generic_all.deb
# apt update
# percona-release setup pxc80
# apt install -y percona-xtradb-cluster

 
cat /etc/my.cnf
[client]
socket=/var/run/mysqld/mysqld.sock

[xtrabackup]
open-files-limit		= 1000000

[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
secure-log-path=/var/lib/mysql-files/
# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800

userstat                        = 1

 └─# cat /etc/my.cnf | grep encrypt
pxc-encrypt-cluster-traffic     = OFF
 
┌──(root㉿pxc1)-[/]
└─# cat /etc/my.cnf | grep address
wsrep_cluster_address           = gcomm://192.168.3.106,192.168.3.107,192.168.3.108
# Node IP address
wsrep_node_address=192.168.3.106

┌──(root㉿pxc1)-[/etc/mysql/conf.d]
└─# systemctl start mysql@bootstrap

mysql> show status like 'wsrep_c%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_cert_deps_distance   | 0                                    |
| wsrep_commit_oooe          | 0                                    |
| wsrep_commit_oool          | 0                                    |
| wsrep_commit_window        | 0                                    |
| wsrep_cert_index_size      | 0                                    |
| wsrep_cert_bucket_count    | 1                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_cert_interval        | 0                                    |
| wsrep_cluster_weight       | 1                                    |
| wsrep_cluster_capabilities |                                      |
| wsrep_cluster_conf_id      | 1                                    |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_state_uuid   | 71a6ebf4-0f20-11f0-b4eb-0a0f463a7185 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
+----------------------------+--------------------------------------+
15 rows in set (0.00 sec)

┌──(root㉿pxc2)-[/etc]
└─# cat /etc/my.cnf | grep address
wsrep_cluster_address           = gcomm://192.168.3.106,192.168.3.107,192.168.3.108
# Node IP address
wsrep_node_address=192.168.3.107

┌──(root㉿pxc2)-[/var/lib/mysql]
└─# rm -Rf *

┌──(root㉿pxc2)-[/var/lib/mysql]
└─# ls -tla
total 8
drwxr-x---  2 mysql mysql 4096 Apr  1 13:36 .
drwxr-xr-x 26 root  root  4096 Apr  1 12:34 ..

┌──(root㉿pxc2)-[/var/lib/mysql]
└─# systemctl start mysql

┌──(root㉿pxc2)-[/etc]
└─# cat /etc/my.cnf | grep address
wsrep_cluster_address           = gcomm://192.168.3.106,192.168.3.107,192.168.3.108
# Node IP address
wsrep_node_address=192.168.3.107
 ┌──(root㉿pxc3)-[/var/lib/mysql]
└─# rm -Rf *

┌──(root㉿pxc3)-[/var/lib/mysql]
└─# systemctl start mysql


mysql> show status like 'wsrep_c%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_cert_deps_distance   | 0                                    |
| wsrep_commit_oooe          | 0                                    |
| wsrep_commit_oool          | 0                                    |
| wsrep_commit_window        | 0                                    |
| wsrep_cert_index_size      | 0                                    |
| wsrep_cert_bucket_count    | 1                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_cert_interval        | 0                                    |
| wsrep_cluster_weight       | 3                                    |
| wsrep_cluster_capabilities |                                      |
| wsrep_cluster_conf_id      | 3                                    |
| wsrep_cluster_size         | 3                                    |
| wsrep_cluster_state_uuid   | 71a6ebf4-0f20-11f0-b4eb-0a0f463a7185 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
+----------------------------+--------------------------------------+
15 rows in set (0.00 sec)

Setup MySQL Exporters and Prometheus 

per each machine...

apt install -y prometheus-mysqld-exporter
 
CREATE USER IF NOT EXISTS 'prometheus'@'localhost' IDENTIFIED BY '<PASSWORDHERE>';
mysql> show grants for 'prometheus'@'localhost'; +------------------------------------------------------------------------------+ | Grants for prometheus@localhost | +------------------------------------------------------------------------------+ | GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO `prometheus`@`localhost` | +------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ┌──(root㉿pxc1)-[~] └─# ls -ltr /etc/mysql/debian.cnf -rw-r--r-- 1 root root 50 Apr 1 16:52 /etc/mysql/debian.cnf # vi /etc/default/prometheus-mysqld-exporter systemctl restart prometheus-mysqld-exporter.service # systemctl restart prometheus-mysqld-exporter.service

Prometheus Server 

root@prometheus:/etc/prometheus# vi prometheus.yml


  - job_name: mysqld-exporter
    static_configs:
      - targets:
          - 'mysql1.sqlhjalp.com:9104'
          - 'mariadb1.sqlhjalp.com:9104'
          - 'ps1.sqlhjalp.com:9104'
          - 'pxc1.sqlhjalp.com:9104'
          - 'pxc2.sqlhjalp.com:9104'
          - 'pxc3.sqlhjalp.com:9104'
          - 'ndb1.sqlhjalp.com:9104'
          - 'ndb2.sqlhjalp.com:9104'
          - 'ndb3.sqlhjalp.com:9104'
        labels:
          country: US
          db_env: 'home'
          environment: "demo"
          linux: debian
          nodeuse: server


mysql_up{environment="demo"}

Element	Value
mysql_up{country="US",db_env="home",environment="demo",instance="mariadb1.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="mysql1.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="ndb1.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="ndb2.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="ndb3.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="ps1.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="pxc1.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="pxc2.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1
mysql_up{country="US",db_env="home",environment="demo",instance="pxc3.sqlhjalp.com:9104",job="mysqld-exporter",linux="debian",nodeuse="server"}	1

Vault Account SETUP 

mysql> CREATE ROLE IF NOT EXISTS vaultaccess;
mysql>  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD,   PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `vaultaccess`@`%` WITH GRANT OPTION;
 
CREATE USER `vaultadmin`@`%` IDENTIFIED BY '<PASSWORDHERE>' DEFAULT ROLE `vaultaccess`@`%` REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT ;
mysql> show grants for vaultadmin;

CREATE ROLE  IF NOT EXISTS  READONLY;
GRANT SELECT,  EXECUTE  ON *.* TO `READONLY`@`%` ;

Mariadb

MariaDB [(none)]> CREATE USER `vaultadmin`@`%` IDENTIFIED BY '<PASSWORDHERE>';
MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO `vaultadmin`@`%` WITH GRANT OPTION;

Vault Database engine - repeated per db instance 

vault write database/config/MYSQL plugin_name=mysql-database-plugin connection_url="{{username}}:{{password}}@tcp(mysql1.sqlhjalp.com:3306)/" allowed_roles="my-role" username="vaultadmin" password="<PASSWORDHERE>"
vault read database/config/MYSQL Key Value --- ----- allowed_roles [] connection_details map[backend:database connection_url:{{username}}:{{password}}@tcp(mysql1.sqlhjalp.com:3306)/ max_connection_lifetime:0s max_idle_connections:0 max_open_connections:4 username:vaultadmin] disable_automated_rotation false password_policy n/a plugin_name mysql-database-plugin plugin_version n/a root_credentials_rotate_statements [] rotation_period 0s rotation_schedule n/a rotation_window 0 skip_static_role_import_rotation false verify_connection true vault read database/roles/DEMOREADONLY Key Value --- ----- creation_statements [CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}'; GRANT SELECT ON *.* TO '{{name}}'@'%';] credential_type password db_name MYSQL default_ttl 1h max_ttl 24h renew_statements [] revocation_statements [DROP USER IF EXISTS '{{name}}'@'%';] rollback_statements []

Vault dynamic user example 

vault read database/creds/DEMOREADONLY
Key                Value
---                -----
lease_id           database/creds/DEMOREADONLY/1SACMdnTGXseMewbA6ek1T42
lease_duration     1h
lease_renewable    true
password           -piWu8YfOFxUkAqR347a
username           v-userpass-k-DEMOREADON-HFRYaNGE


mysql> show grants for 'v-userpass-k-DEMOREADON-HFRYaNGE'@'%';
+---------------------------------------------------------------+
| Grants for v-userpass-k-DEMOREADON-HFRYaNGE@%                 |
+---------------------------------------------------------------+
| GRANT SELECT ON *.* TO `v-userpass-k-DEMOREADON-HFRYaNGE`@`%` |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

In addition..... 

POSTGRESQL

https://docs.vultr.com/how-to-install-postgresql-on-debian-12

# apt install -y postgresql-common
# /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# apt update
# apt-cache  policy postgresql
# apt install postgresql -y
# systemctl start postgresql
# systemctl status postgresql
# sudo -u postgres psqlsudo -u postgres psql
# postgres=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD '<password>';
ALTER ROLE
# 

Oracle Database XE 


wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
wget https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm

# yum install ./oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm  ./oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm

# /etc/init.d/oracle-xe-21c configure

$ export ORACLE_SID=XE 
$ export ORAENV_ASK=NO 
$ . /opt/oracle/product/21c/dbhomeXE/bin/oraenv

ORACLE_HOME = [] ? /opt/oracle/product/21c/dbhomeXE
The Oracle base has been set to /opt/oracle


[root@localhost ~]# echo $ORACLE_HOME
/opt/oracle/product/21c/dbhomeXE
[root@localhost ~]# cd  $ORACLE_HOME
[root@localhost dbhomeXE]# pwd
/opt/oracle/product/21c/dbhomeXE

[root@localhost dbhomeXE]# cd bin 

[root@localhost bin]# sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Apr 3 10:35:15 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

SQL>

SQL> CONNECT SYS AS SYSDBA
Enter password:
Connected.
SQL>

SQL> set linesize 1500
SQL> select username, account_status from DBA_USERS;

USERNAME															 ACCOUNT_STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------
SYS																 OPEN
SYSTEM																 OPEN
XS$NULL 															 LOCKED
OJVMSYS 															 LOCKED
LBACSYS 															 LOCKED
OUTLN																 LOCKED
DBSNMP																 LOCKED
APPQOSSYS															 LOCKED
DBSFWUSER															 LOCKED
GGSYS																 LOCKED
ANONYMOUS															 LOCKED
 
SQL> QUIT
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQLSERVER 

# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2022.repo
# yum install -y mssql-server
# /opt/mssql/bin/mssql-conf setup
# systemctl status mssql-server

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

# yum install -y mssql-tools18 unixODBC-devel

# yum check-update
# yum update mssql-tools18

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile
source ~/.bash_profile

# /opt/mssql/bin/mssql-conf set-sa-password
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

# sqlcmd -S localhost -No  -U sa

# sqlcmd -S localhost -No  -U sa
Password:
1> CREATE DATABASE TestDB;
2> SELECT Name FROM sys.databases;
3> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB

(5 rows affected)


USE TestDB;

CREATE TABLE dbo.Inventory
(
    id INT, name NVARCHAR (50),  quantity INT, PRIMARY KEY (id)
);
GOSH

INSERT INTO dbo.Inventory VALUES (1, 'banana', 150);
INSERT INTO dbo.Inventory VALUES (2, 'orange', 154);

GO


SELECT * FROM dbo.Inventory WHERE quantity > 152;
GO

1> SELECT * FROM dbo.Inventory;
2> GO
id          name                                               quantity
----------- -------------------------------------------------- -----------
          1 banana                                                     150
          2 orange                                                     154

MONGODB - PERCONA

https://docs.percona.com/percona-server-for-mongodb/8.0/install/apt.html

# apt install -y gnupg2 gnupg curl 
# wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
# dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
# percona-release --help | grep psmdb
psmdb36 psmdb40 psmdb42 psmdb44 psmdb60 psmdb50 psmdb70 psmdb80 psmdb40
psmdb60pro psmdb70pro
psmdb-70-pro psmdb-60-pro
psmdb-36 psmdb-40 psmdb-42 psmdb-44 psmdb-60 psmdb-50 psmdb-70 psmdb-80 psmdb40
psmdb-60-pro psmdb-70-pro
psmdb-70-pro psmdb-60-pro

# percona-release enable psmdb-80 release
# apt update
# apt install percona-server-mongodb
# apt-cache madison percona-server-mongodb
# ls -ltr /etc/mongod.conf
-rw-r--r-- 1 root root 1403 Feb 11 23:56 /etc/mongod.conf

# vi /etc/systemd/system/enable-transparent-huge-pages.service
# cat  /etc/systemd/system/enable-transparent-huge-pages.service
[Unit]
Description=Enable Transparent Hugepages (THP)
DefaultDependencies=no
After=sysinit.target local-fs.target
Before=mongod.service

[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo always | tee /sys/kernel/mm/transparent_hugepage/enabled > /dev/null && echo defer+madvise | tee /sys/kernel/mm/transparent_hugepage/defrag > /dev/null && echo 0 | tee /sys/kernel/mm/transparent_hugepage/khugepaged/max_ptes_none > /dev/null && echo 1 | tee /proc/sys/vm/overcommit_memory > /dev/null'

[Install]
WantedBy=basic.target

# systemctl daemon-reload
# systemctl start enable-transparent-huge-pages
# cat /sys/kernel/mm/transparent_hugepage/enabled && cat /sys/kernel/mm/transparent_hugepage/defrag && cat /sys/kernel/mm/transparent_hugepage/khugepaged/max_ptes_none && cat /proc/sys/vm/overcommit_memory
[always] madvise never
always defer [defer+madvise] madvise never
0
1

# systemctl enable enable-transparent-huge-pages
# ls -lr /var/lib/mongodb/
total 0


# systemctl start mongod
# systemctl status mongod
# mongosh
Current Mongosh Log ID:	67ed4eb0cd874b942d98ebcf
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.3.2
MongoNetworkError: connect ECONNREFUSED 127.0.0.1:27017

MONGODB

https://www.mongodb.com/docs/manual/tutorial/install-mongodb-on-debian/

 
# apt-get install gnupg curl
# curl -fsSL https://www.mongodb.org/static/pgp/server-8.0.asc | \
   sudo gpg -o /usr/share/keyrings/mongodb-server-8.0.gpg \
  --dearmor
# echo "deb [ signed-by=/usr/share/keyrings/mongodb-server-8.0.gpg ] http://repo.mongodb.org/apt/debian bookworm/mongodb-org/8.0 main" | sudo tee /etc/apt/sources.list.d/mongodb-org-8.0.list 
# apt-get update
# apt-get install -y mongodb-org
# systemctl daemon-reload
# ulimit -c unlimited
# ulimit -n 64000
# ulimit -f unlimited
# ulimit -t unlimited
# ulimit -l unlimited
# ulimit -m unlimited
# ulimit -u 64000
# ulimit -a
real-time non-blocking time  (microseconds, -R) unlimited
core file size              (blocks, -c) unlimited
data seg size               (kbytes, -d) unlimited
scheduling priority                 (-e) 0
file size                   (blocks, -f) unlimited
pending signals                     (-i) 15471
max locked memory           (kbytes, -l) unlimited
max memory size             (kbytes, -m) unlimited
open files                          (-n) 64000
pipe size                (512 bytes, -p) 8
POSIX message queues         (bytes, -q) 819200
real-time priority                  (-r) 0
stack size                  (kbytes, -s) 8192
cpu time                   (seconds, -t) unlimited
max user processes                  (-u) 64000
virtual memory              (kbytes, -v) unlimited
file locks                          (-x) unlimited

# systemctl start mongod

# wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
# dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
# percona-release --help | grep psmdb
psmdb36 psmdb40 psmdb42 psmdb44 psmdb60 psmdb50 psmdb70 psmdb80 psmdb40
psmdb60pro psmdb70pro
psmdb-70-pro psmdb-60-pro
psmdb-36 psmdb-40 psmdb-42 psmdb-44 psmdb-60 psmdb-50 psmdb-70 psmdb-80 psmdb40
psmdb-60-pro psmdb-70-pro
psmdb-70-pro psmdb-60-pro

# percona-release enable psmdb-80 release
# apt update
# apt install percona-server-mongodb
# apt-cache madison percona-server-mongodb
# ls -ltr /etc/mongod.conf
-rw-r--r-- 1 root root 1403 Feb 11 23:56 /etc/mongod.conf

# vi /etc/systemd/system/enable-transparent-huge-pages.service
# cat  /etc/systemd/system/enable-transparent-huge-pages.service
[Unit]
Description=Enable Transparent Hugepages (THP)
DefaultDependencies=no
After=sysinit.target local-fs.target
Before=mongod.service

[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo always | tee /sys/kernel/mm/transparent_hugepage/enabled > /dev/null && echo defer+madvise | tee /sys/kernel/mm/transparent_hugepage/defrag > /dev/null && echo 0 | tee /sys/kernel/mm/transparent_hugepage/khugepaged/max_ptes_none > /dev/null && echo 1 | tee /proc/sys/vm/overcommit_memory > /dev/null'

[Install]
WantedBy=basic.target

# systemctl daemon-reload
# systemctl start enable-transparent-huge-pages
# cat /sys/kernel/mm/transparent_hugepage/enabled && cat /sys/kernel/mm/transparent_hugepage/defrag && cat /sys/kernel/mm/transparent_hugepage/khugepaged/max_ptes_none && cat /proc/sys/vm/overcommit_memory
[always] madvise never
always defer [defer+madvise] madvise never
0
1

# systemctl enable enable-transparent-huge-pages
# ls -lr /var/lib/mongodb/
total 0


# systemctl start mongod
# systemctl status mongod
# mongosh
Current Mongosh Log ID:	67ed4eb0cd874b942d98ebcf
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.3.2
MongoNetworkError: connect ECONNREFUSED 127.0.0.1:27017


CASSANDRA

https://docs.vultr.com/how-to-install-apache-cassandra-on-debian-12

# apt update
# cat  /etc/apt/source.list
deb https://deb.debian.org/debian bookworm main non-free-firmware
deb http://deb.debian.org/debian bookworm-updates main
deb http://deb.debian.org/debian-security bookworm-security main
deb http://deb.debian.org/debian unstable main non-free contrib 

# apt update
# apt install curl
# apt install openjdk-17-jdk
# java --version
openjdk 17.0.14 2025-01-21
OpenJDK Runtime Environment (build 17.0.14+7-Debian-1deb12u1)
OpenJDK 64-Bit Server VM (build 17.0.14+7-Debian-1deb12u1, mixed mode, sharing)

# echo "deb [signed-by=/etc/apt/keyrings/apache-cassandra.asc] https://debian.cassandra.apache.org 41x main" | sudo tee -a /etc/apt/sources.list.d/cassandra.sources.list
# curl -o /etc/apt/keyrings/apache-cassandra.asc https://downloads.apache.org/cassandra/KEYS
# apt update
# apt install cassandra

# ls -lr /etc/cassandra/cassandra.yaml
-rw-r--r-- 1 root root 91468 Jan 27 07:28 /etc/cassandra/cassandra.yaml

# vi /etc/cassandra/cassandra.yaml
# systemctl restart cassandra

# ls -lr /var/log/cassandra/
total 0

# systemctl status cassandra
# nodetool status
nodetool: Failed to connect to '127.0.0.1:7199' - ConnectException: 'Connection refused'.

# cqlsh -u cassandra -p cassandra

Warning: Using a password on the command line interface can be insecure.
Recommendation: use the credentials file to securely provide the password.

Connection error: ('Unable to connect to any servers', {'127.0.0.1:9042': ConnectionRefusedError(111, "Tried connecting to [('127.0.0.1', 9042)]. Last error: Connection refused")})





Saturday, February 24, 2024

MariaDB Encryption ( data at rest )

Here is a simple example showing a couple options for MariaDB Encryption. 

You have to consider what you want to encrypt . The data communication (data in transit) or the data on the instance (data at rest). 

This post is going to focus on the data at rest option using a AWS free tier node running on Amazon Linux. I will be using the world database on 2 different instances to show updating current tables with encryption as well as new loading tables to be auto-encrypted. 


1st we will start with installs.. quick and simple just for this demo. 

 # yum -y install mariadb105-server
# vi /etc/my.cnf.d/mariadb-server.cnf
# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id 
server_id=100

# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=200
# systemctl start mariadb.service
# mysql
MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using  EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.23-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db     characterset: latin1
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 sec
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+


We will load the world db into server_id 100 instance. 


# mysql < world.sql
# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)  


Now we can see that currently, both instances are not using encryption. 


MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)


Now across both systems, I am going to set up Random Keys and encrypt them. 

#  mkdir /etc/mysql/

#  mkdir /etc/mysql/encryption/
# (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
# (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
#  (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile

# cat /etc/mysql/encryption/keyfile
1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5
2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096
100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6

# cat /etc/mysql/encryption/keyfile
1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a
2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec
100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
openssl enc -aes-256-cbc -md sha1    -pass file:/etc/mysql/encryption/keyfile.key    -in /etc/mysql/encryption/keyfile    -out /etc/mysql/encryption/keyfile.enc

 ls -ltr /etc/mysql/encryption/
total 12
-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile
-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key
-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc


Now we can set up the cnf file to enable the plugin as well as options for encryption. 


# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC

## InnoDB/XtraDB Encryption Setup
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables=ON
innodb_encryption_threads=4
innodb_encrypt_tables=ON
innodb_default_encryption_key_id=1

## Aria Encryption Setup
aria_encrypt_tables = ON

## Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON

Restart MariaDB so we can check on the current status. 

systemctl restart mariadb.service
mysql
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| world/city                 |                 1 |              1 |
| world/country              |                 1 |              1 |
| world/countrylanguage      |                 1 |              1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
+----------------------------+-------------------+----------------+
5 rows in set (0.000 sec)


Load up the world data into the server_id 200 instance as well. 

# mysql < world.sql
# mysql 
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
| world/city                 |                 1 |              1 |
| world/country              |                 1 |              1 |
| world/countrylanguage      |                 1 |              1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)


According to the information_schema.INNODB_TABLESPACES_ENCRYPTION we are encrypted now. However, they do not show that at the schema level.  While they say it is encrypted if showing up in the INNODB_TABLESPACES_ENCRYPTION table, I would rather be sure and have it seen in the table and on the schema. 

MariaDB [(none)]> show create table world.city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)


Up to this point, you can see that both instances have been accounted for in the INNODB_TABLESPACES_ENCRYPTION schema after the restart or loading of the schema and data. 

So... a few table alters will help here... 


MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.074 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.031 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE countrylanguage  ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.033 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)


This is simple and etc so far.. now we need to enable binlogs and double check more. 

vi /etc/my.cnf.d/mariadb-server.cnf
log_bin=demo

cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin
log_bin=demo

# systemctl restart mariadb.service

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)


Checking via a look at the binlogs.... 


mariadb-binlog--base64-output=DECODE-ROWS --verbose  demo.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 256

# Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


Good to see that it says it is being encrypted now. 


MariaDB [world]> create table city2 like city;
Query OK, 0 rows affected (0.013 sec)

MariaDB [world]> insert into city2 select * from city;
Query OK, 4079 rows affected (0.078 sec)
Records: 4079  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city2\G
*************************** 1. row ***************************
       Table: city2
Create Table: CREATE TABLE `city2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)


I want to see these transactions though in the binlog.. how? You can use mariadb_binlog along with --read-from-remote-server to be able to see the data in the logs...


mariadb-binlog  --base64-output=DECODE-ROWS --verbose --read-from-remote-server   demo.000001 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup
ROLLBACK/*!*/;
# at 256
#240225  0:06:06 server id 100  end_log_pos 296 CRC32 0x0c89f3bb Ignorable
# Ignorable event type 164 (Start_encryption)
# at 296
#240225  0:06:06 server id 100  end_log_pos 325 CRC32 0x535776a2 Gtid list []
# at 325
#240225  0:06:06 server id 100  end_log_pos 363 CRC32 0x2ac4a61b Binlog checkpoint demo.000001
# at 363
#240225  0:09:40 server id 100  end_log_pos 405 CRC32 0x93e10dc4 GTID 0-100-1 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=100*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
# at 405
#240225  0:09:40 server id 100  end_log_pos 501 CRC32 0x39269040 Query thread_id=5 exec_time=0 error_code=0
use `world`/*!*/;
SET TIMESTAMP=1708819780/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_
for_timestamp=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table city2 like city
/*!*/;
# at 501
#240225  0:09:49 server id 100  end_log_pos 543 CRC32 0xde82b753 GTID 0-100-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 543
# at 602
#240225  0:09:49 server id 100  end_log_pos 602 CRC32 0x05bbb9e6 Annotate_rows:
#Q> insert into city2 select * from city
#240225  0:09:49 server id 100  end_log_pos 661 CRC32 0x9e0b4e0d Table_map: `world`.`city2` mapped to number 21
# at 661


Hopefully, this can at least help get you started .... 


Resources:

https://mariadb.com/kb/en/securing-mariadb-encryption/