The Ultimate MySQL Database Backup Script

This article and video are part of “The Ultimate Backup Script” serie we are creating to provide you with MySQL database backup scripts that not only allow you to create database backups, but also upload the backup dumps to a remote storage and automate the process daily.

Table of Contents

Why do you need a MySQL database backup?

One might think why backup is necessary for my database? The answer is simple, backup creates a copy of your physical, logical, and operational data. Which you can store at any safe place such as Amazon S3. This copy comes into use if the running database gets corrupted. Database backup can include files like control files, datafiles, and archived redo logs.

If you're looking for a no-code, hastle free solution to setup and automated your MySQL database backed up, have a look at SimpleBackups.

What are we going to do cover this article?

We're going to write a simple script that backup your MySQL database and store it on your Amazon S3 bucket. We will also automate the process using Cron.

Few points to explain before we start:

Why Amazon S3?
For this tutorial, we have chosen Amazon S3 as it is a very common choice.
You can do the same thing if you would like to use another cloud storage provider. The instructions won’t differ a lot as long as the cloud provider is S3-compatible.

What is Cron?
Cron is a software utility that offers time-based job scheduling. It supports Unix computer operating systems. To set up software environments, the developer uses Cron. He/she schedules commands or shell scripts so that they run at chosen times. It could be daily, once a week, or any interval as desired.

What is Chmod?
The chmod a short command of ‘change mode’ enables the admin to set rules for file handling. In other words, with the help of a “chmod” system call. An administrator can change the access permissions of file system objects.

🧑‍💻 You can automate the creation of backup and storing it to Amazon S3 within a few minutes. Below bullets brief about what you are going to learn in this part of the article:

1. Create a script that automates the MySQL backup directory creation
2. Upload/sync the backups with Amazon S3
3. Cron will run this command every day (to back up)

1. Creating a Shell Script for MySQL Database Backup

To streamline the process of generating a shell script for MySQL database backup, follow these steps:

Navigate to Your Home Directory:

cd ~

Create a Directory for Your Scripts:

mkdir scripts
cd scripts

Create a New Script File:

nano db_backup.sh

Paste the Following Script into db_backup.sh:

#!/bin/bash
# Set the backup directory with the current date as the subfolder name
DIR=$(date +%d-%m-%y)
DEST=~/db_backups/$DIR
mkdir -p $DEST

# Replace the placeholders with your MySQL server details
MYSQL_HOST="mysql_hostname"
MYSQL_USER="mysql_user"
MYSQL_PASSWORD="mysql_password"
DATABASE_NAME="database_name"

# Use mysqldump to create a SQL backup file
mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME > $DEST/dbbackup.sql

Be sure to replace the placeholders (mysql_hostname, mysql_user, mysql_password, database_name) with your specific MySQL server information.

Make the Script Executable:

chmod +x ~/scripts/db_backup.sh

Now, you have a fully functional shell script that automates the process of MySQL database backup. Simply run this script to create regular backups of your MySQL database for added data security and peace of mind.

2. Configure the AWS CLI

We'll use the AWS CLI to sync the backup files with Amazon S3.

Before installing the AWS CLI you need to install python-pi. Type the following commands:

apt-get update
apt-get -y install python-pip
curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"

Install the AWS CLI

Type the following command:

pip install awscli

Set up AWS key & Secret

Configuration and credential file settings

cd ~
mkdir .aws
nano ~/.aws/config

Paste in key_id and secret_access_key as shown below

[default]
aws_access_key_id=AKIAIOSFODNN7EXAMPLE
aws_secret_access_key=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

3. Create a script to sync the backup with Amazon S3

Now, let's build the script that will allow you to sync your MySQL database backups with Amazon S3.
This script will be used in conjunction with the script you created in the previous section.

Copy and paste the script into db_sync.sh.

#!/bin/bash

# Set your AWS CLI path (update with your actual path if needed)
AWS_CLI_PATH="/usr/local/bin/aws"

# Set your Amazon S3 bucket name
S3_BUCKET="my-bucket-name"

# Specify the source directory where your backups are stored
SOURCE_DIR="~/db_backups"

# Synchronize the backups with Amazon S3
$AWS_CLI_PATH s3 sync $SOURCE_DIR s3://$S3_BUCKET

Save the file and exit the text editor.

Make the script executable:

chmod +x ~/scripts/db_sync.sh

Now, you have a shell script that can be executed to sync your MySQL database backups with your specified Amazon S3 bucket. Don't forget to replace "my-bucket-name" with your actual S3 bucket name and update the AWS_CLI_PATH if necessary to point to your AWS CLI installation location.

Automate your MySQL database backup with CRON

The last step is to automate the process of MySQL database backup with CRON. To do this, follow these steps:

crontab -e

Paste the below commands at the bottom to automate the process

0 0 * * * ~/scripts/db_backup.sh # take a backup every midnight
0 2 * * * ~/scripts/db_sync.sh # upload the backup at 2am

This way the backup script will run and also sync with Amazon S3 daily.