Search This Blog

Monday, March 19, 2007

Organize MySQL backups and put to ftp server

So we have very important MySQL databases hosted on FreeBSD OS we do not want to lose.
First step is to create backup procedures to make backups to local disk.
But this will not help us much if server will crash.
As the second step we will put local backups to remote ftp server configured to allow only upload with unique filenames. The download and even delete or overwrite is prohibited.
FTP server configuration is the subject of another post.

I prefer to put my scripts in /usr/local/opt/. Let's name the script "mysql_backup.sh"

# cat > /usr/local/opt/mysql_backup.sh
#!/bin/sh
username=USERNAME
password=PASSWORD
backupdir=/path/to/backup
ftp_to=ftp://host.name.to.put.backups.to/`hostname -s`/db
days=7

### Do not edit below
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin
umask 077
now=`date "+%Y-%m-%dT%H:%M:%S"`

databases=`mysql -u ${username} -p${password} -N -e "show databases"`

for db in ${databases} ; do
mysqldump -u ${username} -p${password} --opt -F -l ${db} > ${backupdir}/${db}-${now} && \
bzip2 -9 ${backupdir}/${db}-${now} && \
ftp -Vu ${ftp_to}/${db}-${now}.bz2 ${backupdir}/${db}-${now}.bz2
done

find ${backupdir} -name '*-*.bz2' -a -type f -mtime +${days} -delete

^D

Script enumerates all databases and then dumps each in backup folder using DBNAME-YEAR-MONTH-DAY_HOUR:MINUTE scheme as the name. After, script archives the dump using bzip2 and upload archive to the ftp server. Last part of the script removes files in backup folder older than, in my case, 7 days.

So, everything is ready, and let's modify /etc/crontab to run script every night at 05:30

# echo "30 5 * * * root /usr/local/opt/mysql_backup.sh" >> /etc/crontab

That's it.

P.S.
Will be wise to run script manually before putting to crontab.

38 comments:

Emin said...

You can use the following command to get plain list of databases:
echo "show databases" | mysql -N

Rauf Kuliyev said...

I've made some minor changes, thanks to Emin.

Vovas said...

Nice article! Many thanks, Rauf!
So, I have one more question (sorry, but I don't genius in shell programming). How can I send mysql backups to mail box?

Thanks!

Anonymous said...

[url=http://cialisnowdirect.com/#evnue]cialis 20 mg[/url] - cialis 10 mg , http://cialisnowdirect.com/#thmhi cialis online without prescription

Anonymous said...

[url=http://viagranowdirect.com/#zjfoq]viagra 150 mg[/url] - cheap viagra , http://viagranowdirect.com/#ntmea order viagra

Anonymous said...

[url=http://levitranowdirect.com/#dosxs]levitra 20 mg[/url] - buy levitra , http://levitranowdirect.com/#fsowi buy levitra online

Anonymous said...

[url=http://buycialispremiumpharmacy.com/#xjafc]buy cheap cialis[/url] - buy cialis online , http://buycialispremiumpharmacy.com/#oyzoh cialis online

Anonymous said...

[url=http://buyviagrapremiumpharmacy.com/#nzecr]buy viagra online[/url] - buy viagra , http://buyviagrapremiumpharmacy.com/#bvogh buy viagra online

Anonymous said...

[url=http://buycialispremiumpharmacy.com/#lpbqt]buy cheap cialis[/url] - buy cheap cialis , http://buycialispremiumpharmacy.com/#sqsrb cialis online

Anonymous said...

[url=http://buyviagrapremiumpharmacy.com/#zyrne]buy cheap viagra[/url] - viagra online , http://buyviagrapremiumpharmacy.com/#gwkxy buy cheap viagra

Anonymous said...

[url=http://viagraboutiqueone.com/#xolap]viagra 120 mg[/url] - cheap viagra online , http://viagraboutiqueone.com/#ndegu cheap viagra

Anonymous said...

[url=http://buyonlineaccutanenow.com/#mrwfk]cheap generic accutane[/url] - generic accutane , http://buyonlineaccutanenow.com/#yvxko accutane no prescription

Anonymous said...

[url=http://viagraboutiqueone.com/#gupof]buy viagra[/url] - cheap viagra , http://viagraboutiqueone.com/#ssukj buy generic viagra

Anonymous said...

[url=http://buyonlineaccutanenow.com/#oeudu]buy accutane online[/url] - cheap accutane online , http://buyonlineaccutanenow.com/#igkox accutane no prescription

Anonymous said...

[url=http://viagraboutiqueone.com/#pjqfj]generic viagra[/url] - buy generic viagra , http://viagraboutiqueone.com/#bdcsz viagra 150 mg

Anonymous said...

[url=http://buyonlineaccutanenow.com/#jnjlk]accutane online without prescription[/url] - buy generic accutane , http://buyonlineaccutanenow.com/#rnohj order accutane

Anonymous said...

[url=http://fastcashloandirectly.com/#smbxz]advance payday loans[/url] - payday loans , http://fastcashloandirectly.com/#wcyqw payday loans

Anonymous said...

Hi, purchase klonopin - purchase klonopin online no prescription http://www.bestpriceklonopin.com/, [url=http://www.bestpriceklonopin.com/]klonopin sale [/url]

Anonymous said...

Hi, order isotretinoin online - buy cheap isotretinoin http://www.twincitiescheap.com/, [url=http://www.twincitiescheap.com/]purchase isotretinoin [/url]

Anonymous said...

Li, cheap neurontin - order neurontin http://www.neurontinonlineprice.net/, [url=http://www.neurontinonlineprice.net/]gabapentin cost[/url]

Anonymous said...

Li, buy fluoxetine online no prescription - buy prozac online no prescription http://www.prozacorder365.net/, [url=http://www.prozacorder365.net/]fluoxetine online no prescription [/url]

Anonymous said...

Hi, buy fluoxetine - prozac medication http://www.hawtalta.com/, [url=http://www.hawtalta.com/]prozac cost[/url]

Anonymous said...

4, Maxalt Cost - buy cheap maxalt http://www.maxaltrxonline.net/, [url=http://www.maxaltrxonline.net/]Buy Rizatriptan [/url]

Anonymous said...

4, Buy Lamisil - lamisil cost http://www.lamisilfast24.net/, [url=http://www.lamisilfast24.net/] Buy Lamisil [/url]

Anonymous said...

4, Eszopiclone Online - eszopiclone online http://www.lunestasleepaid.net/, [url=http://www.lunestasleepaid.net/] Lunesta Cost [/url]

Anonymous said...

5, [url=http://www.costofeffexor.net/]effexor xr no prescription [/url] - buy venlafaxine er - generic effexor xr http://www.costofeffexor.net/.

Anonymous said...

12, [url=http://www.solarcraftaudio.com/]purchase gabapentin[/url] - neurontin sale - neurontin price http://www.solarcraftaudio.com/ .

Anonymous said...

ways. On the other hand, both countries are ethnically diverse, economically developed, cialis cialis price,cialis online cialis price, viagra cialis price,buy viagra generic cialis
http://genericcialispricegnf.com#cialis online http://cialisonlinepillsgkd.com#cialis pills http://genericviagraonlinejvrg.com#viagra online http://buyviagraonlinertvr.com#viagra

Anonymous said...

stimulates an enzyme that catalyzes formation of cGMP from the nucleotide generic cialis cialis,cialis generic cialis, generic viagra cialis,viagra online generic cialis
http://genericcialispricegnf.com#cialis online http://cialisonlinepillsgkd.com#cialis pills http://genericviagraonlinejvrg.com#viagra http://buyviagraonlinertvr.com#viagra online

Anonymous said...

Best of, Florida adjusters may later on submit an application for non resident licenses in other states that provide a reciprocal licensing standard. The companies generate these exclusive leads through telemarketing and direct e-mailing. The larger the deductible, time frame your premium is planning to be, but just be sure you can pay the higher deductible in the event you file an incident. vVOYFut, [url=http://www.igetcheapcarinsurance.com/auto-insurance-in-margate-fl/]car insurance companies[/url], GygfloX, [url=http://www.igetcheapcarinsurance.com/car-insurance-estimate-malta/]auto insurance quotes[/url], USCtUgW, [url=http://www.igetcheapcarinsurance.com/vehicle-insurance-bajaj/]car insurance quotes[/url], CvBIreh,

Anonymous said...

Is your 401k, pension, or retirement plan enough to look at care of your families future needs. You consider 10, 15 or twenty years of insurance coverage coverage using a no exam term life insurance policy. And if for example, you're being quoted reduced of $80 monthly, think about it as $20 a week. yrwxVvg, group term life insurance tax deduction, KwDWcSh, life insurance for elderly mother, UoxpUxy, universal life insurance with allstate, JYYKaAf,

Anonymous said...

Blogger: Rauf Kuliyev's notes - Post a Comment [url=http://www.prednosineonline4sale.net/] Generic Prednisone [/url] order prednisolone - Buy Prednisolone Online - purchase prednisone [url=http://www.buymaxaltrxonline.net/]maxalt online no prescription [/url] buy maxalt online no prescription - purchase maxalt online - maxalt online pharmacy - http://www.buymaxaltrxonline.net/ [url=http://www.somarxfastrelief.net/] Carisoprodol Online [/url] buy soma online - Order Soma Online - soma online [url=http://www.somarxfastrelief.net/] Buy Soma [/url] buy soma - Soma Without Prescriptions - buy soma online no prescription

No Deposit Bonus Poker said...

bonusy bez własnego depozytu
kostenlos 3d pokern lernen ohne geld pokersource
freeroll odds calculator riva gratis dollars
jeu de pokerstrategy en ligne argent
No download Holdem Poker link exchange
capitale di partenza bonus pokernetonline soldi
starting capital
ilmainen bonus free cashable play bez depozita
bonus bez depozytu
spelen poker no limit pokersource yourpokercash
The game is conducted with a semi-circular table with seating for 3-5 players, sitting in front of the dealer. Caribbean Poker is played with a standard deck of 52 cards dealt from the shoe . The aim of the game is compiling higher than the dealer of the five cards that each player receives . The game is played in a system player vs. dealer , individual players do not compete against each other .

Free Poker Money said...

In most casinos caribbean poker game is varied progressive jackpot bonus pool that is cumulative . Its current height is displayed on light boards around the tables or table poker for online casinos . Jackpot prize may relate to a particular casino , online casino or operator of the Internet. Before each player has three fields that can bet on each hand : Jackpot field , field and field Ante Department (Bet box) . If you want to play for the pot accumulated , field Jackpot bet a fixed amount determined by the casino, independent of the height of our site.
party full tilt pokerstars link exchange
senza deposito
pokerbonussen uten innskudd jugar al holdem dollars
sans depot
pocetni kapital poker texas holdem bankrollmob
bonusy bez własnego depozytu
badugi pokern regeln gratis geld
freeroll odds calculator riva gratis dollars
Check (call) is inserted into the pot the same amount of money every player who played or bet. If this happens during the final round of betting, the player who first bet, you must show your cards to the player cleared.

fangyaoting said...

20180616 junda
bucks jerseys
pandora
adidas soccer shoes
oakley sunglasses
polo ralph lauren
celtics jerseys
coach outlet canada
dolce and gabbana
fendi handbags
saucony shoes

5689 said...

zzzzz2018.7.30
cheap snapbacks
skechers outlet
mlb jerseys
coach outlet online
polo ralph lauren
pandora jewelry
ferragamo shoes
coach factory outlet
true religion outlet
ralph lauren outlet

yanmaneee said...

supreme hoodie
retro jordans
christian louboutin
nike shox
supreme clothing
converse outlet
yeezy
adidas tubular shadow
christian louboutin shoes
yeezy boost 700

Makelar said...

Meski menjadi keputusan sulit dalam melakukan cara menggugurkan hamil terkadang pilihan tersebut tidak bisa dihindari . Lantas , bagaimana metode yang tepat untuk cara menggugurkan kehamilan dengan cepat dan aman ? Tindakan KURET merupakan langkah yang paling aman di bandingkan dengan yang lainnya , Jika anda saat ini mengalami tanda kehamilan dan ternyata usianya sekitar 1 bulan . Namun kehamilan itu tidak anda inginkan dan ingin melakukan cara menggugurkan kandungan 1 bulan , sebaiknya Anda konsultasi ke pihak medis . Pahami tentang alat reproduksi wanita untuk menjaga kesehatan . Gangguan kewanitaan seperti telat datang bulan membuat perempuan merasa cemas sekali . Apalagi bagi gadis yang belum mempunyai pasangan resmi . Hal ini tentu akan membuat mereka semakin khawatir . Buat anda yang pasangannya kurang bergairah maka sangat perlu untuk melakukan cara merangsang wanita agar kedua belah pihak dapat merasakan saat bercinta .