Skip to content

mariadb-dba-06-backup-and-restore.md

Latest commit

 

History

History
190 lines (132 loc) · 5.84 KB

mariadb-dba-06-backup-and-restore.md

File metadata and controls

190 lines (132 loc) · 5.84 KB

BACKUP AND RESTORE

Learning Objectives

  • Explain key backup principles and their importance, including the importance of verifying and testing backups
  • Demonstrate and compare how to create full backups using different backup methods such as MariaDB Backup and mariadb-dump
  • Apply and assess recovery methods to restore data in MariaDB
  • Design and implement a backup and recovery plan and schedule

BACKING UP DATA

BACKUP PRINCIPLES

  • Helps ensure high availability

  • Cannot prevent user errors

  • Backup data, logs, and configuration files

  • Store backups in multiple locations

    • Onsite for fast access
    • Offsite for security

  • Create consistent backups

  • Backup methods

    • Physical Backup
    • Logical Backup

  • Binary logs for Point-in-Time recovery

  • Always test the backup and recovery regularly

Basic Types of Backup Methods

Physical or Binary Backup

MariaDB Backup, manual data directory copy (after stopping the MariaDB daemon), or VM/Cloud/LVM/ZFS snapshots

  • A physical backup typically refers to a copy of the entire on disk database
  • Used to mitigate a single or multiple host failure
  • Can build replicas quickly
  • Quick full recovery time
  • Slow to recover single row or table (user error)
  • Recovered only to the same storage engine with the same tablespaces

Logical Backup

mariadb-dump, mydumper, SELECT INTO OUTFILE

  • Produces SQL files containing data that can regenerate a database
  • Easily backup and restore single row, table, or database
  • Can separate schema and data
  • Can work across versions and forks
  • SQL can be parsed with standard UNIX tools
  • Restore process automatically replicated
  • Long full restore time
  • A SQL dump is independent of storage engine, and can be restored to a different storage engine, or used for migration
  • Process can be slow and requires locks

MARIADB BACKUP

MariaDB's own backup package (mariabackup) is available from MariaDB repositories

  • Supports MariaDB’s features and storage engines
  • Supports encryption
  • Supports incremental backups
  • Also available on Windows
  • MariaDB Enterprise Backup contains improvements such as reduced locking

Backing Up Data - MariaDB Backup

Full Backups

To use MariaDB Backup you need to create a user on your MariaDB Server with RELOAD, LOCK TABLES and REPLICATION CLIENT privileges.

MariaDB [(none)]> CREATE USER '<backupuser>'@'localhost' IDENTIFIED BY '<password>';
MariaDB [(none)]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO '<backupuser>'@'localhost';

To take a full backup at the OS command-line use:

# mariabackup --backup --target-dir <backupdir> --user <backupuser> --password <password>
# mariabackup --prepare --target-dir <backupdir>

BACKING UP DATA - MARIADB-DUMP

The standard MariaDB logical dump tool copies schema and data to a SQL text file

To use mariadb-dump you need to create a user on your MariaDB Server with SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, and TRIGGER privileges

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'mariadb';

GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backupuser'@'localhost';

To take a full backup at the OS command line use

# mariadb-dump -u backupuser -p --all-databases --single-transaction --flush-logs -r /path/to/full-backup-YYYYMMDD.sql

PLANNING AND SCHEDULING BACKUPS

Take inventory of databases

  • List of databases & tables
  • Number of rows
  • Frequency of changes
  • How active
  • Sensitivity of data

Write a backup schedule

Write a verification schedule

Verify and practice restoring from backups

  • Backups are useless unless they have been tested
  • Recovery procedures should be tested at least once per quarter
  • Keep track of restore times during tests

Naming standard for backup files

Frequency — days and times

Location — security and off-site

RESTORING DATA

RESTORING DATA - MARIADB BACKUP

Full Backups

Working with a full backup taken with MariaDB Backup, you restore the backup into an empty data directory

# mariabackup --copy-back --target-dir <backupdir> --datadir <datadir>

Afterwards it might be necessary to set the ownership of the data directory contents

# chown -R mysql:mysql <datadir>

RESTORING DATA - MARIADB-DUMP

Restoring from a logical backup

# mariadb < /path/to/full-backup-YYYYMMDD.sql

Or to avoid interpretation by the shell

# mariadb
SOURCE /path/to/full-backup-YYYYMMDD.sql

You can also use standard linux tools such as vi or grep to edit or extract the backup

# grep city worldbackup.sql
CREATE TABLE `world`.`city` ( ...

Verifying Backups

  • Backups do not exist until you are certain they can be recovered so prepare a backup immediately
  • Some of the reasons for preparing backups are to:
    • Enable recovery during a disaster
    • Migrate to a new server
    • Build a new replica
    • Build a test environment that is cloned from production
  • Recovery procedures should be tested on a regular basis, at least once a quarter
  • Validate the correctness of your backups
  • Provide metrics on recovery time and recovery point
  • Test by refreshing data to stage/test or development
  • Test different kinds of restores as well
    • Do I know how to restore all of the data?
    • Do I know how to restore just a single table?
    • Do I know how to perform Point-in-Time recovery?
    • Do I know how to restore a replica?
  • Restore and check for:
    • Errors
    • Data Size
    • Checksums
    • Resultsets

Lesson Summary

  • Explain key backup principles and their importance, including the importance of verifying and testing backups
  • Demonstrate and compare how to create full backups using different backup methods such as MariaDB Backup and mariadb-dump
  • Apply and assess recovery methods to restore data in MariaDB
  • Design and implement a backup and recovery plan and schedule
⚡