Backup MySQL databases daily on Ubuntu

You own an Ubuntu 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 an Ubuntu 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.