Webinar slides: Backup Management for MySQL, MariaDB, PostgreSQL & MongoDB with ClusterControl

November 2018
Backup Management for
MySQL, MariaDB, PostgreSQL &
MongoDB with ClusterControl
Bartłomiej Oleś
Presenter
bart@severalnines.com

Copyright 2018 Severalnines AB
I'm Jean-Jérôme from the Severalnines Team and
I'm your host for today's webinar!
Feel free to ask any questions in the Questions
section of this application or via the Chat box.
You can also contact me directly via the chat box
or via email: info@severalnines.com during or
after the webinar.
Your host & some logistics

Free to download
Initial 30 days Enterprise trial
Converts into free Community Edition
Enterprise / paid versions available

Automation & Management
Deployment (Free Community)
● Deploy a Cluster in Minutes
○ On-Prem
○ Cloud (AWS/Azure/Google) - paid
Monitoring (Free Community)
● Systems View with 1 sec Resolution
● DB / OS stats & Performance Advisors
● Configurable Dashboards
● Query Analyzer
● Real-time / historical
Management (Paid Features)
● Backup Management
● Upgrades & Patching
● Security & Compliance
● Operational Reports
● Automatic Recovery & Repair
● Performance Management
● Automatic Performance Advisors

Supported Databases

Our Customers

Poll - multiple choice
Which databases do you backup?
● MySQL
● MariaDB
● PostgreSQL
● MongoDB
● Other

Copyright 2018 Severalnines ABCopyright 2018 Severalnines AB
Protecting Data
● key concerns
● outage timeline - RPO, RTO
● business expectations
● matching disaster recovery plans to the
business requirements
Database Backup Best Practices
● what can go wrong
● position or time based point in time recovery
● logical vs physical backups
● upload to the cloud
● encryption & compression of backup data
● backup retention
Agenda
ClusterControl - Backup Management System
● backup system architecture
● supported backup methods
Polyglot Backup Management System (Demo)
● schedule, manage & operate backups for
MySQL, MariaDB, MongoDB & PostgreSQL
● define backup policies, retention, history
● validation, automatic restore verification
● reporting

Protecting Data

Protecting Data - Key Concerns
● No room for failure
● Restore complexity
● Various recovery scenarios
● Always busy with other tasks, backups can wait?
● Implementation time?
● Is my backup good?
● How long it will take to perform restore?
Various scenarios?
● Centralized backup management vs. cron scripts
● Backup notification system
● Database growth vs restore time. Monitoring
metrics
● Dependant on other teams

● Protect all your business data
○ One system for your open source databases
○ Lower (Recovery Point Objective) RPO with point-in-time restore
○ Automatic test of backups ensures data integrity
○ Reporting on which databases are not backed up, or backup success/failure
● Cost effective disaster recovery and data archival on-prem or cloud
○ Retention policies for compliance
○ Data is compressed, encrypted and can be automatically uploaded to the cloud
● Secure your business data
○ Data encryption
■ At-rest
■ In-transit
Business Expectations

Matching Disaster Recovery Plans to the Business

Backup with No Hot Site
● Physical vs Logical backup
○ High impact on RTO
● Combine Full & Incremental
○ PITR-compatible to reduce RPO
● Schrödinger’s backup
○ “The condition of any backup is
unknown until a restore is attempted”
● Encryption
● Keep a copy of latest backup in active site

Backup with Hot Site
● We can reinstall DBs and apps
from scratch and restore data
● Recovery time predictable
● In case of AWS, pre-configured
AMIs can be used to quickly
provision the application
environment

Asynchronous Replication to Hot Site
● Low RTO
○ ‘Almost current’ data
enables fast failover
● Low RPO
● Add a delayed slave to guard
against operator error
● Backup still important

Synchronous Replication to Hot Site
● Highest tier of DR
○ Minimal RPO and RTO
● Data on primary site and hot
sites have same transactional
state
○ Failover instantaneous
and automatic
● Failure detection time is main
culprit that adds to RTO
● 3 sites to avoid network
partitioning

Database Backup Best Practices

What Can Go Wrong ?
● No backup
● Home grown script
● Linux cron as backup scheduler?
● When did you test your backup?
● Complex manual restore procedure
● Staff training
● Restore on standalone system

Position Or Time Based - Point In Time Recovery
● Creating backup compatible with Point-In-Time Recovery
● Point-In-Time Recovery from a backup
● “Time Based” PITR
● “Position Based” PITR

Logical vs Physical backups
● Logical backups
○ Plain text, in human-readable
○ Extract even a single row and
restore it
○ Restoring an entire logical
backup can be quite slow
○ Database has to process all of
the queries required to load
● Physical backups
○ Grab the data as it is
○ Data is just a bunch of files
stored on some disk
○ There might be database
changes in the meantime
○ Any physical backup has to be
consistent
○ Great for recovering whole data
sets - the speed is limited by
hardware: disk and sometimes
network (if you copy the data
over the network)

Upload to the cloud
● Additional backup retention in cloud
● Recent backups stored in local data
centre
● Encrypt files in transit
● Encrypt files stored in cloud
● Separate access for backup key
location

Encryption Of Backup Data
● Automatic encryption
● Key management
● Regulatory requirements
● Separate backup keys from data
backup
● Keys needs backup as well

Compression of Backup Data
● Compression levels
● CPU vs disk utilization
● Parallel compression
● Internal compression vs external

Backup Retention
● Local Server
○ Up to 1 week
● Local Datacenter
○ Up to 2 weeks
● Remote Datacenter
○ Up to 4 weeks
○ Plus keep monthly backups &
annual backups as required

Test & Verification of backup
Backup + Test/Verification + Restore

Poll - multiple choice
How do you backup your databases?
● Homegrown scripts
● Storage based (e.g. LVM snapshots, NetApp)
● DB Vendor specific products (e.g. Oracle Enterprise, MongoDB Ops
Manager,...)
● Multi-database backup products (e.g., ClusterControl)
● Other / Relying on my DBaaS/cloud service provider

ClusterControl - Backup Management
System

ClusterControl Backup Architecture

Backup Management
Cluster Type Supported Backup Method
MySQL (Replication,
Galera, NDB Cluster,
Group Replication,
MariaDB)
● mysqldump
● Percona Xtrabackup (full and incremental)
● MariaDB Backup (MariaDB only)
● NDB Backup (MySQL Cluster only)
MongoDB (Replica
Set, Sharded
Cluster)
● mongodump
● mongodb-consistent-backup ( Percona Server for MongoDB only)
PostgreSQL
(Streaming
Replication)
● Pg_dumpall
● pg_basebackup

Live Demo

Cluster Control Backup Management
Demo
● Schedule, manage and operate backups
● Define backup policies, retention, history
● Validation - Automatic restore verification
● Backup reporting

ClusterControl CLI - s9s backup
$ s9s backup --create --backup-method=mysqldump --cluster-id=1 --nodes=10.0.0.5:3306
--backup-directory=/storage/backups
Job with ID 4 registered
$ s9s backup --list --cluster-id=1 --long --human-readable
ID CID STATE OWNER HOSTNAME CREATED SIZE FILENAME
1 1 COMPLETED dba 10.0.0.5 07:21:39 252K mysqldump_2018-11-11_072135_mysqldb.sql.gz
1 1 COMPLETED dba 10.0.0.5 07:21:43 1014 mysqldump_2018-11-11_072135_schema.sql.gz
1 1 COMPLETED dba 10.0.0.5 07:22:03 109M mysqldump_2018-11-11_072135_data.sql.gz
1 1 COMPLETED dba 10.0.0.5 07:22:07 679
mysqldump_2018-11-11_072135_triggerseventsroutines.sql.gz
2 1 COMPLETED dba 10.0.0.5 07:30:20 252K mysqldump_2018-11-11_073016_mysqldb.sql.gz
2 1 COMPLETED dba 10.0.0.5 07:30:24 1014 mysqldump_2018-11-11_073016_schema.sql.gz
2 1 COMPLETED dba 10.0.0.5 07:30:44 109M mysqldump_2018-11-11_073016_data.sql.gz
2 1 COMPLETED dba 10.0.0.5 07:30:49 679
mysqldump_2018-11-11_073016_triggerseventsroutines.sql.gz

Poll - multiple choice (choose one of below)
How often do you test your backups?
● Every day
● Every month
● Once a quarter
● I don’t test my backups
● Other

Q & A

End of Year Promotion
Get Three Months Free
25% In
Savings
Just Sign By December 20th!
with an Annual Contract

Links & Resources
● Backup Management with ClusterControl
● Download / install ClusterControl
● ClusterControl Community Edition Page
● Contact us: info@severalnines.com

Webinar slides: Backup Management for MySQL, MariaDB, PostgreSQL & MongoDB with ClusterControl

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to Webinar slides: Backup Management for MySQL, MariaDB, PostgreSQL & MongoDB with ClusterControl

Similar to Webinar slides: Backup Management for MySQL, MariaDB, PostgreSQL & MongoDB with ClusterControl (20)

More from Severalnines

More from Severalnines (14)

Recently uploaded

Recently uploaded (20)

Webinar slides: Backup Management for MySQL, MariaDB, PostgreSQL & MongoDB with ClusterControl