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.