MySQL Enterprise Backup User's Guide (Version 3.7.1)


This is the User's Guide for the MySQL Enterprise Backup product, the successor to the InnoDB Hot Backup product. This manual describes the procedures to back up and restore MySQL databases. It covers techniques for minimizing time and storage overhead during backups, and to keep the database available during backup operations. It illustrates the features and syntax of the mysqlbackup command, for example, how to back up selected databases or tables, how to back up only the changes since a previous backup, and how to transfer the backup data efficiently to a different server.

For legal information, see the Legal Notices.

Document generated on: 2014-10-13 (revision: 5163)

Table of Contents

Preface and Legal Notices
I Getting Started with MySQL Enterprise Backup
1 Introduction to MySQL Enterprise Backup
1.1 Types of Backups
1.2 The mysqlbackup Command
1.3 Making Backups Faster and Smaller
1.4 Files that Are Backed Up
1.5 Overview of Restoring a Database
2 Installing MySQL Enterprise Backup
II Using MySQL Enterprise Backup
3 Backing Up a Database Server
3.1 Before the First Backup
3.1.1 Collect Database Information
3.1.2 Grant MySQL Privileges to Backup Administrator
3.1.3 Designate a Location for Backup Data
3.2 The Typical Backup / Verify / Restore Cycle
3.2.1 Backing Up an Entire MySQL Instance
3.2.2 Verifying a Backup
3.2.3 Restoring a Database at its Original Location
3.3 Backup Scenarios and Examples
3.3.1 Making a Full Backup
3.3.2 Making an Incremental Backup
3.3.3 Making a Compressed Backup
3.3.4 Making a Partial Backup
3.3.5 Making a Single-File Backup
3.3.6 Backing Up In-Memory Database Data
4 mysqlbackup Command Reference
4.1 mysqlbackup Command-Line Options
4.1.1 Subcommands
4.1.2 Standard Options
4.1.3 Connection Options
4.1.4 Server Repository Options
4.1.5 Backup Repository Options
4.1.6 Metadata Options
4.1.7 Compression Options
4.1.8 Incremental Backup Options
4.1.9 Partial Backup Options
4.1.10 Single-File Backup Options
4.1.11 Capacity Options
4.1.12 Options for Special Backup Types
4.2 Configuration Files and Parameters
4.2.1 Source Repository Parameters
4.2.2 Backup Repository Parameters
4.2.3 Other Parameters
5 Recovering or Restoring a Database
5.1 Preparing the Backup to be Restored
5.2 Performing a Restore Operation
5.3 Point-in-Time Recovery from a Hot Backup
5.4 Setting Up a New Replication Slave
5.5 Restoring a Master Database in Replication
5.6 Backing Up and Restoring a Single .ibd File
5.7 Restoring a Backup to a Different Database Version
6 Using MySQL Enterprise Backup with Media Management Software (MMS) Products
6.1 Backing Up to Tape with Oracle Secure Backup
7 Troubleshooting for MySQL Enterprise Backup
7.1 Monitoring Backups with MySQL Enterprise Monitor
7.2 Error codes of MySQL Enterprise Backup
7.3 Working Around Corruption Problems
7.4 Using the MySQL Enterprise Backup Logs
7.5 Using the MySQL Enterprise Backup Manifest
8 Frequently Asked Questions for MySQL Enterprise Backup
III Appendixes
A MySQL Enterprise Backup Limitations
A.1 Limitations of mysqlbackup Command
B Compatibility Information for MySQL Enterprise Backup Releases and InnoDB Hot Backup
B.1 Compatibility with Older MySQL or InnoDB Versions
B.2 Compatibility of Backup Data with Other MySQL Enterprise Backup Versions
B.3 Expanded Use of Configuration Files
B.4 Relative and Absolute Paths
B.5 New and Changed Options in MySQL Enterprise Backup 3.6 and Higher
B.6 Comparison of MySQL Enterprise Backup and InnoDB Hot Backup
B.7 ibbackup and innobackup Commands
C Extended Examples
C.1 Sample Directory Structure for Full Backup
C.2 Sample Directory Structure for Compressed Backup
C.3 Sample Directory Structure for Incremental Backup
C.4 Validating a Single-File Backup Image
D MySQL Enterprise Backup Change History
D.1 Changes in MySQL Enterprise Backup 3.7.1 (2012-03-23)
D.2 Changes in MySQL Enterprise Backup 3.7.0 (2012-01-04)
D.3 Changes in MySQL Enterprise Backup 3.6.1 (2011-09-28)
D.4 Changes in MySQL Enterprise Backup 3.6.0 (2011-07-01)
D.5 Changes in MySQL Enterprise Backup 3.5.4 (2011-04-21)
D.6 Changes in MySQL Enterprise Backup 3.5.2 (2010-12-16)
D.7 Changes in MySQL Enterprise Backup 3.5.1 (2010-11-01)
E Licenses for Third-Party Components
E.1 RegEX-Spencer Library License
E.2 zlib License
E.3 Percona Multiple I/O Threads Patch License
E.4 Google SMP Patch License
E.5 Google Controlling Master Thread I/O Rate Patch License
E.6 RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License
MySQL Enterprise Backup Glossary

List of Tables

1.1 Files in a MySQL Enterprise Backup Output Directory
3.1 Information Needed to Back Up a Database
7.1 OS Errors for Linux and Other Unix-Like Systems
7.2 OS Errors for Windows Systems
B.1 New and Changed mysqlbackup Options in MySQL Enterprise Backup 3.6 and Higher

List of Examples

3.1 Making an Uncompressed Backup of InnoDB Tables
3.2 Making an Uncompressed Partial Backup of InnoDB Tables
3.3 Making a Compressed Partial Backup
3.4 Single-File Backup to Absolute Path
3.5 Single-File Backup to Relative Path
3.6 Single-File Backup to Standard Output
3.7 Convert Existing Backup Directory to Single Image
3.8 Extract Existing Image to Backup Directory
3.9 List Single-File Backup Contents
3.10 Extract Single-File Backup into Current Directory
3.11 Extract Single-File Backup into a Backup Directory
3.12 Selective Extract of Single File
3.13 Selective Extract of Single Directory
3.14 Checking a Single-File Backup for Corruption Problems
3.15 Dealing with Absolute Path Names
3.16 Single-File Backup to a Remote Host
4.1 Simple Backup with Connection Parameters from Default Configuration File
4.2 Basic Incremental Backup
4.3 Apply Log to Full Backup
4.4 Incremental Backup
4.5 Example backup-my.cnf file
5.1 Applying the Log to a Backup
5.2 Applying the Log to a Compressed Backup
5.3 Applying an Incremental Backup to a Full Backup
5.4 Shutting Down and Restoring a Database
5.5 Steps to Back Up on MySQL 5.1 and Restore on MySQL 5.5
6.1 Sample mysqlbackup Commands Using MySQL Enterprise Backup with Oracle Secure Backup
B.1 Simple Backup Emulating ibbackup Behavior