Effective MySQL Backup and Recovery

Effective MySQL Backup and Recovery

by Ronald Bradford
Released May 2012
Publisher(s): McGraw Hill Computing
ISBN: 9780071788588

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The Essential Guide to Protecting Your Business Information

Written by Oracle ACE Director and MySQL expert Ronald Bradford, Effective MySQL: Backup and Recovery covers all the options with detailed descriptions and syntax examples to ensure an appropriate backup and recovery plan can be developed. Featuring a side-by-side evaluation of the various backup strategies and complementary recovery implementations, this Oracle Press book helps you to protect your MySQL data from a wide range of disaster situations.

  • Learn how different backup strategies affect locking and system availability
  • Identify the importance of static backup options and point-in-time requirements
  • Recognize the important business factors to determine your optimal data protection requirements
  • Understand the benefits of using MySQL replication for leveraging different backup strategies
  • Understand the benefits and risks of implementing solutions with MySQL in the cloud
  • Identify key configuration variables that affect data durability and performance
  • Find out about the types of possible disasters and develop a robust plan to address them
  • Optimize your backup approach with compression, streaming, incremental, and parallel options

Table of contents

  1. Cover 
  2. About the Authors
  3. About the Technical Editors
  4. Copyright
  5. Dedication
  6. Contents 
  7. Acknowledgments
  8. Introduction
  9. 1. The Five Minute DBA
    1. Approaching a MySQL Backup
      1. Determining Your Database Size
      2. Choosing a Locking Strategy
      3. Execution Time
      4. Combining Information
    2. Performing a MySQL Backup
      1. Running mysqldump
      2. Securing Your Backup
      3. Benefits with mysqldump
      4. More Information
      5. Other Options
    3. Conclusion
  10. 2. Understanding Backup Options
    1. Terminology
    2. Choosing a Backup Strategy
      1. Database Availability
      2. Storage Engines
      3. Locking Strategies
      4. MySQL Topology
    3. Static Backup Options
      1. Filesystem Backup
      2. SQL Dump
      3. Table Extract
      4. Filesystem Snapshot
      5. InnoDB Hot Backup
      6. Options Not Discussed
    4. Point in Time Requirements
      1. Binary Logs
      2. Binary Log Position
      3. Binary Log Backup Options
    5. Hardware Considerations
    6. Data Source Consistency
    7. Backup Security
    8. Conclusion
  11. 3. Understanding Business Requirements for Disaster Recovery
    1. Defining Requirements
    2. Defining Responsibilities
      1. Terminology
      2. Technical Resource Responsibilities
      3. Decision Maker Responsibilities
      4. Identifying Dependencies
    3. Case Study
      1. The MySQL Topology
      2. Your Backup and Recovery Strategy
      3. A Real Life Disaster
      4. Technical Outcomes
      5. The Decision Process
      6. Essential External Communication
    4. Planning for the Worst Situation
      1. Total SAN Failure
      2. Power Disruption
      3. Explosion
      4. FBI Seizure
      5. Blackout
      6. Human Factors
      7. Human Resources
    5. Developing a Strategic Plan
    6. Conclusion
  12. 4. Using MySQL Replication
    1. MySQL Replication Architecture
      1. MySQL Replication Characteristics
    2. MySQL Replication Limitations
      1. Replication Lag
      2. Consistency
      3. Completeness
    3. Replication Design Considerations
      1. Binary Log Row Format
      2. Semi-synchronous Replication
    4. Replication Backup Considerations
      1. Additional Prerequisite Checks
      2. Cold Backup Options
      3. mysqldump Options
      4. Filesystem Snapshot Options
      5. MySQL Enterprise Backup (MEB) Options
      6. XtraBackup Options
    5. Architecture Design Considerations
    6. Upcoming Replication Functionality
    7. Conclusion
  13. 5. Using Recovery Options
    1. A Word About Testing
    2. Determining the Type of Recovery Necessary
      1. MySQL Software Failure
      2. Crash Recovery
      3. MylSAM Table Recovery
      4. Other Storage Engines
      5. Table Definition Recovery
    3. Performing a Static Recovery
      1. MySQL Software Installation
      2. MySQL Data
    4. Performing a Point in Time Recovery
      1. Binary Log Position
      2. Standalone Recovery
      3. Leveraging the Replication Stream
      4. Binary Log Mirroring
    5. Recovery Verification
    6. The Backup and Recovery Quiz
    7. Other Important Components
    8. Conclusion
  14. 6. MySQL Configuration Options
    1. Data Management
      1. Data Locations
      2. Data Consistency
      3. Binary Logging
    2. MySQL Replication
    3. Recovery
    4. Conclusion
  15. 7. Disaster Scenarios
    1. Handling a MySQL Disaster
    2. Notable MySQL Disasters
      1. Magnolia
      2. Couch Surfing
      3. Journal Space
      4. Percona
    3. Other Notable Data Disasters
      1. The Sidekick/Microsoft Data Loss
      2. Github
      3. TD Bank
    4. General MySQL Disaster Situations
      1. Binary Logging Not Enabled
      2. A Single Server
      3. Appropriate MySQL Security
      4. Appropriate MySQL Configuration
      5. Deleting MySQL Data
      6. Deleting the InnoDB Data File
      7. Deleting MySQL Binary Logs
    5. Existing Backup and Recovery Procedure Disasters
      1. MySQL Software Upgrades
      2. Operating System Security Patch Upgrade
      3. Handling MylSAM Corruption
      4. Missing Database Schemas
      5. Restoring a Backup on a Running MySQL Instance
    6. Handling InnoDB Specific Situations
      1. Automatic Recovery
      2. InnoDB Data Dictionary Inconsistency
      3. Automatic Recovery Crashes the Database Server .
    7. Other MySQL Situations
      1. Replication Inconsistency
      2. RDS Recovery Failure
    8. Common Downtime Causes
    9. External Help
    10. Conclusion
  16. 8. Optimizing Backup and Recovery
    1. Example Backup Environment
    2. Using Compression
      1. mysqldump
      2. MySQL Enterprise Backup (MEB)
      3. XtraBackup
    3. Streaming Backups
      1. Using SSH
      2. Using nc
      3. MySQL Enterprise Backup (MEB)
      4. XtraBackup
    4. Remote Backups
      1. mysqldump
      2. MySQL Enterprise Backup (MEB)
      3. XtraBackup
    5. Parallel Processing
      1. mydumper
      2. XtraBackup
    6. Incremental Backups
      1. MySQL Enterprise Backup (MEB)
      2. XtraBackup
    7. Partial Backups
      1. mysqldump
      2. MySQL Enterprise Backup (MEB)
      3. XtraBackup
    8. MySQL Backup Security
      1. Encryption with ezNcrypt
    9. Architectural Considerations
    10. Conclusion
  17. 9. MySQL in the Cloud
    1. Amazon Relational Database Service (RDS)
      1. Example Database Creation
      2. MySQL Versions
      3. Backup Options
      4. Recovery Options
    2. Google Cloud SQL
      1. Example Database Creation
      2. Backup Options
      3. Restore Options
    3. HP Cloud Database as a Service (DBaaS)
      1. MySQL Versions
      2. Example Database Creation
      3. Backup Options
      4. Recovery Options
    4. Cloud Impact on Backup and Recovery
    5. Conclusion
  18. Index

Product information

  • Title: Effective MySQL Backup and Recovery
  • Author(s): Ronald Bradford
  • Release date: May 2012
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071788588