You are previewing Effective MySQL Backup and Recovery.
O'Reilly logo
Effective MySQL Backup and Recovery

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.

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