How to set up automatic MySQL or MariaDB backups with cron

Backups are a critical component of any database management strategy. Regular backups ensure that data can be recovered in case of accidental deletions, database corruption, or hardware failures. For databases like MySQL and MariaDB, automated backups can be set up using cron, a time-based job scheduler available in Unix-like operating systems.

Using a combination of mysqldump (a utility for creating backups of MySQL and MariaDB databases) and cron, database administrators can schedule backups to take place at specific intervals, such as daily, weekly, or monthly. This provides peace of mind, ensuring that up-to-date backups are always available without manual intervention.

Whether you're running a blog, e-commerce site, or any application with a database, regular automatic backups are a must. In this guide, we will walk you through setting up automated backups for MySQL or MariaDB databases using cron.

1. Install mysqldump if it isn't already.

   $ sudo apt-get install mysql-client # Ubuntu and Debian

2. Create a directory to store the backups.

   $ mkdir ~/mysql_backups

3. Set up a password-less login for mysqldump by creating a .my.cnf file in your home directory with the necessary credentials.

   [mysqldump]
   user=mysql_user
   password=my_password

   Ensure this file is kept secure using

   chmod 600 ~/.my.cnf

   to prevent unauthorized access.

4. Open the cron job editor.

   $ crontab -e

5. Schedule a backup at a specific time (for example, every day at 2 am). Add the following line to the crontab editor:

   0 2 * * * /usr/bin/mysqldump -u mysql_user -pmy_password my_database > ~/mysql_backups/database_backup_$(date +%F).sql

6. Save and close the crontab editor.
7. Ensure that your backup location has sufficient space and is preferably backed up to another location or external service.

   Regularly check the backup directory to confirm backups are being created as expected. Also, consider rotating or deleting older backups to save space.

8. Test the backup manually by running the mysqldump command from the cron job. Check the backup directory for the output file to confirm it's working correctly.

   $ /usr/bin/mysqldump -u mysql_user -pmy_password my_database > ~/mysql_backups/database_backup_test.sql

By following the above steps, your MySQL or MariaDB databases will be backed up automatically at the scheduled intervals. Regularly testing and verifying the integrity of backups is essential to ensure that the data can be successfully restored when needed.