Shell Script to Take MySQL Backup and Copy it to Remote Linux & Windows Server using Script

A--Introduction to Source Server

1 - I am having Ubuntu 22.04 VM installed locally on my VMware workstation and the IP address is 192.168.0.148

2 - I have installed MySQL 8.0 on this server

3 - I have two Databases EMPLOYEES and TUTORIALS, out of which today I will be taking a backup of the EMPLOYEES Database through Shell Script.

4 - I have created two folders Data and scripts inside my user directory(/home/vadmin).

B--Create Shell Script to Take MySQL Database Backup

1 - Create DB_Backup.sh file under /home/vadmin/scripts

#!/bin/bash

USER="root"

PASSWORD="*******"

DATABASE="EMPLOYEES"

SAVEDIR="/home/vadmin/Data"

/usr/bin/nice -n 19 /usr/bin/mysqldump -u $USER --password=$PASSWORD --default-character-set=utf8 $DATABASE -c | /usr/bin/nice -n 19 /bin/gzip -9 > $SAVEDIR/$DATABASE-$(date '+%Y%m%d-%H').sql.gz

Save and close the file.

2 - Give executable rights on the file to the current user.

3 - If I execute this file and check in the Data directory, the backup is available.

4 - I can go to cron job scheduling with the command "crontab -e" and schedule the backup at 01 am daily like below,

Save and close the file, now backup will happen at 1 am daily.

C--Copy backup files to the remote Linux server.

1 - I can copy the backup file to the remote server using the below command,

scp -r /home/vadmin/Data ubuntu@13.127.193.172:/home/ubuntu

13.127.193.172 in the above command is the Public IP address of my AWS EC2 instance.

2 - If I log in to EC2 instance 13.127.193.172, the file is available.

D--Copy/Download the Backup Files to Windows Server Using Script

1 - I have created three folders called "App", "Data" and "Script" in C drive.

2 - I have downloaded the WinSCP software from the below link,

https://winscp.net/download/WinSCP-5.21.8-Setup.exe

3 - Double click on the downloaded exe and select "Accept".

4 - Select "Custom Installation" and click on "Next".

5 - On the next page give the path of App folder as below and click "Next".

6 - Leave all the settings as it is on this page and click "Next". Don't change anything on the upcoming page, click "Install" on the last page and finish the installation.

7 - Go to the "App" folder inside C and double-click on "WinSCP.exe".

8 - On the WinSCP window go to Tools and then Preferences.

9 - On the Preferences window, go to "Storage" and select "Automatic INI file" on the right side.

10 - Click on New Site, and give the Host name, User name, and password. Click on Save.

11 - Give the Site name, tick on save the password and click on ok.

12 - Verify the connectivity by clicking on Login.

13 - Go to sessions & click on disconnect the session and then close session.

14 - Now in the Script folder inside C, create 192.168.0.148.txt. And fill the content of the text file as below,

15 - In the above text file, 192.168.0.148 is the site name that we saved in WinSCP."C:\Data" is the local path where we want to download the data from the Linux server. "/home/vadmin/Data" is the path of the Linux server where the backup data is present.

16 - Now one new Untitled notepad and fill in the below details into it.

17 - Do the save as and save it as "192.168.0.148.cmd" inside the Script folder. Keep the save as type "All files".

18 - Now inside C:\Script we have two files.

19 - Right click on "192.168.0.148.cmd" and do the run as administrator.

20 - It will run and download the MySQL backup data from the Linux server to the Windows system. If you go to the "Data" folder inside C, you will find the file has been downloaded.

21 - Go to Task Scheduler, right-click, and select "Create a Basic Task".

22 - Select Daily on the Trigger page and provide the execution time at 1:30 am.

23 - On the Action page select "Start a program" and click "Next".

24 - Click on browse and select file 192.168.0.148.cmd inside the Script folder. Click on "Next".

25 - Tick on "Open the Properties dialog.." and click on"Finish".

26 - In this dialog box select "Run whether user is logged on or not" and click "OK".

Provide the password and click OK.

27 - If you go to Task Scheduler Library you will find the task created in the previous step.

28 - Now in the Linux server MySQL backup will happen at 01 am every day and it will be copied or downloaded to Windows Server at 01:30 am every day.

Wrapping Up

While trying the above script in your environment, give naming conventions as per your infrastructure.

Still, there's a lot to improve.

Thank you for reading. Let me know your suggestion for what's next.

And if you want to see more content like this, keep an eye on the blog of course, and subscribe to my Youtube Channel.