Creating Backup Directories
Begin by establishing a dedicated directory for backups:
mkdir -p /home/apps/backup/mysqllive
Securing Database Credentials
For enhanced security, store database credentials in a configuration file instead of embedding them in scripts.
Create and edit the MySQL dump configuration file:
sudo vim /etc/mysql/conf.d/mysqldump.cnf
Add the following content to the file:
host=127.0.0.1
user=your_mysql_user
password=your_mysql_password
Alternatively, configure general MySQL settings in another file:
sudo vim /etc/mysql/conf.d/mysql.cnf
Include these lines:
[mysqldump]
host=127.0.0.1
user=your_mysql_user
password=your_mysql_password
Implementing the Backup Script
Develop a shell script for automating the backup process:
vim /home/apps/backup/mysql_backups.sh
Insert the following script:
#!/bin/bash
# Number of backup copies to retain (15 days)
count_limit=15
# Backup destination directory
backup_path=/home/apps/backup/mysqllive
# Timestamp for current backup
timestamp=$(date +%Y-%m-%d-%H:%M:%S)
# Tool used for dumping databases
backup_tool=mysqldump
# Target database name
database=dst5hy
# Create backup directory if it does not exist
if [ ! -d "$backup_path" ]; then
mkdir -p "$backup_path"
fi
# Execute database dump
$backup_tool "$database" > "$backup_path/$database-$timestamp.sql"
# Log creation of new backup
printf "Created %s/%s-%s.sql\n" "$backup_path" "$database" "$timestamp" >> "$backup_path/log.txt"
# Determine oldest backup for deletion
oldest_backup=$(ls -t "$backup_path"/*.sql | tail -1)
# Count total backups
backup_count=$(ls -1 "$backup_path"/*.sql | wc -l)
# Remove oldest backup if limit exceeded
if [ "$backup_count" -gt "$count_limit" ]; then
rm "$oldest_backup"
printf "Removed %s\n" "$oldest_backup" >> "$backup_path/log.txt"
fi
Setting Execution Permissions
Set appropriate permissions for the script:
chmod 755 /home/apps/backup/mysql_backups.sh
Execute manually to verify functionality:
bash /home/apps/backup/mysql_backups.sh
Configuring Scheduled Execution
Edit the system's crontab to schedule automated execution:
sudo vim /etc/crontab
Add the following entry to run the backup daily at 2 AM:
0 2 * * * root /home/apps/backup/mysql_backups.sh
Restart the cron service for changes to take effect:
sudo systemctl restart cron
Confirm scheduled tasks with:
crontab -l
Alternatively, use the interactive editor:
crontab -e
Use online tools for generating Cron expressions if needed: