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.

Possible Duplicate:
How do I reduce the database size on AWS RDS MySQL?

I have a mysql DB on Amazon. It is bigger than I need and I don't want to pay so much.

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 wack it!

Step by Step please... Thanks!

enter image description here

share|improve this question

migrated from stackoverflow.com Oct 25 '12 at 20:12

This question came from our site for professional and enthusiast programmers.

marked as duplicate by RolandoMySQLDBA, Paul White, ypercube, Mark Storey-Smith, Nick Chammas Nov 8 '12 at 8:54

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Can I assume you have tried an Optimize table? –  Lars Sep 17 '12 at 18:10
    
Do you have this set up as a multi-AZ database? –  Mike Brant Sep 17 '12 at 21:33
    
By the way AWS RDS will NOT EVER Let you reduce DB Size on RDS. –  cerd Oct 24 '12 at 7:57

3 Answers 3

You can modify your instance to a minimum of 5 GB of storage.

Here are the related documentation: http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/USER_ModifyInstance.MySQL.html

share|improve this answer

Use the PHP AWS SDK (http://docs.amazonwebservices.com/AWSSDKforPHP/latest/

See the RDS Section:

Something along these lines might work (extracted from example, slightly modified).

    $rds = new AmazonRDS();

$response = $rds->create_db_instance('myInstance', 5, 'db.m1.small', 'MySQL', 'dbUser', 'dbPassword', array(
    'DBName' => 'myDatabase',
    'DBSecurityGroups' => 'default',
    'PreferredMaintenanceWindow' => 'Sun:05:00-Sun:09:00',
    'BackupRetentionPeriod' => 1,
    'PreferredBackupWindow' => '03:00-05:00',
    'MultiAZ' => true,
    'AutoMinorVersionUpgrade' => true
));

// Success?
var_dump($response->isOK())

So that will create your new db, then simply follow the migration guide after downsizing your orginal DB at http://aws.amazon.com/articles/2933

share|improve this answer

One idea (wholly un-researched, so there might be a better way, but this should work):

  1. Create a new RDS instance that is smaller in size.
  2. Dump the old one into the new one:

    mysqldump -h <old-RDS> -u <user> -p<password> | mysql -h <new-RDS> -u <user> -p<password>
    
  3. Whack the old one.

Of course, this assumes your total db size is smaller than 20GB, and that you're just looking to end up with a smaller instance. If you're trying to shrink the size of the database, in total, that's a different question.

share|improve this answer
    
Amazon has snapshots. I was hoping snapshots somehow could be used. Your method requires a complete download/upload which will cost (I/O Rate $0.10 per 1 million requests). –  aaaaaaa Sep 18 '12 at 16:27
    
@aaaaaaa Well, yes. It does. But you're talking < 20GB right? That's about $2: aws.amazon.com/rds. Look at the data in / out pricing. Also, how many millions of objects do you have in your db? I can't imagine this would cost more than a lunch at an upscale fast-food restaurant. –  Christopher Sep 18 '12 at 16:31
    
Of course if you have an EC2 instance in the same AZ as the two RDS instances, you can run this from there and save on transfer costs. –  Michael Mior Oct 23 '12 at 16:15
    
@Christopher +1 - concise way of my answer, I would select this as my anser. –  cerd Oct 24 '12 at 7:56

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