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

  • 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
  • 4
    By the way AWS RDS will NOT EVER Let you reduce DB Size on RDS. – cerd Oct 24 '12 at 7:57

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.

| 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
  • 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. – Michael Mior Oct 23 '12 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 – T. Brian Jones Jun 8 '16 at 2:43
  • 1
    In my case, the migration didn't work without @T.BrianJones's enhancement. – Nino Škopac Dec 2 '16 at 19:52

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

Here is the related documentation.

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

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

| improve this answer | |

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