Backup MySQL/MariaDB to SPFS
In this blog post we're explaining different methods of backing up and restoring your MariaDB/MySQL with standard tools direct to a Spectrum Protect environment using SPFS.
Before your start make sure you have SPFS installed and configured. If you need help to configure SPFS please see our blog Installing SPFS on Linux
Prerequisite
Make sure you have following i
|
Path to MySQL/MariaDB configuration files |
|
|
MySQL/MariaDB port number |
See port entry in MySQL/MariaDB config file, default port is 3306 |
|
ID and Password for privileged MySQL/MariaDB user |
Export your Database User as $DB_USER and export user password as $DB_PASSWORD |
|
Path to SPFS Mountpoint |
/backup |
Backup Methods
MySQL and MariaDB have a set of tools build in and here do we show how you can use them together with SPFS to backup and restore your databases.
mysqldump
To backup a single database with mysqldump you can use following command
mysqldump \
--single-transaction \
--username=$DB_USER \
--password=$DB_PASSWORD \
<DATABASE NAME> > /<SPFS MOUNTPOINT>/<DATABASE_NAME>.dump
If you want to backup all databases at the same time you can use following command.
for DB in $(mysql -e 'show databases' -s --skip-column-names
do
mysqldump $DB_NAME > "/backup/$DB.dump"
if [ $? –ne 0]; RC=$(( $RC + 1 ))
done
exit $RC
When you need to restore a database from mysqldump you can do following command
mysql \
--username=$DB_USER \
--password=$DB_PASS \
$DB_NAME < /backup/$DB_NAME.dump
mysqlbackup
To backup all data at the same time with mysqlbackup you can use following command
mysqlbackup \
--user=$DB_USER \
--password \
--host=$DB_HOST \
--backup-image=/backup/my.bkp \
--backup-dir=/backup/ \
backup-to-image
When you need to restore you will restore everything when using mysqlbackup
mysqlbackup \
--datadir=/var/lib/mysql \
--backup-image=/backup/my.bkp \
--backup-dir=/backup/ \
copy-back-and-apply-log
mydumper
In Ubuntu and Debian you have the backup tool called mydumper and to backup your database you can use following command
mydumper \
--database=$DB_NAME \
--host=$DB_HOST \
--user=$DB_USER \
--password=$DB_PASS \
--outputdir=/backup/ \
--rows=500000 \
--compress \
--build-empty-files \
--threads=2 \
--compress-protocol
When you want to restore a database with mydumper you can do that easy by following command.
myloader \
--database=$DB_NAME \
--directory=$DB_DUMP \
--queries-per-transaction=50000 \
--threads=10 \
--compress-protocol \
--verbose=3
I hope you found this blog post useful and I wish you a great summer!
