innobackupex should sanity check that mysql datadir is empty before --copy-back

Bug #737569 reported by Henrik Ingo on 2011-03-18
26
This bug affects 5 people
Affects Status Importance Assigned to Milestone
Percona XtraBackup
Fix Released
Medium
Valentine Gostev
1.6
Fix Released
Medium
Valentine Gostev
2.0
Fix Released
Medium
Valentine Gostev

Bug Description

Steps to reproduce:

I tried the following flow of events using xtrabackup/innobackupex:

sudo innobackupex-1.5.1 --user=root ./
sudo innobackupex --apply-log 2011.../
mysql> DROP DATABASE sakila;
sudo innobackupex --user=root --copy-back 2011.../

In the last step I 1) had left mysql server running and 2) except for the one dropped database, the /var/lib/mysql directory was still there.

Expected result:

(I was just trying and had no expectations, but...)
Either a restored database, or some other consistent state + error message.

Actual result:

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex: Starting to copy MyISAM tables, indexes,
innobackupex: .MRG, .TRG, .TRN, .ARM, .ARZ, .opt, and .frm files
innobackupex: in '/home/ingo/hacking/xtrabackup/2011-03-18_15-37-41'
innobackupex: back to original data directory '/var/lib/mysql'
innobackupex: Copying directory '/home/ingo/hacking/xtrabackup/2011-03-18_15-37-41/sakila'
innobackupex: Copying directory '/home/ingo/hacking/xtrabackup/2011-03-18_15-37-41/mysql'
mkdir: cannot create directory `/var/lib/mysql/mysql': File exists
innobackupex: Error: Failed to create directory '/var/lib/mysql/mysql' : No such file or directory at /usr/bin/innobackupex line 542.

In MySQL, still without restarting:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sakila |
+--------------------+
3 rows in set (0.00 sec)

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)

mysql> select * from actor limit 10;
ERROR 1146 (42S02): Table 'sakila.actor' doesn't exist
mysql>

Suggested fix:

I might be missing some use cases here, but from the above it seems innobackupex --copy-back should check that /var/lib/mysql is empty before it does anything.

Workaround:

Make sure "ls /var/lib/mysql" shows an empty directory before running innobackupex --copy-back

Related branches

Stewart Smith (stewart) on 2011-10-20
Changed in percona-xtrabackup:
importance: Undecided → Medium
Changed in percona-xtrabackup:
assignee: nobody → Valentine Gostev (longbow)
Changed in percona-xtrabackup:
status: New → Fix Committed
xeoshow (xeoshow) wrote :

Hello,

I am a user of XtraBackup, Regarding bug #737569, looks like the fix is not correct for some scenario: If I have created 3 databases, and now just want to restore one of them (suppose the db name is mydb01), then we will have at least 3 folders in /var/lib/mysql.

But the workround suggrested: Make sure "ls /var/lib/mysql" shows an empty directory before running innobackupex --copy-back

Under above scenario, we can not make the "/var/lib/mysql" to be an empty dir, since the other 2 dbs should keep as they are, right?

So, in my understanding, the workround should be: Make sure "ls /var/lib/mysql/mydb01" shows an empty directory OR not existing before running innobackupex --copy-back

I have tested under my local env and verified, but not sure if I missed something......

Thanks.

Valentine Gostev (longbow) wrote :

Hello,

partial backup restore is not supported. If you have innodb_file_per_table enabled for tables in these databases - you might want to use --export option to restore data from backup partially.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-564

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers