Skip to main content

Cloud SQL using Cli

Create a Cloud SQL instance, database, and table​

In this blog, you provision a new Cloud SQL instance, create a database on that instance, and then create a database table with a schema that can be used by the demo application to store messages.

Enable Cloud SQL Administration API​

You enable Cloud SQL Administration API and verify that Cloud SQL is preprovisioned.

In Cloud Shell, enable the Cloud SQL Administration API:

gcloud services enable sqladmin.googleapis.com

Confirm that the Cloud SQL Administration API is enabled:

gcloud services list | grep sqladmin

List the Cloud SQL instances:

gcloud sql instances list

No instances are listed yet.

Create a Cloud SQL instance​

You create a new Cloud SQL instance.

Provision a new Cloud SQL instance:

gcloud sql instances create guestbook --region=placeholder

Provisioning the Cloud SQL instance will take a couple of minutes to complete.

The output should indicate that the Cloud SQL instance creation was done.

Create a database in the Cloud SQL instance​

You create a database to be used by the demo application.

Create a messages database in the MySQL instance:

gcloud sql databases create messages --instance guestbook

Connect to Cloud SQL and create the schema​

By default, Cloud SQL is not accessible through public IP addresses. You can connect to Cloud SQL in the following ways:

Use a local Cloud SQL proxy.

Use gcloud to connect through a CLI client.

From the Java application, use the MySQL JDBC driver with an SSL socket factory for secured connection.

You create the database schema to be used by the demo application to store messages.

Use gcloud CLI to connect to the database:

gcloud sql connect guestbook

This command temporarily allowlists the IP address for the connection.

Press Enter at the following prompt to leave the password empty for this lab:

Allowlisting your IP for incoming connection for 5 minutes...done.

Connecting to database with SQL user [root].Enter password:

The root password is empty by default.

The prompt changes to mysql> to indicate that you are now working in the MySQL command-line environment.

Note: For security reasons, the default setting for Cloud SQL does not allow connections to the public IP unless an address is explicitly allowlisted.

The gcloud sql connect command line automatically and temporarily allowlists your incoming connection.

It takes a minute or two for the allowlisting process to complete before the MySQL administration client can connect.

List the databases:

show databases;

This command lists all of the databases on the Cloud SQL instance, which should include the messages database that you configured in previous steps.

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| messages           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.04 sec)

Switch to the messages database:

use messages;

Create the table:

CREATE TABLE guestbook_message (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name CHAR(128) NOT NULL,
  message CHAR(255),
  image_uri CHAR(255),
  PRIMARY KEY (id)
);


Exit the MySQL management utility by executing the following command:

```bash
exit

You return to the standard Cloud Shell user prompt.