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

mysql credentials

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
folder structure

4 thoughts on “How To Backup MySQL/MariaDB Daily”

  1. Could you adapt this script for the mysqlsh utility? It has compression and parallelism out of the box.

    i.e.

    root@mydb:/backup/mysqlsh_backups/manual# read -s pw
    root@mydb:/backup/mysqlsh_backups/manual# mysqlsh dba@localhost -e “util.dumpInstance(‘/backup/mysqlsh_backups/manual’,{dryRun: true, showProgress: ‘false’, threads: 8, consistent: false})” –password=”$pw”

    Reply
  2. I forgot to mention that the my.cnf file should be part of the backup as well. We have had situation where the cloud provider, colleague or even DBA upgraded the database and somehow the YUM package manager remove/overwrite the cnf file without a backup.

    Reply
  3. Hello,
    there’s small issue which can be a problem for the beginners following the guides.

    Script was saved as:
    opt/mysql-backup.sh

    But in crontab it was defined with different name
    @daily /usr/bin/bash /opt/backup-mysql.sh

    so it should be
    @daily /usr/bin/bash /opt/mysql-backup.sh

    Thank you for the guide!

    Reply

Leave a Comment