MariaDB Backups

Backup types

------------

1. Logical backups

  - information and records are in plain text

  - DB structure is retrieve by querying the DB

  - slower because server must access DB and convert it into logical format

  - performed while server is online

  - backups don't include log/configuration files

2. Physical backups

  - consist if raw copies of DB directories

  - output is more compact

  - can include config/log files

  - portable only to other machines w/ similar H/W and software

  - faster than logical backups

  - should be performed while server is offline or while tables in all DBs

    are locked to prevent changes during backup

Performing Logical Backups

--------------------------

mysqldmp -u root -p inventory > /backup/inventory.dmp

  -> -p will prompt for password

  -> inventory is the DB to backup

  -> use --all-databases to logically backup all DBs

  -> mysqldump requires atleast SELECT, SHOW VIEW, and TRIGGER privileges

  -> other useful options:

     --add-drop-tables     -- adds DROP TABLE before each CREATE TABLE statement

     --no-data             -- dumps only DB structure, not contents

     --lock-all-tables     -- no new record can be inserted during backup

     --add-drop-databases  -- adds DROP DATABASE before each CREATE DATABASE

                              statement

Performing Physical Backups

---------------------------

Example of tools that is used are ibbackup, cp, mysqlhotcopy, and lvm.

Using LVM keps downtime of DB short. This great when DB is put on

a dedicated partition.

mysqladmin variables | grep datadir # -- checks where MariaDB files are stored

df /var/lib/mysql # -- verifies on what logical volume it resides

vgdisplay vg0 | grep Free # -- prints free space on volume group

systemctl stop mariadb # -- shuts down service

lvcreate -L20G -s -n mariadb-backup /dev/vg0/mariadb  -- creates LVM snapshot

mount /dev/vg0/mariadb-backup /mnt/snapshot # -- you mount the snapshot

tar cvf /root/mydb-backup.tar /mnt/snapshot

lvremove /dev/vg0/mariadb-backup

systemctl start mariadb # -- starts service again

Restoring a backup

------------------

Logical restore:

mysql -u root -p inventory < /backup/mariadb.dmp

mysql < /home/student/mydb.mysqldump

Physical restore:

systemctl stop mariadb

rm -rf /var/lib/mysql/*

tar xvf /root/mydb-backup.tar -C /