How to Automatically Backup a MySQL or MariaDB Server with MySQLDump

The Problem

Databases like MySQL and MariaDB can get corrupted pretty easily, due to the fact that much of the database is stored in RAM, so a hard crash can leave entire tables completely corrupted. This is something I know first hand from experience. A disk backup or snapshot is not sufficient to backup your database!

The Solution

A included program called MySQLDump! (Man PageMySQLDump allows you export the entire contents of your database into a text file that is standard SQL commands. This means you can rebuild your database by just executing the text file in any SQL language. This script can be automated with a cron job to make sure that it happens daily!

This script does a few things:

  • Dumps the entire contents of the database to a text file
  • Compresses the file into a zip file
  • Logs what is going on
  • (optional) You can expand this to copy the zip file to a NAS using RSYNC

What You Need to Get Started

Getting this setup does not really require too much! I am running this on Ubuntu 20.04, with MariaDB, but this should work with any OS running MySQL or MariaDB. MySQLDump comes stock with these! You will also need to have both zip and crontab installed on your machine.

How to Setup the Automatic Backup

Step 1: Create a New User for the Backup to Run On

We want to use a new SQL user on your database that has read only access to the database tables. This is for both security, and simplicity. (You do not want a root password just stored on your system in plain text)

To do this login to your database with a root account by running:

sudo mysql

You then will create a new backup user by executing the following SQL commands to create the backup user

GRANT LOCK TABLES, SELECT ON DATABASE_NAME.* TO ‘BACKUP_USER’@’localhost’

IDENTIFIED BY ‘PASSWORD’; #Change the password to a random string

FLUSH PRIVILEGES;

Step 2: Create the Backup Script

We are going to be using a backup script that I found and modified

First go to your user folder and create a new backup file and close the permissions.

cd ~

mkdir Database_Backup

touch db_backup.sh

sudo chmod 700 db_backup.sh

nano db_backup.sh

Now you are going to paste in this code: Updating the username password, and folder locations:

Copy to Clipboard

You should now be able to test the script by running

./db_backup.sh 

which should create a zipped output in the ~/Database_Backup directory

Step 3: Execute it in a cron job

We now want the cronjob to execute the backup at least once a day. To do this simply type:

crontab -e

To edit the crontab schedule. We are going to want to add a line to the end of this file which runs the backup script

30 22 * * * /home/will/db_backup.sh #edit this to be where your script is

in this case the backup will be run every day at 10:30.

And now you are done! the backups should automatically be running!

Step 4: Checking the Backup

Check back in in a few days and your ~/Database_Backup folder should be full of backups!

You can unzip one of these files to see the data within it!

One Comment
  1. Jay April 22, 2024 at 10:18 pm - Reply

    I got the error:

    User
    mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces

    Needed to add PROCESS privilege to backup user. Also, you’re using –all-databases flag so you’d need to grant privileges on *.*

    Nice script though. Currently using it with the modifications :D

Leave A Comment