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

[Henrik Ingo]

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

[xeoshow (xeoshow)]

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)]

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.

[Shahriyar Rzayev (rzayev-sehriyar)]

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