How To Backup MySQL/MariaDB Daily

The database is an important asset, which usually people forgot to backup. Before something goes wrong prepare your backup, and do the backup before going to sleep tonight.

MySQL has a built-in tool to do the backup and restore called mysqldump. mysqldump can’t automate the backup process, but we can create a script to do that. It’s recommended you understand how to use mysqldump before doing some automation because it’s make life easier and less trouble.

We’ll create a folder to store all backups on the same server ;), later we can push to S3, NextCloud, B2 whatever cloud storage you preferred.

Requirements :
Backup stored at: /backup/YYYY-MM-DD
YYYY-MM-DD using Year-Month-Day format
database compressed to reduce database size
data older than 5 day deleted

Create a file in /opt/mysql-backup.sh, copy and paste following code

#!/bin/bash
backup_path=/backup
expiry_date=5
current_date=$(date +%Y-%m-%d)
 
# create folder if it does not exist
if [ ! -d "$backup_path" ]; then
        mkdir "$backup_path"
fi
 
if [ ! -d "$backup_path/$current_date" ]; then
    mkdir -p "$backup_path/$current_date"
    if [ ! -f $backup_path/$current_date/db-$(date +%H%M).sql ]; then
            mysqldump --all-databases | gzip -c > $backup_path/$current_date/db-$(date +%H%M).sql.gz
    fi
else
    if [ ! -f $backup_path/$current_date/db-$(date +%H%M).sql ]; then
            mysqldump --all-databases | gzip -c > $backup_path/$current_date/db-$(date +%H%M).sql.gz
    fi
fi
# delete backup older than x day
find $backup_path -type d -mtime +$expiry_date | xargs rm -Rf

For MySQL/MariaDB credential, most of the distro allow root to login without the credential, if you’re using normal user create ~/.my.cnf

[client]
user = USER
password = PASSWORD

test the script by running /opt/mysql-backup.sh command, it won’t gave any output if success. Check the /backup folder

ls -lR /backup/mysql

create a crontab to run the script every day. Edit the crontab files

crontab -e

put

@daily /usr/bin/bash /opt/mysql-backup.sh

after few days check the /backup directory, you’ll get a nice stucture folder