I have an RDS MySQL database on Amazon Web Services.
I want to reduce the size of the database in the control panel in order to save money.
Q: How do I make it smaller? Their site says you have to do it some magic way (from backups or such), but I don't want to mistakenly whack it!
Step by Step please… Thanks!
This is what happened: Amazon asked what my desired max size should be. I chose 10GB. I then added data until the table exceeds 10GB. Amazon then automatically increased the size limit to 20GB. In their manual, they state "increasing" is automatic, but "decreasing" is not.
After removing some of the excess data so the data size is under 10GB again, is there a way to make a new 10GB db and copy the data to it using a snapshot perhaps?
Best Answer
Write a script that will create a script
When you are satisfied that the script will do the job then do this:
Give it a Try !!!
CAVEAT
Since the default for innodb_file_per_table is ON, each InnoDB table being optimized will actually have its
.ibd
file shrunk. Any MyISAM tables (which I hope you don't have) will also have their.MYD
and.MYI
files shrunk as well.UPDATE 2012-09-18 18:19 EDT
I just tried the following:
Requested storage size (5) cannot be less then the current storage size (10)
)If I restore the snapshot, it creates a third Instance. DB Snapshot must be the whole Server Instance, not a dump of any kind.
You will have to do the following messy procedure:
At step 4, please run this Big Storage Engine Space Query on both the 20GB and 10GB instances
You will see how much wasted space disappeared when migrating the mysqldump to the new 10GB Instance. This will definitely defragment all InnoDB tables. You may need to run the above script (
/root/OptimizeAllTables.sql
) I gave you above to runOPTIMIZE TABLE
on all the tables in thesigns
database once a week to keep the.ibd
as small as possible. You can also run the Big Storage Engine Space Query to Monitor when it starts approaching 10GB.