.::: Enable Dynamic Data Masking (DDM) in MariaDB 12 using view, list view :::.

 

Oh, kalau MariaDB, beda dengan SQL Server. MariaDB tidak memiliki fitur Dynamic Data Masking (MASKED WITH) bawaan seperti SQL Server.

Untuk tabel customer Anda, cara yang paling umum adalah membuat VIEW yang menampilkan data sudah dimasking. Data asli tetap tersimpan di tabel.

1. Data asli

CREATE TABLE customer(
    KODE_CUSTOMER CHAR(6),
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    PRIMARY KEY(KODE_CUSTOMER)
);

SELECT * FROM customer;

2. Buat VIEW untuk data yang dimasking

CREATE VIEW v_customer_masked AS
SELECT
    KODE_CUSTOMER,
    NAMA_CUSTOMER,
    CONCAT(
        LEFT(ALAMAT_CUSTOMER, 3),
        '********'
    ) AS ALAMAT_CUSTOMER,
    KOTA_CUSTOMER,
    CONCAT(
        '########',
        RIGHT(TELEPON_CUSTOMER, 4)
    ) AS TELEPON_CUSTOMER,
    '********' AS TELEPON_CUSTOMER_ALL
FROM customer;
 

.::: Enable Dynamic Data Masking (DDM) SQL Server MSSQL 2025 include using view :::.

 

1. Tabel awal

 
CREATE TABLE customer(
    KODE_CUSTOMER CHAR(6),
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    PRIMARY KEY(KODE_CUSTOMER)
);
 

2. Tambahkan Dynamic Data Masking for telepon & alamat 

ALTER TABLE customer
ALTER COLUMN TELEPON_CUSTOMER
ADD MASKED WITH (FUNCTION = 'partial(0,"XXXXXXX",4)');

ALTER TABLE customer
ALTER COLUMN ALAMAT_CUSTOMER
ADD MASKED WITH (FUNCTION = 'partial(3,"XXXX",3)');

ALTER TABLE customer
ALTER COLUMN KOTA_CUSTOMER
ADD MASKED WITH (FUNCTION = 'default()');
  

.::: Check ETA (Estimated Time of Arrival) restore status include All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera MariaDB MarinaDB - ETA All Table, reinisiasi using Utilisation CPU, Memory, eta_all_table_util.sh,eta_reinisiasi_util.sh :::.

 

correlation with https://teguhth.blogspot.com/2026/09/check-eta-estimated-time-of-arrival.html

1. Total database/folder (result 11 folder or db) <eta_reinisiasi>
 
ls -d /var/lib/mysql/*/ | wc -l
 
[root@teguhth10x ~]# ls -d /var/lib/mysql/*/ | wc -l
12
[root@teguhth10x ~]#

2. sample size a database mariadb (sample result 516132 kib or 528,519,168 bytes) <eta_all_table>
 
du -s /var/lib/mysql/marinadb/
du -sh /var/lib/mysql/marinadb/

#or exclude

du -s --exclude='pembelian.ibd' /var/lib/mysql/marinadb
du -sh --exclude='pembelian.ibd' /var/lib/mysql/marinadb
 
[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]# 
 

.::: Create Store Procedure Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB MarinaDB MySQL Version 2 :::.

 

correlation https://teguhth.blogspot.com/2026/04/create-store-procedure-sync-manual_10.html <diperbarui disini>
correlation https://teguhth.blogspot.com/2026/04/shell-script-sync-manual-table-add.html shell script to check
correlation https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html basic compare 

1. check 

SELECT @@hostname,@@version;

SELECT
    TABLE_SCHEMA,
    TABLE_TYPE,
    COUNT(*) AS total_objek
FROM information_schema.tables
WHERE TABLE_SCHEMA IN ('teguhth', 'aisyahdb')
GROUP BY TABLE_SCHEMA, TABLE_TYPE;
 

2. Hitung jumlah kolom yang perlu dibandingkan

SELECT
    TABLE_SCHEMA,
    COUNT(*) AS total_kolom
FROM information_schema.columns
WHERE TABLE_SCHEMA IN (
    'teguhth',
    'aisyahdb'
)
GROUP BY TABLE_SCHEMA;
 

.::: Store Procedure: Enlarge Table & Database PostgreSQL to 1,000,000 Rows :::.

 

1. table barang 

ALTER TABLE barang
ALTER COLUMN kode_barang TYPE VARCHAR(11);

-- 2. Pastikan KODE_BARANG NOT NULL
ALTER TABLE barang
ALTER COLUMN kode_barang SET NOT NULL;


DROP PROCEDURE IF EXISTS generate_barang(INT);

CREATE OR REPLACE PROCEDURE generate_barang(IN jumlah INT)
LANGUAGE plpgsql
AS $$
DECLARE
    mulai BIGINT;
BEGIN

    SELECT COALESCE(
        MAX(
            NULLIF(
                regexp_replace(kode_barang, '^ELK-', ''),
                ''
            )::BIGINT
        ),
        0
    )
    INTO mulai
    FROM barang
    WHERE kode_barang ~ '^ELK-[0-9]+$';

    RAISE NOTICE 'Nomor ELK terakhir = %', mulai;
    RAISE NOTICE 'Generate % data mulai dari ELK-%',
                 jumlah,
                 mulai + 1;
 

.::: Backup MariaDB mysqldump mariadb-dump using --ignore-table-data, --ignore-table Some Table :::.

  

1. sample create table dummy sample typ*

CREATE TABLE typa (text VARCHAR(100));
CREATE TABLE typb (text VARCHAR(100));
CREATE TABLE typc (text VARCHAR(100));
CREATE TABLE typd (text VARCHAR(100));
CREATE TABLE type (text VARCHAR(100));

INSERT INTO typa (text) VALUES ('typa sample typa');
INSERT INTO typb (text) VALUES ('typb sample typb');
INSERT INTO typc (text) VALUES ('typc sample typc');
INSERT INTO typd (text) VALUES ('typd sample typd');
INSERT INTO type (text) VALUES ('type sample type');
 

.::: Server Resource & MariaDB Health Check local_check, Local_check_bar, Local_check_loop using mpstat, sysstat :::.

 

correlation with 

https://teguhth.blogspot.com/2024/01/shell-script-to-get-cpu-memory-usage.html

1. install mpstat & run 

dnf install -y sysstat

mpstat 1 1 | awk '/Average:/ {printf "%.2f", 100-$NF}'
 

 

.::: Shell Script to Compare MariaDB Restore_log, backup_log with dump .sql include resume log :::.

  

A. Activity test compare 

1. prepare file 

[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# ls -lh
total 296M
-rw-r--r-- 1 root root 1023 Sep 22 14:53 cek_compare_backup_error_basic.sh
-rw-r--r-- 1 root root 1.4K Sep 22 14:50 cek_compare_backup_error_detail.sh
-rw-r--r-- 1 root root 2.0K Sep 22 14:50 cek_compare_backup_error_detail_compres.sh
-rw-r--r-- 1 root root 2.5K Sep 22 14:50 cek_compare_backup_error_detail_compres_enc.sh
-rw-r--r-- 1 root root  973 Sep 22 15:58 restore_error.log
-rw-r--r-- 1 root root 139M Sep 22 14:53 teguhth_ori.sql
-rw-r--r-- 1 root root  19M Sep 22 14:51 teguhth_ori.sql.gz
-rw-r--r-- 1 root root 139M Sep 22 14:52 teguhthenc.sql.enc
[root@teguhth10x mirortest]#
 

.::: Script Check ETA (Estimated Time of Arrival) Backup Database MariaDB on % Percent in .sql or .sql.gz :::.

  

1. check size database (kb)

du -s /var/lib/mysql/teguhth

du -s --exclude='pembelian.ibd' /var/lib/mysql/teguhth

[root@teguhth10x testbackup]# du -sh /var/lib/mysql/teguhth
505M    /var/lib/mysql/teguhth
[root@teguhth10x testbackup]# du -s /var/lib/mysql/teguhth
516132  /var/lib/mysql/teguhth

2. backup in sql or comprese 

mariadb-dump -uroot -pxxx teguhth > teguhth_all.sql
mariadb-dump -uroot -pxxx teguhth | gzip -c > teguhth_all.sql.gz

mariadb-dump -uroot -pxxx marinadb > marinadb_all.sql
mariadb-dump -uroot -pxxx marinadb | gzip -c > marinadb_all.sql.gz
 

.::: Check ETA (Estimated Time of Arrival) restore status include All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera MariaDB MarinaDB - ETA All Final, ETA All Table, Eta Table :::.

 

correlation 
Check ETA (Estimated Time of Arrival) restore status MariaDB MarinaDB on Persent (%) 

Check ETA (Estimated Time of Arrival) restore status All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera 
 

https://teguhth.blogspot.com/2025/10/script-drop-all-database-non-system-in.html

 

1. Total database/folder (result 11 folder or db) <eta_reinisiasi>
 

ls -d /var/lib/mysql/*/ | wc -l

 

[root@teguhth10x ~]# ls -d /var/lib/mysql/*/ | wc -l
12
[root@teguhth10x ~]#

2. sample size a database mariadb (sample result 516132 kib or 528,519,168 bytes) <eta_all_table>
 

du -s /var/lib/mysql/marinadb/
du -sh /var/lib/mysql/marinadb/

#or 

du -s --exclude='pembelian.ibd' /var/lib/mysql/marinadb
du -sh --exclude='pembelian.ibd' /var/lib/mysql/marinadb

 
[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]# 

 

[root@teguhth10x ~]# du -s --exclude='pembelian.ibd' /var/lib/mysql/marinadb
389152  /var/lib/mysql/marinadb
[root@teguhth10x ~]#
[root@teguhth10x ~]# du -sh --exclude='pembelian.ibd' /var/lib/mysql/marinadb
381M    /var/lib/mysql/marinadb
[root@teguhth10x ~]#
 

2 A. jumlah table via file <eta_all_table>

 

find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l

 

[root@teguhth10x ~]# find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l
5
[root@teguhth10x ~]# find /var/lib/mysql/marinadb -maxdepth 1 -type f -name "*.ibd" | wc -l
5
[root@teguhth10x ~]# 

.::: Store Procedure: Enlarge Table & Database MariaDB to 1,000,000 Rows :::.

 

 

correlation with https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

1. table barang 

ALTER TABLE barang MODIFY KODE_BARANG CHAR(10) NOT NULL;

DELIMITER //

CREATE PROCEDURE generate_barang(IN jumlah INT)
BEGIN
    DECLARE i INT;
    DECLARE mulai INT;

    SELECT COALESCE(
        MAX(CAST(SUBSTRING(KODE_BARANG, 5) AS UNSIGNED)),
        0
    )
    INTO mulai
    FROM barang
    WHERE KODE_BARANG LIKE 'ELK-%';
 

.::: Shell Script to Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB some database, table :::.

correlation https://teguhth.blogspot.com/2025/11/grouping-year-month-day-time-date-for.html

1. run check all hardcode
 

2. run check manual 
 

3. script check hardcode

[root@teguhth-all countx]# cat table_count.sh
hostx=$(hostname -s)
ipx='10.10.10.90'
portx='3306'
userdb='admin'
pwddb='xxx'
dbname='teguhth'
tblname='datefilter'
cldatex='datex'
 

.::: Script Scheduling crontab SCP Linux to other server like Backup Daily (run from source server ) :::.

 

1. enable ssh-key 

hostname -I | awk '{print $1}'
ssh-keygen -t rsa
ssh-copy-id -p 22 root@10.10.10.9
 

2. manual command

scp /data/sample/* root@10.10.10.9:/dummy/
scp -P22 /data/sample/*  root@10.10.10.9:/dummy/