Categories
Database Backup

** Discover how DevOps engineers and DBAs can detect corrupted database backups before disaster strikes. Learn advanced techniques for PostgreSQL, SQL Server, and MySQL, including automated restore testing and checksum validation.

Dunida maamulka xog-kaydinta (database) iyo injineernimada kalsoonida goobaha (site reliability engineering) ee khatarta badan leh, waxaa jira qaanuun caan ah: Schrödinger’s Backup. Xaaladda kayd kasta oo xog ah lama oga ilaa aad isku daydo inaad soo celiso. Ilaa wakhtigaas, waxay ku jirtaa xaalad quantum ah oo ah mid si dhammaystiran u shaqaynaysa iyo mid gebi ahaanba xumaatay labadaba.

Injineerada DevOps iyo maamulayaasha xog-kaydinta (DBA), ogaanshaha in kayd xogeed oo muhiim ah uu xumaaday inta lagu jiro dhacdo firfircoon waa riyo xun oo aan la soo koobi karin. Waxay u beddeshaa hawlgal soo-celin caadi ah mid keena khasaare xogeed oo baaxad leh. “Gacan-ku-dhiiglahan aamusan” ee daacadnimada xogta inta badan lama dareemo sababtoo ah shaqooyinka kaydinta ayaa inta badan soo sheega Exit Code 0 oo guul ah xitaa marka xogta la kaydiyay ay tahay mid waxyeello gaartay.

Hagahan dhammaystiran, waxaan ku falanqayn doonnaa qaab-dhismeedka musuqmaasuqa kaydinta, waxaan sahamin doonnaa farsamooyinka xaqiijinta ee gaarka u ah xog-kaydinta, waxaana tusi doonnaa sida loo dhiso dhuumo (pipelines) soo-celin oo toos ah oo aan la jebin karin oo loogu talagalay jawiga wax-soo-saarka.

Qaab-dhismeedka Musuqmaasuqa Kaydinta

Si loo ogaado musuqmaasuqa, waa inaad marka hore fahantaa sida uu u dhaco. Musuqmaasuqa kaydinta guud ahaan wuxuu u qaybsamaa laba qaybood: mid jireed (heerka kaabayaasha) iyo mid macquul ah (heerka codsiga).

Musuqmaasuqa Jireed

Musuqmaasuqa jireed wuxuu dhacaa marka xogta dhabta ah ee ku jirta qalabka kaydinta la beddelo. Tani waxay dhici kartaa inta lagu jiro habka akhrinta ee ka imanaya disk-ga isha, inta lagu jiro gudbinta shabakadda, ama marka ay ku nasanayso kaydinta bartilmaameedka.
* Bit Rot: Hoos u dhaca tartiib-tartiibka ah ee qalabka kaydinta ayaa beddeli kara xogta si aamusan.
* Khaladaadka Gudbinta: In kasta oo TCP uu leeyahay checksums, haddana waa kuwo aad u daciif ah (16-bit). Jawiyada xawaaraha sare leh waxay la kulmi karaan musuqmaasuq xogeed oo aamusan oo TCP uu ku guuldareysto inuu qabto.
* Cilladaha Koontaroolaha Kaydinta: Cayayaanka qalabka ee koontaroolayaasha RAID ama marooyinka SAN waxay qori karaan xog qashin ah iyagoo u sheegaya nidaamka hawlgalka (OS) inay guulaysteen.

Musuqmaasuqa Macquulka ah

Musuqmaasuqa macquulka ah ayaa laga yaabaa inuu ka khatar badan yahay sababtoo ah faylka kaydinta laftiisu waa mid dhammaystiran, laakiin xogta ku jirta waa mid jaban.
* Qashin la geliyo, Qashin ayaa ka soo baxaya (GIGO): Haddii xog-kaydintaada nool ay leedahay index xumaaday ama bog jaban, qalabkaaga kaydinta waxaa laga yaabaa inuu si daacad ah u koobiyo boggaas xumaaday. Shaqada kaydintu way guulaysataa, laakiin soo-celintu way fashilmi doontaa ama waxay soo saari doontaa xog-kaydin jaban.
* Wax kala iibsiga aan dhammaystirnayn: Sawirrada heerka fayl-nidaamka ee la qaado iyada oo aan si habboon loo qaboojin I/O-ga xog-kaydinta (tusaale, iyada oo aan la isticmaalin FLUSH TABLES WITH READ LOCK ee MySQL) waxay keenaan bogag jaban iyo xaalado aan la soo celin karin.

Ogaanshaha Firfircoon: Checksums iyo Cryptographic Hashing

Khadka koowaad ee difaaca ka dhanka ah musuqmaasuqa jireed waa xaqiijinta cryptographic. Ku tiirsanaanta cabbirka faylka ama taariikhaha wax-ka-beddelka kuma filna.

Daarista Checksums-ka Heerka Xog-kaydinta

Nidaamyada maamulka xog-kaydinta ee casriga ah (RDBMS) waxay taageeraan checksums-ka heerka bogga. Marka la daarayo, xog-kaydintu waxay xisaabisaa checksum bog kasta ka hor inta aan loo qorin disk-ga. Marka bogga la akhriyo (ama su’aal ama hab kaydin), checksum-ka waa la xaqiijiyaa.

Waayo PostgreSQL, waxaad awood u siin kartaa checksums-ka xogta inta lagu jiro bilowga cluster-ka:

# Bilow cluster cusub oo PostgreSQL ah iyadoo checksums-ka la daaray
initdb --data-checksums -D /var/lib/postgresql/data

Fiiro gaar ah: Haddii aad hore u lahayd cluster PostgreSQL, waxaad isticmaali kartaa utility-ga pg_checksums si aad u daarto adigoon khadka ku jirin (offline).

Waayo Microsoft SQL Server, hubi in PAGE_VERIFY loo dejiyay CHECKSUM (waa default-ka noocyada casriga ah, laakiin mudan in lagu hubiyo nidaamyada duugga ah):

ALTER DATABASE [ProductionDB] SET PAGE_VERIFY CHECKSUM;
GO

Xaqiijinta Kaydinta ee Nasashada

Marka kaydku uu soo dego bartilmaameedkaaga kaydinta, daacadnimadiisa waa in si cryptographic ah loo xaqiijiyaa. Goobaha kaydinta ee ganacsiga sida CloudSave waxay si toos ah u xisaabiyaan oo u xaqiijiyaan SHA-256 hashes ee baloogyada kaydinta inta lagu jiro gudbinta iyo nasashada. Haddii aad maamulayso qoraallo gaar ah (custom scripts), waa inaad tan gacanta ku fulisaa:

# Soo saar SHA-256 hash ka dib abuurista kaydka
sha256sum prod_db_backup.tar.gz > prod_db_backup.tar.gz.sha256

# Xaqiiji hash-ka server-ka kaydinta
sha256sum -c prod_db_backup.tar.gz.sha256

Farsamooyinka Xaqiijinta ee Gaarka u ah Xog-kaydinta

Matoorada xog-kaydinta ee kala duwan waxay bixiyaan qalab asal ah si loo xaqiijiyo daacadnimada agabka kaydinta.

PostgreSQL: pg_verifybackup

Waxaa lagu soo bandhigay PostgreSQL 13, pg_verifybackup waa isbeddel weyn oo loogu talagalay kaydinta jireed ee lagu qaaday pg_basebackup. Waxay akhridaa faylka backup_manifest ee la soo saaray inta lagu jiro kaydinta waxayna xaqiijisaa in dhammaan faylasha ay jiraan iyo in checksums-koodu ay is-waafaqayaan.

# Ku socodsii xaqiijinta tusaha kaydinta aasaasiga ah ee jireed
pg_verifybackup /mnt/backups/postgres/base_backup_20231025/

Haddii hal bit uu ka beddelmo mid ka mid ah faylasha xogta, pg_verifybackup waxay tuuri doontaa khalad halis ah, taas oo u oggolaanaysa nidaamyadaada kormeerka inay isla markiiba uga digaan kooxda DBA.

Microsoft SQL Server: RESTORE VERIFYONLY

SQL Server wuxuu bixiyaa amar asal ah si loo xaqiijiyo daacadnimada jireed ee faylka kaydinta iyada oo aan dhab ahaan loo soo celin. Waxay hubisaa cinwaannada kaydinta waxayna xaqiijisaa checksums-ka bogga (haddii la daaray inta lagu jiro kaydinta).

RESTORE VERIFYONLY 
FROM DISK = 'Z:BackupsProdDB_Full.bak' 
WITH CHECKSUM;

Digniin: RESTORE VERIFYONLY waxay xaqiijinaysaa oo keliya in faylka kaydinta la akhrin karo iyo in checksums-ka jireed ay is-waafaqayaan. Ma dammaanad qaadayso daacadnimada macquulka ah. Si loo hubiyo daacadnimada macquulka ah, waa inaad samaysaa soo-celin dhammaystiran oo aad ku socodsiisaa DBCC CHECKDB.

MySQL / InnoDB: Percona XtraBackup

Jawiyada MySQL, kaydinta jireed waxaa inta badan maamula Percona XtraBackup. Habka kaydintu wuxuu ka kooban yahay koobiyeynta faylasha, laakiin kaydku ma aha mid joogto ah ilaa laga codsado log-yada wax-kala-iibsiga (redo logs). Wejiga --prepare wuxuu u shaqeeyaa sidii hubin daacadnimo oo ku dhex-jira.

# Diyaarinta kaydka waxay codsanaysaa redo logs-ka. 
# Haddii kaydku xumaado, tallaabadani way fashilmi doontaa.
xtrabackup --prepare --target-dir=/data/backups/mysql/

Heerka Dahabka ah: Tijaabinta Soo-celinta ee Tooska ah

Checksums iyo amarada xaqiijinta waa lagama maarmaan, laakiin kuma filna. Habka kaliya ee si dhab ah loo caddeeyo in kaydku uu shaqaynayo waa in la soo celiyo. Jawiyada DevOps ee casriga ah, habkan waa in si buuxda loo toosiyaa.

Adigoo ula dhaqmaya kaydinta sidii koodh, waxaad dhisi kartaa dhuumo CI/CD ah oo loogu talagalay soo-celinta xog-kaydintaada. Dhuumahan waa inay bixiyaan kaabayaal ku-meel-gaar ah, fuliyaan soo-celinta, socodsiiyaan su’aalo xaqiijin ah, oo ay dumiyaan jawiga.

Dhisidda Dhuumo Soo-celin oo Toos ah

Hoos waxaa ku yaal tusaale qoraal Bash ah oo maalin kasta lagu kicin karo shaqo cron ah ama orodyahan CI (sida GitLab CI ama GitHub Actions) si loo xaqiijiyo dump-ka macquulka ah ee PostgreSQL.

#!/bin/bash
set -e

BACKUP_FILE="/mnt/storage/prod_db_latest.dump"
DB_NAME="prod_db"
CONTAINER_NAME="pg_restore_test"

echo "[INFO] Bilowga Tijaabada Soo-celinta Tooska ah..."

# 1. Bilow weel (container) PostgreSQL oo ku-meel-gaar ah
docker run --name $CONTAINER_NAME 
  -e POSTGRES_PASSWORD=testpass 
  -d postgres:15

# Sug inta PostgreSQL uu diyaar noqonayo
echo "[INFO] Sugitaanka xog-kaydinta si ay u bilowdo..."
until docker exec $CONTAINER_NAME pg_isready -U postgres; do
  sleep 2
done

# 2. Abuur xog-kaydinta bartilmaameedka
docker exec $CONTAINER_NAME psql -U postgres -c "CREATE DATABASE $DB_NAME;"

# 3. Fulinta soo-celinta
echo "[INFO] Soo-celinta kaydka..."
docker cp $BACKUP_FILE $CONTAINER_NAME:/tmp/backup.dump
docker exec $CONTAINER_NAME pg_restore -U postgres -d $DB_NAME -1 /tmp/backup.dump

# 4. Socodsii Su'aalaha Xaqiijinta Macquulka ah
echo "[INFO] Socodsiinta su'aalaha xaqiijinta..."
# Hubi haddii miiska isticmaalayaashu uu leeyahay in ka badan 10,000 oo diiwaan
USER_COUNT=$(docker exec $CONTAINER_NAME psql -U postgres -d $DB_NAME -t -c "SELECT COUNT(*) FROM users;")

if [ "$USER_COUNT" -lt 10000 ]; then
    echo "[ERROR] Xaqiijinta macquulka ah way fashilantay. Waxaa la filayay >10000 isticmaale, waxaa la helay $USER_COUNT"
    # Kici digniinta PagerDuty / Slack halkan
    exit 1
else
    echo "[SUCCESS] Xaqiijinta macquulka ah way guulaysatay. Tirada isticmaalaha: $USER_COUNT"
fi

# 5. Dumi jawiga ku-meel-gaarka ah
echo "[INFO] Nadiifinta..."
docker rm -f $CONTAINER_NAME

echo "[INFO] Tijaabada Soo-celinta Tooska ah si guul leh ayay u dhammaatay."

Maxaad tahay inaad xaqiijiso?

Markaad samaynayso tijaabinta soo-celinta tooska ah, ha hubin oo keliya haddii xog-kaydintu bilowdo. Socodsii su’aalo xaqiijin oo gaar u ah codsiga:
1. Tirada Safafka: Hubi in miisaska aasaasiga ah ay leeyihiin tirada safafka la filayo (tusaale, miiska users waa inuusan noqon mid madhan).
2. Xogta Cusub: Weydii diiwaanada la abuuray 24-kii saac ee la soo dhaafay si loo hubiyo in kaydku uusan duugoobin.
3. Daacadnimada Tixraaca: Socodsii qoraallo si aad u hubiso furayaasha shisheeye ee maqan, kuwaas oo tilmaamaya musuqmaasuq macquul ah.

Kormeerka iyo Digniinta ee Cilladaha Kaydinta

Ogaanshaha musuqmaasuqa ka hor inta uusan masiibo dhicin waxay u baahan tahay indho-indhayn adag. Marka laga soo tago xaaladaha guusha/fashilka, waa inaad kormeertaa metadata-ga shaqooyinkaaga kaydinta si aad u ogaato cilladaha.

Kormeerka Heuristic

Ku dar metadata-gaaga kaydinta Prometheus oo ku sawir Grafana. Deji digniino loogu talagalay heuristics-ka soo socda:
* Hoos u dhaca Cabbirka ee Kediska ah: Haddii kaydkaaga maalinlaha ah uu yahay 500GB, oo kaydka maanta uu yahay 50MB, shaqadu waxaa laga yaabaa inay si guul leh u dhammaatay (Exit Code 0), laakiin waxay u badan tahay inay kaydisay schema madhan.
* Cilladaha Muddada: Haddii kaydka oo caadi ahaan qaata 2 saacadood uu ku dhammaado 5 daqiiqo, waxbaa la booday. Taas beddelkeeda, haddii ay qaadato 10 saacadood, waxaa laga yaabaa inaad leedahay hoos u dhac I/O ah oo disk-ga ah kaas oo keeni kara musuqmaasuq.
* Isku-ururinta WAL/Archive Log: Haddii xog-kaydintaadu ay soo saarayso Write-Ahead Logs (WAL) laakiin nidaamka kaydintu uusan u kaydinayn si dhakhso ah, waxaad halis ugu jirtaa farqi ku yimaada silsiladdaada Soo-celinta Waqtiga-gaar ah (PITR).

Hirgelinta Qaanuunka 3-2-1 oo leh Hubinta Daacadnimada

Qaanuunka kaydinta ee 3-2-1 ee warshadaha (3 koobi oo xog ah, 2 warbaahin oo kala duwan, 1 meel ka baxsan goobta) waa mid wax ku ool ah oo keliya haddii dhammaan koobiyada la xaqiijiyo.

Halkan waa meesha ka faa’iidaysiga xal ganacsi sida CloudSave ay si weyn u yarayso culayska hawlgalka. Halkii aad qori lahayd oo aad ilaalin lahayd qoraallo bash oo adag oo loogu talagalay node kasta oo xog-kaydin ah, CloudSave waxay si toos ah ula midoobaysaa kaabayaashaaga si ay u toosiso wareegga nolosha 3-2-1. Waxay bixisaa kayd aan la beddeli karin—oo ka ilaalinaya ransomware—iyo astaamo ku dhex-jira, jadwalka xaqiijinta soo-celinta tooska ah. CloudSave waxay si toos ah u bilaabi kartaa jawi sandbox oo go’doonsan, ku dhejin kartaa kaydka, socodsiin kartaa qoraalladaada xaqiijinta SQL ee gaarka ah, oo ay ku soo celin kartaa heerka caafimaadka dashboard-kaaga dhexe.

Gabagabo

Kaydinta xog-kaydinta ee xumaaday waa gacan-ku-dhiigle aamusan oo burburin kara ganacsiyada. Ku tiirsanaanta oo keliya Exit Code 0 ee qoraalka kaydinta waa khamaar khatar ah.

Si aad si dhab ah u ilaaliso jawigaaga wax-soo-saarka, waa inaad qaadataa istiraatiijiyad difaac-qoto-dheer:
1. Daar checksums-ka heerka bogga ee gudaha matoorkaaga xog-kaydinta.
2. Isticmaal qalabka xaqiijinta ee asalka ah (pg_verifybackup, RESTORE VERIFYONLY) isla markiiba ka dib abuurista kaydka.
3. Kormeero metadata-ga kaydinta (cabbirka, muddada) si aad u ogaato cilladaha heuristic.
4. Hirgeli tijaabinta soo-celinta ee tooska ah oo ku-meel-gaarka ah qayb ka mid ah dhuumahaaga hawlgalka maalinlaha ah.

Adigoo ka beddelaya maskaxda kaydinta ee “dabka qabo oo illow” una beddelaya qaab “xaqiijinta soo-celinta joogtada ah”, waxaad hubinaysaa in marka masiibadu ay si lama huraan ah u dhacdo, xogtaadu ay diyaar tahay, lagu kalsoonaan karo, oo si buuxda loo soo celin karo.

Categories