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

  • /etc/my.cnf

  • /etc/mysql/my.cnf

  • $MYSQL_HOME/my.cnf

  • [datadir]/my.cnf

  • ~/.my.cnf

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!

#AtYourService
Christian Petersson

Subscribe to blog