Stefkoff's Blog

Introduction

When you have a database server (production or not), you may want to back up the database regular, because everything can happen - disk failure, hardware failure, etc.

In this blog post, I will how you how to back up all databases, compress them and upload them to AWS S3 object storage.

Requirements

• This tutorial will be valid for Ubuntu 22.04.
• You need to have a running MySQL server
• You need to have mysqldump script installed on the machine that you will making the back-up. It could be installed

by sudo apt-get install mysql-client

• You also need to have root access to

all the database that needs to be dumped.

• Make sure that you will have enough free disk space, because depending on the size of the file, you may run out of

disk space when the dump is generated

• You need to have an AWS account and credentials (AWS KEY and AWS SECRET KEY)

Dump all databases to an .sql file

In order to dump all the database, run the following command:

mysqldump -u USER -p --all-databases --skip-add-locks > alldb.sql // Replace USER with your user that has access to all the database

• --skip-lock-tables will not lock the tables when a query is executed

When running this command, you will be prompted to enter the password for your user. If you wish to include the password at the command you can do the following:

mysqldump -u USER -pPASSWRD --all-databases --skip-add-locks > alldb.sql

You can see that the password (PASSWORD) is appended immediately after -p flag. Note: if the password contains special characters, you may surround the password in ' like this:

mysqldump -u USER -p'PASSWORD' --all-databases --skip-add-locks > alldb.sql

Exposing the password to the command line is not a good practice, because it can be seen by anyone

After some amount of time, your .sql file will be generated and will contain the SQL of all the databases from the server.

Compressing the .sql file

When we dump all the databases, there were dumped in a text format, which takes a lot of space. Now, we will modify the above command in order to compress the .sql file and reduce the size of the dump file.

We are going to use GZIP command. Make sure that you have installed gzip in your Ubuntu installations:

sudo apt update
sudo apt install gzip

Now, execute the following command:

mysqldump -u USER -p'PASSWORD' --all-databases --skip-add-locks | gzip > backup.$(date +%F.%H%M%S).sql.gz

Here we are using the same command, but instead to redirect the output from the dump command, we are piping the output to gzip and redirecting the output from the compression to a .sql.gz file. Also, for the name of the compressed file, we are specifying the current date

Executing this command will take more time from the last time, but at least the end result will save some space.

If you list the content of the directory where the back file is, you will see the file is actually there:

stefkoff@stefkoff-mysql-server:~$ ls -la | grep backup.
-rw-rw-r--  1 stefkoff stefkoff  8860655596 May  9 12:30 backup.2024-05-09.115631.sql.gz
stefkoff@stefkoff-mysql-server:~$ 

Creating the bucket

Now, when we have the file, it is time to create a bucket inside AWS. First login to your console, navigate the S3, and create a new bucket. Enter the name for you bucket and the rest of the options as there are.

Upload the dump to S3

We are going to use the s3cmd package in order to upload the files to the S3 bucket. If you do not have the package installed on your system yet, you can install it by:

sudo apt update
sudo apt install s3cmd

After s3cmd is installed, you need to configure it, in order to save your credentials to a local file for further usage. You have to run the following command:

s3cmd --configure

First af all, you will be prompted to your Access Key. Then the same for the Secret Key. Next is the default region. It is good to specify the default region of you AWS S3 bucket. Then confirm the endpoint s3.amazonaws.com (or just press enter). Confirm all the rest of the setting and hit two Yes on the last questions. At the end s3cmd will generate a config if file as follows /home/$USER/.s3cfg. Now we can use s3cmd to upload files to the bucket.

In order to upload the backup file, you can execute the following command:

s3cmd put backup.2024-05-09.115631.sql.gz s3://BUCKET_NAME // replace BUCKET_NAME with your S3 actual bucket name

After few minutes (depending on the file size) you file will be uploaded to AWS S3 bucket with the same name as it is.

Putting all together

We have managed to run all of this, but we have to do it at once and probably by some cron task, so it can be done automatically at certain time of the day. The thinks that we will do in a single shell file are the following:

• Create the compressed back-up file
• Upload the file to AWS S3

Here is how you can do it in one shell-script:

#!/bin/bash

# fail on every error
set -e

# save the current date to be used for the filenames later
CURRENT_DATE=$(date +%F.%H%M%S)
# variable for the backup filename
BACKUP_FILENAME="backup.$CURRENT_DATE.sql.gz"
# mysql user
MYSQL_USER=root
# mysql password
MYSQL_PASSWORD=password
#extra options that will be passed to mysql dump command
MYSQL_EXTRA_PARAMS="--all-databases --skip-add-locks"
# mysqldump absolute location
MYSQLDUMP_COMMAND=$(which mysqldump)
S3_BUCKET_NAME="stefkoff-db-dumps"

# make sure that mysqldump is installed
if [ -z "$MYSQLDUMP_COMMAND" ]; then
  echo "mysqldump command not found. Exiting"
  exit 1
fi

# s3cmd absolute path
S3CMD_COMMAND=$(which s3cmd)
if [ -z "$S3CMD_COMMAND" ]; then
  echo "s3cmd command not found. Exiting"
  exit 1
fi

#gzip absolute path
GZIP_COMMAND=$(which gzip)

# make sure gip is installed
if [ -z "$GZIP_COMMAND" ]; then
  echo "gzip command not found. Exiting"
  exit 1
fi

# dump all database
/bin/bash -c "$MYSQLDUMP_COMMAND -u $MYSQL_USER -p'$MYSQL_PASSWORD' $MYSQL_EXTRA_PARAMS | $GZIP_COMMAND > $BACKUP_FILENAME"

# check if the backup file exists
if [ ! -f "$BACKUP_FILENAME" ]; then
  echo "Something went wrong and cannot upload the file to S3. Exiting"
  exit 1
fi

/bin/bash -c "$S3CMD_COMMAND put $BACKUP_FILENAME s3://$S3_BUCKET_NAME"

# remove the backup file, one it is successfully uploaded to the bucket
rm "$BACKUP_FILENAME"

# (OPTIONAL) remove previously uploaded files
/bin/bash -c "$S3CMD_COMMAND ls s3://$S3_BUCKET_NAME | awk '{print \$4}' | grep -v s3://$S3_BUCKET_NAME/$BACKUP_FILENAME | xargs -I {} $S3CMD_COMMAND del {}"

NOTE the last command, it will delete the previous uploaded backups from the bucket

Save the script as backup-db.sh somewhere and allow the users to execute the file: chmod +x backup-db.sh

If you execute the file by ./backup-db.sh all above the operations should be done in one time

Setup cron task

Now we want to execute the script at certain recurring time, so we want to be sure that we will have a "fresh" backup, before the point of failure. You can add the following cron record:

0 0 * * 6 /bin/bash -c "cd /home/USER && ./backup-db.sh" // replace USER with your user

In my case, I will execute the command once a week (sunday at 00:00). It is good to have a daily back, and you can replace it by:

0 0 * * * /bin/bash -c "cd /home/USER && ./backup-db.sh"

Conclusion

Doing a regular backups, especially for a DB servers is a MUST and everyone have to do it, not only on production environments, but also for a local usage, because when we lost the data, recovering will take a huge amount of time, which can be critical when working.