My Oracle Support Banner

If Schema/Database Is Dropped During Backup, It Still Exists After the Restore (Doc ID 2149415.1)

Last updated on MARCH 08, 2017

Applies to:

MySQL Enterprise Backup - Version 3.12 and later
Information in this document applies to any platform.

Symptoms

If a database is dropped while creating a backup, then it still exists after restoring the backup. It is expected that the restored instance will not include the database dropped during the backup.

The issue can be reproduced at will with the following steps:

  1. Prepare a schema to be dropped:
    origin> CREATE SCHEMA db1;
    Query OK, 1 row affected (0.00 sec)

    origin> CREATE TABLE db1.t1 (id int unsigned NOT NULL PRIMARY KEY, val varchar(10)) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.14 sec)

    origin> INSERT INTO db1.t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e');
    Query OK, 5 rows affected (0.08 sec)
    Records: 5 Duplicates: 0 Warnings: 0

     
  2. Create the backup and drop the schema while the backup is in progress. Also create a new schema afterwards to show this does get restored.
    shell$ mysqlbackup --user=root --password --socket=... --backup-dir=/backups/full backup-and-apply-log
    ...

    # While the backup is copying the file-per-table tablespace files:
    origin> DROP SCHEMA db1; CREATE SCHEMA db2;
    Query OK, 1 row affected (0.16 sec)

    Query OK, 1 row affected (0.02 sec)

     
  3. Restore the backup on the target (/etc/my.cnf is just to pickup datadir etc):
    shell$ mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/backups/full copy-back
     
  4. Check for the existence of the <datadir>/db1 directory on the target:
    shell$ ls <datadir>/db1
    (nothing returned, but directory exists)
     
  5. Start the target instance.
  6. Check the schemata:
    targe> SHOW SCHEMAS;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db1                |
    | db2                |
    | employees          |
    | mysql              |
    | performance_schema |
    | sys                |
    | world              |
    +--------------------+
    8 rows in set (0.01 sec)

    target> SHOW TABLES FROM db1;
    Empty set (0.01 sec)

     

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.