Simple MySQL Shell script for backup using Mysqldump

Here we will write a Shell/Bash script for taking MySQL backup using Mysqldump command. You can schedule this in cron for daily backups.

Shell Script for Backup using MySQLDump:

#!/bin/bash

# MySQL database credentials
user="your_username"
password="your_password"
host="localhost"
db_name="your_database_name"

# Other options
backup_path="/path/to/backup/directory"
date=$(date +"%d-%b-%Y")

# Backup filename
backup_file="$backup_path/$db_name-$date.sql"

# Dump the MySQL database
mysqldump --user=$user --password=$password --host=$host $db_name > $backup_file

# Compress the backup file
gzip $backup_file

# Print a message indicating the backup was successful
echo "Backup of database '$db_name' completed successfully at $date"

Here’s an explanation of what each part of the script does:

1. Define the MySQL database credentials, including the username, password, host, and database name.
2. Define the backup path and date, which will be used to create the backup file name.
3. Create the backup file name by concatenating the database name and date with a dash.
4. Use mysqldump to dump the database to a SQL file with the defined file name.
5. Use gzip to compress the backup file.
6. Print a message indicating that the backup was completed successfully.

Note that you’ll need to replace the your_username, your_password, your_database_name, and /path/to/backup/directory values with your own. You can also modify the script to include additional options, such as excluding certain tables or data.