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