Slide 1

Slide 1 text

Testing and Verifying your MySQL Backup Strategy EffectiveMySQL.com - Performance, Scalability, Site Reliability Ronald Bradford http://ronaldbradford.com @RonaldBradford 2016.11

Slide 2

Slide 2 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability About the Author 17 years with MySQL / 27 years with RDBMS Senior Consultant at MySQL Inc (06-08) Consultant for Oracle Corporation (96-99) Published author of 4 MySQL books Ronald BRADFORD http://ronaldbradford.com/presentations/

Slide 3

Slide 3 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability "No one cares if you can backup, only that you can restore." Adapted from W. Curtis Preston - Backup & Recovery (O'Reilly 2009)

Slide 4

Slide 4 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Problem To verify your backup is to restore Test frequently Test for exceptions Restoring is a two part process Restore static backup Restore to point in time

Slide 5

Slide 5 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability "Testing is about breaking your software. Testing is not about checking if it works." Ronald Bradford, 2006

Slide 6

Slide 6 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Requirements? What is most important? Your restore completes successfully? Your restore matches your backup files? Your restored system has all the data it should have? Your system is back online ASAP? All of these requirements are necessary for success

Slide 7

Slide 7 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Constraints? What part takes the most time to restore? Copying your backup files across network? Uncompressing your backup? Restore the backup? Point in time recovery?

Slide 8

Slide 8 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Product Options

Slide 9

Slide 9 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options mysqldump mydumper XtraBackup MySQL Enterprise Backup LVM/SAN Snapshot Filesystem copy New options review

Slide 10

Slide 10 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability mysqldump Included with binary distribution Open source Maintained by Oracle Only tool for schema information Poor defaults

Slide 11

Slide 11 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability mysqldump Locks schemas (by default) Inconsistent for multi schema applications Does not obtain position needed for point in time recovery Does not backup all objects by default Single threaded Reads all data (into database buffers) Defaults

Slide 12

Slide 12 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability mysqldump ASCII copy of data editable Cross platform/version compatible Can perform partial backups

Slide 13

Slide 13 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability mydumper Parallel implementation based on mysqldump Open source Community developed and maintained Max Bubenick - Percona https://launchpad.net/mydumper

Slide 14

Slide 14 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability xtrabackup Open source Maintained by 3rd party company Widely used for large InnoDB installations https://www.percona.com/doc/percona-xtrabackup/

Slide 15

Slide 15 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability xtrabackup Non-blocking backup (of transactional data) Consistent file copy

Slide 16

Slide 16 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability MEB Commercial license bundled with subscription support Oracle provided enterprise version Features similar to Xtrabackup MySQL enterprise backup

Slide 17

Slide 17 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability snapshot LVM/SAN Snapshot DB agnostic MyLVMBackup Open source Community Maintained http://www.lenzg.net/mylvmbackup/

Slide 18

Slide 18 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability snapshot Read Consistent view FLUSH TABLES WITH READ LOCK Can take a long time on highly concurrent systems

Slide 19

Slide 19 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Latest mysqlpump (5.7.8) Open source Developed by Oracle rewrite of mysqldump parallel implementation of mysqldump https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

Slide 20

Slide 20 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability What is missing?

Slide 21

Slide 21 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability point in time MySQL Binary Logs Are they enabled? Binary log position Obtained consistently SaaS (per schema issues)

Slide 22

Slide 22 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options cp rsync Use a slave --log-slave-updates DRBD - Disk Replicated Block Device mysqlbinlog --read-from-remote-server (New in 5.6)

Slide 23

Slide 23 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability binary log file copy Binary logs are sequential files cp/rsync mysqladmin flush-logs e.g. AWS 5 minutes strategy

Slide 24

Slide 24 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability mirrored binary log Remote copy binary logs as created No automatic restart management Requires MySQL 5.6+ (can be on a slave)

Slide 25

Slide 25 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability B&R Strategy Considerations

Slide 26

Slide 26 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Design choices Time to backup Time to restore Consistency Flexibility Partial Capabilities Cost

Slide 27

Slide 27 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability storage engines InnoDB only MyISAM mysql schema Other storage engines Mixed data solutions

Slide 28

Slide 28 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Technical Requirements (Regardless of method)

Slide 29

Slide 29 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Requirements Schema & Data Schema Size/Tables etc Replication architecture Auditing Security Checksums Storage Engines Do these help to test and verify?

Slide 30

Slide 30 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability schema & objects Can only be obtained via mysqldump Required for test systems & subsets Auditability of schema changes

Slide 31

Slide 31 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability meta info Schema size Tables size Table objects

Slide 32

Slide 32 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability meta info SELECT FORMAT(SUM(data_length+index_length)/1024/1024,2) AS total_mb, FORMAT(SUM(data_length)/1024/1024,2) AS data_mb, FORMAT(SUM(index_length)/1024/1024,2) AS index_mb, COUNT(DISTINCT table_schema) AS schema_cnt, COUNT(*) AS tables, CURDATE() AS today, VERSION() FROM information_schema.tables\G *************************** 1. row *************************** total_mb: 5344.63 data_mb: 4545.49 index_mb: 799.13 schema_cnt: 7 tables: 103 today: 2012-04-03 VERSION(): 5.1.61-0ubuntu0.11.10.1-log Your daily verification step should include this ALL Schemas Size https://github.com/ronaldbradford/mysql/blob/master/sql/database_size.sql

Slide 33

Slide 33 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Meta Info DROP TABLE IF EXISTS db_size; CREATE TABLE db_size( table_schema VARCHAR(64) NOT NULL, table_name VARCHAR(64) NOT NULL, engine VARCHAR(64) NOT NULL, row_format VARCHAR(10) NULL, table_rows INT UNSIGNED NOT NULL, avg_row INT UNSIGNED NOT NULL, total_mb DECIMAL(7,2) NOT NULL, data_mb DECIMAL(7,2) NOT NULL, index_mb DECIMAL(7,2) NOT NULL, created_date DATETIME NOT NULL, INDEX (created_date,table_name) ) ENGINE=InnoDB; Table size Keep a copy of all tablesize info per backup https://github.com/ronaldbradford/mysql/blob/master/sql/db_size_table.sql

Slide 34

Slide 34 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Meta Info DELIMITER $$ DROP PROCEDURE IF EXISTS generate_db_size$$ CREATE PROCEDURE generate_db_size() BEGIN DECLARE l_created_date DATETIME; SET l_created_date := NOW(); INSERT INTO db_size(table_schema, table_name, engine, row_format, table_rows, avg_row, total_mb, data_mb, index_mb, created_date) SELECT table_schema, table_name, engine, row_format, table_rows, avg_row_length AS avg_row, ROUND((data_length+index_length)/1024/1024,2) AS total_mb, ROUND((data_length)/1024/1024,2) AS data_mb, ROUND((index_length)/1024/1024,2) AS index_mb, l_created_date FROM information_schema.tables WHERE table_schema=DATABASE() AND table_type='BASE TABLE' ORDER BY 6 DESC; SELECT l_created_date AS created_date, DATABASE() AS table_schema, COUNT(*) AS tables, FORMAT(SUM(total_mb),2) AS total_mb, FORMAT(SUM(data_mb),2) AS data_mb, FORMAT(SUM(index_mb),2) AS index_mb FROM db_size WHERE created_date = l_created_date; Table size https://github.com/ronaldbradford/mysql/blob/master/sql/generate_db_size.sql

Slide 35

Slide 35 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability architecture Do you backup the master or the slave? Binary position option changes Data drift? Do you backup the entire DB? Is some data static? Can a recovered system start without need for all data? replication topology

Slide 36

Slide 36 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options $ sudo su - $ sync ; lvcreate -L1G -s -n dbsnapshot /dev/db/p0 $ mkdir -p /mnt/dbsnapshot $ mount -o ro /dev/db/dbsnapshot /mnt/dbsnapshot $ du -sh /mnt/dbsnapshot $ ls -al /mnt/dbsnapshot $ mkdir /mysql/backup/snapshot1 $ cp -r /mnt/dbsnapshot/* /mysql/backup/snapshot1 $ sudo su - $ mylvmbackup SNAPSHOT USAGE http://effectiveMySQL.com/article/configuring-a-new-hard-drive-for-lvm http://effectiveMySQL.com/article/using-mysql-with-lvm http://www.lenzg.net/mylvmbackup/ TIP: mylvmbackup does all the hard work

Slide 37

Slide 37 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Testing & Verification

Slide 38

Slide 38 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Situations Incomplete backup file Missing objects Locking Missing point in time position Restore time considerations Fake objects Trigger hell ^C does not kill mysqldump

Slide 39

Slide 39 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options $ time mysqldump --all-databases > /mysql/backup/dump1.sql real 1m31.631s user 1m12.533s sys 0m10.893s $ echo $? 0 $ ls -lh /mysql/backup/dump1.sql -rw-rw-r-- 1 uid gid 2.9G 2012-04-03 03:04 /mysql/ backup/dump1.sql mysqldump USAGE

Slide 40

Slide 40 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options $ time mysqldump --all-databases > /mysql/backup/dump1.sql mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES $ echo $? 2 VERIFICATION TIP: Error checking is essential and easy to implement

Slide 41

Slide 41 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Error examples mysqldump: Couldn't execute 'SHOW FIELDS FROM `alarm`': View 'schema.alarm' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356) mysqldump: Got error: 1044: Access denied for user 'rbradfor'@'localhost' to database 'schema' when using LOCK TABLES Invalid objects Lack of permissions

Slide 42

Slide 42 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options $ time mysqldump --all-databases \ --events --routines > /mysql/backup/dump1.sql mysqldump USAGE All objects are NOT included by default

Slide 43

Slide 43 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options $ time mysqldump --all-databases \ --events --routines \ --single-transaction > /mysql/backup/dump1.sql mysqldump USAGE Remove default schema locking

Slide 44

Slide 44 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Options $ time mysqldump --all-databases \ --events --routines \ --single-transaction \ --master-data > /mysql/backup/dump1.sql mysqldump USAGE Point in Time recovery position $ time mysqldump --all-databases \ --events --routines \ --single-transaction \ --dump-slave > /mysql/backup/dump1.sql mysqldump: Error: Binlogging on server not active Fail on test system? Different for master or slave

Slide 45

Slide 45 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup improvment # Schema $ mysqldump --all-databases --no-data \ --skip-triggers --add-drop-database \ > /mysql/backup/schema.sql # Objects $ mysqldump --all-databases --no-data --no-create-info \ --events --routines --triggers \ > /mysql/backup/objects.sql # Just the data $ mysqldump --all-databases --no-create-info \ --single-transaction --master-data --skip-triggers \ > /mysql/backup/data.sql Separate schema/objects TIP: Include daily dumps of database objects

Slide 46

Slide 46 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Restore Options $ time mysqldump --all-databases > /mysql/backup/dump1.sql real 1m31.631s ... $ time mysql –u[user] -p -f < dump1.sql > dump1.out 2>&1 real 14m13.817s user 1m6.960s sys 0m1.516s $ echo $? 0 $ ls -l dump1.out -rw-rw-r-- 1 uid gid 0 2012-04-08 04:07 dump1.out mysqldump USAGE Continue on error or stop on error?

Slide 47

Slide 47 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Fake objects DROP TABLE IF EXISTS `demo`; /*!50001 DROP VIEW IF EXISTS `demo`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `demo` ( `zip` tinyint NOT NULL, `lat` tinyint NOT NULL, `lon` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; /*!50001 DROP TABLE IF EXISTS `demo`*/; /*!50001 DROP VIEW IF EXISTS `demo`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `demo` AS select `uszip`.`zip` AS `zip`,`uszip`.`lat` AS `lat`,`uszip`.`lon` AS `lon` from `uszip` */; mysqldump USAGE

Slide 48

Slide 48 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Fake objects -- -- Temporary view structure for view `demo` -- DROP TABLE IF EXISTS `demo`; /*!50001 DROP VIEW IF EXISTS `demo`*/; /*!50001 CREATE VIEW `demo` AS SELECT 1 AS `zip`, 1 AS `lat`, 1 AS `lon`*/; ... -- -- Final view structure for view `demo` -- /*!50001 DROP VIEW IF EXISTS `demo`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `demo` AS select `uszip`.`zip` AS `zip`,`uszip`.`lat` AS `lat`,`uszip`.`lon` AS `lon` from `uszip` */; mysqldump USAGE 5.7 syntax

Slide 49

Slide 49 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Compression How long to compress the data? Which compression do you use? How long to uncompress the data? 95% of recovery is from last backup Best strategy is to have an uncompressed copy on server.

Slide 50

Slide 50 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Compression Utility Comp (s) Dec (s) Saving lzo (-3) 21 34 48% pigz (-1) 43 33 64% pigz [-6] 105 25 69% gzip [-6] 232 78 69% bzip2 540 175 74% lzo (-9) 20m 82 58% lzma 58m 180 78% xz 59m 160 78% Depends greatly on data types

Slide 51

Slide 51 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup needs Static Backup Useless without Binary Log position mysql> SHOW MASTER STATUS\G File: mysql-bin.020616 Position: 63395562 Binlog_Do_DB: Binlog_Ignore_DB: WARNING: Can work on slave and provide the wrong information

Slide 52

Slide 52 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Backup Needs $ mysqldump --master-data (or --dump-slave) CHANGE MASTER TO MASTER_LOG_FILE= 'mysql-bin.000146', MASTER_LOG_POS=810715371; $ cat xtrabackup_binlog_info mysql-bin.000001 37522 $ grep binlog meta/backup_variables.txt binlog_position=mysql-bin.000017:5555 MEB Xtrabackup $ cat export-20120407-230027/metadata Log: mysql-bin.000017 Pos: 8328 mydumper

Slide 53

Slide 53 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Situations Lost connection Transaction too large ROW format statement SET max_allowed_packet Single threaded Binary Log

Slide 54

Slide 54 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability A different strategy?

Slide 55

Slide 55 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability what about Failover Is failover a better option? Do you have high availability (HA)implemented? Top Benefit Improved time to recovery Top Risk [Potential] loss of high availability

Slide 56

Slide 56 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability failover issues Failover does not cater for all situations Data recovery to a prior point in time e.g. security problem building testing systems Building out replicas Entirely different set of problems to verify

Slide 57

Slide 57 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability What to consider Forget the technology? What is most important for your business? B&R/DR decision should be based on loss/ cost to business RPO/RDO/SLA

Slide 58

Slide 58 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Terminology MTTD - Mean Time To Detect MTTR - Mean Time to Recover RPO - Recovery Point Objective RDO - Recovery Data Objective SLA - Service Level Agreement Determining business priorities is important for any strategy

Slide 59

Slide 59 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Conclusion B&R is a complex process Business will want all of the data, all of the time Justify the business needs first Discounts [easier] options

Slide 60

Slide 60 text

220 pages dedicated to B&R http://j.mp/EM-book2

Slide 61

Slide 61 text

http://ronaldbradford.com [email protected] @RonaldBradford Ronald Bradford