Creating a CloudSQL server instance from a database dump
For a project based on the Google Cloud Platform recently, one of the very first things that came up was how to easily set up a new database from scratch on the platform from the command line, so that we could automate it to quickly set up new development environments.
To be able to do this, you must install the Google Cloud Command Line Tools as well as login to your account on the command line.
Configuration
These are the variables that will be used for the script with reasonable defaults.
We need the name for a temporary Google Storage bucket to store the dump in, the name for the CloudSQL instance we should create, the Google Cloud project to fetch the data from (here we use the current project by calling the gcloud command line tool) as well as a name for the database to create on the SQL server.
Bucket to hold SQL database dump
To be able to import a SQL dump into CloudSQL, it needs to be uploaded to a
Google Storage bucket. If you already have one for the project, you can reuse
it. If not, create a new bucket to hold this data (change the region eu
to your own):
Then to copy your local database dump (we assume the name is: a_mysql_dump.sql
)
up to Google Storage, run the following command:
Create SQL instance and database
To create the database, you use the create command from the gcloud command line tools. Here you specify the instance size, what region the instance should be in, and what version of MySQL (or Postgres) the server should run. You can easily change this to match your needs.
Then to create the database on the server, you need to run another command.
If you are doing the setup from a script, it usually takes 10-15 seconds before the instance accepts
incoming SQL connections. So add a sleep 20
timeout after this command if you are making a script.
You should also set a root password that you can use to login in to the instance;
You can use this command to set the password for any SQL user, simple change the root %
to the
user you wish to update.
Give access to service account
You need to give access to the service account on the CloudSQL server, to the storage bucket where the database dump is stored. You do this by first fetching the service account email of the SQL server:
The email will be some gibberish like: lh6srs65thehhcwv22hv33jygu@dirty-banana-80.iam.gserviceaccount.com
To grant access to the bucket, run the following two commands. You need to give write access to the bucket, and read access to the database dump file.
(it will not work without write access to the bucket, belive me I’ve tried)
Import database
After all this setup, we are finally ready to import the dump into the CloudSQL
database. This is done by running gcloud sql import
pointing it the the file we uploaded to the storage bucket:
If this results in the incredibly unhelpful ERROR: (gcloud.sql.import.sql) ERROR_RDBMS
this means there is an error in your SQL database file (or that you did not
grant the proper permissions to the Google Storage bucket). Unfortunately there is no real way to get
the actual error from the server. You can try importing the dump to a local MySQL server to see
the error.
And that’s it! You should now have a fresh CloudSQL server, with your database dump loaded. Enjoy!