A basic script for backup MySQL database

This is a simple example of a Bash script for backing up a MySQL database using the mysqldump command. This script creates a timestamped SQL dump file for the specified database and compresses it for storage.

Create a new file, for example, backup_mysql.sh, and make it executable:

touch backup_mysql.sh
 chmod +x backup_mysql.sh

Edit the script using a text editor:

nano backup_mysql.sh

Add the following content to the script:

#!/bin/bash

# MySQL Database Backup Script

# Database credentials
db_user=”your_mysql_user”
db_password=”your_mysql_password”
db_name=”your_database_name”

# Backup destination directory
backup_dest=”/path/to/mysql/backup”

# Backup file name format (e.g., db_backup_2023-12-01.sql.gz)
backup_filename=”db_backup_$(date ‘+%Y-%m-%d’).sql.gz”

# Log file for backup process
log_file=”/var/log/mysql_backup.log”

# Create backup
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] Starting MySQL backup” >> “$log_file

mysqldump -u “$db_user” -p”$db_password” “$db_name” | gzip > “$backup_dest/$backup_filename”

if [ $? -eq 0 ]; then
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] MySQL backup completed successfully” >> “$log_file”
else
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] MySQL backup failed” >> “$log_file”
fi

Customize the script:

Set the db_user, db_password, and db_name variables with your MySQL credentials and database name.
Adjust the backup_dest variable to the directory where you want to store the MySQL backup files.
Adjust the backup_filename format according to your preferences.
Update the log_file variable to the desired location for the log file.

Save the changes and exit the text editor.

To run the MySQL backup manually:

./backup_mysql.sh

To schedule regular MySQL backups, you can use cron jobs. Edit the crontab file:

crontab -e

Add the following line to run the MySQL backup script daily at a specific time:

0 3 * * * /path/to/backup_mysql.sh

This example runs the script daily at 3:00 AM. Adjust the timing according to your preferences.

Save the changes and exit the crontab editor.

This script is a basic example, and depending on your MySQL setup, you might need to consider additional factors like multiple databases, database server location, and security considerations. Always ensure that sensitive information such as passwords is stored securely and that the backup process is tested and monitored regularly.