.::: DBLink MariaDB Manipulation with Shell Scipt to insert table from Server A to Server B :::.

 

A. sample source server A server B sample 

1. record source & destination

=== source ===
ip       : 10.10.10.90
porr     : 3306
database : dbatools
table    : dbsizeos

=== destination ==
ip       : 10.10.10.9
porr     : 3306
database : dbatools
table    : list_dbsizeos
 
2. create table in server A & server B in dbatools

### server A
use dbatools

CREATE TABLE IF NOT EXISTS dbsizeos (
    capture_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip VARCHAR(45) NOT NULL,
    hostname VARCHAR(100) NOT NULL,
    db_name VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    size_mb DECIMAL(12,2) NOT NULL) ;
    
### server B
use dbatools

CREATE TABLE IF NOT EXISTS list_dbsizeos (
    capture_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip VARCHAR(45) NOT NULL,
    hostname VARCHAR(100) NOT NULL,
    db_name VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    size_mb DECIMAL(12,2) NOT NULL) ;
    
3. create script insert_sizedb.sh in server A 

[root@teguhth-source sizex]# pwd
/data/sizex
[root@teguhth-source sizex]# cat insert_sizedb.sh
#!/bin/bash

MYSQL_USER="admin"
MYSQL_PASS="admin"
MYSQL_HOST="localhost"
MYSQL_DB="dbatools"

BASE_DIR="/var/lib/mysql"

# ambil IP dari OS
IP_OS=$(hostname -I | awk '{print $1}' | sed 's/ //g')

echo "Database Size Report (Folder Based Only)"
echo "======================================="
printf "%-25s %15s %15s\n" "DATABASE" "BYTES" "MB"
echo "------------------------------------------------"

mariadb -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -sNe "
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','performance_schema','mysql','sys');
" | while read dbname; do

    # ambil size folder (KB)
    size_kb=$(du -s ${BASE_DIR}/${dbname} 2>/dev/null | awk '{print $1}')
    size_kb=${size_kb:-0}

    # konversi
    size_bytes=$((size_kb * 1024))
    size_mb=$(awk "BEGIN {printf \"%.2f\", $size_kb/1024}")

    # tampilkan
    printf "%-25s %15s %15s\n" "$dbname" "$size_bytes" "$size_mb"

    # insert ke MariaDB
    mariadb -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} ${MYSQL_DB} -sNe "
    INSERT INTO dbsizeos
        (ip, hostname, db_name, size_bytes, size_mb)
    VALUES
        ('$IP_OS', @@hostname, '$dbname', $size_bytes, $size_mb);
    "

done

echo "------------------------------------------------"
echo -n "TOTAL DATA DIRECTORY: "
du -sh "$BASE_DIR"

[root@teguhth-source sizex]#

4. create script dblink_manipulation.sh in server A 

[root@teguhth-source sizex]# pwd
/data/sizex
[root@teguhth-source sizex]# cat dblink_manipulation.sh
#!/bin/bash

# ===== SOURCE (SERVER A) =====
SRC_HOST="10.10.10.90"
SRC_PORT="3306"
SRC_USER="admin"
SRC_PASS="admin"
SRC_DB="dbatools"

SRC_OS_HOSTNAME=$(hostname)

SRC_REAL_HOSTNAME=$(mariadb \
  -h${SRC_HOST} -P${SRC_PORT} \
  -u${SRC_USER} -p${SRC_PASS} \
  --skip-ssl -B -N -e "SELECT @@hostname;")


# ===== TARGET (SERVER B) =====
DST_HOST="10.10.10.9"
DST_PORT="3306"
DST_USER="admin"
DST_PASS="admin"
DST_DB="dbatools"

echo "Sync dbsizeos (A) -> list_dbsizeos (B)"
echo "====================================="
echo "Source hostname (OS): $SRC_OS_HOSTNAME"

# ambil capture_time terbaru
LATEST_TIME=$(mariadb \
  -h${SRC_HOST} -P${SRC_PORT} \
  -u${SRC_USER} -p${SRC_PASS} \
  ${SRC_DB} \
  --skip-ssl -B -N -e \
"SELECT MAX(capture_time) FROM dbsizeos;")

if [ -z "$LATEST_TIME" ]; then
    echo "? Tidak ada data di Server A"
    exit 1
fi

echo "Latest capture_time : $LATEST_TIME"

# ambil data & insert ke Server B
mariadb \
  -h${SRC_HOST} -P${SRC_PORT} \
  -u${SRC_USER} -p${SRC_PASS} \
  ${SRC_DB} \
  --skip-ssl -B -N -e "
SELECT
    capture_time,
    ip,
    db_name,
    size_bytes,
    size_mb
FROM dbsizeos
WHERE capture_time = '$LATEST_TIME';
" | while IFS=$'\t' read -r capture_time ip db_name size_bytes size_mb
do
    echo "--------------"
    echo "INSERT INTO list_dbsizeos
(capture_time, ip, hostname, db_name, size_bytes, size_mb)
VALUES
('$capture_time', '$ip', '$SRC_OS_HOSTNAME', '$db_name', $size_bytes, $size_mb);"
    echo "--------------"

    mariadb \
      -h${DST_HOST} -P${DST_PORT} \
      -u${DST_USER} -p${DST_PASS} \
      ${DST_DB} \
      --skip-ssl -e "
INSERT INTO list_dbsizeos
    (capture_time, ip, hostname, db_name, size_bytes, size_mb)
VALUES
    ('$capture_time', '$ip', '$SRC_OS_HOSTNAME', '$db_name', $size_bytes, $size_mb);
"
done

echo "? Sync selesai"
[root@teguhth-source sizex]#

B. Simulation

1. run insert_sizedb.sh to record to table dbsizeos
 

2. run dblink_manipulation.sh to transfer from Server A to Server B 

3. check Server B & check list result in table list_dbsizeos