Automatisk Backup i MySQL

[norsemanGrey]

Heisann!

 

Jeg trenger å få til automatisk backup og purging av en database ved gjevne mellomrom (hver måned). Ønske er at det i slutten av hver måned skal taes backup og sletting av all data fra foregående måned, mens data fra nåværende måned skal bli liggende. Dvs. at f.eks. i slutten av november skal data fra oktober backes opp og slettes fra databasen. Håper på tilbakemld og forslag om hvordan dette kan løses.

[siDDis]

Lag eit batch script som settes inn i ein cronservice

Det finnes mange eksempler på det du vil ha i eksisterandes linuxdistrubusjoner på korleis logg/backup-rotering blir utført.

[quantum]

Sjekk select ... into outfile

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

Etterpå kjører du en delete from ... med samme where-betingelse som select'en.

 

Hvis du vil ha med ddl i fila kan du bruke noe a'la

 

create table october_backup as select ... from datatable where ...

 

og deretter bruke mysqldump mydatabase october_backup fra et shell.

 

Til slutt kjører du delete from datatable where

og drop table october_backup.

 

Where-betingelsen du bruker kan f.eks. være

 

... where log_date < date_sub(date(sysdate()), interval 1 month);

... where month(log_date) < month(sysdate())

 

eller noe lignende. Bare pass på at where-betingelsen plukker ut akkurat de samme dataene til backup som den gjør til sletting etterpå. Bruker du f.eks. sysdate() så er den jo i utgangspunktet noe annet når du kjører backupen, enn når du kjører delete'n etterpå. De to variantene ovenfor funker så lenge du ikke kjører dem hhv. akkurat v. midnatt eller månedsskifte. Alternativt kan du løse dette med et do_backup-felt i tabellen som du først setter til true / 1, deretter tar backup der verdien er 1 og så sletter de samme.

 

For å kjøre dette månedlig må du som en annen nevner benytte en funksjon i operativsystemet ditt, hva nå det måtte være, finner ikke noe særlig om scheduling i MySQL-manualen annet ifm. clustering, så det mangler vel rett og slett? Eller tar jeg feil?

Endret 30. november 2009 av quantum

[norsemanGrey]

Sjekk select ... into outfile

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

Etterpå kjører du en delete from ... med samme where-betingelse som select'en.

 

Hvis du vil ha med ddl i fila kan du bruke noe a'la

 

create table october_backup as select ... from datatable where ...

 

og deretter bruke mysqldump mydatabase october_backup fra et shell.

 

Til slutt kjører du delete from datatable where

og drop table october_backup.

 

Where-betingelsen du bruker kan f.eks. være

 

... where log_date < date_sub(date(sysdate()), interval 1 month);

... where month(log_date) < month(sysdate())

 

eller noe lignende. Bare pass på at where-betingelsen plukker ut akkurat de samme dataene til backup som den gjør til sletting etterpå. Bruker du f.eks. sysdate() så er den jo i utgangspunktet noe annet når du kjører backupen, enn når du kjører delete'n etterpå. De to variantene ovenfor funker så lenge du ikke kjører dem hhv. akkurat v. midnatt eller månedsskifte. Alternativt kan du løse dette med et do_backup-felt i tabellen som du først setter til true / 1, deretter tar backup der verdien er 1 og så sletter de samme.

 

For å kjøre dette månedlig må du som en annen nevner benytte en funksjon i operativsystemet ditt, hva nå det måtte være, finner ikke noe særlig om scheduling i MySQL-manualen annet ifm. clustering, så det mangler vel rett og slett? Eller tar jeg feil?

 

Mange takk for svar! Det var meget nyttig

[quantum]

Mange takk for svar! Det var meget nyttig

where month(log_date) < month(sysdate())

 

... bare husk at denne ikke funker så bra rundt årsskifte da ... velg en annen variant.

[Lars H.]

Hei.

 

Ikke helt svar på spørsmålet, men jeg har et diskusjonsforum, Sagaforumet der jeg lar en cron-job ta backup av databasen hver natt. Denne backup'n zippes, og så sendes den som vedlegg til en mail til en Gmail-konto jeg har. Der er det god plass for vedlegget, og det overskriver ikke det som ble lagt inn forrige natt. En gang i uken sjekker jeg denne mailkontoen og sletter gamle versjoner av backup'n. Dermed har jeg til en hver tid 7 generasjoner av databasen lagret. Kanskje ikke helt svar på spørsmålet til trådstarter, men kanskje et greit tips for andre?

 

Lars H.

Endret 2. desember 2009 av Lars H.

[quantum]

Hei.

 

Ikke helt svar på spørsmålet, men jeg har et diskusjonsforum, Sagaforumet der jeg lar en cron-job ta backup av databasen hver natt. Denne backup'n zippes, og så sendes den som vedlegg til en mail til en Gmail-konto jeg har. Der er det god plass for vedlegget, og det overskriver ikke det som ble lagt inn forrige natt. En gang i uken sjekker jeg denne mailkontoen og sletter gamle versjoner av backup'n. Dermed har jeg til en hver tid 7 generasjoner av databasen lagret. Kanskje ikke helt svar på spørsmålet til trådstarter, men kanskje et greit tips for andre?

 

Lars H.

 

hehe, med litt flaks kan du kjøre spørringer mot databasen din via google ... hver gang de lanserer en ny «feature» i disse cloud-greiene sine ...

 

men det funker vel greit om det ikke er superhemmlige data og du overlever en backup-failure i ny og ne...

[Lars H.]

hehe, med litt flaks kan du kjøre spørringer mot databasen din via google ... hver gang de lanserer en ny «feature» i disse cloud-greiene sine ...

Det der forsto jeg null av, hva slags spørringer er det du snakker om? Og hva mener du med "cloud-greier"?

men det funker vel greit om det ikke er superhemmlige data og du overlever en backup-failure i ny og ne...

Nei, dette er ikke hemmelige data i det hele tatt. Det er innlegg fra brukere på et forum, og disse innleggene er jo til en hver tid tilgjengelig via forumet. En trenger ikke en gang å være registrert på forumet for å lese dette (men en må registrere seg for å skrive innlegg).

 

Lars H.

[quantum]

Det der forsto jeg null av, hva slags spørringer er det du snakker om? Og hva mener du med "cloud-greier"?

I sin iver har google ved enkelte anledninger indeksert opp ymse private brukerdata, som f.eks. mailvedlegg, eller andre dokumenter som folk har lagret hos google, og gjort disse søkbare og synlige via vanlig google-søk. Det hadde jo selvsagt vært synd for deg dersom databasen din hadde inneholdt data som ikke burde vært på avveie. Oppsiden er at du får det hele publisert på nett med et enkelt søkeinterface :o)

[Lars H.]

OK, da forstår jeg. Ser jo også at de fleste søkemotorene stadig er innom forumet i den åpne delen. For litt mer "sensitivt" stoff har vi imidlertid et underforum kun tilgjengelig for påloggede brukere der søkemotorene ikke uten videre kommer inn. Men direkte "hemmelig" stoff, er det ikke snakk om der heller.

 

For overføring av innholdet i selve databasen, kunne jeg selvfølgelig gjort med med SFTP til min egen NAS backup-boks slik jeg gjør for bilder lagt inn på forumet for å hindre Google i å snoke i mailen. Men som sagt, det er ikke snakk om "hemmelige" data i mit tilfelle, så da er vel opplegget mitt greit?

 

Lars H.

Endret 3. desember 2009 av Lars H.

[quantum]

... så da er vel opplegget mitt greit?

Det er greit for meg ;-)

 

Det er nok marginalt enklere å holde seg med færrest mulig backup-metoder, men iom. at du har det oppe å kjøre blir det vel ett fett. Forskjellen på å overføre til nas-box'en og å sende backup̈́en ut av huset er at i førstnevnte tilfelle blir backup'en borte om huset brenner opp, i sistnevnte tilfelle forlater ikke backup'en maskinen dersom internettforbindelsen er nede.

[norsemanGrey]

Sjekk select ... into outfile

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

Etterpå kjører du en delete from ... med samme where-betingelse som select'en.

 

Hvis du vil ha med ddl i fila kan du bruke noe a'la

 

create table october_backup as select ... from datatable where ...

 

og deretter bruke mysqldump mydatabase october_backup fra et shell.

 

Til slutt kjører du delete from datatable where

og drop table october_backup.

 

Where-betingelsen du bruker kan f.eks. være

 

... where log_date < date_sub(date(sysdate()), interval 1 month);

... where month(log_date) < month(sysdate())

 

eller noe lignende. Bare pass på at where-betingelsen plukker ut akkurat de samme dataene til backup som den gjør til sletting etterpå. Bruker du f.eks. sysdate() så er den jo i utgangspunktet noe annet når du kjører backupen, enn når du kjører delete'n etterpå. De to variantene ovenfor funker så lenge du ikke kjører dem hhv. akkurat v. midnatt eller månedsskifte. Alternativt kan du løse dette med et do_backup-felt i tabellen som du først setter til true / 1, deretter tar backup der verdien er 1 og så sletter de samme.

 

For å kjøre dette månedlig må du som en annen nevner benytte en funksjon i operativsystemet ditt, hva nå det måtte være, finner ikke noe særlig om scheduling i MySQL-manualen annet ifm. clustering, så det mangler vel rett og slett? Eller tar jeg feil?

 

 

jeg har kommet over et litt kjedelig problem når det gjelder sletting av data etter backup. Problemet er at backup av dataen kan ta ganske lang tid (avhenging av intervallet jeg tar backup av)og jeg får jo ikke noe tilbakemelding på når databasen er ferdig med å lagre all data til texstfilen, så dermed vet jeg ikke når jeg kan sende DELETE spørringen og få slettet dataen jeg har tatt backup av. Noen forslag til hvordan dette kan løses??

[quantum]

jeg har kommet over et litt kjedelig problem når det gjelder sletting av data etter backup. Problemet er at backup av dataen kan ta ganske lang tid (avhenging av intervallet jeg tar backup av)og jeg får jo ikke noe tilbakemelding på når databasen er ferdig med å lagre all data til texstfilen, så dermed vet jeg ikke når jeg kan sende DELETE spørringen og få slettet dataen jeg har tatt backup av. Noen forslag til hvordan dette kan løses??

 

Kan du ikke bare kjøre alt fra ett script da?

 

La oss si du har scriptene backup.sh og truncate.sql

 

backup.sh kan f.eks. se sånn ut

 

#/bin/sh

mysqldump ... blablabla > dump.sql

mysql ... blabalbla < truncate.sql

EOF

 

og truncate.sql

 

truncate table data1;

truncate table data2;

...

commit;

EOF

 

da lager mysqldump backup først, og så kjøres slettescriptet når backup'en er ferdig.

 

PS: dersom du bruker mysqldump da. bruker du select into outfile blir det jo nesten enda enklere :-)

Endret 4. januar 2010 av quantum

[norsemanGrey]

jeg har kommet over et litt kjedelig problem når det gjelder sletting av data etter backup. Problemet er at backup av dataen kan ta ganske lang tid (avhenging av intervallet jeg tar backup av)og jeg får jo ikke noe tilbakemelding på når databasen er ferdig med å lagre all data til texstfilen, så dermed vet jeg ikke når jeg kan sende DELETE spørringen og få slettet dataen jeg har tatt backup av. Noen forslag til hvordan dette kan løses??

 

Kan du ikke bare kjøre alt fra ett script da?

 

La oss si du har scriptene backup.sh og truncate.sql

 

backup.sh kan f.eks. se sånn ut

 

#/bin/sh

mysqldump ... blablabla > dump.sql

mysql ... blabalbla < truncate.sql

EOF

 

og truncate.sql

 

truncate table data1;

truncate table data2;

...

commit;

EOF

 

da lager mysqldump backup først, og så kjøres slettescriptet når backup'en er ferdig.

 

PS: dersom du bruker mysqldump da. bruker du select into outfile blir det jo nesten enda enklere :-)

 

Jeg bruker SELECT INTO OUTFILE, men spørringen blir bygd basert på en god del logikk i scriptingen (VBScript) i programmet jeg kjører den fra så det blir en god del styr å kjøre dette i et separat script. Jeg vil gjerne ha dette samlet i programmet. Er det noen måte jeg kan få dette til å fungere på da? Backup av store intervaller får programmet jeg kjører spørringen fra bare en "timeout" fra MySQL databasen etter en hvis tid.

[quantum]

Jeg bruker SELECT INTO OUTFILE, men spørringen blir bygd basert på en god del logikk i scriptingen (VBScript) i programmet jeg kjører den fra så det blir en god del styr å kjøre dette i et separat script. Jeg vil gjerne ha dette samlet i programmet. Er det noen måte jeg kan få dette til å fungere på da? Backup av store intervaller får programmet jeg kjører spørringen fra bare en "timeout" fra MySQL databasen etter en hvis tid.

Men hva er problemet med å ha alt i ett script da? Skjønner ikke hva problemet er jeg ... først selectene og så delete'ne.

[norsemanGrey]

Jeg bruker SELECT INTO OUTFILE, men spørringen blir bygd basert på en god del logikk i scriptingen (VBScript) i programmet jeg kjører den fra så det blir en god del styr å kjøre dette i et separat script. Jeg vil gjerne ha dette samlet i programmet. Er det noen måte jeg kan få dette til å fungere på da? Backup av store intervaller får programmet jeg kjører spørringen fra bare en "timeout" fra MySQL databasen etter en hvis tid.

Men hva er problemet med å ha alt i ett script da? Skjønner ikke hva problemet er jeg ... først selectene og så delete'ne.

 

I VBScriptet gjør bygger jeg opp en SQL spørring (SELECT * INTO OUTFILE)for å gjøre backupen. Denne spørringen blir så sendt til databasen. Problemet er at jeg ikke vet når databasen er ferdig med å gjøre backupen slik at jeg trygt kan sende en spørring for å slette dataen jeg har tatt backup av.

[nomore]

Hva mener du med "sendt til databasen"?

 

Om spørringen blir kjørt via en MySQL-kommando i scriptet så vil ikke neste linje i scriptet bli kjørt før MySQL-kommandoen er ferdig.

[quantum]

Hva mener du med "sendt til databasen"?

 

Om spørringen blir kjørt via en MySQL-kommando i scriptet så vil ikke neste linje i scriptet bli kjørt før MySQL-kommandoen er ferdig.

Det var det jeg tenkte også. Medmindre han fyrer av en masse select'er paralellt som bakgrunnsprosesser fra dette vb-scriptet da for å speede opp ting. I såfall blir jo løsningen å ikke gjøre dét ...

[norsemanGrey]

Hva mener du med "sendt til databasen"?

 

Om spørringen blir kjørt via en MySQL-kommando i scriptet så vil ikke neste linje i scriptet bli kjørt før MySQL-kommandoen er ferdig.

 

Jo scriptet og applikasjonen går videre etter at spørringen er sendt til databasen. Applikasjonen vet jo ikke hva MySQL databasen holder på med. I scriptet bygger jeg opp spørringen som en tekststreng og sender den til databasen gjennom en ADODB connection. Det tar jo lang tid å ta backup av databasen. Hvis applikasjonen skulle vente på at database ble ferdig med å utføre kommandoen ville hele programmet stoppet opp.

Endret 6. januar 2010 av SeaWolf

[quantum]

Jo scriptet og applikasjonen går videre etter at spørringen er sendt til databasen. Applikasjonen vet jo ikke hva MySQL databasen holder på med. I scriptet bygger jeg opp spørringen som en tekststreng og sender den til databasen gjennom en ADODB connection. Det tar jo lang tid å ta backup av databasen. Hvis applikasjonen skulle vente på at database ble ferdig med å utføre kommandoen ville hele programmet stoppet opp.

 

Helt umulig å gi deg noen konkrete tips, ingen her kan gjette hva applikasjonen din gjør og ikke gjør.

 

Hvis du gjør et asynkront kall til databasen så skjer vel det i en egen tråd da? I denne tråden kan du kanskje kjøre flere sekvensielle kall til databasen, først selectene, så deletene?

 

Tror nesten du må forklare litt mer detaljert hvordan du har laget greiene.