Jump to content
Habilis

Automatic SQL database backup

Recommended Posts

Hello guys I wrote an article lately about automatic SQL database backup

 

For Linux Systems (Sorry Wind0z guys and gals...)

 

This is the one I'm using for my projects

 

https://habilisbest.com/sql-database-automatic-backups

 

Features:

 

  • Backups database names specified in a list
  • compresses and stores them
  • Uses filename format for easy retrieval [DatabaseName].YYYY-mm-dd.gz
  • Removes backups older than 28 days (to keep it nice and clean)
  • Writes log files
  • You can configure it to run every 12 hours with cron (crazy server owners...)

 

I run it every 3 days. Because, I don't give a damn about my players I'm confident in my Raspberry Pi server!

 

Would like your feedback and if you can think of any other features I can develop, please speak up!

Edited by Habilis

Share this post


Link to post
Share on other sites

would be usefull if  it can send them to another remote server for that extra layer of security.

There is a script to make bash talk to dropbox

 

https://github.com/andreafabrizi/Dropbox-Uploader

 

Will try to integrate it to my solution.

 

But, on the other hand, the db backup folder takes 1.5 Mb at this moment and it contains 5 * 3 database backups.

 

I suppose at this rate archived backups could be sent by Email to make the script somewhat less complex.

Edited by Habilis

Share this post


Link to post
Share on other sites

Hello habilis,
is it good to host an ro in raspberry? i mean is it more efficient than to rent a vps?

Share this post


Link to post
Share on other sites

Hello!

Depends what VPS...

 

5$ VPS from DigiOcean featuring

1 core processor

512mb Ram

20 GB SSD

 

COMPARED TO

 

Raspberry Pi 3 model b featuring

Quad Core processor

1GB of Ram

? GB of ??  (I use 64GB SD class 10 card (I got some for 12$))

 

but buying and setting up a Raspberry Pi  might cost you  a year's worth of hosting a VPS

Raspberry Pi server costs ~70-80$ (RPi 3, Power Supply, SD card)

Assuming it uses your residential Internet...

 

80/5 = 16 (months of VPS at Digi Ocean)

 

Anyways, people have to see for themselves.

 

I just have bunch of Raspberry Pi laying around, so I play with them...

Edited by Habilis

Share this post


Link to post
Share on other sites

Hello!

Depends what VPS...

 

5$ VPS from DigiOcean featuring

1 core processor

512mb Ram

20 GB SSD

 

COMPARED TO

 

Raspberry Pi 3 model b featuring

Quad Core processor

1GB of Ram

? GB of ??  (I use 64GB SD class 10 card (I got some for 12$))

 

but buying and setting up a Raspberry Pi  might cost you  a year's worth of hosting a VPS

Raspberry Pi server costs ~70-80$ (RPi 3, Power Supply, SD card)

Assuming it uses your residential Internet...

 

80/5 = 16 (months of VPS at Digi Ocean)

 

Anyways, people have to see for themselves.

 

I just have bunch of Raspberry Pi laying around, so I play with them...

Yea i also thought that internet connection is an issue in my country. isp give very slow connection and that would be a problem for players specially internationally. thanks for the brief explanation though. 

Share this post


Link to post
Share on other sites

would be usefull if  it can send them to another remote server for that extra layer of security.

 

Thank you so much for your FeedBack!

 

Well here is V2 of my Database Backup solution

 

ZXDtdho.jpg

 

 

Ow yeah, Don't worry about someone hacking your GoogleDrive, if you put 14 character+ Alphanumeric password on yout backup archives it is going to be bruted loong time...

 

Featuring

 

1. generate a backup of databases specified in a list
2. Name them with yyy-mm-dd[DatabaseName] format
3. Compress them into .7z using maximum compression
4. Set a password on .7z archive
5. Send them to cloud storage Google Drive
6. Write logs
7. Removes all compressed backups 28 days or older (keeping local folder nice and clean)
8. Can be automated with cron to run even every 12 hours
Edited by Habilis

Share this post


Link to post
Share on other sites
For those who wondering,
not only Google Drive is supported, in the guide I provide a link to rclone github, cool guys, you should check them out!
 
 
 
 
They have a list of all remote options supported (If you are by any chance Russian, they Even support Yandex Disk, amazing isn't it?)
 

 

  • Google Drive
  • Amazon S3
  • Openstack Swift / Rackspace cloud files / Memset Memstore
  • Dropbox
  • Google Cloud Storage
  • Amazon Drive
  • Microsoft OneDrive
  • Hubic
  • Backblaze B2
  • Yandex Disk
  • SFTP
  • The local filesystem

 

Share this post


Link to post
Share on other sites

 

Hello!

Depends what VPS...

 

5$ VPS from DigiOcean featuring

1 core processor

512mb Ram

20 GB SSD

 

COMPARED TO

 

Raspberry Pi 3 model b featuring

Quad Core processor

1GB of Ram

? GB of ??  (I use 64GB SD class 10 card (I got some for 12$))

 

but buying and setting up a Raspberry Pi  might cost you  a year's worth of hosting a VPS

Raspberry Pi server costs ~70-80$ (RPi 3, Power Supply, SD card)

Assuming it uses your residential Internet...

 

80/5 = 16 (months of VPS at Digi Ocean)

 

Anyways, people have to see for themselves.

 

I just have bunch of Raspberry Pi laying around, so I play with them...

Yea i also thought that internet connection is an issue in my country. isp give very slow connection and that would be a problem for players specially internationally. thanks for the brief explanation though. 

 

My Raspberry Pi 3 Dev server

 

Been running 67 days without restart

CPU Temperature ranging in 40-45 Degrees C.

 

cuaFlOP.jpg

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.