1 / 48

Safe Harbour Statement

Safe Harbour Statement.

semah
Download Presentation

Safe Harbour Statement

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Safe Harbour Statement THE FOLLOWING IS INTENDED TO OUTLINE OUR GENERAL PRODUCT DIRECTION. IT IS INTENDED FOR INFORMATION PURPOSES ONLY, AND MAY NOT BE INCORPORATED INTO ANY CONTRACT. IT IS NOT A COMMITMENT TO DELIVER ANY MATERIAL, CODE, OR FUNCTIONALITY, AND SHOULD NOT BE RELIED UPON IN MAKING PURCHASING DECISIONS. THE DEVELOPMENT, RELEASE, AND TIMING OF ANY FEATURES O FUNCTIONALITY DESCRIBED FOR ORACLE’S PRODUCTS REMAINS AT THE SOLE DISCRETION OF ORACLE.

  2. Making Backups in Extreme Situations Sveta SmirnovaPrincipal Technical Support Engineer Matt Lord MySQL Product Manager

  3. Program Agenda • Introduction • Agenda Overview • Dealing With Space Constraints • Dealing With Time Constraints • Dealing with Resource Usage Constraints • Minimizing Locking Impacts • Handling Data Corruption • Conclusion

  4. Introduction

  5. Who We Are Matt Lord • Former MySQL Support engineer • Worked with hundreds of backup issues • Now Product Manager Sveta Smirnova • MySQL Support engineer • Support representative in MySQL Enterprise Backup (MEB) team

  6. Why Backups? 1. They are a critical insurance against: • Hardware failure • User error • Program error 2. You can use them to deploy new instances 3. Setup replication clones 4. Data is your most valuable IP. You have to protect it from loss.

  7. The Backup Conundrum 1. Backup tools guarantee consistency by: • Locking tables • Running long transactions • Even using global locks 2. Backup jobs generate a lot of system overhead 3. Backups take up a lot of storage space How do you backup your critical production systems then? • Servers are performing critical business functions • Downtime is not acceptable • Negative performance impacts are not acceptable • Storage space is critical

  8. So How Do I Perform Backups... • without using local storage? • with minimal local resource usage? • with minimal impact on the MySQL instance I'm backing up? • when I need the backup to complete in a small time window? • without locking tables and entire MySQL instances? • when the data or the backup itself has corrupt data?

  9. What We Won't Cover • Full backups • Partial backups • Incremental backups • Backup automation • General backup tuning Instead, see: • MySQL Enterprise Backup BOF [BOF3558, Saturday, 5:30 PM] • MySQL Backup-and-Recovery [CON7659, Sunday, 11:30 AM] • Backing Up MySQL Databases [CON3278, Sunday, 1:00 PM] • MySQL Enterprise Edition – Hands-On Lab, Sunday, 10:00 AM

  10. Dealing With Storage Constraints

  11. When There Is Little Storage Space • Very large data directory size • Small amount of local storage space left • But MEB process should be run on the same machine!

  12. Backup Options • Compressed backups: --compress, --compress-level=LEVEL • Stream backups over SSH • Backup directly to tape, using the SBT interface • Create partial backups: --include, --with-tts • Create incremental backups • Don’t backup empty and unused pages: --skip-unused-pages

  13. Backup Options: Compression • Compression ratios of 80% or more are possible! shell# mysqlbackup --backup-dir=/opt/mysql/backups/uncompressed \ > backup 2>/dev/null && du -csh /opt/mysql/backups/uncompressed 334M /opt/mysql/backups/uncompressed 334M total shell# mysqlbackup --backup-dir=/opt/mysql/backups/compressed \ > --compress --compress-level=9 backup 2>/dev/null && du –csh \ > /opt/mysql/backups/compressed 81M /opt/mysql/backups/compressed 81M total

  14. Backup Options: Stream Backups • We can avoid local storage altogether Simply specify the tmpdir as the backup location (for temporary data) • Should be big enough to keep all changes made during backup • Still no space requirements for data files shell# mysqlbackup --backup-image=- --backup-dir=/tmp/backup \ > --disable-manifest backup-to-image 2>/dev/null | ssh matt@solo \ > "cat > /tmp/backup.img“ shell# ssh matt@solo "ls -lh /tmp/backup.img" -rw-r--r-- 1 matt staff 333M Sep 10 15:54 /tmp/backup.img

  15. Backup Options: Using the SBT Interface • We can avoid local storage altogether • Backup directly to your company’s MMS • Oracle Secure Backup (fully supported) • IBM Tivoli Storage Manager • Symantec NetBackup shell-osb# mysqlbackup --backup-image=sbt:backup-mattprod-2013-09-08 \ > --backup-dir=/tmp/backup backup-to-image shell-tsm# mysqlbackup --backup-image=sbt:my-tsm–backup \ > --sbt-lib-path=/usr/lib/libobk.so \ > --sbt-environment=“TDPO_OPTFILE=/opt/ibm/tsm/tdpo.opt” \ > --backup-dir=/tmp/backup backup-to-image

  16. Backup Options: Partial Backups • Only backup your important schemas and tables • MEB now supports InnoDB transportable tablespaces shell# mysqlbackup --include=sakila.* --only-innodb-with-frm=related \ > --backup-dir=/opt/mysql/backups backup shell# mysqlbackup --use-tts=with-minimum-locking \ > --include=employees.* --backup-dir=/opt/mysql/backups backup

  17. Backup Options: Incremental Backups • Only backup what’s changed since your last full backup shell# mysqlbackup --incremental \ > --incremental-base=dir:/opt/mysql/backup/monday \ > --incremental-backup-dir=/opt/mysql/backup/tuesday backup shell# mysqlbackup --incremental \ > --incremental-base=history:last_backup --with-timestamp \ > --incremental-backup-dir=/opt/mysql/backup backup

  18. Backup Options: Skip Blank & Unused Pages • Tell InnoDB not to backup blank or unused pages • Use this in combination with compression for optimal storage efficiency shell# mysqlbackup --backup-dir=/opt/mysql/backups/compressed \ > --compress --compress-level=9 --skip-unused-pages backup

  19. Restore Options • MEB 3.9 introduces single-step restores • Even for compressed and single image file backups • This allows us to avoid unnecessary duplication of data shell# ssh matt@solo "cat /opt/mysql/backups/backup.img" | mysqlbackup \ > --backup-dir=/tmp/backup --uncompress --backup-image=- \ > --datadir=/var/lib/mysql --innodb_log_group_home_dir=. \ > --innodb_log_files_in_group=4 --innodb_log_file_size=2G \ > --innodb_data_file_path="ibdata1:1G:autoextend" > copy-back-and-apply-log

  20. Dealing With Time Constraints

  21. I Need the Backup Now! • Scheduled maintenance windows may be 30 mins or less • The backup may be needed immediately for a new deployment • One or more schemas may need to be moved to another host ASAP

  22. Backups Methods • Direct FS backup • Requires mysqld to be shutdown, or at least globally locked • Logical backups (mysqldump) • VERY slow, consumes a lot of disk space, requires too many locks • MySQL Enterprise Backup (MEB) • Provides online hot backups for transactional storage engine InnoDB • Includes many performance and resource usage controls • Supports partial backups and transportable tablespaces • Supports incremental backups

  23. MySQL Enterprise Backup 49x faster than mysqldump

  24. MySQL Enterprise Backup 80x faster than mysqldump

  25. MEB Performance Options • Increase buffers for CPU intensive multi-threaded ops: --number-of-buffers • Increase read I/O threads: --read-threads • Increase write I/O threads: --write-threads • Increase CPU processing threads: --process-threads • Increase the maximum memory used for caching: --limit-memory • Don't backup unused pages in InnoDB tables: --skip-unused-pages

  26. MEB Example: Time Comparison • 2G datadir, 1 7.2K HDD, 4 vCPUs , 16G of RAM shell# time mysqlbackup --backup-dir=/opt/mysql/backups --with-timestamp backup 2>/tmp/log.txt real 0m34.403s user 0m3.069s sys 0m2.043s shell# time mysqlbackup --backup-dir=/opt/mysql/backups \ > --process-threads=4 --limit-memory=1000 --skip-unused-pages \ > --with-timestamp backup 2>/tmp/log.txt real 0m29.883s user 0m3.121s sys 0m1.889s • Cut backup times by 50%+ on enterprise class machines with large datasets

  27. Dealing With Resource Usage Constraints

  28. Why is the Application Slow Right Now? • Backups can be CPU intensive • Backups can be I/O intensive, also causing CPU cycles in IO_Wait • Backups may use various locks within MySQL • So how to take backups without having a noticeable affect…

  29. Backup Options • Limit memory usage: --limit-memory • Limit CPU usage: --process-threads • Sleep X ms after each time we copy 1 block of data: --sleep • Avoid internal locks entirely: --no-locking, --innodb-only • Limit disk I/O: --read-threads, --write-threads • Don’t copy empty and unused pages: --skip-unused-pages • Process limits at OS level: cgroups, taskset, cpulimit, etc.

  30. MEB Example: Resource Usage • 2G datadir, 1 7.2K HDD, 4 vCPUs , 16G of RAM shell# /usr/bin/time -f 'Exec Time: %E, CPU: %P, MEM: %MKB, IO: %O' \ > mysqlbackup --backup-dir=/opt/mysql/backups --with-timestamp \ > backup 2>&1 | tail -1 Exec Time: 0:23.11, CPU: 21%, MEM: 951648KB, IO: 4229360 shell# taskset -c 1 /usr/bin/time -f \ > 'Exec Time: %E, CPU: %P, MEM: %MKB, IO: %O' mysqlbackup \ > --backup-dir=/opt/mysql/backups --limit-memory=50 \ > --process-threads=1 --skip-unused-pages --no-locking --sleep=2000 \ > --with-timestamp backup 2>&1 | tail -1 Exec Time: 4:22.06, CPU: 1%, MEM: 222096KB, IO: 3384064

  31. Minimizing Locking Impacts

  32. MEB Locking Related Options • Skip ALL locking: --no-locking (not 100% safe for non-transactional data) • Only backup transactional data: --innodb-only • Incremental backups using the redo log: --incremental-with-redo-log-only • Partial backups with minimal locking: --use-tts=with-minimum-locking

  33. I Thought MEB Backups Were “Hot”? • FLUSH TABLES WITH READ LOCK is used at the end • Necessary to get a snapshot of all non-transactional data • Can be avoided with various options: • Don’t take any locks at all: --no-locking • Only backup InnoDB tables: --only-innodb[-with-frm]

  34. What About the Non-Transactional Data? • mysqlhotcopy – lock and copy each non-transactional table • mysqldump – lock and logically export each table • FS snapshots – copy individual table files out of the snapshot • Manage the backups on an existing MySQL Replication Slave • mysqlbinlog –read-from-remote-server --raww • The amount of non-transactional data will continue to shrink in MySQL 5.7

  35. Recap: Backups With Minimal Impact Limit shared resource usage: • MEB: --limit-memory, --sleep, --[read|write|process]-threads • Stream the backup to a remote FS Backup only as much data as required: • MEB: Partial backups with --use-tts=with-minimum-locking • MEB: Skip unused pages with --skip-unused-pages (InnoDB only) • MEB: Redo log only incremental backups (InnoDB only) Use 100% non-locking backups: • MEB: --only-innodb, --only-innodb-with-frm, --no-locking • Not 100% safe for .frm files, and for non-transactional data

  36. Handling Data Corruption

  37. Backups with Corruption Sometimes tables get corrupted! If production is corrupted, but the backup is fine: • Do a full or partial (--with-tts) restore from the backup • Apply changes from binary logs If both the production data, and the backup itself are corrupted: • InnoDB • --innodb_force_recovery • SELECT INTO OUTFILE • MyISAM • myisamchk

  38. Backups with Corruption What to do if: • You have only full MEB backup, but need to restore single table • You can not setup spare instance • You can not use TTS MyISAM • Simply copy .frm, .MYI, and .MYD files InnoDB • Things get harder • Each table needs to have the same table ID in the shared tablespace, in both the backup and production server

  39. Backups with Corruption:Full Backup and InnoDB If you can setup separate instance and restore full backup there • Do it! • If you can use TTS • Backup needed table using MEB with option --use-tts • Restore it

  40. Backups are Corrupted: Full Backup and InnoDB If you can not! • The problem • Table ID in the InnoDB shared tablespace must be same in the backup and production server • They are if: • this is a backup of the same production server • you originally cloned this server from this backup and • You use option –innodb-file-per-table • Simply copy the .ibd file

  41. Backups are Corrupted: Full Backup and InnoDB If table ID is not the same and you use option –innodb-file_per_table • Recover manually • Simulate the internal InnoDB table counter • Start spare instance • Create, then drop table with the same structure as the original one until counter increases to appropriate value • Import backed up tablespace into spare instance • Copy .ibd file to production server

  42. Backups are Corrupted: Full Backup and InnoDB But how will you find what to type in CREATE TABLE statement? • mysqlfrm, the FRM reader will help you $ mysqlfrm --basedir=/usr/local/bin/mysql test1:city.frm --port=3333 # Starting the spawned server on port 3333 ... done. # Reading .frm files # # Reading the city.frm file. # # CREATE statement for city.frm: # CREATE TABLE `test1`.`city` ( …

  43. Conclusion

  44. Conclusion • Backups are a must • Production constraints are a fact of life • Difficult problems can be solved with MySQL Enterprise Backup • MySQL Support is there to help with your specific problems

  45. References Documentation: • http://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/index.html • http://dev.mysql.com/doc/workbench/en/mysqlfrm.html • https://blogs.oracle.com/mysqlenterprisebackup/ MySQL Enterprise Backup Trial Downloads: • https://edelivery.oracle.com/

  46. Graphic Section Divider

More Related