13

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.

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!

RDS DB instance storage

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

3 Answers 3

4

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.

6
  • 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, 2012 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, 2012 at 16:31
  • 1
    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. Oct 23, 2012 at 16:15
  • 1
    This is an awesome solution. I fleshed it out a little more: mysqldump -h old-db-host -u old-db-user -pold-db-pass old-db-name | mysql -h new-db-host -u new-db-user -pnew-db-pass new-db-name Jun 8, 2016 at 2:43
  • 1
    In my case, the migration didn't work without @T.BrianJones's enhancement.
    – The Onin
    Dec 2, 2016 at 19:52
1

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

Here is the related documentation.

1
0

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

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