Another MySQL DBA: Percona

Showing posts with label Percona. Show all posts

Sunday, March 1, 2026

Does Every PXC Node Need XtraBackup Installed?

One question that surfaces regularly in the Percona forums: Does every node in a Percona XtraDB Cluster (PXC) need to have XtraBackup installed? It's a fair question, especially when managing a mixed environment or trying to minimize the software footprint on certain nodes. Here is what the actual mechanics and testing confirm.

The Short Answer (But Read On)

It depends on what you want that node to do. The nuance matters quite a bit here, so it is worth walking through how State Snapshot Transfer (SST) works in PXC and why XtraBackup's presence — or absence — on a given node is significant.

A Quick Refresher on SST in PXC

When a new node joins a Percona XtraDB Cluster, or when an existing node has been down long enough that Incremental State Transfer (IST) is no longer possible, the cluster performs a State Snapshot Transfer (SST). This is essentially a full data copy from a donor node to the joiner node.

PXC supports multiple SST methods, configured in my.cnf:

[mysqld]
wsrep_sst_method = xtrabackup-v2

The available SST methods include:

xtrabackup-v2 — The recommended method for PXC, using Percona XtraBackup; performs SST without locking the donor for extended periods
clone — Available in PXC 8.0.22+ using MySQL's built-in Clone Plugin; removes the XtraBackup dependency for SST
mysqldump — Slower and locks the donor during transfer; not recommended for production
rsync — Requires the donor to be read-only during transfer, blocking writes; also not recommended for live clusters

The xtrabackup-v2 method has historically been the default approach and remains widely used in existing deployments precisely because it keeps the donor node available for writes during the transfer. The other legacy methods can block writes on the donor, which is generally unacceptable in a production cluster. Note that Percona has been increasingly recommending the clone method for new installations on PXC 8.0.22 and later, as it removes the external tool dependency at the SST layer.

Where Does XtraBackup Need to Be Installed?

When an SST using xtrabackup-v2 is triggered, both the donor and the joiner need XtraBackup installed and accessible. Here is why both sides are involved:

The donor runs XtraBackup to stream the snapshot data outbound
The joiner runs XtraBackup — specifically the xbstream and xbcrypt utilities — to receive and apply that streamed data

If the joiner node does not have XtraBackup installed and you attempt to bring it into the cluster using xtrabackup-v2, the SST will fail. The error log on the joiner will typically show something like this:

[ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2
...
wsrep_sst_xtrabackup-v2: line 522: xbstream: command not found
[ERROR] WSREP: SST failed: 2 (No such file or directory)

That is a clear and unambiguous failure mode. If xbstream is not present on the joiner, the SST will not complete.

What About Nodes That Will Never Be a Joiner?

Technically, if a node will always act as a donor and never needs to rejoin the cluster from scratch, you could argue it only needs XtraBackup in its donor capacity. In practice, however, any node can become a joiner — after a crash, after planned maintenance, or after recovering from a network partition. There is no reliable way to guarantee a node will never need to receive an SST.

The practical guidance here is straightforward: install XtraBackup on every PXC node, without exception. The overhead of having it installed is negligible. The cost of a failed SST during an unplanned outage is not.

The Clone Plugin Alternative (PXC 8.0.22+)

Starting with PXC 8.0.22, Percona added support for the MySQL Clone Plugin as an SST method. This is worth knowing about because it removes the XtraBackup dependency for SST purposes entirely:

[mysqld]
wsrep_sst_method = clone

With the clone method, the Clone Plugin must be loaded on all nodes:

INSTALL PLUGIN clone SONAME 'mysql_clone.so';
SHOW PLUGINS WHERE Name = 'clone';

+-------+--------+-------+----------------+---------+
| Name  | Status | Type  | Library        | License |
+-------+--------+-------+----------------+---------+
| clone | ACTIVE | CLONE | mysql_clone.so | GPL     |
+-------+--------+-------+----------------+---------+

The clone method is a solid option for standardizing without XtraBackup as an SST dependency. That said, XtraBackup still has real value for your external backup strategy regardless of which SST method you choose. SST is a cluster synchronization mechanism — it is not a backup, and it should never be treated as one.

Checking Your Current SST Configuration

You can verify your current SST method and Galera-related settings with:

SHOW VARIABLES LIKE 'wsrep_sst_method';

+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| wsrep_sst_method | xtrabackup-v2 |
+------------------+---------------+

To check cluster state and confirm which node may be acting as donor:

SHOW STATUS LIKE 'wsrep_local_state_comment';

+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+

SHOW STATUS LIKE 'wsrep_connected';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| wsrep_connected | ON    |
+-----------------+-------+

SHOW STATUS LIKE 'wsrep_cluster_size';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Practical Observations

A few things worth noting from working with PXC environments directly:

The version of XtraBackup must match your PXC version. Using XtraBackup 2.x with PXC 8.0 will cause SST failures. Use Percona XtraBackup 8.0 with PXC 8.0, and confirm version alignment after any upgrade.
Even if you switch to the clone SST method, keep XtraBackup installed for scheduled backups. Your backup strategy and your SST method are separate concerns and should be treated as such.
The wsrep_sst_donor variable lets you specify a preferred donor node, which is useful for directing SST away from your busiest or most latency-sensitive member.
If you are running Percona Toolkit alongside PXC, be aware of how DDL replication works in your specific PXC version — Total Order Isolation (TOI) versus Rolling Schema Upgrade (RSU) behavior differs and is worth a dedicated look before running schema changes in production.

Summary

To answer the question directly: if you are using xtrabackup-v2 as your SST method — which remains the default in many existing PXC deployments — then yes, XtraBackup needs to be installed on every cluster member. Any node can be either a donor or a joiner depending on circumstances, and both roles require XtraBackup to be present when using this method.

If you are on PXC 8.0.22 or later and want to eliminate that dependency at the SST layer, the Clone Plugin method is a viable alternative and is increasingly Percona's recommended choice for new deployments. If you are starting fresh, PXC 8.4 LTS is the current long-term support release and the recommended target for new installations. Even when using clone for SST, XtraBackup remains the right tool for your actual backup jobs.

Do not try to save a few megabytes of disk space by skipping XtraBackup on select nodes. The SST failure that eventually results from that decision is not a trade-off worth making.

Resources

Saturday, February 21, 2026

MySQL 8.0 JSON Functions: Practical Examples and Indexing

This post covers a hands-on walkthrough of MySQL 8.0's JSON functions. JSON support has been in MySQL since 5.7, but 8.0 added a meaningful set of improvements — better indexing strategies, new functions, and multi-valued indexes — that make working with JSON data considerably more practical. The following documents several of the most commonly needed patterns, including EXPLAIN output and performance observations worth knowing about.

This isn't a "JSON vs. relational" debate post. If you're storing JSON in MySQL, you probably already have your reasons. The goal here is to make sure you're using the available tooling effectively.

Environment

mysql> SELECT @@version, @@version_comment\G
*************************** 1. row ***************************
        @@version: 8.0.36
@@version_comment: MySQL Community Server - GPL

Testing was done on a VM with 8GB RAM and innodb_buffer_pool_size set to 4G. One housekeeping note worth mentioning: query_cache_type is irrelevant in 8.0 since the query cache was removed entirely. If you migrated a 5.7 instance and still have that variable in your my.cnf, remove it — MySQL 8.0 will throw a startup error.

Setting Up a Test Table

The test table simulates a fairly common pattern — an application storing user profile data and event metadata as JSON blobs:

CREATE TABLE user_events (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  event_data  JSON NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB;

INSERT INTO user_events (user_id, event_data) VALUES
(1, '{"action":"login","ip":"192.168.1.10","tags":["mobile","vpn"],"score":88}'),
(1, '{"action":"purchase","ip":"192.168.1.10","tags":["desktop"],"score":72,"amount":49.99}'),
(2, '{"action":"login","ip":"10.0.0.5","tags":["mobile"],"score":91}'),
(3, '{"action":"logout","ip":"10.0.0.9","tags":["desktop","vpn"],"score":65}'),
(2, '{"action":"purchase","ip":"10.0.0.5","tags":["mobile"],"score":84,"amount":129.00}');

Basic Extraction: JSON_VALUE vs. JSON_EXTRACT

JSON_VALUE() was introduced in MySQL 8.0.21 and is the cleaner way to extract scalar values with built-in type casting. Before that, you were using JSON_EXTRACT() (or the -> shorthand) and casting manually, which works but adds noise to your queries.

-- Pre-8.0.21 approach
SELECT user_id,
       JSON_EXTRACT(event_data, '$.action') AS action,
       CAST(JSON_EXTRACT(event_data, '$.score') AS UNSIGNED) AS score
FROM user_events;

-- Cleaner 8.0.21+ approach
SELECT user_id,
       JSON_VALUE(event_data, '$.action') AS action,
       JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED) AS score
FROM user_events;

Output from the second query:

+---------+----------+-------+
| user_id | action   | score |
+---------+----------+-------+
|       1 | login    |    88 |
|       1 | purchase |    72 |
|       2 | login    |    91 |
|       3 | logout   |    65 |
|       2 | purchase |    84 |
+---------+----------+-------+
5 rows in set (0.00 sec)

The RETURNING clause is genuinely useful. It eliminates the awkward double-cast pattern and makes intent clearer when reading query code later.

Multi-Valued Indexes: The Real Game Changer

This is where 8.0 actually moved the needle for JSON workloads. Multi-valued indexes, available since MySQL 8.0.17, let you index array elements inside a JSON column directly. Here's what that looks like in practice:

ALTER TABLE user_events
  ADD INDEX idx_tags ((CAST(event_data->'$.tags' AS CHAR(64) ARRAY)));

Here is what EXPLAIN shows before and after on a query filtering by tag value:

-- Without the multi-valued index:
EXPLAIN SELECT * FROM user_events
WHERE JSON_CONTAINS(event_data->'$.tags', '"vpn"')\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_events
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where

-- After adding the multi-valued index:
EXPLAIN SELECT * FROM user_events
WHERE JSON_CONTAINS(event_data->'$.tags', '"vpn"')\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_events
   partitions: NULL
         type: range
possible_keys: idx_tags
          key: idx_tags
      key_len: 67
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where

Full table scan down to a range scan. On 5 rows this is trivial, but on a table with millions of rows and frequent tag-based filtering, that difference is significant. The improvement scales directly with table size and query frequency.

One important gotcha: MEMBER OF() and JSON_OVERLAPS() also benefit from multi-valued indexes, but JSON_SEARCH() does not. This matters when choosing your query pattern at design time:

-- This WILL use the multi-valued index:
SELECT * FROM user_events
WHERE 'vpn' MEMBER OF (event_data->'$.tags');

-- This will NOT use it:
SELECT * FROM user_events
WHERE JSON_SEARCH(event_data->'$.tags', 'one', 'vpn') IS NOT NULL;

Aggregating and Transforming JSON

A few aggregation functions worth knowing well:

-- Build a JSON array of actions per user
SELECT user_id,
       JSON_ARRAYAGG(JSON_VALUE(event_data, '$.action')) AS actions
FROM user_events
GROUP BY user_id;

+---------+----------------------+
| user_id | actions              |
+---------+----------------------+
|       1 | ["login","purchase"] |
|       2 | ["login","purchase"] |
|       3 | ["logout"]           |
+---------+----------------------+
3 rows in set (0.01 sec)

-- Summarize into a JSON object keyed by action
SELECT user_id,
       JSON_OBJECTAGG(
         JSON_VALUE(event_data, '$.action'),
         JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED)
       ) AS score_by_action
FROM user_events
GROUP BY user_id;

+---------+--------------------------------+
| user_id | score_by_action                |
+---------+--------------------------------+
|       1 | {"login": 88, "purchase": 72}  |
|       2 | {"login": 91, "purchase": 84}  |
|       3 | {"logout": 65}                 |
+---------+--------------------------------+
3 rows in set (0.00 sec)

JSON_OBJECTAGG() will throw an error if there are duplicate keys within a group. This is worth knowing before you encounter it in a production ETL pipeline. In that case, you'll need to deduplicate upstream or handle it in application logic before the data reaches this aggregation step.

Checking SHOW STATUS After JSON-Heavy Queries

When evaluating query patterns, checking handler metrics is a useful habit:

FLUSH STATUS;

SELECT * FROM user_events
WHERE JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED) > 80;

SHOW STATUS LIKE 'Handler_read%';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_read_first         | 1     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 4     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 6     |
+----------------------------+-------+
7 rows in set (0.00 sec)

The Handler_read_rnd_next value confirms a full scan — no surprise since there's no functional index on the score value. For score-based filtering at scale, a generated column with an index is the right answer:

ALTER TABLE user_events
  ADD COLUMN score_val TINYINT UNSIGNED
    GENERATED ALWAYS AS (JSON_VALUE(event_data, '$.score' RETURNING UNSIGNED)) VIRTUAL,
  ADD INDEX idx_score (score_val);

After adding that, the same query drops to a proper index range scan. Generated columns on JSON fields are available in both MySQL 8.0 and Percona Server 8.0, and they remain the most reliable path for scalar JSON field filtering at any meaningful scale.

If you're running Percona Server, pt-query-digest from the Percona Toolkit is still the most practical way to identify which JSON-heavy queries are actually causing pain in production before you start adding indexes speculatively.

Practical Observations

Multi-valued indexes (8.0.17+) are a long overdue improvement and work well when your query patterns align with JSON_CONTAINS() or MEMBER OF()
JSON_VALUE() with RETURNING (8.0.21+) is cleaner than the old cast-after-extract pattern and worth adopting consistently
Generated columns plus indexes remain the most reliable path for scalar JSON field filtering at scale
Watch for JSON_OBJECTAGG() duplicate key errors in grouped data — it surfaces as a hard error in ETL pipelines and can be easy to miss in testing if your sample data happens to be clean
Always verify index usage with EXPLAIN — the optimizer doesn't always pick up multi-valued indexes in complex WHERE clauses, and it's worth confirming rather than assuming

Summary

MySQL 8.0's JSON improvements are genuinely useful, particularly multi-valued indexes and JSON_VALUE() with type casting. They don't replace good schema design, but for cases where JSON storage is appropriate or inherited, you now have real tools to work with rather than just hoping the optimizer figures it out. The generated column pattern in particular is worth evaluating early if you know certain JSON fields will be used in WHERE clauses regularly.

Useful references:

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")})

Wednesday, May 23, 2018

Proxy MySQL :: HAproxy || ProxySQL & KeepAlived

So when it comes to routing your MySQL traffic several options exist.

HAproxy
MariaDB MaxScale
NGINX
MySQL Router
ProxySQL
You can even still get MySQL Proxy if you wanted it but it is EOF.

Now I have seen HAproxy used more often with clients, it is pretty straight forward to set up. Percona has an example for those interested:

https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/haproxy.html

Personally I like ProxySQL. Percona also has few blogs on this as well

Percona also has ProxySQL version available

https://www.percona.com/downloads/proxysql/

I was thinking I would write up some examples but overall Percona has explained it all very well. I do not want to take anything away from those posts, instead point out that a lot of good information is available via those urls. So instead of rewriting what has already been written, I will create a collection of information for those interested.

First compare and decide for yourself what you need and want. The following link of course is going to be biased towards ProxySQL but it gives you an overall scope for you to consider.

http://www.proxysql.com/compare

If you have a cluster or master to master and you do not care which server the writes vs reads go onto, just as long as you have a connection; then HAproxy is likely a simple fast set up for you.

The bonus with ProxySQL is the ability to sort traffic in a weighted fashion, EASY. So you can have writes go to node 1, and selects pull from node 2 and node 3. Documentation on this can be found here:

https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO)

Yes it can be done with HAproxy but you have to instruct the application accordingly.

https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial

This is handled in ProxySQL based on your query rules.

Now the obvious question here: OK so how do you keep ProxySQL from becoming the single point of failure?

You can invest is a robust load balancer and etc etc etc ... Toss hardware at it.... Or make it easy on yourself and support open source and use KeepAlived. This is VERY easy to set up and all of it is documented again well here:

https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/
http://www.keepalived.org/doc/
To be fair here is an example for keepalived and HAproxy as well
https://andyleonard.com/2011/02/01/haproxy-and-keepalived-example-configuration/

If you ever dealt with lua and mysql-proxy, ProxySQL and Keepalived should be very simple for you. If you still want it for some reason: https://launchpad.net/mysql-proxy

Regardless if you choose HAproxy, ProxySQL or another solution, you need to ensure not to replace once single point of failure with another and keepalived is a great for that. So little reason to not do this if you are using a proxy.

So a few more things on ProxySQL.

If you track hosts that connect to your database via your reporting or monitoring , realize those IPS or hostnames are now going to be the proxy server.
What about all the users you already have in MySQL then? Can you migrate them to proxysql? Yes you can. It takes a few steps but it is do able. Here is an example of this:

https://dba.stackexchange.com/questions/164705/how-to-easily-bring-80-mysql-users-into-proxysql

Make sure you understand the Multi layer configuration system. Save your info to disk!

Can ProxySQL run on the MySQL Default port 3306

Yes Edit the mysql-interfaces

Keep in mind now your max_connections. If you have Max_connections in mysql set to 500, then that is your limit of course for standard users. With ProxySQL you can now spread users across the system and set a max per node. So to help ensure you do not hit 500 connections set the mysql-max_connections a little bit lower than MySQL value.
Take advantage of the Monitor Module and STATS .. Know what is going on with your proxy and traffic.
Take advantage of Query Caching if applicable for your application.

Saturday, June 14, 2014

Installing Percona XtraDB Cluster

So of course Percona has documentation that explain the process. The goal of this blog is to go into a little more detail in hopes that can help someone.

Hyperlinks for review:

CentOS - http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/cenots_howto.html
Ubuntu - http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/ubuntu_howto.html
Installing Percona XtraDB Cluster from Binaries -

http://www.percona.com/doc/percona-xtradb-cluster/5.5/installation.html

Prerequisites

Firewall has been set up to allow connecting to ports 3306, 4444, 4567 and 4568
Stop iptables for internal local networks or adjust iptable rules.


/etc/init.d/iptables stop

SELinux is disabled


echo 0 >/selinux/enforce

vi /etc/selinux/config

Set Up SSH Keys and place into authorized_keys so all the id_rsa.pub values are in authorized_keys on all servers.


# ssh-keygen -t rsa

# cd /root/.ssh/

# cp id_rsa.pub authorized_keys 

# chmod 600  /root/.ssh/authorized_keys

# chmod 700  /root/.ssh/

So I started with a basic server install of CentOS 6.5.


# yum -y install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

# yum -y install http://mirror.pnl.gov/epel/6/x86_64/epel-release-6-8.noarch.rpm

# wget http://www.percona.com/downloads/RPM-GPG-KEY-percona /etc/pki/rpm-gpg/RPM-GPG-KEY-percona 

# wget http://www.percona.com/downloads/RPM-GPG-KEY-percona /etc/pki/rpm-gpg/RPM-GPG-KEY-percona 

# yum -y install socat

To avoid any conflicts I removed the mysql-libs and related dependencies


# rpm -e mysql-libs postfix cronie redhat-lsb-core redhat-lsb-printing redhat-lsb-graphics libcgroup numad redhat-lsb sysstat crontabs cronie-anacron redhat-lsb-compat

Then I installed the Percona Cluster packages.


# yum -y install Percona-XtraDB-Cluster-full-56

[root@node1 ~]#  /etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster)......... SUCCESS! 

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" 

mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"

mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

So we can replace the items we removed per node..


yum -y install postfix cronie redhat-lsb-core redhat-lsb-printing redhat-lsb-graphics libcgroup numad redhat-lsb sysstat crontabs cronie-anacron redhat-lsb-compat

So repeating the above steps will install the packages so that we can configure the cluster next.


[root@node2 ~]# /etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster)......... SUCCESS!

[root@node3 ~]# /etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster)........ SUCCESS!

While we have three instances of MySQL running it is not yet a cluster.

Configuring the nodes

Node 1 /etc/my.cnf


[mysqld]



datadir=/var/lib/mysql

user=mysql



# Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so



# Cluster connection URL contains the IPs of node#1, node#2 and node#3

# node 1 192.168.0.33

# nod3 2 192.168.0.34

# nod3 3 192.168.0.35

wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35



# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW



# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB



# This changes how InnoDB auto increment locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2



# Node #1 address

wsrep_node_address=192.168.0.33



# SST method

#wsrep_sst_method=xtrabackup

 wsrep_sst_method=rsync      #

# wsrep_sst_method=rsync_wan  #

# wsrep_sst_method=mysqldump  # SLOW



# Cluster name

wsrep_cluster_name=percona_cluster



# Authentication for SST method

wsrep_sst_auth="root:<password_here>" 



# server_id

server_id=3232235553  #SELECT INET_ATON('192.168.0.33')



#[client]

socket=/var/lib/mysql/mysql.sock

Starting the 1st Cluster Node


 /etc/init.d/mysql start --wsrep-cluster-address="gcomm://"

Starting MySQL (Percona XtraDB Cluster)...................................... SUCCESS!



[root@node1 mysql]# cat grastate.dat 

# GALERA saved state

version: 2.1

uuid:    97c457f8-f3d2-11e3-9b4e-374ebb7427e6

seqno:   -1

cert_index:

The cluster is only one node at the moment.


mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G

*************************** 1. row ***************************

@@hostname: node1.localdomain

1 row in set (0.01 sec)



*************************** 1. row ***************************

Variable_name: wsrep_cluster_size

        Value: 1

OK now now one is up and running we can start node 2
Node 2 /etc/my.cnf


[mysqld]



datadir=/var/lib/mysql

user=mysql



# Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so



# Cluster connection URL contains the IPs of node#1, node#2 and node#3

# node 1 192.168.0.33

# nod3 2 192.168.0.34

# nod3 3 192.168.0.35

wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35



# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW



# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB



# This changes how InnoDB auto increment locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2



# Node #1 address

wsrep_node_address=192.168.0.34



# SST method

#wsrep_sst_method=xtrabackup

 wsrep_sst_method=rsync      #

# wsrep_sst_method=rsync_wan  #

# wsrep_sst_method=mysqldump  # SLOW





# Cluster name

wsrep_cluster_name=percona_cluster



# Authentication for SST method

wsrep_sst_auth="root:"



# to enable debug level logging, set this to 1

wsrep_debug=1



# server_id

server_id=3232235554  # SELECT INET_ATON('192.168.0.34') 



#[client]

socket=/var/lib/mysql/mysql.sock



 [root@node2 mysql]#/etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster)........................... SUCCESS!

Now compare our values on each node.


mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G

*************************** 1. row ***************************

@@hostname: node1.localdomain

1 row in set (0.01 sec)



*************************** 1. row ***************************

Variable_name: wsrep_cluster_size

        Value: 2



mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G

*************************** 1. row ***************************

@@hostname: node2.localdomain

1 row in set (0.00 sec)



*************************** 1. row ***************************

Variable_name: wsrep_cluster_size

        Value: 2

1 row in set (0.18 sec)

Now we add node 3 into the mix.

Node 3 /etc/my.cnf


[mysqld]



datadir=/var/lib/mysql

user=mysql



# Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so



# Cluster connection URL contains the IPs of node#1, node#2 and node#3

# node 1 192.168.0.33

# nod3 2 192.168.0.34

# nod3 3 192.168.0.35

wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35



# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW



# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB



# This changes how InnoDB auto increment locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2



# Node #1 address

wsrep_node_address=192.168.0.35



# SST method

# wsrep_sst_method=xtrabackup

 wsrep_sst_method=rsync      #

# wsrep_sst_method=rsync_wan  #

# wsrep_sst_method=mysqldump  # SLOW





# Cluster name

wsrep_cluster_name=percona_cluster



# Authentication for SST method

wsrep_sst_auth="root:"



# to enable debug level logging, set this to 1

wsrep_debug=1



# server_id

server_id=3232235555 # SELECT INET_ATON('192.168.0.35')



#[client]

socket=/var/lib/mysql/mysql.sock



[root@node3 mysql]#/etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster)........................... SUCCESS!



[root@node3 mysql]# cat grastate.dat 

# GALERA saved state

version: 2.1

uuid:    97c457f8-f3d2-11e3-9b4e-374ebb7427e6

seqno:   -1

cert_index:

So how do all of our nodes look like now.


 mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G

*************************** 1. row ***************************

@@hostname: node1.localdomain

1 row in set (0.01 sec)



*************************** 1. row ***************************

Variable_name: wsrep_cluster_size

        Value: 3



mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G

*************************** 1. row ***************************

@@hostname: node2.localdomain

1 row in set (0.00 sec)



*************************** 1. row ***************************

Variable_name: wsrep_cluster_size

        Value: 3



mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G

*************************** 1. row ***************************

@@hostname: node3.localdomain

1 row in set (0.00 sec)



*************************** 1. row ***************************

Variable_name: wsrep_cluster_size

        Value: 3

Test the nodes
So now we can load some data and test it out..


[root@node2 ~]# wget http://downloads.mysql.com/docs/world_innodb.sql.gz

[root@node2 ~]# gzip -d world_innodb.sql.gz

[root@node2 ~]# mysql -e "create database world"

[root@node2 ~]# mysql world < world_innodb.sql

SO now that everything is loaded… is it all across the cluster?


     @@hostname: node1.localdomain

DATABASE_SCHEMA: world

         ENGINE: InnoDB

   count_tables: 3

    TOTAL_DB_GB: 0.001



     @@hostname: node2.localdomain

DATABASE_SCHEMA: world

         ENGINE: InnoDB

   count_tables: 3

    TOTAL_DB_GB: 0.001



     @@hostname: node3.localdomain

DATABASE_SCHEMA: world

         ENGINE: InnoDB

   count_tables: 3

    TOTAL_DB_GB: 0.001

Looks like it is working.

Pages

Sunday, March 1, 2026

Does Every PXC Node Need XtraBackup Installed?

The Short Answer (But Read On)

A Quick Refresher on SST in PXC

Where Does XtraBackup Need to Be Installed?

What About Nodes That Will Never Be a Joiner?

The Clone Plugin Alternative (PXC 8.0.22+)

Checking Your Current SST Configuration

Practical Observations

Summary

Resources

Saturday, February 21, 2026

MySQL 8.0 JSON Functions: Practical Examples and Indexing

Environment

Setting Up a Test Table

Basic Extraction: JSON_VALUE vs. JSON_EXTRACT

Multi-Valued Indexes: The Real Game Changer

Aggregating and Transforming JSON

Checking SHOW STATUS After JSON-Heavy Queries

Practical Observations

Summary

Tuesday, April 15, 2025

HOMELAB

Debian BASE

MariaDB 11 Rolling

MySQL Innovation

MySQL Innovation NDB CLuster

Percona Server

Percona Cluster

Setup MySQL Exporters and Prometheus

Prometheus Server

Vault Account SETUP

Vault Database engine - repeated per db instance

Vault dynamic user example

POSTGRESQL

Oracle Database XE

SQLSERVER

MONGODB - PERCONA

MONGODB

CASSANDRA

Wednesday, May 23, 2018

Proxy MySQL :: HAproxy || ProxySQL & KeepAlived

Saturday, June 14, 2014

Installing Percona XtraDB Cluster