How to reduce Google Cloud SQL instance size?

Question

I have a Google Cloud SQL MySQL 2nd Gen 5.7 instance with 70GB of unused storage. I want to reduce the unused storage of my instance as this is one of the major hosting costs.

The only way that I'm seeing as a possibility is dumping all the database to a new Google Cloud SQL instance created with the right storage capacity. However this approach has several pitfalls:

• It would require a lot of time to dump and load the database (this would require severe downtime in the websites that are using the db)
• I would have to change all the websites that use that database because the database's IP would change

What alternatives do I have?

Bibliopole

Answer 1

As it is indicated in the official documentation, it is not possible to reduce the storage capacity of a Cloud SQL instance. You can give more capacity to an instance if needed, but the change is permanent and cannot be rolled back.

The best option for you right now would be to create a SQL dump file and export your current data into it. Then, in a new Cloud SQL instance with the desired capacity, import the SQL dump file so that all your data is stored in the new instance. To reduce time and costs for this process, please follow these best practices, including the use of the appropriate flags, compressing your data and other tips available for faster and less expensive imports and exports.

However, the possibility of reducing the capacity of a Cloud SQL instance has been requested as a new feature and is currently being worked on. Head to the link and click on the star icon on the top left corner to get email notifications on any progress made on this request.

Mousy

However, it seems that the instance name cannot be reused for 7 days. How to solve it? – Conflux

Answer 2

This answer may be a late but one solution would be to file a case in their support feature.

The image below is the answer of one of their technical support regarding the storage issue:

Discombobulate

This is super useful, going to ping our GCP reps about this. Not sure why they wouldn't state this online – Burdett

Answer 3

Now Possible to migrate automatically

For anyone coming across this later, the official line from Google is to use the database migration service to copy the database to a new instance with a lesser/appropriate disk size. It does mean creating a new database instance and having to change connect strings in connecting apps but can do it.

The overview on how to do it is here: https://cloud.google.com/blog/topics/developers-practitioners/regain-cloud-sql-disk-space-database-migration-service

Denysedenzil

This is not reducing the instance's storage, it's creating a new instance with less storage via DMS – Lecialecithin

@DanielSerodio - Correct. The answer's title has been updated to better reflect this. – Denysedenzil

Answer 4

There are no alternatives to reduce Instance size(Storage capacity) except one way which you mentioned in your question.

So, the way is first, export data from your old db instance:

Then, import the data into your new db instance which you newly created with reduced Instance size:

And as you know, it's impossible to reduce Instance size for all PostgreSQL, MySQL and SQL Server on Cloud SQL and only increasing Instance size is possible after creating your db instance.

Pleiades