Backup MySQL databases daily on Debian

You own a Debian OS at your VPS / VDS / dedicated server and want to automatically daily secure all of your MySQL- or MariaDB databases? Then you've come to the right place!

In this article, we'll show you how to automatically export all your MySQL databases on a Debian operating system.

First, we need to make sure we have the latest updates installed on our server. You can achieve this with a simple command:

apt update && apt upgrade -y

If you haven't installed the Nano text editor yet, you can do so with the following command:

apt install nano -y

Now we create a Bash script that takes on the task of exporting and deleting the oldest backups. For our example, we call the script 'mysql_export_all.sh' and save it in the directory '/opt/mysqlbackups':

mkdir /opt/mysqlbackups/
nano /opt/mysqlbackups/mysql_export_all.sh

In this script we could write the following:

#!/bin/bash

USER='root'
PASSWORD='yourpassword'
DATE=$(date +%Y-%m-%d-%H-%M)
BACKUP_DIR='/opt/mysqlbackups'

mkdir -p $BACKUP_DIR

mysqldump -u$USER -p$PASSWORD --all-databases > $BACKUP_DIR/alldbs_$DATE.sql

find $BACKUP_DIR -not -name "$(basename "$0")" -mtime +7 -exec rm {} \;

Remember to replace 'root' and 'yourpassword' with your MySQL username and password.

Now we make the script executable:

chmod +x /opt/mysqlbackups/mysql_export_all.sh

Now we need to add this script to our cron job. Use the following command to open the cron job editor:

export VISUAL=nano; crontab -e

For a daily backup at 5 AM, we could add the following:

0 5 * * * /opt/mysqlbackups/mysql_export_all.sh

With this setup, all your MySQL databases will now be automatically exported every day at 5 AM and backups older than 7 days will automatically be deleted.