How to set up MariaDB or MySQL automatic backups using cron

Automating database backups is critical for protecting data in MariaDB and MySQL environments. Regular backups prevent data loss due to hardware failures, corruption, or user error. Using cron, you can schedule automatic backups with mysqldump, ensuring the database is consistently backed up at specified intervals without manual intervention.

The mysqldump utility exports database content into SQL files, allowing for a full or partial backup. Automating this process through cron jobs ensures that backups occur at regular intervals, reducing the risk of data loss in MariaDB or MySQL databases. Storing the password securely in ~/.my.cnf eliminates the need for hard-coded credentials in the script, improving security.

This method provides a simple, reliable approach for setting up scheduled backups. By configuring the right cron job, administrators can automate backups to match the server's usage patterns, ensuring the database is safely backed up without requiring manual processes.

1. Ensure the mysqldump utility is installed on your system.

   $ mysqldump --version

   This command checks if mysqldump is installed and available for use.

2. Set up a ~/.my.cnf file to securely store your MariaDB or MySQL credentials.

   [client]
   user=root
   password=your_password

   Replace “your_password” with your actual password. This allows you to run backup commands without including your password in the script.

3. Create a backup script that uses mysqldump to export your database.

   #!/bin/bash
   mysqldump --defaults-extra-file=~/.my.cnf --all-databases > /path/to/backup/backup-$(date +\%F).sql

   This script exports all databases into a timestamped SQL file. Adjust the path to specify the correct backup directory.

4. Make the backup script executable.

   $ chmod +x /path/to/backup_script.sh

5. Test the backup script manually to ensure it works correctly.

   $ /path/to/backup_script.sh

   After running the script, check the backup directory to ensure the SQL file has been created successfully.

6. Edit the crontab to schedule the backup script to run automatically.

   $ crontab -e

7. Add a new line in crontab to define the backup schedule.

   0 2 * * * /path/to/backup_script.sh

   This example schedules the backup to run every day at 2 AM. Adjust the time and frequency based on your backup needs.

8. Save and exit the crontab editor.
9. Verify that the backup script runs as scheduled by checking the backup directory after the scheduled time.

   $ ls /path/to/backup/

   Ensure that new backup files are being created according to the defined schedule.