MariaDB und MySQL Backups: Bewährte Verfahren für Entwicklung und Umsetzung einer angemessenen...

© 2015, MariaDB Corp.

MariaDB and MySQL Backup Best Prac6ces Ralf Gebhardt, Sales Engineer -‐ MariaDB


A Deep Bench of Talent ●  Nexus of Open Source Database Innova2on

●  Broad Community Adop2on Linux distros, leading web companies

●  Innova2ve products enhance MariaDB.

●  Enterprise Subscrip2ons documenta6on, op6mized binaries, patches, bug fixes, included upgrades, 24/7 support, certain legal protec6ons, customer portal.

●  Core MySQL founding team, including Monty Widenius and David Axmark -‐ over 400 years cumula6ve MySQL Experience.

●  Proven Open Source and Cloud management team


Leader, Gartner Magic Quadrant for Operational Databases, 2014

●  Unusual to debut as a leader!

●  Strengths: ○  Func6onality ○  Value ○  Community ○  Partners

MariaDB received one of the three highest scores for value for money, as it did for suitability of pricing method. It also received one of the highest scores for "no problems encountered."

-‐-‐ Gartner


Topics for Today

●  Things to Consider ●  Terms ●  Types of Backups ●  Principles and Strategies ●  Backup Tools ●  Q & A


Things to Consider -‐ The Data

●  What is the nature of the data?

○  Does it compress well? ●  How much data do you have?

○  Will it grow? ●  Is the data itself sensi6ve?

●  Is the data regulated?


Things to Consider -‐ The Users

●  What is the data worth to your organiza6on?

●  What internal groups own the data?

●  How is the data used?

●  Is the data customer facing or used internally?


Things to Consider -‐ Bad Things

●  What would happen if the data was permanently lost? ○  Could it be re-‐created easily? ○  Are there legal ramifica6ons?

●  How long can the data be unavailable?

○  How many minutes or hours or days? ○  What would each cost the organiza6on?

●  What are the expecta6ons of the organiza6on?

○  Unrealis6c assump6ons?


Some Backup Terms

Consistency -‐ A backup is consistent if it accurately contains all the data from a specific point in 6me. Cold Backup -‐ A backup performed with MariaDB / MySQL stopped Warm Backup -‐ A backup performed with the database running that significantly affects performance (locks for example)


Some Backup Terms

Hot Backup -‐ A backup performed with the database running that does not significantly affect performance. Logical Backup -‐ A text based backup that includes data and commands to recreate the schema and restore the data Physical Backup -‐ A physical copy of the database files


Logical Backups

●  Produce text files with SQL statements that can be replayed to rebuild database

●  Allows backup of databases or specific tables -‐

schema or only data ●  A SQL dump is independent of storage engine, and

can be restored to a different storage engine, or used for migra6on


Logical Backups

●  Process can be slow and requires Locks. ●  Use a local drive, not across network

●  Mul6ple op6ons exist:

mysqldump SELECT INTO OUTFILE


Making Physical Backups

●  Produces a binary copy of data ●  Faster than dumping to SQL text file

●  Recovered only to the same storage engine

●  Not useful for migra6ons

●  In the event of file corrup6on, errors remain

unseen and affect the backup too


Making Physical Backups

●  Mul6ple op6ons

○  Copy manually data directory (stopping mysqld necessary)

○  LVM on Linux for volume snapshot

○  (Data complete and consistent, but s6ll

trigger InnoDB recovery acer restore) ○  InnoDB Hot Backup tool, or XtraBackup


Recovery with Logical Backups

●  Recovery is a simple with logical backups ○  $ cat backup.sql | mysql ○  Using the mysql command line client command:

mysql> source backup.sql ●  They can also be rela6vely slow

●  mysqldump generates UTF8 Text Files


Recovery with Physical Backups

●  Stop the server ●  Replace the data with the backup data ●  Start the server and let InnoDB perform recovery ●  Tip: You can perform this on another system,

make a new copy of the recovered data directory ○  This saves 6me should you need to use it to

recover in an emergency


Recovery with Binary Logs

●  Restore databases with backup dump file, and use the binary logs to execute remaining SQL statements to a specific point

●  Get a list of binary log files and then determine current log file:

○  SHOW BINARY LOGS; ○  SHOW MASTER STATUS;

●  Use mysqlbinlog to convert binary log and pipe to mysql client:

○  mysqlbinlog binlog.000005 binlog.000006 | mysql -‐u root -‐p


Point-‐in-‐Time Recovery

●  Give a stop 6me to mysqlbinlog to recover un6l then (e.g., Noon): ○  mysqlbinlog -‐-‐stop-‐date6me=`2013-‐11-‐03 11:59:59 ́ \ ○  /var/log/mysql/bin.000006 | mysql -‐u root -‐p

●  Give a start 6me to mysqlbinlog to recover from then:

○  mysqlbinlog -‐-‐start-‐date6me=`2013-‐11-‐03 12:01:00 ́ \ ○  /var/log/mysql/bin.000006 | mysql -‐u root -‐p


Point-‐in-‐Time Recovery -‐ Event Posi6ons

●  Determine event posi6on numbers (log_pos): ○  mysqlbinlog -‐-‐start-‐date6me=`2013-‐11-‐03 11:55:00 ́ \ ○  -‐-‐stop-‐date6me=`2013-‐11-‐03 12:05:00 ́ \ ○  /var/log/mysql/bin.000001 > /tmp/mysql_restore.sql

●  Restore dump file, then run mysqlbinlog un6l stop posi6on and

Resume from a start posi6on: ○  mysqlbinlog -‐-‐stop-‐posi6on=342839 /var/log/mysql/bin.

0000006 \ ○  | mysql -‐u root -‐p ○  mysqlbinlog -‐-‐start-‐posi6on=342841 /var/l


Point-‐in-‐Time Recovery -‐ Event Posi6ons

●  The mysqldump op6on –master-‐data is very important

●  Using –master-‐data=2 stores the binlog file and offset in the

backup as a comment


Replica6on as a Backup Method ●  Use MySQL replica6on to copy data to slave (close

to real 6me, but technically asynchronous) ●  Recovery can be as fast and simple

○  Switch traffic to slave, restore master, switch traffic back to master

○  Alterna6vely, replay binary logs with mysqlbinlog tool

●  Allows other methods to be executed on slave

without increasing load on master ●  Allows the use of other replica6on features


Backup Principles ●  Even with HA in place, you still need backups

○  For example, HA can’t stop user errors

●  Consider both nightly snapshots and real-time backup ○  Daily snapshots (mysqldump, InnoDB Hot Backup,

LVM) and real-time backups (Replication)

●  Store backups in multiple locations ○  On-site for fast access; Off-site for security


Backup Principles

●  Include both data and config files in your backup ○  Copy data, logs, and configura6on Files ○  Synchronize binary logs with backup files!

●  Test Backup and Recovery

○  Note how long recovery takes


Backup Tools The table below shows a comparison of the common backup tools available for MySQL and MariaDB.


Tools -‐ mysqldump

●  Creates Logical Backups ●  Is included with MariaDB and MySQL

●  Very useful in doing par6al backups and selec6ve restores

●  Is technically a warm backup when run in default way


Tools -‐ mysqldump

●  Creates locks during backup ●  Simple to use

●  Single threaded

○  SLOW to create backup ○  SLOW to restore


Tools -‐ mydumper

●  Creates logical backups ●  A faster alterna6ve to mysqldump as it uses mul6ple

threads ●  Uses companion applica6on myloader to restore ●  Creates files per table which makes restores

mul6threaded ●  Almost no locking with InnoDB ●  Uses compression ●  Created by Domas Mituzas


Tools -‐ XtraBackup

●  Fast BackUp Process ●  Transac6ons Processed during BackUp Process, a warm

backup if not hot ●  Low Drain on System Resources ●  Back-‐Up Files Verified Automa6cally ●  Faster Restore Process ●  Incremental Backup ●  Streaming Backup


Tools -‐ LVM Snapshots Logical Volume Manager (LVM) is a block device subsystem included with Linux that sits between the file system and the physical disk. Among other things, it provides the ability to quickly take snapshots of a disk volume. The steps to create a backup are: 1.  Flush the logs and lock the tables “FLUSH TABLES WITH READ

LOCK” (“FLUSH TABLES FOR EXPORT” with 10.0.8+) 2.  Create the snapshot 3.  Unlock the tables “UNLOCK TABLES” 4.  Mount the snapshot volume and back it up or make a copy 5.  Delete the snapshot (there is a performance penalty while it

exists)


Tools -‐ LVM Snapshots mylvmbackup is a scripted solu6on that automates much of this process. It was created by Lenz Grimmer and can find it here:

h{p://www.lenzg.net/mylvmbackup/


MariaDB & MySQL Remote DBA Subscrip6on •  Expert management of your databases.

•  Your DBA is an extension of your team.

•  Connect via VPN, SSH, Windows remote desktop.

•  Proactive, routine administration.

•  On-demand activities.

•  Subscription for normal administration, credits for on-demand.

•  Examples: backup configuration, monitoring, health checks, managing replication, migrations, tuning.

Add a top database expert to your team, on-call for you.


Thank You! [email protected]

MariaDB und MySQL Backups: Bewährte Verfahren für Entwicklung und Umsetzung einer angemessenen...

Software

Transcript of MariaDB und MySQL Backups: Bewährte Verfahren für Entwicklung und Umsetzung einer angemessenen...