A Shell script is a script that lets us write different types of commands in a single file for execution. We could also type them manually one by one. If we want to use a shell script, we must first write these commands into a text file, and then we can run them repeatedly whenever we need.
It is assumed that you already know about shell scripting, mysqldump, and crontab. First, we will provide the complete script, and then explain it step by step.
Applicable Operating Systems: Any Linux or UNIX.
1. Creating the Script
This Shell script can automatically back up your database. Simply copy and paste this script into a text editor, and enter your database username, password, and database name. Here, we use the mysqldump command to back up the database. Each line of the script command will be explained later.
(1) Create two directories, “backup” and “oldbackup”, in the directory where you want to place the backup files. Here, the root directory is used.
(2) Create and edit the file “backup.sh”
3 |
echo “You are In Backup Directory” |
5 |
echo “Old Databases are Moved to oldbackup folder” |
6 |
Now=$(date +”%d-%m-%Y--%H:%M:%S”) |
8 |
mysqldump –u user-name –p ‘password’ database-name > $File |
9 |
echo “Your Database Backup Successfully Completed” |
(3) Set executable permission for the backup.sh script file
1 |
# chmod +x /backup/backup.sh |
(4) Script Execution
After the script runs, you will get the following output:
1 |
root@Server1:/download#./backup.sh |
2 |
You areinDownload Directory |
3 |
Old Backup DatabaseisMoved to oldbackup folder |
4 |
database backup successful completed |
5 |
root@Server1:/download# |
Note: When executing the script for the first time, a “no such file” message may appear because the old backup file does not exist yet. Just run the script again, and the issue will be resolved.
2. Script Explanation
In line 8 of the command, you need to input your own database username, password, and database name after the mysqldump command.
When executing this script, it first enters the /backup directory (which must match the directory you created), then moves the old database backups to the /oldbackup folder. Next, it generates a filename based on the system’s date and time. Finally, the mysqldump command generates a database backup file in “.sql” format.
3. Scheduling Backups Using Cron
Using Cron, you can schedule this script to run automatically. Use the crontab command to edit the cron scheduled tasks.
Enter the following code in the editor, save and exit:
1 |
013* * * * /backup/backup.sh |
This task means the database will be backed up to the specified folder at 1 PM every day (you can adjust the backup frequency according to your actual needs). For detailed information on setting up cron tasks, refer to the crontab manual.
With this, a scheduled automatic MySQL backup function is implemented. To ensure database security, Li Huai suggests everyone develop the habit of backing up databases.
Original Link: A Simple Shell Script To Backup Mysql Database
