How to set up automatic MySQL or MariaDB backups with cron

MySQL and MariaDB are popular relational database management systems that power a wide range of applications, from small personal projects to large enterprise solutions. Ensuring data integrity and continuity in case of disasters is critical for any application. Regular database backups are a cornerstone of a robust disaster recovery strategy, and automating the process is essential to ensure consistency and save time.

Cron is a time-based job scheduler in Unix-like operating systems that enables automation of tasks, such as running scripts or executing commands at specified intervals. By leveraging the power of cron, you can create a highly effective, automated backup solution for your MySQL or MariaDB databases.

In this guide, we will walk you through the process of setting up automatic backups for your MySQL or MariaDB databases using cron. The steps below will demonstrate how to create a backup script, configure the necessary permissions, and schedule the script to run automatically using the cron job scheduler.

1. Install mysqldump using your package manager if you don't already have it installed.

   sudo apt update && sudo apt-get install mysql-server

2. Create the directory and set the appropriate permissions to store your database backups.

   sudo mkdir -p /backups/mysql
   sudo chown -R mysql:mysql /backups/mysql
   sudo chmod 700 /backups/mysql

3. Open a new file with your text editor.
4. Add the following lines to the file, replacing user, password, and host with your MySQL or MariaDB credentials.

   #!/bin/bash
   TIMESTAMP=$(date +"%Y%m%d%H%M")
   BACKUP_DIR="/backups/mysql"
   MYSQL_USER="user"
   MYSQL_PASSWORD="password"
   MYSQL_HOST="host"
   
   databases=`mysql --user=$MYSQL_USER --password=$MYSQL_PASSWORD --host=$MYSQL_HOST -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
   
   for db in $databases; do
     mysqldump --force --opt --user=$MYSQL_USER --password=$MYSQL_PASSWORD --host=$MYSQL_HOST --databases $db | gzip > "$BACKUP_DIR/$db-$TIMESTAMP.sql.gz"
   done

5. Save the file and exit the text editor.
6. Set the script's execute permission.

   sudo chmod +x /usr/local/bin/mysql_backup.sh

7. Manually execute the script to test.

   /usr/local/bin/mysql_backup.sh

8. Verify that the backup files are created in the backup directory.
9. Open the crontab for the root user.

   sudo crontab -e

10. Add a new line to the file with the desired schedule, followed by the path to the backup script to schedule the backup.

    0 3 * * * /usr/local/bin/mysql_backup.sh

11. Save the file and exit the text editor.
12. Check the status of the cron service.

    sudo crontab -l