shell script to backup mysql database

Shell Script to Backup MySQL Database

Shell scripts allow you to easily automate tasks and processes in Linux. When you run a website, you might need to regularly take backup of your MySQL database. You can easily do this using shell scripts. In this article, we will look at how to create a shell script to backup MySQL database.


Shell Script to Backup MySQL Database

Here are the steps to backup MySQL database.


1. Create Shell Script

Open terminal and run the following command to create an empty shell script file.

$ sudo vi /home/db_backup.sh


2. Add shell script to backup MySQL

Add the following code to your shell script file. Replace dbname, dbuser and dbpass with your database name, username and password respectively.

#!/bin/sh

echo "starting db backup"
day="$(date +'%A')"
db_backup="mydb_${day}.sql"
sudo mysqldump  -udbuser -pdbpass --no-tablespaces dbname  >/home/${db_backup}
echo "db backup complete"

Save and close the file.

In the above code, we first define the execution environment for shell script. Then we echo message to indicate that backup has begun. Then we create a variable day to store current day name (Sunday, Monday, etc). We use this variable to define the file name of final backup file (.sql) for the database backup. Next, we run the MySQL command to backup database, and store it in /home folder. Finally, we echo another message indicating that database backup is complete.

In this case, the script will create mydb_Sunday.sql on Sunday, mydb_Monday.sql on Monday and so on, giving you past 7 days backup if you run it every day.

Please mention the full path of your backup file so that you know where it is created and do not face any permission denied error messages.

If you want to compress the above backup data, you can modify the mysqldump command as shown. We basically pipe the output of mysqldump command to gzip command to create the gzip file. Consequently, we modify the final file name for backup to be a .gz file instead of a .sql file.

db_backup="mydb_${day}.gz"
sudo mysqldump  -udbuser -pdbpass --no-tablespaces dbname | gzip -c >/home/${db_backup}


3. Make Shell Script Executable

Run the following command to make your shell script executable

$ sudo chmod +x /home/db_backup.sh


4. Test shell script

Run shell script using the following command

$ sudo /home/db_backup.sh
starting db backup 
db backup complete


5. Automate DB Backup

You can even add the above command as a cronjob to regularly take database backup every day/week/month as per your requirement.

Here is an example. Open crontab file with the following command.

$ sudo crontab -e

Add the following line to it. It will run your db_backup.sh script everyday at 10.a.m. You can change it as per your requirement.

0 10 * * * sudo /home/db_backup.sh >/dev/null 2>&1

Save and close the file. In the above code “0 10 * * *” indicates that the cron job should be run every day at 10.a.m. “sudo /home/db_backup.sh” is the command to be run for cron job. “> /dev/null 2>&1” indicates that all standard output and error messages must be sent to /dev/null, that is, should not be displayed.

Please ensure that you use the keyword sudo while scheduling cronjob, to avoid “permission denied” errors. Also mention the full path to your shell script to avoid “file not found” errors.

In this article, we have learnt how to create a shell script to backup MySQL database, and also create a cronjob to run the shell script regularly every day. This will ensure that you are able to regularly backup your database. If you run the above shell script everyday as a cronjob, it will create 7 different backup files, one for each day of the week, thereby giving you backup for past 7 days. Everyday it will overwrite the corresponding backup file with same file name, of past week, giving you 7 most recent backups always.

Also read:

Htaccess Redirect If URL Contains String or Word
How to Change Root Password in MySQL
How to Run Shell Script as Cron Job
How to Create Cronjob using Shell Script
How to Get Current Directory in Shell Script

Leave a Reply

Your email address will not be published.