Backing up your database regularly is crucial to ensure that your data is safe and recoverable. In this guide, we’ll outline how you can automate the backup of multiple MariaDB databases using a Bash script and a Cron job.
Prerequisites:
- MariaDB or MySQL server installed.
- Access to the command line and necessary privileges to back up databases.
1. The Bash Script:
Start by creating a Bash script that will handle the backup process:
a. Create a Bash Script:
nano /path/to/your/script/db_backup.sh
b. Add the Following Content:
# Get the current date to append to the backup file
DATE=$(date +“%Y%m%d%H%M”)
# Names of the databases
DB1=“your_first_database_name”
DB2=“your_second_database_name”
# Directory where backups will be stored
BACKUP_DIR=“/path/to/backup_directory”
# Backup the first database using its credentials and compress it
mysqldump –defaults-extra-file=/path/to/your/credentials/${DB1}_creds.cnf $DB1 | gzip > “${BACKUP_DIR}/${DB1}_backup_${DATE}.sql.gz”
# Backup the second database using its credentials and compress it
mysqldump –defaults-extra-file=/path/to/your/credentials/${DB2}_creds.cnf $DB2 | gzip > “${BACKUP_DIR}/${DB2}_backup_${DATE}.sql.gz”
# Delete backups older than 2 weeks
find $BACKUP_DIR -name “*.sql.gz” –type f -mtime +14 –exec rm -f {} \;
echo "Backup process completed! Check the directory: ${BACKUP_DIR}"
c. Make the Script Executable:
chmod +x /path/to/your/script/db_backup.sh
2. Setting Up the Credentials File:
a. Create Credentials File for Each Database:
It’s good practice not to hardcode database credentials directly in scripts:
For your first database:
nano /path/to/your/credentials/your_first_database_name_creds.cnf
Add the following content:
[client]
user=your_mariadb_username
password=your_mariadb_password
Repeat for the second database.
3. Automate the Backup with Cron:
a. Open Crontab:
crontab -e
b. Schedule the Backup Script:
At the end of the crontab file, add the following line to run the script daily at 2 AM:
0 2 * * * /path/to/your/script/db_backup.sh >> /path/to/your/log/backup_log.txt 2>&1
4. Final Notes:
- Ensure you have enough storage space in the backup directory for your backups.
- Check the backup logs (
backup_log.txt) regularly to ensure backups are being created without errors. - Make sure the backup directory is secure and only accessible by the necessary personnel.
With this setup, you’ll have automated backups for your databases running daily. It provides peace of mind knowing that your data is routinely backed up and can be restored when necessar
