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.

Steps to set up automatic MySQL or MariaDB backups with 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.

    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.

Discuss the article:

Comment anonymously. Login not required.