Restore the data from Snapshot Backup when the data spans over multiple directories? Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF.

We have done some testing work on one of the slave say server A.and it takes a huge amount of disk space due to out test work on that slave. (Approx 300GB increased due to our test work) as dropping the database would not help us due to innodb_file_per_table OFF.

ibdata is on LVM.
datadir is on /EBSroot
innodb logs are on some other location


The total ibdata size is approx 600GB due to this test work it increased to 900GB.

Now i want to reclaim the space.I am thinking as

delete the ibdata from server A.and take the snapshot backup from the one of the slave and restore on this slave A.

but i am not getting the correct steps.For How to take the backup from one of the slave and restore on this slave A.

i have studied alot on how to take snapshot backup of MySQL
i found some Caveats as : Handling data spread on multiple volumes (DB logs on separate LV or DBs spread across multiple LVs).

So how can do this without any issues.because my ibdata/logfiles/datadir are all on the different locations.I have to also  restart the replication as well.

I need the best possible way with minimum downtime.

please help..
To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of


MetaData Issues : I wrote about this as well


Why does InnoDB store all databases in one file? (See WARNING)
How to Recover an InnoDB table whose files were moved around
Error &#39;Unknown table engine &#39;InnoDB&#39;&#39; on query. after restarting mysql

Performance Issues : I wrote about that back in August 2011


MySQL InnoDB data file directly on a disk slice (fixed-size)?



Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave


Step 01) mysqldump all databases
Step 02) shutdown mysql
Step 03) delete ibdata1
Step 04) startup mysql
Step 05) reload the mysqldump


Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.