Mysql – Restore the data from Snapshot Backup when the data spans over multiple directories

backupinnodbMySQLsnapshot

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..

Best Answer

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

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.

Related Question