Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
@RolandoMySQLDBA ,@Dtest : Can you Please look into my question?? –  Abdul Manaf May 21 '12 at 10:10
add comment

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

share|improve this answer
    
It is Ok to take the mysqldump of this much amount of data ? Snapshot will not work with this.?? –  Abdul Manaf May 23 '12 at 5:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.