Regular MySQL backups are essential to protect your data from accidental loss, corruption, or server failures. Automating this process ensures your database is backed up daily without manual intervention. Additionally, it is important to remove older backups to save disk space.
In this article, we will explain how to create a cron job to take daily backups of your MySQL database and automatically delete backup files older than 10 days.
Step 1: Log in to Your Linux Server via SSH
Use an SSH client like PuTTY (Windows) or the terminal (Linux/macOS) to connect to your server:
# ssh username@server_ip
Step 2: Open the Crontab File
Run the following command in your crontab:
# crontab -e
Step 3: Add a Cron Job to Take Daily MySQL Backups
Insert the following cron job to create a daily backup at 12:00 PM:
# 00 12 * * * mysqldump -uroot -ppassword --opt accu_4 > /home/mysql_backup/accu_4-$(date +"\%Y_\%m_\%d").sql >> /dev/null 2>&1
Explanation:
- 00 12 → Runs every day at 12:00 PM.
- mysqldump → Command to export the database.
- -uroot -ppassword → MySQL username and password (replace root and password with actual credentials).
- accu_4 → Replace with your actual database name.
- /home/mysql_backup/ → Path where backups will be stored (modify as needed).
- $(date +"%Y_%m_%d") → Appends current date to the backup filename.
- /dev/null 2>&1 → Suppresses any warning or error messages.
After saving, your system will now take a backup every day at the specified time.
Step 4: Verify the Backup
After the cron runs, check your backup directory to ensure the .sql file is created:
# ls -lh /home/mysql_backup/
Step 5: Create a Script to Remove Old Backups (Older Than 10 Days)
Create a cleanup script in the /opt directory:
# vi /opt/remove.sh
Add the following code:
!/bin/
find /home/mysql_backup/*.sql -mtime +10 -exec rm -f {} \;
Save and exit (:wq).
Step 6: Make the script executable:
# chmod +x /opt/remove.sh
Step 7: Schedule a Cron Job to Remove Old Backups
crontab again:
# crontab -e
Add the following line to run the cleanup script daily at 1:00 PM:
00 13 * * * /opt/remove.sh
Conclusion:
By following the above steps, you have successfully automated daily MySQL database backups and configured a cleanup script to remove backup files older than 10 days. This ensures your database is regularly backed up while preventing excessive disk usage.
