MySQL Enterprise Backup User's Guide (Version 3.12.4)

Abstract

This is the User's Guide for the MySQL Enterprise 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 notes detailing the changes in each release, see the MySQL Enterprise Backup 3.12 Release Notes.

For legal information, see the Legal Notices.

For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other MySQL users.

Licensing information.  This product may include third-party software, used under license. See the MySQL Enterprise Backup 3.12 License Information User Manual for licensing information, including licensing information relating to third-party software that may be included in this MySQL Enterprise Backup release.

Document generated on: 2020-01-23 (revision: 8007)

---

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 Client

1.3 Overview of Backup Performance and Capacity Considerations

1.4 Files that Are Backed Up

1.5 Overview of Restoring a Database

2 Installing MySQL Enterprise Backup

3 What's New in MySQL Enterprise Backup 3.12?

II Using MySQL Enterprise Backup     [+/-]

4 Backing Up a Database Server     [+/-]

4.1 Before the First Backup     [+/-]

4.1.1 Collect Database Information

4.1.2 Grant MySQL Privileges to Backup Administrator

4.1.3 Designate a Location for Backup Data

4.2 The Typical Backup / Verify / Restore Cycle     [+/-]

4.2.1 Backing Up an Entire MySQL Instance

4.2.2 Verifying a Backup

4.2.3 Restoring a Database

4.3 Backup Scenarios and Examples     [+/-]

4.3.1 Making a Full Backup

4.3.2 Making a Differential or Incremental Backup

4.3.3 Making a Compressed Backup

4.3.4 Making a Partial Backup

4.3.5 Making a Single-File Backup

4.3.6 Making an Optimistic Backup

4.3.7 Making a Back Up of In-Memory Database Data

4.3.8 Making Scheduled Backups

4.4 Making Backups with a Distributed File System (DFS) or Storage Access Network (SAN)

5 Recovering or Restoring a Database     [+/-]

5.1 Preparing the Backup to be Restored

5.2 Performing a Restore Operation     [+/-]

5.2.1 Restoring a Compressed Backup

5.2.2 Restoring an Encrypted Backup Image

5.2.3 Restoring an Incremental Backup

5.2.4 Restoring Backups Created with the --use-tts Option

5.2.5 Restoring External InnoDB Tablespaces to Different Locations

5.2.6 Restoring a Backup from Cloud Storage to a MySQL Server

5.3 Point-in-Time Recovery

5.4 Restoring a Backup with a Database Upgrade or Downgrade

6 Using MySQL Enterprise Backup with Replication     [+/-]

6.1 Setting Up a New Replication Slave

6.2 Backing up and Restoring a Slave Database

6.3 Restoring a Master Database

7 Performance Considerations for MySQL Enterprise Backup     [+/-]

7.1 Optimizing Backup Performance

7.2 Optimizing Restore Performance

8 Encryption for Backups

9 Using MySQL Enterprise Backup with Media Management Software (MMS) Products     [+/-]

9.1 Backing Up to Tape with Oracle Secure Backup

10 Monitoring Backups with MySQL Enterprise Monitor

11 Troubleshooting for MySQL Enterprise Backup     [+/-]

11.1 Error codes of MySQL Enterprise Backup

11.2 Working Around Corruption Problems

11.3 Using the MySQL Enterprise Backup Logs

11.4 Using the MySQL Enterprise Backup Manifest

III mysqlbackup Command Reference     [+/-]

12 mysqlbackup

13 mysqlbackup commands     [+/-]

13.1 Backup Operations

13.2 Apply-Log Operations

13.3 Restore Operations

13.4 Validation Operations

13.5 Single-File Backup Operations

14 mysqlbackup Command-Line Options     [+/-]

14.1 Standard Options

14.2 Connection Options

14.3 Server Repository Options

14.4 Backup Repository Options

14.5 Metadata Options

14.6 Compression Options

14.7 Incremental Backup Options

14.8 Partial Backup and Restore Options

14.9 Single-File Backup Options

14.10 Performance / Scalability / Capacity Options

14.11 Message Logging Options

14.12 Progress Report Options

14.13 Encryption Options

14.14 Cloud Storage Options

14.15 Options for Special Backup Types

15 Configuration Files and Parameters

IV Appendixes     [+/-]

A Frequently Asked Questions for MySQL Enterprise Backup

B Limitations of MySQL Enterprise Backup

C Compatibility Information for MySQL Enterprise Backup     [+/-]

C.1 Cross-Platform Compatibility

C.2 Compatibility with MySQL Versions

C.3 Compatibility with Older Versions of MySQL Enterprise Backup

C.4 Compatibility Notes for Specific MySQL Versions

D MySQL Enterprise Backup Release Notes

MySQL Enterprise Backup Glossary

Index

List of Tables   [+/-]

1.1 Files in a MySQL Enterprise Backup Output Directory

4.1 Information Needed to Back Up a Database

14.1 List of All Options

List of Examples   [+/-]

4.1 Making an Uncompressed Partial Backup of InnoDB Tables

4.2 Making a Compressed Partial Backup

4.3 Single-File Backup to Absolute Path

4.4 Single-File Backup to Relative Path

4.5 Single-File Backup to Standard Output

4.6 Convert Existing Backup Directory to Single Image

4.7 Extract Existing Image to Backup Directory

4.8 List Single-File Backup Contents

4.9 Validate a Single-File Backup

4.10 Extract Single-File Backup into Current Directory

4.11 Extract Single-File Backup into a Backup Directory

4.12 Selective Extract of Single File

4.13 Selective Extract of Single Directory

4.14 Dealing with Absolute Path Names

4.15 Single-File Backup to a Remote Host

4.16 Single-file Backup to a Remote MySQL Server

4.17 Stream a Backup Directory to a Remote MySQL Server

4.18 Creating a Cloud Backup on Oracle Cloud Infrastructure Object Storage Classic

4.19 Creating a Cloud Backup on Oracle Cloud Infrastructure Object Storage

4.20 Creating a Cloud Incremental Backup on Oracle Cloud Infrastructure Object Storage

4.21 Creating a Cloud Backup on an OpenStack Object Storage

4.22 Creating a Cloud Backup on Amazon S3

4.23 Extract an Existing Image from an Oracle Cloud Infrastructure Object Storage Classic Container to a Backup Directory

4.24 Extract an Existing Image from Amazon S3 Cloud Storage to a Backup Directory

4.25 Optimistic Backup Using the Option optimistic-time=YYMMDDHHMMSS

4.26 Optimistic Backup Using the Option optimistic-time=now

4.27 Optimistic Backup Using the optimistic-busy-tables Option

4.28 Optimistic and Partial Backup Using both the optimistic-busy-tables and optimistic-time Options

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 Restoring a Backup Directory using copy-back-and-apply-log

5.6 Restoring a Single-file Backup using copy-back-and-apply-log

5.7 Restoring a Compressed Backup

5.8 Restoring an Encrypted Backup Image

5.9 Restoring an Incremental Backup Image

5.10 Restoring Selected Tables from a TTS Backup

5.11 Restoring and Renaming a Table from a TTS Backup

5.12 Restoring a Single-file Backup from an Oracle Cloud Infrastructure (OCI) Object Storage Classic Container to a MySQL Server

5.13 Restoring a Single-file Backup from an Oracle Cloud Infrastructure (OCI) Object Storage to a MySQL Server

5.14 Restoring a Cloud Incremental Backup from an Oracle Cloud Infrastructure (OCI) Object Storage Service to a MySQL Server

5.15 Restoring a Single-file Backup from an OpenStack Object Storage to a MySQL Server

5.16 Restoring a Single-file Backup from Amazon S3 to a MySQL Server

9.1 Sample mysqlbackup Commands Using MySQL Enterprise Backup with Oracle Secure Backup

13.1 Apply Log to Full Backup

15.1 Example backup-my.cnf file