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.

33 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,

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

sandy said...


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

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

Anonymous said...

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

Anonymous 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