[Advanced] How to automate Database Backup in VPS

[vert]

Hello guys,

You can setup your VPS with linux to automate your database backup, i'll use one VPS with Mysql pre-installed of

O conteúdo está oculto, favor efetuar login ou se cadastrar!

• Entre
• ou
• Cadastre-se

 in this tutorial.

If you register with referral link, you will receive $10 bonus credits after confirm your account.

 

VPS

1. Register in

O conteúdo está oculto, favor efetuar login ou se cadastrar!

• Entre
• ou
• Cadastre-se

 verify your account

2. Go to create droplet

3. Click in one click apps and select Mysql

4. Select your droplet size (i recommend the $5 monthly)  

5. Select the place you prefer

6. Select options (i recommend those options)

7. You can rename your droplet if you want

8. Create Droplet

 

Github

1. If you don't have one account in

O conteúdo está oculto, favor efetuar login ou se cadastrar!

• Entre
• ou
• Cadastre-se

, you need create one

2. Open 

O conteúdo está oculto, favor efetuar login ou se cadastrar!

• Entre
• ou
• Cadastre-se

3. Click in Fork this repository and go to settings of repository

4. Scroll to the bottom of page and click in "make private" repository (i strongly recommend use a private repository for backups and not public)

5. Now, you already have a place to upload your database backups with security

 

Droplet (VPS) - You need login as sudo user

1. Log in into VPS and configure him as you like

2. After configured the myslq, we will make the scripts to automate the backups of database

3. Type in terminal: cd /

4. Now, we gonna clone our repository, in my case, i'll type in terminal: git clone 

O conteúdo está oculto, favor efetuar login ou se cadastrar!

• Entre
• ou
• Cadastre-se

 backup / In your case: git clone repository-url backup

4.1 (This is very important! - If you clone my public repository, your database backup will be sent to my repository, you need clone your repository)

5. You need to tell to git who are you, enter with your github credentials and after clone the repository in folder "backup", type: cd backup

6. Type: git config credentials.helper store to save your credentials

7. Type: git pull (this command will get all data from repository, if not ask for your user and password, everything is OK, if ask, try the step 6 again)

8. Give permission to mysql script and github script with: chmod +x /backup/mysql/mysql.sh and chmod +x /backup/github/github.sh

8.1 Now, you need configure your settings into /backup/mysql/mysql.sh

9. Change {my user - default: root} to your mysql user, change {my mysql password} to your password and the two {database name} to your database name

10. Test your script, type: cd / and after, sh /backup/mysql/mysql.sh

11. If everything okay, you can see your new commit with title like: Server Database Update - Backup

12. After finish all the steps at here, we just need tell to the VPS do this automatic with CRON tasks.

13. Type: crontab -e and select 2 (for use nano as editor)

14. In the bottom of file, type: 30 2 * * * sudo sh /backup/mysql/mysql.sh (info: minute hour day-of-month month day-of-week "comand") - This will make backups every day at 2:30 AM -

O conteúdo está oculto, favor efetuar login ou se cadastrar!

• Entre
• ou
• Cadastre-se

15. After write the file, type: ctrl - x and press y and press enter to confirm

 

You automated backup is ready!

 

Editado Janeiro 25, 2018 por vert