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:
- R1 Intel N100 Mini PC 4C/4T,Support 44T(2 * 22T) 2-Bay Storage, 3-in-1 Mini PC 32GB DDR4 1TB NVME SSD Windows WiFi 6 Dual 2.5G LAN Soft Router HTPC
- GMKtec G3 Plus Mini PC, Intel Twin Lake N150(Upgraded N100) 16GB RAM 512GB M.2 2280 SSD, Mini Desktop Computer Dual HDMI/WiFi6/BT5.2
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")})
