Step-by-Step Guide for Separate Database Backups with Individual Logs
This guide will help you back up each MariaDB database individually, store them in separate files, and maintain individual logs for each backup.
1. Create a Backup Directory
sudo mkdir -p /var/backups/mariadb/separate
sudo mkdir -p /var/log/mariadb_backup
sudo chown -R mysql:mysql /var/backups/mariadb
sudo chmod 700 /var/backups/mariadb
sudo chmod 700 /var/log/mariadb_backup
2. Create the Backup Script
Create the backup script:
sudo nano /usr/local/bin/mariadb_separate_backup.sh
Add the following content:
#!/bin/bash
# Variables
BACKUP_DIR="/var/backups/mariadb"
LOG_DIR="/var/log/mariadb_backup"
DATE=$(date +'%Y-%m-%d_%H-%M-%S')
USER="root"
PASSWORD="admin@123"
# Get a list of all databases except system databases
DATABASES=$(mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")
# Loop through each database and back it up
for DB in $DATABASES; do
BACKUP_FILE="$BACKUP_DIR/${DB}_backup_$DATE.sql"
LOG_FILE="$LOG_DIR/${DB}_backup_$DATE.log"
echo "Starting backup for database: $DB" | tee -a $LOG_FILE
# Backup database
mysqldump -u $USER -p$PASSWORD $DB > $BACKUP_FILE 2>> $LOG_FILE
if [ $? -eq 0 ]; then
echo "Backup completed successfully for database: $DB" | tee -a $LOG_FILE
gzip $BACKUP_FILE
else
echo "Error occurred during backup of database: $DB" | tee -a $LOG_FILE
fi
# Remove logs older than 7 days
find $LOG_DIR -type f -name "${DB}_backup_*.log" -mtime +7 -exec rm {} \;
# Remove backups older than 7 days
find $BACKUP_DIR -type f -name "${DB}_backup_*.sql.gz" -mtime +7 -exec rm {} \;
done
Replace your_password with your MariaDB root password.
Adjust -mtime +7 if you want to keep backups/logs for a different duration.
Save and close the file.
3. Make the Script Executable
sudo chmod +x /usr/local/bin/mariadb_separate_backup.sh
4. Schedule the Script with Cron
Edit the root user's crontab:
sudo crontab -e
Add the following line to schedule backups daily at 6:00 AM:
0 6 * * * /usr/local/bin/mariadb_separate_backup.sh >> /var/log/mariadb_backup/main_backup.log 2>&1
0 6 * * *: Runs daily at 6:00 AM.
>> /var/log/mariadb_backup/main_backup.log 2>&1: Logs general script execution details.
5. Verify Cron Job
Ensure the cron job is listed:
sudo crontab -l
6. Test the Script Manually
Run the script manually to verify:
sudo /usr/local/bin/mariadb_separate_backup.sh
Check the backup files:
ls -l /var/backups/mariadb/separate
Check the logs:
ls -l /var/log/mariadb_backup
View an individual log file:
cat /var/log/mariadb_backup/<your_database>_backup_<timestamp>.log
7. Verify Logs and Backups
Ensure .sql.gz backup files are created for each database.
Ensure individual log files are generated for each database.
Summary of Configuration
Backup Directory: /var/backups/mariadb/separate
Log Directory: /var/log/mariadb_backup
Cron Job Time: 6:00 AM Daily
Backup Retention: 7 days
Your MariaDB setup is now configured to back up each database individually with separate logs and automated maintenance!
