Backup MySQL Databases

Backing up MySQL databases is a crucial task, and automating the process can save both time and effort. The following article explains how to use a bash script to backup your MySQL databases.

Script Overview

The provided script automates the process of backing up MySQL databases on a server. Here’s a breakdown of what it does:

1. Defines Backup Location and Credentials: The script specifies the backup directory and database credentials.
2. Creates Backup Directory: If the backup directory doesn’t exist, it’s created.
3. Lists Databases: Retrieves the list of databases on the server, excluding certain system databases.
4. Exports Databases: Each database is exported to its own SQL file, named according to the database name and timestamp.
5. Archives Exported Files: All the SQL files are then archived into a single tar.gz file.
6. Prints Success Message: Upon successful execution, a success message is printed to the console.

Usage Instructions

Follow these steps to use the script:

#!/bin/bash

backup_dir="/opt/backups/mysql"
timestamp="$(date +%Y-%m-%d_%H-%M-%S)"
username=""
password=""
server="localhost"

# Ensure backup directory exists
if [ ! -d "$backup_dir" ]; then
mkdir "$backup_dir"
fi

# Get list of databases
databases=$(mysql -h"$server" -u"$username" -p"$password" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")

# Export each database to its own file
for db in $databases; do
target_file="$backup_dir/$db.$timestamp.sql"
mysqldump -h"$server" -u"$username" -p"$password" --opt --databases "$db" > "$target_file"
echo "Exported database $db to $target_file"
done

# Archive all export files into a single tar.gz file
archive_file="$backup_dir/mysql_backups_$timestamp.tar.gz"
tar -czvf "$archive_file" "$backup_dir"/*.sql

# Output success message
echo "All database exports archived to $archive_file"

1. Save the Script

Copy the script into a file, e.g., backup_mysql.sh. You may want to modify the following variables to match your setup:

• backup_dir: Directory where backups will be saved.
• username: MySQL username.
• password: MySQL password.
• server: MySQL server address (use localhost if it’s on the same machine).

2. Make the Script Executable

Open a terminal and navigate to the script’s directory. Run the following command:

chmod +x backup_mysql.sh

3. Run the Script

Execute the script by running:

./backup_mysql.sh

4. Verify the Backup

Check the specified backup directory to ensure that the SQL files and tar.gz archive have been created successfully.

5. Secure the Script

Since the script contains sensitive information like the MySQL username and password, make sure to set appropriate permissions on the script file to restrict access.

Conclusion

This bash script offers an automated and straightforward way to back up MySQL databases. By adjusting the variables at the beginning of the script, you can customize it to fit your environment and needs. Always remember to handle the script with care, considering the sensitive information it contains.