Using Cloud SQL for MySQL

This page shows how to connect to a Google Cloud SQL for MySQL Second Generation instance from an App Engine application in the flexible environment, and how to read and write to Cloud SQL. Cloud SQL is a SQL database that lives in Google's cloud.

To learn more about Google Cloud SQL, see the Cloud SQL documentation. For information on Cloud SQL pricing and limits, see the Cloud SQL Pricing page. App Engine applications are also subject to the App Engine quotas.

Before you begin

  1. Create or select a Cloud Platform project in the Cloud Platform Console and then ensure that project includes an App Engine application and billing is enabled:
    Go to App Engine

    The Dashboard opens if an App Engine application already exists in your project and billing is enabled. Otherwise, follow the prompts for choosing a region and enabling billing.

  2. Enable the Cloud SQL API.

    Enable the API

  3. To deploy your app with the gcloud tool, you must download, install, and initialize the Google Cloud SDK:
    Download the SDK
  4. Install the .NET Core SDK, LTS version.
  5. If you are using Visual Studio, to build and run .NET core applications you must install .NET Core tools.
  6. If you are using Visual Studio, to make it easy to deploy to App Engine install Google Cloud Tools for Visual Studio.

Creating an instance and setting the password

To create and configure a Cloud SQL instance:

  1. Create a Cloud SQL Second Generation instance.
  2. If you haven't already, set the password for the default user on your Cloud SQL instance:
    gcloud sql users set-password root % --instance [INSTANCE_NAME] --password [PASSWORD]
    

Create a user and configure SSL access to the Cloud SQL instance

  1. Follow instructions to create a client certificate and require SSL.

  2. From the Instance details page in the Cloud Platform Console in the previous step, click Access Control > Users.

  3. Click Create user account.

  4. Enter a name and password.

  5. Click Create.

  6. From the Instance details page, click Access Controls > Authorization.

  7. Click + Add Network.

  8. Enter all for the name.

  9. Enter 0.0.0.0/0 for the network.

  10. Click Done, then Save.

  11. To generate a client.pfx file from the certificate files you created in step 1, enter at the command line:

    openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -certfile server-ca.pem -out client.pfx
    

    If you don't have a machine with openssl installed, use Google Cloud Shell.

  12. Replace the client.pfx file in the dotnet-docs-samples\appengine\flexible\CloudSql project with the client.pfx you created.

Setting the connection string and adding a library

  1. Set up the local environment to support connections for local testing.

    For example, for the provided code sample, add the connection string to appsettings.json.

    The connection string includes the user, password, and IP address:

    "ConnectionString": "Uid=aspnetuser;Pwd=;Host=1.2.3.4",
    

    The connection string is used to create the connection:

    var connectionString = new MySqlConnectionStringBuilder(
        Configuration["CloudSql:ConnectionString"])
    {
        SslMode = MySqlSslMode.Required,
        CertificateFile = 
            Configuration["CloudSql:CertificateFile"]
    };
    if (string.IsNullOrEmpty(connectionString.Database))
        connectionString.Database = "visitors";
    DbConnection connection = 
        new MySqlConnection(connectionString.ConnectionString);
    

Running the sample code

The following sample writes visit information to Cloud SQL and then reads and returns the last ten visits:

// Insert a visit into the database:
using (var insertVisitCommand = _connection.CreateCommand())
{
    insertVisitCommand.CommandText =
        @"INSERT INTO visits (user_ip) values (@user_ip)";
    var userIp = insertVisitCommand.CreateParameter();
    userIp.ParameterName = "@user_ip";
    userIp.DbType = DbType.String;
    userIp.Value =
        FormatAddress(HttpContext.Connection.RemoteIpAddress);
    insertVisitCommand.Parameters.Add(userIp);
    await insertVisitCommand.ExecuteNonQueryAsync();
}

// Look up the last 10 visits.
using (var lookupCommand = _connection.CreateCommand())
{
    lookupCommand.CommandText = @"
        SELECT * FROM visits
        ORDER BY time_stamp DESC LIMIT 10";
    List<string> lines = new List<string>();
    var reader = await lookupCommand.ExecuteReaderAsync();
    HomeModel model = new HomeModel() { 
        VisitorLog = new List<VisitorLogEntry>()
    };
    while (await reader.ReadAsync()) {
        model.VisitorLog.Add(new VisitorLogEntry() {
            IpAddress = reader.GetString(1),
            TimeStamp = reader.GetDateTime(0)
        });
    }
    return View(model);
}

Testing and deploying

Visual Studio

To test your application locally:

  1. In Visual Studio, open dotnet-docs-samples\appengine\flexible\AppEngineFlex.sln.
  2. Press F5.

To deploy your application:

  1. In Solution Explorer, right-click CloudSql, and choose Publish CloudSql to Google Cloud...
  2. Click App Engine Flex.
  3. Click Publish.

Command Line

To test your application locally:

  1. Run the following commands from the dotnet-docs-samples\appengine\flexible\ directory:
    dotnet restore
    dotnet run
  2. In your web browser, navigate to http://localhost:5000/
  3. In your terminal window, press Ctrl+C to exit the web server.

To deploy your application:

  1. Navigate to the dotnet-docs-samples\appengine\flexible\ directory.
  2. Run the following commands:
    dotnet restore
    dotnet publish
    gcloud app deploy .\bin\Debug\netcoreapp1.0\publish\app.yaml
  3. Launch your browser to view the app at http://[YOUR_PROJECT_ID].appspot.com, by running the following command:
    gcloud app browse

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

App Engine flexible environment for .NET docs