How to backup MySQL databases before doing a MySQL upgrade

Introduction

Having database backups before performing an upgrade is critical. Do not proceed with MySQL/MariaDB upgrades without creating backups of those databases into sql dumps. It is also crucial to pay attention to any errors ensuring the dumps are good.

Procedure

There are two methods. The first is to dump all the databases into a single SQL dump but the caveat here is that this can only be used to restore the entire datadir which is typically /var/lib/mysql.

This will dump that entire MySQL datadir into one compressed file.

mysqldump --all-databases | bzip2 -c > /home/databasebackup.sql.bz2

The second method and is far better because it can be used to restore a single database if needed.

Create a script like /root/backupdbs.sh with the following contents:

#!/bin/bash

BACKUPDIR="/home/dbbackups/"

if [ ! -e $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
chmod 700 $BACKUPDIR
fi

for x in `mysqlshow | grep -v \_schema | awk -F "| " '{print $2}'`; do mysqldump $x | bzip2 -9czq > $BACKUPDIR$x-sql.bz2; done

Then run the following:

bash /root/backupdbs.sh

This will create the folder /home/dbbackups/ and dump individual database backups into /home/dbbackups/.

In the end, it is best to have both as they both have pro's and con's.

If you need to restore any of these sql dumps they need to first be uncompressed. Please refer to the following document on uncompressing bzip2 files.

How to uncompress bzip2 files with .bz2 extensions.