mariadbwebinardr07301000156458558219.pdf

MariaDB disaster recovery

1. Best practices:
Disaster recovery with
MariaDB Platform
Shane K Johnson, Senior Director of Product Marketing, MariaDB Corporation

2. Agenda
●Introduction
●Disaster recovery options
○Point-in-time restore
○Point-in-time rollback
○Delayed replication
○System-versioned tables
○Multiple data centers
●Reference architectures

3. The cost of downtime
●Small companies: $8,000/hour
●Mid-size organizations: $74,000/hour
●Large enterprises: $700,000/hour
●Amazon (2018 Prime Day): $72-99 million, 75 minute outage
●eCommerce:
○walmart.com: $2.4M/hour
○homedepot.com: $858K/hour
○bestbuy.com: $698K/hour
○costco.com: $696K/hour
○macys.com: $664K/hour

4. Goals
●Recovery time objective (RTO)
○Limit on duration of downtime, shorter is better
○How long should it take to restore the database
●Recovery point objective (RPO)
○Limit on amount of data lost, small is better
○How much data will be lost after the recovery

5. Methods
●Backups
○As frequent as possible, without interrupting applications
○Stored on tape (yesterday), stored on cloud storage (today)
●Replication
○Maintain a duplicate system off-site (multiple data centers, hybrid cloud)
●Preventative
○High availability
○Hardware – surge protectors, UPS, etc.
○Software – antivirus and firewall

6. Types of disasters
●Power outage
●Fire
●Network damage
Total failure
●Malicious attack/accident
●Deleted data
●Modified data
●Storage failure
Data loss/corruption

7. Big outages last year
●Amazon Prime Day (due to Aurora Postgres)
●Google Cloud (due to Google Cloud Datastore)
●Equinix (due to storm)
●Amazon Alexa (due to storm)
●Iomart (due to farmer, yep, he cut the fiber cable)
●Microsoft Office 365 (due to software update)
●AWS RDS (due to hardware/network failure in North Virginia region)
●Microsoft Azure (due to extreme heat in Ireland data center)

8. MariaDB Platform
disaster recovery options

9. Point-in-time restore
●Restore data to a previous point in time (offline process)
a.Empty the database or install a new database
b.Restore data from a backup
c.Start the database
d.Replay transactions since the last backup

10. Best practice: create a backup user
CREATE USER 'mariabackup'@'localhost'
IDENTIFIED BY 'mbu_passwd';

GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT
ON *.* TO 'mariabackup'@'localhost';

11. Point-in-time restore: tools
●MariaDB Enterprise Backup (backup and restore data)
a.Backup compressed and/or encrypted tables
b.Create compressed and/or encrypted backups (of standard tables)
c.Take full, incremental and partial backups
d.Perform backups on local or remote databases
e.Can use encrypted database connections (TLS)
●mariadb-binlog (replay transactions)

12. MariaDB Enterprise Backup
1.Backup
a.# mariabackup --backup --target-dir=/data/backups/full
b.# mariabackup --backup --incremental-basedir=/data/backups/full --target-dir=/data/backups/inc1
c.# mariabackup --backup --incremental-basedir=/data/backups/full --target-dir=/data/backups/inc2
2.Prepare
a.# mariabackup --prepare --target-dir=/data/backups/full
b.# mariabackup --prepare --target-dir=/data/backups/full --incremental-dir=/data/backups/inc2
3.Restore
a.# mariabackup --copy-back --target-dir=/data/backups/full
b.# chown -R mysql:mysql /var/lib/mysql
# mariabackup --backup --target-dir=/data/backups/full --user=mariabackup --password=mbu_passwd

13. mariadb-binlog
1.Prepare the backup, and find the backups binlog position
a.# cat /data/backups/full/xtraback_binlog_info
mariadb-node4.00001 321
2.Update the configuration to use a new data dictionary
a.[mysqld]
datadir=/var/lib/mysql_new
3.Restore the backup, and start the database
4.Replay transactions since the backup
a.$ mariadb-binlog --start-position=321 --stop-datetime="2019-07-30 7:00:00" \
/var/lib/mysql/mariadb-node4.00001 > mariadb-binlog.sql
b.$ mariadb -u root -p < mariadb-binlog.sql

14. Best practices: options
●Prepare the backup after every incremental backup
○Rather than preparing the backup with multiple incremental backups
○Faster restore, but fewer recovery points or more storage used
●Enable parallel data transfer (--parallel=n)
○Helpful is using InnoDB file per table, or
○The InnoDB system tablespace is configured with multiple files

15. MariaDB Enterprise Backup
1.Backup
a.# mariabackup --backup --target-dir=/data/backups/full
b.# mariabackup --prepare --target-dir=/data/backups/full
c.# mariabackup --backup --incremental-basedir=/data/backups/full --target-dir=/data/backups/inc1
d.# mariabackup --prepare --target-dir=/data/backups/full --incremental-dir=/data/backups/inc1
e.# mariabackup --backup --incremental-basedir=/data/backups/inc1 --target-dir=/data/backups/inc2
f.# mariabackup --prepare --target-dir=/data/backups/full --incremental-dir=/data/backups/inc2
2.Restore
a.# mariabackup --copy-back --target-dir=/data/backups/full
b.# chown -R mysql:mysql /var/lib/mysql
# mariabackup --backup --target-dir=/data/backups/full --user=mariabackup --password=mbu_passwd

16. Point-in-time rollback
●Rewind the database to a previous point in time (online process)
a.Inverts recent transactions
■INSERT -> DELETE
■DELETE -> INSERT
■UPDATE BEFORE -> UPDATE AFTER

17. Point-in-time rollback: tools
●MariaDB Flashback
○Uses the binary log
■mariadb --log-in[=name]
■binlog_format=ROW
■binlog_row_image=FULL
■Alternatively, mariadb --flashback
●Create a script
○# mariadb-binlog /var/lib/mysql/mariadb-bin.000001 -d mydatabase -T mytable \
--start-datetime="2019-07-30 7:05:00" --flashback > flashback.sql
●Execute the script
○# mariadb < flashback.sql

18. System-versioned tables
●System-versioned tables are great for auditing, but
●They are great for disaster recovery as well...
○MariaDB preserves historical changes to a row
■UPDATEs -> INSERTs
○Rows are never deleted
■DELETEs -> UPDATEs
●DBAs can restore rows to a previous point in time

19. Example
CREATE TABLE tbl_customers(
id INT,
name VARCHAR(10),
status VARCHAR(10))
WITH SYSTEM VERSIONING ;

ALTER TABLE tbl_customers ADD SYSTEM VERSIONING ;

20. id name status
1 Shane Silver

21. id name status
1 Shane Gold
1 Shane Silver
// JANUARY 1, 2018
UPDATE TBL_CUSTOMERS SET status = 'Gold'
WHERE id = 1;

22. id name status
1 Shane Platinum
1 Shane Gold
1 Shane Silver
// JANUARY 1, 2019
UPDATE TBL_CUSTOMERS SET status = 'Platinum'
WHERE id = 1;

23. id name status
1 Shane Bronze
1 Shane Platinum
1 Shane Gold
1 Shane Silver
// JULY 29, 2019
UPDATE TBL_CUSTOMERS SET status = 'BRONZE'
WHERE id = 1;

24. // JULY 30, 2019
SELECT id, name, status, row_start, row_end
FROM TBL_CUSTOMERS
FOR SYSTEM_TIME ALL WHERE id=1;


id name status row_start row_end
1 Shane Bronze 2019-07-29 2038-01-19
1 Shane Platinum 2019-01-01 2019-07-28
1 Shane Gold 2018-01-01 2018-12-31
1 Shane Silver 2017-08-12 2017-12-31

25. id name status
1 Shane Platinum
// JULY 30, 2019
SELECT * FROM TBL_CUSTOMERS
FOR SYSTEM_TIME AS OF '2019-07-28'
WHERE id = 1;

26. Delayed replication
●Ensure data loss is not propagated to replicas
○Can use multiple replicas with different delays
■15 minutes: an issue is detected right away
■1 hour: maybe it takes a little longer to identify the issue
■1 day: someone missed the issue, but found it later
○Perform backups on delayed replicas to prevent data loss from reaching backups
●Set the delay on the replica (in seconds)
○CHANGE MASTER TO MASTER_DELAY=900; // 15 minutes

27. Multiple data centers
●Maintain a disaster recovery site
●Deploy MariaDB Platform in multiple data centers
○Multi-cloud or hybrid-cloud
○Asynchronous or synchronous replication

28. Reference
architectures

29. MariaDB
Server (P)
MariaDB
Server (R1)
MariaDB
Server (R2)
MariaDB
MaxScale
Writes Reads
MariaDB
Enterprise Backup
Delayed replication and
system-versioned tables

30. MariaDB
Server (P)
MariaDB
Server (R1)
MariaDB
Server (R2)
MariaDB
MaxScale
Writes Reads
MariaDB
Enterprise Backup
MariaDB
Server (R3)
MariaDB
Server (R4)
Delayed
replication
System
versioned
tables

31. MariaDB
Server (M)
MariaDB
Server (M)
MariaDB
Server (M)
MariaDB MaxScale
Reads Writes
MariaDB Enterprise Backup
Binlog server
MariaDB
Server
(versioning)
MariaDB
Server (1h)
(versioning)
MariaDB
Server (1d)
(versioning)
MariaDB
Server (1w)
(versioning)
MariaDB
Server (P)
MariaDB
Server (R)
MariaDB MaxScale
Writes Reads
Binlog server

32. Thank you
Questions?