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.

37 comments:

Unknown 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.

Vladimir 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...

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,

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

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 .

sandy said...


https://khalejmovers.com/شركة-شحن-من-المدينة-المنورة-لمصر/

https://khalejmovers.com
https://khalejmovers.com/شركة-شحن-من-المدينة-المنورة-لمصر/
https://khalejmovers.com/دينا-نقل-عفش-جدة/
https://khalejmovers.com/شركة-نقل-عفش-من-جدة-الى-الاردن/

cara menggugurkan kandungan said...

now present in your city
0. nutrisi agar cepat hamil
1. gizi seimbang ibu menyusui
2. cara mempercepat haid
3. manfaat kurma untuk persalinan
4. manfaat buah nanas
5. aktivitas penyebab keguguran
6. apakah usg berbahaya
7. penyebab telat haid
8. cara mengatasi keputihan
9. tanda tanda menopause

Unknown said...

this article the original source check this look these up read the full info here view it

Unknown said...

replica bags aaa Bonuses t3b69u1y72 7a replica bags philippines replica bags sydney useful reference b5e45p7l10 replica bags chicago look at here now m0a81q5n07 replica louis vuitton bag replica bags in delhi u9c73y0l91