Last active
June 16, 2024 08:30
-
-
Save ph33nx/7d2ab6430d1c207a6fa80dc38067e2bc to your computer and use it in GitHub Desktop.
This bash script automates the process of daily backups for all MySQL/MariaDB databases on a Linux server. It stores each database backup in a dedicated directory under /var/backups/mysql/ and names the backup files using the format <database_name>_<date_time>.sql. Additionally, the script includes a cleanup function that deletes backups older t…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| # backup_mysql_db.sh | |
| # This script backs up each MySQL/MariaDB database into its own file daily. | |
| # The backups are stored in /var/backups/mysql/<database_name>/<database_name>_<date_time>.sql | |
| # Backups older than 30 days are automatically deleted. | |
| # | |
| # Author: Ph33nx | |
| # GitHub: https://github.com/ph33nx | |
| # | |
| # Usage: | |
| # 1. Save this script as backup_mysql_db.sh | |
| # 2. Make the script executable: chmod +x backup_mysql_db.sh | |
| # 3. Schedule it to run daily using crontab: | |
| # sudo crontab -e | |
| # Add the following line to run the script at 2 AM daily: | |
| # 0 2 * * * /path/to/backup_mysql_db.sh | |
| # Define variables | |
| BACKUP_ROOT_DIR="/var/backups/mysql" | |
| DATE=$(date +%F_%H-%M-%S) | |
| # Ensure the script is run as root | |
| if [[ $EUID -ne 0 ]]; then | |
| echo "This script must be run as root" | |
| exit 1 | |
| fi | |
| # Create backup root directory if it does not exist | |
| if [ ! -d "$BACKUP_ROOT_DIR" ]; then | |
| mkdir -p $BACKUP_ROOT_DIR | |
| echo "Created backup root directory: $BACKUP_ROOT_DIR" | |
| fi | |
| # Get a list of databases | |
| databases=$(mysql -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)") | |
| # Loop through each database and back it up | |
| for db in $databases; do | |
| BACKUP_DIR="$BACKUP_ROOT_DIR/$db" | |
| # Create a directory for each database if it doesn't exist | |
| if [ ! -d "$BACKUP_DIR" ]; then | |
| mkdir -p $BACKUP_DIR | |
| echo "Created backup directory for $db: $BACKUP_DIR" | |
| fi | |
| BACKUP_FILE="${BACKUP_DIR}/${db}_${DATE}.sql" | |
| echo "Backing up $db to $BACKUP_FILE..." | |
| # Perform the backup | |
| mysqldump --databases $db > $BACKUP_FILE | |
| # Find and delete backups older than 30 days | |
| find $BACKUP_DIR -type f -name "${db}_*.sql" -mtime +30 -exec rm {} \; | |
| done | |
| echo "Backup completed." |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment