How to Restore Selected Table/Schema from full backup using MySQL Enterprise Backup(MEB)

 

Before i talk about “Selective Restore” , it is more important to know below details:-

• What is MySQL Enterprise Backup ?
• Features of MySQL Enterprise Backup.
• Benchmarking “mysqldump” Vs “mysql enterprise backup”
• What is TLR (table level Recovery)
• Limitations of TLR
• Conclusion 

What is MySQL Enterprise Backup ?

It is a multi-platform, high-performance tool, offering rich features like “hot” (online) backup, incremental and differential backup, selective backup and restore, support for direct cloud storage backup, backup encryption and compression, and many other valuable features.

This MEB is bundled with MySQL Commercial Editions.

More info:- https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/intro.html

Features of MySQL Enterprise Backup

MySQL Enterprise Backup delivers:

·       "Hot" Online Backups - Backups take place entirely online, without interrupting MySQL transactions

·       High Performance - Save time with faster backup and recovery

·       Incremental Backup - Backup only data that has changed since the last backup

·       Partial Backup - Target particular tables or tablespaces

·       Full Instance Backup - Backs up data, as well as configuration and other information to easily create a complete "replica"

·       Advanced Optimistic Backup - Uses heuristics to optimize and reduce backups and shorten recovery time by assessing usage patterns

·       Fast Recovery - Get servers back online and create replicated servers

·       Point-in-Time Recovery (PITR) - Recover to a specific transaction

·       Online "Hot" Selective Restore - bring back only selected tables into a running database

·       Direct Cloud Storage Backup via S3 and Swift APIs - Backup and Restore directly to/from Oracle Storage Cloud, S3 and other Cloud Storage using AWS S3 API

·       Advanced LZ4 Compression - Support highly efficient, low impact and ultra fast LZ4 compression, as well as LZMA and zlib

·       AES 256 encryption - Built in 256-bit Advanced Encryption Standard (AES) encryption to secure all the sensitive backup data

·       NEW! Supports MySQL TDE - Enables secure archival quality backup and restore of TDE encrypted database files and keys

·       Streaming "Zero storage" Single Step Backup and Restore - Run a full or partial backup from one server and a restore to another in one streamed step without staged storage

·       Backup Validation - Provides assurance checks to confirm backup integrity and quality by confirming that internal pages are valid and file checksums match.

·       Exclude Tables - Exclude unnecessary tables from your Backups, saving backup time and space

·       Binlog and Relay log backup - Simplifies cloning source to replica servers for HA replication

·       Include Tables - Backup only required tables for better granularity and usability.

·       Continuous Monitoring - Monitor the progress and disk space usage

·       Selective Backup/Restore - An efficient and transportable method to backup InnoDB tables using Transportable Tablespaces

·       Table renaming on restore of Transportable Tablespace (TTS) backups

·       Compression - Cut costs by reducing storage requirements up to 90%

·       Backup to Tape - Stream backup to tape or other media management solutions

·       Partial Restore - Recover targeted tables or tablespaces

·       Restore to a Separate Location - Rapidly create clones for fast replication setup

·       Reduce Failures - Use a proven high quality solution from the developers of MySQL

·       Multi-platform - Backup and Restore on Linux, Windows, Mac & Solaris

 

Benchmarking “mysqldump” Vs “mysql enterprise backup”

 

References:- https://www.mysql.com/products/enterprise/backup.html 

What is TLR (table level Recovery)

Table-Level Recovery (TLR) allows selected tables (or schemas) to be restored from a backup (be it a full backup , a partial backup, or a backup created using transportable tablespaces (TTS) ).

This features only available from MySQL 8.0.20 on wards.

Earlier version to restore a particular table or else schema it is only supported for TTS (transportable table space)backups.

Now , starting with MySQL 8.0.20 , users will get flexibility to take backups without TTS options and restore selective tables from full snapshot.

As Example:-

Suppose , I have taken Backup “DBbackup.img”  ,this backup image contains backup of 03 Schemas (Sales , World ,Sakila) , after some days I wanted to restore particular table from World database , let’s say “Country” Table.

How to RESTORE “country table “ from full backup “DBbackup.img”  ?

C:\Program Files\MySQL\MySQL Enterprise Backup 8.0>mysqlbackup --host=127.0.0.1 --protocol=tcp -uroot  -pxxxxxx  --include-tables="^world\.country" --backup-dir="D:\SampleBackupRestoreFolder\full\backuptmp441" --backup-image="D:\SampleBackupRestoreFolder\full\DBbackup.img" copy-back-and-apply-log Sample Output MySQL Enterprise Backup  Ver 8.0.21-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial) Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Starting with following command line ...  mysqlbackup --host=127.0.0.1 --protocol=tcp -uroot -pxxxxxxxxxx         --include-tables=^world\.country         --backup-dir=D:\SampleBackupRestoreFolder\full\backuptmp441         --backup-image=D:\SampleBackupRestoreFolder\full\DBbackup.img         copy-back-and-apply-log   200815 15:30:08 MAIN    INFO: Backup Image MEB version string: 8.0.21 [2020-06-16  18:15:12] 200815 15:30:08 MAIN    INFO: MySQL server version is '8.0.21' IMPORTANT: Please check that mysqlbackup run completes successfully.            At the end of a successful 'copy-back-and-apply-log' run mysqlbackup            prints "mysqlbackup completed OK!". 200815 15:30:09 MAIN    INFO: Importing table: `world`.`country`. 200815 15:30:09 MAIN    INFO: Analyzing table: `world`.`country`. 200815 15:30:09 MAIN    INFO: Importing table: `world`.`countrylanguage`. 200815 15:30:09 MAIN    INFO: Analyzing table: `world`.`countrylanguage`. 200815 15:30:09 MAIN    INFO: Apply-log operation completed successfully. 200815 15:30:09 MAIN    INFO: Backup has been restored successfully.   mysqlbackup completed OK!

 general requirements for performing a TLR or partial restore:

·      The destination server must be running. ·      The destination server must be using the same page size that was used on the server on which the backup was made. ·      The innodb_file_per_table option must be enabled on the destination server. For non-TTS backups: ·      The tables being restored must already exist on the destination server, in the same table definition. For TTS backups: ·      The tables being restored must not already exist on the destination server.

Limitations for a TLR or partial restore:

·      Individual partitions cannot be selectively restored. Tables selected by the --include-tables and --exclude-tables options are always restored in full. ·      Partial restores cannot be performed with incremental backups. ·      Binary, relay, and undo logs are not restored. ·      To Restore a particular table if it is associated with FKs then Restoration will failed , so to over this run mysql> set global foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) <Restore Commands> mysql> set global foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec)

 More info about TLR:-https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore.partial.html

Conclusion

Backup are must!!! , MySQL Enterprise Backup helps us to solve difficult problems in day to day activities, provision new instance, compliance with regulatory requirements.

Most important you will get immediate support from MySQL Support engineer.