Categories
Database Backup

** Discover the hidden dangers of DIY database backup scripts. Learn why custom Bash scripts fail in production, the risks of logical dumps, and how to secure your data with enterprise solutions.

Wonke uMqondisi weDatha (DBA) kanye neNjiniyela yezinhlelo, ngesikhathi esithile emsebenzini wabo, bake babhala iskripthi se-shell esenziwe ngokwezifiso ukuze benze ikhophi yasenqolobaneni (backup) yedatha. Lokhu kucishe kufane nesiko lokudlula esigabeni esithile. Ezigabeni zokuqala zephrojekthi, umsebenzi olula we-cron owenza i-mysqldump noma i-pg_dump efakwe ku-gzip ubonakala njengesixazululo esihle, esilula, nesingabizi kakhulu.

Nokho, njengoba ingqalasizinda ikhula, imithamo yedatha iyanda, futhi izivumelwano zokusebenza (SLAs) ziba nzima kakhulu, leso skripthi se-Bash esinemigqa eyi-10 sishintsha kancane kancane sibe yibhomu elizayo. Izindawo zokukhiqiza zidinga ukutholakala okuphezulu, izinhloso eziqinile ze-Recovery Point Objectives (RPO), kanye ne-Recovery Time Objectives (RTO) esheshayo. Ukuthembela ezikripthini ze-DIY zokwenza ikhophi yasenqolobaneni kulezi zindawo kuletha izingozi ezinkulu ezihlobene nokuvumelana kwedatha, ukwehluleka okungabonakali, ubuthakathaka bezokuphepha, kanye nezinqubo zokubuyisela ezingalawuleki.

Kulesi sihloko, sizohlaziya amaphutha ezakhiwo kanye nezingozi ezifihliwe zezikripthi ze-DIY zokwenza ikhophi yasenqolobaneni yedatha, sihlole izingibe zobuchwepheshe zokwenza amakhophi anengqondo (logical) uma eqhathaniswa nawomzimba (physical), futhi sixoxe ngokuthi singashintshela kanjani ezixazululweni ezisezingeni lebhizinisi ezifana ne-CloudSave ukuze sivikele idatha yakho ebaluleke kakhulu.

Inkohliso Yokulula: Ukuhlaziya Iskripthi Se-DIY Esijwayelekile

Ukuze siqonde ingozi, kufanele siqale ngokubheka ukwakheka kweskripthi esijwayelekile se-DIY sokwenza ikhophi yasenqolobaneni. Indlela evamile yedatha ye-MySQL ivame ukubukeka kanje:

#!/bin/bash
# Iskripthi Esilula Se-DIY MySQL Backup
BACKUP_DIR="/mnt/backups"
DATE=$(date +%F)
DB_USER="admin"
DB_PASS="SuperSecret123!"

mysqldump -u $DB_USER -p$DB_PASS my_database | gzip > $BACKUP_DIR/mydb_$DATE.sql.gz

# Susa amakhophi asenqolobaneni amadala kunezinsuku ezingama-30
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +30 -exec rm {} ;

Uma ubheka okokuqala, lesi skripthi sifeza umgomo: sikhipha idatha, siyicindezela, futhi silawula ukugcinwa kwayo. Kodwa ngaphansi kwendawo, sigcwele amaphutha abucayi azogcina eholele ekulahlekeni kwedatha endaweni yokukhiqiza.

Ingozi 1: Ukwehluleka Okungabonakali kanye Nogibe Lwe-Pipe

Enye yezingozi ezimbi kakhulu zezikripthi ze-DIY ukwehluleka okungabonakali. Eskripthini esingenhla, umyalo we-mysqldump ufakwa (|) ngokuqondile ku-gzip.

Ku-Bash, isimo sokuphuma se-pipeline yisimo sokuphuma somyalo wokugcina ku-pipeline. Uma iseva yedatha iphelelwa yinkumbulo, ilahlekelwa ukuxhumana, noma ihlangabezana netafula elikhiyiwe phakathi nendawo yokudumpa, i-mysqldump izohluleka futhi ikhiphe iphutha. Nokho, i-gzip izoyicindezela ngempumelelo idatha eyincenye eyitholile futhi iphume nekhodi yesimo engu-0 (impumelelo).

Uhlelo lwakho lokuqapha, oluhlola ikhodi yokuphuma yomsebenzi we-cron, luzobika ukuthi ikhophi yasenqolobaneni iphumelele. Uzoba nefayela le-.gz elivumelekile kudiski, kodwa ngaphakathi kuzoba nefayela le-SQL elinqunyiwe nelingenamsebenzi. Ngeke ukuthole lokhu kuze kube yilapho uzama ukubuyisela idatha ebalulekile.

Ukunciphisa (nemikhawulo yako)

Onjiniyela bavame ukuzama ukulungisa lokhu ngokuvumela ukuphathwa kwamaphutha okuqinile ku-Bash:

set -e
set -o pipefail

Nakuba i-set -o pipefail iqinisekisa ukuthi iskripthi sehluleka uma noma yimuphi umyalo ku-pipeline wehluleka, kusadingeka ukuthi wakhe izindlela eziqinile zokuxwayisa, ukungena ngemvume, kanye nezindlela zokuphinda uzame (retry) ezizungeze iskripthi. Lapho iphutha lenethiwekhi elidlulayo libangela ukwehluleka ngo-2:00 ekuseni, iskripthi se-DIY simane sife. Izinkundla zebhizinisi ziphatha la maphutha adlulayo ngokuzama kabusha okuhlakaniphile nokukhulayo.

Ingozi 2: Ukuvumelana Kwedatha kanye Nezinkinga Zokukhiya

Izikripthi ze-DIY zithembele kakhulu kumakhophi anengqondo (mysqldump, pg_dump). Amakhophi anengqondo akhipha idatha ngokusebenzisa imiyalo ye-SELECT kuwo wonke amatafula. Kudatha yokukhiqiza esebenza kakhulu, idatha iyashintsha njalo. Uma iskripthi sithatha imizuzu engama-45 ukudumpa idatha engu-100GB, idatha ekuqaleni kwedump izobe isidala ngemizuzu engama-45 kunedatha ekugcineni, lokhu kwephula ukuhambisana kwe-ACID.

Ukuhambisana Kokuthengiselana kwe-MySQL

Ukuze uzuze isifinyezo esihambisanayo ku-MySQL usebenzisa i-InnoDB, kufanele udlulise amafulegi athile:

mysqldump --single-transaction --quick --routines --events -u user -p db > dump.sql

Ifulegi le---single-transaction libeka izinga lokuhlukaniswa libe ngu-REPEATABLE READ futhi liqale ukuthengiselana ngaphambi kokudumpa. Nokho, uma idatha yakho isaqukethe amatafula amadala e-MyISAM, leli fulegi ngeke liwavimbele ekukhiyeni, okungenzeka kumise ukufunda/ukubhala kwemikhiqizo ngenkathi ikhophi yasenqolobaneni isebenza. Ngaphezu kwalokho, noma yimiphi imiyalo ye-ALTER TABLE, DROP TABLE, noma RENAME TABLE eyenziwe ngonjiniyela phakathi nekhophi yasenqolobaneni izophula isifinyezo se-REPEATABLE READ, okubangela ukuthi idump yehluleke.

I-PostgreSQL kanye ne-WAL Archiving

Ku-PostgreSQL, i-pg_dump inikeza amakhophi anengqondo ahambisanayo, kodwa amakhophi anengqondo wodwa awakwazi ukunikeza i-Point-in-Time Recovery (PITR). Uma idatha yakho iphahlazeka ngo-4:00 ntambama futhi iskripthi sakho sokugcina se-cron sisebenze phakathi kwamabili, ulahlekelwa idatha yamahora ayi-16.

Ukuzuza i-PITR kudinga ukugcinwa okuqhubekayo kwama-Write-Ahead Logs (WAL). Ukubhala iskripthi se-DIY ukuze uphathe i-archive_command ngokuphepha kunzima kakhulu.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'

Uma indawo yokugcina (/mnt/wal_archive/) igcwala noma ingatholakali, i-archive_command izohluleka. I-PostgreSQL izobe isigcina amafayela e-WAL endaweni kuze kube yilapho idiski eyinhloko igcwala, okubangela ukuphazamiseka okuphelele kwedatha. Izikripthi ze-DIY azivamile ukuba nethuluzi elidingekayo lokuqapha ukuqoqwa kwe-WAL nokuxwayisa abaphathi ngaphambi kokuba kwenzeke ukuphazamiseka.

Ingozi 3: I-Retention Roulette

Bheka emuva kumyalo wokugcina eskripthini sethu sokuqala:

find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +30 -exec rm {} ;

Lokhu kuwumcimbi wokulahleka kwedatha okuyinhlekelele okulindele ukwenzeka. Cabanga ngesimo lapho ushintsho lokumisa lwephula ukuqinisekiswa kwe-mysqldump. Iskripthi sehluleka ukudala amakhophi amasha, kodwa umyalo we-find uyaqhubeka nokusebenza njalo ebusuku, ususa ngokuzikhandla amafayela amadala kunezinsuku ezingama-30.

Ngemuva kwezinsuku ezingama-30 zokwehluleka okuthulile kwekhophi yasenqolobaneni, umyalo we-find uzosusa ikhophi yakho yokugcina enhle. Manje usushiywe namakhophi ayiziro.

Isoftware yebhizinisi yokwenza amakhophi asenqolobaneni efana ne-CloudSave isebenzisa izinqubomgomo zokugcina ezisekelwe esimweni. Iyaqonda umehluko phakathi kokuthi “susa amakhophi amadala kunezinsuku ezingama-30” kanye nokuthi “qinisekisa ukuthi okungenani kukhona amaphuzu okubuyisela ayi-30 aphumelelayo ngaphambi kokususa idatha endala.”

Ingozi 4: Ezokuphepha, Ukubethela, kanye Nezindawo Ezingabonakali Zokuhambisana

Enkathini ye-ransomware kanye nezinhlaka eziqinile zokuhambisana (GDPR, HIPAA, SOC 2), amakhophi asenqolobaneni ayilitshe eliyinhloko. Izikripthi ze-DIY zivame ukwephula izinqubo ezinhle kakhulu zokuphepha:

  1. Izimpawu zokungena ezifakiwe (Hardcoded Credentials): Ukugcina amaphasiwedi edatha ezikripthini ezicacile noma ezincazelweni ze-cron kuyingozi enkulu yezokuphepha. Nakuba amathuluzi afana ne-mysql_config_editor ye-MySQL noma ifayela le-.pgpass le-PostgreSQL elinciphisa lokhu, asadinga ukuphatha amafayela okhiye bendawo kuseva.
  2. Ukuntuleka kokubethela (Encryption) lapho kugcinwe khona: Ukudumpa i-SQL eluhlaza kudiski kushiya i-PII/PHI ebucayi iveziwe.
  3. Ama-pipeline okubethela ayinkimbinkimbi: Ukuzama ukubethela amakhophi asenqolobaneni ngokushesha usebenzisa i-GPG kuletha umthwalo omkhulu we-CPU kanye nezinkinga zokuphatha okhiye.
# I-pipeline ye-DIY ebethelwe
pg_dump mydb | gzip | gpg --symmetric --cipher-algo AES256 --passphrase-file /etc/keys/backup.key > backup.sql.gz.gpg

Uma iseva ikhwantalazwa, umhlaseli ufinyelela kukho kokubili ikhophi ebethelwe kanye nefayela le-/etc/keys/backup.key, okwenza ukubethela kungabi namsebenzi. Ngaphezu kwalokho, uma i-DBA eyadala ukhiye we-GPG ishiya inkampani futhi ukhiye ulahleka, amakhophi asenqolobaneni awakwazi ukubuyiselwa.

Ingozi 5: I-RTO Reality Check (Ukubuyisela Kunzima Kunokwenza Ikhophi)

Isivivinyo sokugcina sekhophi yasenqolobaneni ukubuyisela. Amakhophi anengqondo akhiqizwe yizikripthi ze-DIY aziwa ngokuba kancane ukubuyiselwa. I-SQL dump engu-500GB ingathatha imizuzu eyi-15 ukudalwa, kodwa ukuyibuyisela kudinga injini yedatha ukuthi ihlaziye i-SQL, yakhe kabusha izinkomba, futhi ibale kabusha imikhawulo. Lokhu kungathatha amahora noma ngisho nezinsuku, kuqede i-RTO yakho.

Kuma-database amakhulu okukhiqiza, amakhophi omzimba (ukukopisha amafayela edatha uqobo) ayimpoqo. Nakuba amathuluzi afana ne-Percona XtraBackup noma i-pg_basebackup ekhona, ukuwasongeza ezikripthini ze-Bash ze-DIY kuyinkimbinkimbi kakhulu. Kufanele uphathe izifinyezo ze-LVM, uphathe ukuthula kwesistimu yefayela, futhi uqinisekise ukuthi ikhophi yasenqolobaneni idluliselwa ngaphandle kwesayithi ngaphandle kokugcwalisa isixhumi senethiwekhi.

Isicupho se-LVM Snapshot

Onjiniyela abaningi bazama amakhophi omzimba “angena-downtime” besebenzisa izifinyezo ze-LVM:

# Dala isifinyezo
lvcreate --size 20G --snapshot --name db_snap /dev/vg0/db_vol

# Khweza bese ukopisha
mount /dev/vg0/db_snap /mnt/snap
tar -czf /backups/db_physical.tar.gz /mnt/snap/mysql

Uma idatha ithola ukwanda okungazelelwe ku-I/O yokubhala, isifinyezo se-LVM esingu-20G singagcwalisa ngokushesha. Lapho isifinyezo se-LVM sigcwala, siba yize, futhi ikhophi yasenqolobaneni iyahluleka. Okubi nakakhulu, izifinyezo ze-LVM ezisetshenziswa kakhulu zinganciphisa kakhulu ukusebenza kwe-I/O kwevolumu yedatha eyinhloko, okubangela ukwanda kokubambezeleka kohlelo lokusebenza.

Ukushintshela Ekuvikelweni Kwezinga Lebhizinisi

Ukushintsha kusuka ezikripthini ze-DIY kuya endaweni yebhizinisi kuyingqophamlando ebalulekile yokuvuthwa kwanoma yiliphi ithimba lengqalasizinda. Umgomo uwukusuka “ekuthembeni ukuthi iskripthi sisebenzile” ukuya ekubeni nobufakazi obubetheliwe bokubuyiseka.

Izinkundla ezifana ne-CloudSave ziklanywe ngokukhethekile ukuze ziqede izindawo ezingabonakali zezikripthi ze-DIY. Ngokusebenzisa ama-ejenti aqaphela uhlelo, i-CloudSave isebenzisana ngokuqondile nama-API edatha (MySQL, PostgreSQL, MS SQL, Oracle) ukuze ihlele amakhophi omzimba nawengqondo ahambisanayo ngaphandle kokukhiya amatafula noma ukunciphisa ukusebenza.

Izinzuzo Eziyinhloko Zokusuka Ezikripthini:

  1. Ukuqinisekiswa Okuzenzakalelayo: Izinkundla zesimanje azimane zithathe amakhophi asenqolobaneni; ziyawahlola. I-CloudSave ingakwazi ukuzenzakalelayo ukuvula isibonelo sedatha sesikhashana, ibuyisele ikhophi yasenqolobaneni, iqhube ukuhlolwa kokuvumelana (isb., DBCC CHECKDB), futhi ikudilize, inikeze umbiko oqinisekisiwe wokuthi ikhophi yasenqolobaneni iyasebenziseka ngempela.
  2. Isitoreji Esingenakuguqulwa (Immutable Storage): Ukuze ulwe ne-ransomware, amakhophi asenqolobaneni kufanele angaguquki. Izikripthi ze-DIY azikwazi ukubhala kalula kwisitoreji se-WORM (Bhala Kanye, Funda Okuningi). Izixazululo zebhizinisi zihlanganisa ngokwemvelo ne-S3 Object Lock kanye nesitoreji samafu esingenakuguqulwa, okuqinisekisa ukuthi ngisho noma iseva ikhwantalazwe ngokuphelele, amakhophi asenqolobaneni awakwazi ukususwa noma ukubethelwa ngumhlaseli.
  3. I-PITR Elula: Esikhundleni sokuhlanganisa mathupha ikhophi yesisekelo namakhulu amafayela e-WAL usebenzisa amapharamitha ayinkimbinkimbi e-recovery.conf noma e-postgresql.auto.conf, izinkundla zinikeza umugqa wesikhathi obonakalayo. Umane ukhethe umzuzu oqondile ofuna ukuwubuyisela kuwo, futhi isoftware iphatha ukudlalwa kabusha kwelogi ngokuzenzakalelayo.
  4. Ukunciphisa nokuCindezela (Deduplication and Compression): Izikripthi ze-DIY zithembele ku-gzip, ecindezela ifayela ngalinye ngokwehlukana. Isoftware yebhizinisi yokwenza amakhophi asenqolobaneni isebenzisa ukunciphisa okusezingeni lebhulokhi lomhlaba wonke, inciphisa kakhulu izindleko zesitoreji kanye nomkhawulokudonsa wenethiwekhi lapho idlulisela amakhophi asenqolobaneni ngaphandle kwesayithi.

Isiphetho

Ukubhala iskripthi se-Bash esenziwe ngokwezifiso ukuze wenze ikhophi yasenqolobaneni yedatha kulula. Ukubhala iskripthi esiphatha ukwehluleka kwe-pipeline okuthulile, siqinisekisa ukuhambisana kwe-ACID, siphathe okhiye bokubethela ngokuphepha, sivimbele ukulahleka kwedatha okusekelwe ekugcineni, futhi siqinisekise izivumelwano eziqinile ze-RTO/RPO cishe akunakwenzeka.

Ezindaweni zokukhiqiza, idatha iyimpahla ebaluleke kakhulu yebhizinisi. Ukuphatha ukuvikelwa kwayo njengephrojekthi eseceleni egcinwa yimigqa embalwa yamakhulu yeskripthi se-shell kuyingozi engekho ibhizinisi elingakwazi ukuyikhokhela. Ngokuhlola amasu akho amanje okwenza amakhophi asenqolobaneni, ukuqonda imikhawulo yama-dump anengqondo, nokufudukela ezinkundleni eziqinile, ezizenzakalelayo ezifana ne-CloudSave, amaqembu e-DevOps ne-DBA angakwazi ukuqeda “isici sebhasi” sezikripthi ezenziwe ngokwezifiso futhi aqinisekise ukuthi idatha yawo iyakwazi ukumelana nezimo ngempela.

Categories