How to Use Cron for MySQL Backups and Auto-Deletion July 9, 2025

Regular backups are vital for safeguarding your MySQL data. This guide explains how to automate daily MySQL database backups using cron and how to clean up old backup files older than 10 days to manage disk space efficiently.

Let us follow the steps:

1. Log in to the Linux Server via SSH.

   ssh username@your-server-ip

   Replace username and your-server-ip with your actual credentials.

2. Create MySQL credentials file.

   [client]
   user=root
   password=yourpassword

3. Set secure permissions: 

   chmod 600 /root/.my.cnf

4. Open the Crontab editor and edit the crontab file:

   crontab -e

5. Add a Cron Job for Daily MySQL Backup by adding the following line to your crontab to schedule a backup at 12:00 AM daily:

   0 0 * * * /usr/bin/mysqldump --single-transaction --quick --routines --triggers accu_4 | gzip > /home/mysql_backup/accu_4-$(date +\%Y_\%m_\%d).sql.gz 2>> /home/mysql_backup/backup.log

   Let us understand the above command,

   1. 00 12 * * * – Runs the command daily at 12:00 AM.
   2. mysqldump – Utility to export the database.
   3. -uroot -ppassword – Substitute with your original MySQL username and password.
   4. whuk_1 – Substitute with your real database name.
   5. /home/mysql_backup/ – Replace with your chosen backup directory.
   6. $(date +\%Y_\%m_\%d) – Adds the present date to the backup file.
   7. /dev/null 2>&1 – Prevents output and error messages.
      Important: Make sure the target backup directory exists and is writable by the cron user.
6. After 12:00 AM, verify the backup directory to ensure the “.sql” file has been created successfully.
7. Now, to prevent your server from running out of disk space, remove backups older than 10 days using the following steps:
   1.  Create a Cleanup Script:
      1.  Create a shell script to delete old backup files:

         vi /opt/remove.sh

      2. Paste the following code:

         find /home/mysql_backup/*.sql -mtime +10 -exec rm -f {} \;

         Save and exit the editor. Replace /home/mysql_backup/ with your actual backup directory path.

   2. Schedule the Cleanup Script with Cron:
      Add the below code to your crontab to run the cleanup daily at 2:00 PM:

      00 14 * * * sh /opt/remove.sh

      This command will eliminate “.sql” files in the backup directory that are older than 10 days.

This way, with these cron jobs in place, your MySQL database will be backed up daily, and old backups will be automatically removed to conserve disk space. Always check your backup and cleanup scripts before depending on them in the production arena. However, if you need any help, feel free to contact our support team.

New to setting up cron tasks? Learn How to Create a Cron Job in Linux