Authorization and Security

Securing access to the database instance is of utmost importance. In some situations, when we are dealing with development or testing an environment, we can temporarily ignore security and allow open access to the instance; however, when we move to a live (production) environment and start dealing with actual data, then the insecure configuration can be risky, e.g. consider an application handling Credit Card details. Leaving the database open to all will result in a disaster by compromising the database.

Let’s look at ways of securing access to our CloudSQL instance. This depends on the ways of accessing.

Authorized Network

If the application VMs accessing the CloudSQL instance are spread across varied networks (across multiple providers) but have static IP addresses assigned, then we can use the Connections section to authorize the network, limiting connectivity from the configured networks only. Navigate to the instance detail page. Select Connections Tab. Figure 4-3 shows the Connections tab.

Click on Add Network button. This enables us to add a new network for authorization as shown in Figure 4-4.

Add networks using CIDR notations. Let’s look at few examples of CIDR notation. If we have a single instance e.g. 199.27.25.21 from where we need to restrict access, we should specify CIDR notation as 199.27.25.21/32. Using /32 implies that the IP is an exact IP.

If we have a range of instances following a pattern e.g. instances with IP addresses from 199.27.25.1 to 199.27.25.24 follows a pattern where only the last number is varying. In this case we can add network using the CIDR notation 199.27.25.0/24. Using /24 implies that the first three numbers are fixed and only the last number varies.

Once we are done with the configuration, Click Save. This updates the list of authorized networks. This configuration limits access at the network level. This will make the instance invisible to networks.

Though the above configuration restricts the access to our CloudSQL instance but the communication from the authorized network remains unencrypted (this is the default nature of CloudSQL). This default setting can possibly lead to data being intercepted when in transit leading to data breach.

When working with sensitive data e.g. Credit Card Details, Bank Account details, PIDs, securing data in transit is extremely important. Let’s next look at the CloudSQL configuration for securing the data in transit.

Data in Transit

SSL (Secure Socket Layer) is recommended to be used with authorized networks to secure data in transit.

For the ones who are new to SSL, SSL is a standard way for safely sending sensitive data from point A (web server) to Point B (CloudSQL instance) over unsecured public network where someone could intercept and either capture the information or act as man in the middle and maneuver the data while in transit.

Let’s look at the steps for enabling SSL based connection for our instance mysqllive. Click on Connections tab in the Instance detail page. Figure 4-5 shows the SSL Connections section underneath the tab. Click on Allow only SSL Connections.

In few seconds the settings will be updated to allow only secured connections and the section will look as shown in Figure 4-6.

To establish a secure connection from the client, we will be requiring the following:

• Server CA’s certificate

• Client certificate and

• Client Private Key

Let’s generate all the three. We will start with generating the server CA certificate first. Figure 4-7 shows the Configure SSL server certificates section. Click Create new certificate button.

Once the certificate is generated it appears in the console as shown in Figure 4-8.

Click on Download button. It downloads the certificate as server-ca.pem file.

Next we will generate the Client certificate. Figure 4-9 shows the Configure SSL Client Certificates section. Click on Create a Client Certificate button.

It prompts for name as shown in Figure 4-10. We should have a naming convention in place, it helps to better manage the keys. E.g. Naming based on the environment used, keys used for application development can be named as app-dev. For this example, let’s name it mysqlliveclient.

Click on Create. It will take few seconds to update. Once updated, you will see a popup as shown in Figure 4-11 with the client private key and certificate.

Download the files as client-key.pem and client-cert.pem. Upload all the .pem files to the client environment. Use the below command to check connectivity to your instance

  mysql -uroot -p -h 35.226.72.252 --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem

  --ssl-key=client-key.pem

35.226.72.252 is our CloudSQL public IP. On successful connectivity MySQL prompt will be displayed as shown below.

adminuser@airo0002:~$ mysql -uroot -p -h 35.224.169.212 --ssl-ca=./SSL/server-ca.pem --ssl-cert=./SSL/client-cert.pem --ssl-key=./SSL/client-key.pem

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 578

Server version: 5.7.14-google-log (Google)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

In order to verify that the connection is encrypted, use the command SHOW STATUS, the output of the command is as follows. It displays the algorithm used for encryption.

mysql> SHOW STATUS LIKE 'Ssl_cipher';

+---------------+------------+

| Variable_name | Value      |

+---------------+------------+

| Ssl_cipher    | AES256-SHA |

+---------------+------------+

1 row in set (0.27 sec)

Also note, if SSL is enabled on the CloudSQL instance and when we try to connect to the instance without specifying the certificates, it will not let us connect to the instance. We can validate using the command

mysql -uroot -p -h 35.226.72.252

The output will be as follows

adminuser@airo0002:~$ mysql -uroot -p -h 35.224.169.212

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'192.8.160.188' (using password: YES)

adminuser@airo0002:~$

Though authorized networks with SSL encryption provide a secure method for establishing connection however this model will not fit very well if we have a dynamic environment.

Such environments have no static IPs available, they are dynamic in nature, clients may live for a short period and the environment may rapidly scale to large numbers.

In order to address this CloudSQL provides the Cloud SQL proxy. The Cloud SQL Proxy provides secure access without having to authorize (whitelist) individual IP addresses of the instances. It doesn’t even require SSL configuration.

Cloud Proxy

Cloud proxy is a preferable option to securely connect to your Cloud SQL instance, no matter from where it is accessed. Security and easier connection management are general features of proxies.

• Secure connections: The proxies help shield the real IP addresses of your instances. It automatically encrypts traffic to and from the database.

• Easier connection management: Proxy handles the authentication with Google Cloud SQL, removing the need to provide static IP addresses for authorizing the network.

Let's take a very quick look at how a cloud proxy works. Figure 4-12 illustrates how a client application running on a client machine communicates with Cloud SQL via a TCP secure tunnel.

The Cloud SQL proxy works by having a proxy client running on the client machine in the local environment. The client application communicates with the proxy client using standard database protocol e.g. JDBC/ODBC. The client does not directly talk to CloudSQL as a result bringing in great deal of additional security. The proxy client in turn uses a secure TCP tunnel to communicate to the CloudSQL.

While setting up the proxy on client we need to provide details of the proxy server. We also need to specify where to find the credentials which will be used to authenticate the client applications. We have covered all the steps required for connecting using Cloud Proxy in the previous chapter.

In the above examples we chose Public IP addresses and Cloud Proxy to connect to our CloudSQL instance. In addition to the above two options Google also has Private Networking connectivity option. Private network also termed as Virtual Private Cloud (VPC) enables much easier, faster and secure connection to CloudSQL.

Private IP Address

For connectivity using Private IP Addresses we have to select Private IP option from the Connections Tab. Figure 4-13 shows the Connectivity section underneath the Connections Tab.

Select Private IP. For Private IP enablement CloudSQL requires the Service Networking API to be enabled. Selecting Private IP prompts the pop up as shown in Figure 4-14. Select Enable API button.

Within few seconds the API will be enabled and we will be prompted to select the network for which we need to enable the private connection as shown in Figure 4-15.

New Networks can be created using the Networking Section from the Left Navigation Menu as shown in Figure 4-16.

Note

Creation of the network is not in scope of this book.

We leave the default selection and Click on Allocate and Connect. Within few seconds a Private IP for the selected network will be allocated to the instance. Click Save for the details to be updated. In order to validate the Private IP allocated. Navigate to the Overview Tab and take a look at the Connect to this instance card. The Private IP allocated will be visible as shown in Figure 4-17.

Note

this option is only visible if private IP is selected for the instance.

Login to your compute VM and use the below command to connect to the CloudSQL instance using Private IP.

architectbigdata@instance-1:~$ mysql -h 10.6.160.3 -u root –p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 40

Server version: 5.7.14-google-log (Google)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

That’s all to it. In effect this option enables connectivity to the CloudSQL instance using GCP VPC which further facilitates private communication to our CloudSQL instance. Private IP has the following advantages as compared to Public IP and Cloud Proxy

• Enhanced Network Security as the instance is no longer exposed to the outside world.

• Reduced Network Latency

Authorization

With the access part handled it’ also important to determine the control the user has on your instance. This control is divided further into

• instance management e.g. start/stop/create database etc. and

• data management e.g. ability to insert, delete, select data.

The first is controlled via the GCP IAM access control and the other is controlled via the database engine user creation. Let’s first look at the IAM access control.

IAM is the identity and access management service of GCP which enables us to control actions on our resources e.g. our CloudSQL instance.

Let’s begin with looking at the way we can grant instance access to a resource. Navigate to IAM in the Left Navigation panel as shown in Figure 4-18.

This takes us to a page shown in Figure 4-19 where all users of our project is listed.

We can either edit permissions of an existing user or add new user with defined roles. To edit a user access, click on edit icon next to its name. This opens a page as shown in Figure 4-20.

We can either delete the role assigned by clicking on the delete icon next to the role or change the role from the dropdown or add additional role to the user by clicking on Add Another Role. Figure 4-21 shows the roles displayed on click on the Role dropdown.

Figure 4-22 shows the SQL options available.

Select the role. For this example, we chose CloudSQLAdmin role as shown in Figure 4-23. Click on Save for the permissions to be effective.

While choosing the roles we also had an option to Manage Roles. Clicking on Manage Roles takes us to the Roles page wherein we can search for and create new roles. Figure 4-24 shows the roles for CloudSQL.

We can click on Create Role to merge permissions together and create roles. More on IAM access management can be referred in the Project Access Control in GCP documentation¹. Figure 4-25 shows the screen which enables us to add a new user and grant permissions to the user.

In the name enter the name of the member and select the role like we did while editing. Click on Save to add the user with the permissions enabled.

With the IAM access controlled let’s next look at the database users. Database users are created to control the access on the instance data and the operations a user can perform on the data. Navigate to the Instance detail page and click on Users Tab. This lists the default user created with the instance. Click on Create User Account. This takes us to screen as shown in Figure 4-26.

Enter the user name and password. The users created have the same privileges as the default user. To manage the access we will have to use GRANT/REVOKE in case of MySQL² and ALTER ROLE in case of Postgres.³ As an admin it’s important for us to provide granular controls on actions a user can perform on our instance.

With the access and authorization part handled, the next important activity is to ensure that at any point in time if our database becomes inconsistent because of accidental update or delete we are able to get our database back to a stable state.

Backups and Restores

In database world the backup & restore mechanism enables us to get our instance back to a stable state at a point in time. The trick is not in taking backups but in scheduling backups in such a way that we can restore to the latest stable data without much data loss.

In CloudSQL automated daily backups are by default selected while provisioning the instance and they are scheduled to start in a defined period. We should change the time period which best suits our environment workload.

Navigate to the Instance detail page. Click on the Backups tab. It displays the Backup settings which was configured while creating the instance as shown in Figure 4-27.

Click Manage automated backups button, it opens a Pop-up as shown in Figure 4-28. Adjust the time as per your requirement.

Though the backups don’t have any impact on write operations still it’s best to choose off peak hours so that it doesn’t overlap with users trying to modify data in the system.

CloudSQL retains the most recent seven automated backups per instance. These backups are incremental which means the oldest backup is a full backup and all the subsequent backups are incremental i.e. only the changed data from the previous backup are captured. This helps save storage required for backups.

Let’s create on-demand backup for our mysqllive instance. Navigate to the instance detail page and click on the Backups Tab. Figure 4-29 shows the Backups Tab.

Click on Create backup button. This opens a pop up, provide an optional description and click on Create. In few minutes the on-demand backup is ready and is displayed under the Backups tab as shown in Figure 4-30.

All the backups we create will be deleted when we delete the instance. CloudSQL stores the backup in two regions for redundancy to ensure safety against any disaster situations. However, if the region from where the backup was originally taken is unavailable, then we will not be able to restore from it. This limitation should be kept in mind while devising the backup and high availability strategy.

Now that we have looked at scheduling and taking backups, the next logical step is to restore the backup. In CloudSQL restoration is as simple as taking backups and involve few clicks. Navigate to the instance detail page, Click on Backups tab. This lists all the backups. Figure 4-31 lists the backups created for our instance.

Let’s restore the On-demand backup. Click context menu next to the On-demand backup. Figure 4-32 shows the context menu options. Select restore option.

This opens the Restoration pop-up as shown in Figure 4-33 prompting us to specify the target instance. Restoration can be on the same instance from where the backup was taken or it can be altogether a new instance as well. Select the instance and click Ok.

Note that while restoration is in progress the target instance remains unavailable. Within few minutes the data is restored back and the instance becomes available.

CloudSQL MySQL - Binary Logging

Binary logging is an option which is by default enabled along with automated backups while provisioning CloudSQL – MySQL instance. Binary logging records every data modification operation in an independent log post the last stable backup.

This will be helpful in situations where we accidently end up modifying some critical information. We can use the last stable backup and the log file coordinates till that operation to restore the data back to the state prior to the operation.

It is recommended to keep it on if the application deals with lots of data operations as it will be extremely helpful to restore the state back to a specific point in time. However, we need to note that since binary logging logs all operations, it impacts the write performance.

Like backups the Manage automated backup settings can be used to enable or disable binary logging. Figure 4-34 shows the binary logging enablement option.

This change restarts the instance, so a few minutes’ downtime should be expected when changing this configuration.

Note

Automated backup should be enabled for enabling binary logging.

Database restores using the binary logging differ from restoring from a backup. A point in time recovery creates a new instance with the recovered data. While restoring we choose the stable backup and specify the binary log file name and the position of the event till which we want to recover. If we don’t specify the coordinates, then the instance is recovered up to the latest binary log coordinates.

With backups enabled and scheduled, let’s next look at maintenance for our instance. Maintenance is important for proper functioning of the instance. It involves tasks such as applying security patches, upgrading the instance to newer releases so that the known bugs and issues are fixed.

Maintenance

CloudSQL handles the maintenance activities for us. It’s scheduled to run any day any time by default when an instance is provisioned. We should set the maintenance window to day and time when it’s acceptable to carry out the maintenance activities, so that the end users of our application are not impacted.

E.g. if the application is Reporting and Analytics application which is used by business users, then late nights on weekend start is a good time, however if it is a social networking kind of application then early weekdays will be a good time.

Let’s set the maintenance window for mysqldblive. Navigate to the instance detail page. Figure 4-35 shows the top panel of the page. Click on Edit button.

This takes us to the instance configuration page. Click on Set maintenance schedule. Figure 4-36 shows the default configurations for maintenance schedule.

First choose day of the week from Maintenance Window dropdown. This prompts to specify the time period. As mentioned above we should choose the window which suits our application workload. For this example, we choose Saturday, between midnight 12:00 AM – 1:00 AM. Figure 4-37 shows the configurations done.

Also note that the time is in our local time zone, so while choosing the time we need to take into consideration the location of our end users.

E.g. if the instance is provisioned in IST as it’s above and the end users are located in US then the choice of late Saturday night should take into consideration the time zone difference and set accordingly. Next we choose the Maintenance Timing. Figure 4-38 shows the maintenance timing options available which are: Earlier and Later.

This decides the time when the upgrades will be applied, as soon as a new release happens (earlier) or with delay (later). For this example, we choose later, which is also a recommended option.

With the selections in place, let’s click on Save. This updates the schedule. It can be verified in the Maintenance Card in Overview tab of the instance detail page. Figure 4-39 shows the maintenance card.

With the schedule set, Google takes care of the rest. With connections secured, backups and maintenance scheduled, let’s look at managing resources.

As few of us might already be aware that as the load increases whether its data or end users accessing our application, the instances start to show performance issues. We have to accordingly increase the resources (Vertical scaling) for maintaining optimal performance.

Managing Resources

Let’s first begin with instance type. Navigate to the Instance detail page and click Edit in the top panel. It takes us to the instance configuration page. Select Configure Machine Type and storage option. Figure 4-40 shows the Configure Machine Type and Storage section. It shows the instance type which our instance is presently running on, we can see that we started a db-n1-standard-1 instance type which is a single core machine. Let’s click on Change and look at the instance types available.

Figure 4-41 shows all the options available. The list is divided into three categories - Shared, Standard and High memory.

If our load increases (or decreases), we can choose to upgrade (or downgrade) to any of the available instance types in just a single click. The choice of the instance type will be completely dependent on your application workload.

E.g. For memory intensive applications, it’s best to choose from high memory instances. These applications typically would pin a lot of data in memory so that the response time of returning results from the database is faster.

We can look at the variants d1-n1-standard-4 and d1-ni-highmem-4. Though both have same cores, but they differ in the memory allocated.

Also note that this configuration change will require restart of the instance. In effect, we will have a minimal downtime while Google changes the resources of our instance. So, we should choose to upgrade in a time period which will have less impact on the end users. For this example, we simply upgrade to the next higher machine under standard – db-n1-standard-2, a two core standard machine. Click Select. This will show a prompt as shown in Figure 4-42. Click on SAVE and RESTART.

Within few minutes the instance type will be upgraded (or downgraded). Choice of instance type is slightly different for PostgreSQL instances, instead of a list of machine types, we have the knobs available to adjust the Core and memory as shown in Figure 4-43.

We need to note that when we change the cores, memory is automatically changed to match the core settings, but the user can change it further to either increase or decrease as per the needs. The on demand and elasticity provided by cloud makes it a matter of few clicks to increase and decrease capacity. With the instance type upgraded, let’s now look at the storage capacity. Figure 4-44 shows the choices available for Storage Capacity.

By default, it is set to 10GB and enabled for automatic storage increases to handle the increase in data load.

In effect, in CloudSQL the probability of running into low disk space issue is rare as it automatically increases the storage as we near the capacity. The growth factor is set to indefinite i.e. there’s no restriction.

However, as a best practice we should limit the growth. Because though increasing the disk size is instantaneous (this change doesn’t require any restart), going back is not possible. If a temporary data fluctuation happens, we will end up with a large storage capacity, then the actual requirement.

Though we don’t require changing the disk size, however we need to note that the size is proportional to the performance. Larger the disk size, better is the IOPS capacity and fetches. So, if our application is doing frequent disk access then it’s good to start with a larger disk size for better disk performance.

To increase the disk size, just enter your desired size next to the storage capacity and click save. The change is almost instantaneous as no restart is required.

As already mentioned while increasing the size is just few clicks, once increased we will not be able to decrease the size. Keep this in mind while changing the capacity as storage on cloud is charged on pay per use basis.

Now that we have an upgraded instance it doesn’t imply that the database engine will automatically tune to the changes. As with on-premises database, we will have to tune the parameters to effectively utilize the resource increase for maximized performance. Let’s understand with an example.

We have a reporting application using mysqllive instance for data analysis. This ends up creating in-memory temporary tables for data processing. The in memory temporary table size is controlled by max_heap_table_size parameter of MySQL database engine. This is by default set to a 16 MB limit.

As the temporary table size crosses the limit it gets converted from in memory to on disk table, impacting the performance of the processing query as in memory processing is significantly faster than disk processing.

Let’s say with this observation of data size increase, we upgrade the instance to a high memory instance type. Now we have sufficient memory at our disposal, but to reap benefit of this memory increase for temporary table processing we will have to tune the max_heap_table_size parameter. We choose to set it to 256 MB.

CloudSQL enables us to tune in our instance specific parameters using Database flags . Let’s modify the max_heap_table_size parameter of mysqllive. Navigate again to instance detail page and click on edit. In the instance configuration page, expand the Add Database Flag section as shown in Figure 4-45 .

Click on Add Item. In the dropdown, search for max_heap_table_size and select the flag. With every flag selected, CloudSQL displays the possible value range. Figure 4-46 shows the value range for max_heap_table_size flag. Pay close attention to the units, here the size is to be specified in bytes.

We choose a value in the range displayed and that’s all to it. To add more, click on Add Item again. Once done setting all the flags, click on Save.

Like the instance type upgrade, this configuration change also requires instance restart. Figure 4-47 shows the prompt.

Click on Save and Restart and within few minutes the parameters are configured. An exhaustive list of parameters is already available in the respective database engine documentation sites.

Let’s finally look at the most important activity, being highly available ensuring our instance is fault tolerant. Goal is to ensure that the instance continues to run and remain available despite component or service failures. This is extremely important if we are dealing with critical applications.

High Availability

Replication is the mechanism of replicating data across multiple instances ensuring data redundancy. This help alleviates system failures. In effect, replication enables highly available system. Let’s look at replication in CloudSQL.

Replication

CloudSQL provides a clustered environment for replication comprising of a primary (or master) instance, a replica for fault tolerance and one or more read replicas. Let’s first look at Read Replicas.

Read Replica

Read replica serves only Read requests to the client. It is a copy of the primary (or master) instance. It reflects changes to the primary instance in almost real time. The read replicas follow the primary (or master) instance to pull the updates. Figure 4-48 shows the process.

MySQL Read Replicas sync with the Primary instance using asynchronous replication. In asynchronous replication the primary instance keeps writing the events to its binary log. But it does not track whether or when a replica has accessed and processed the events.

Note

Replication in MySQL expects binary logging to be enabled. Please refer to the section on binary logging for more details.

PostgreSQL Read Replicas use the PostgreSQL default replication methodology - write ahead log (WAL) shipping. Every transaction in PostgreSQL is written to a transaction log called WAL (write-ahead log) to achieve durability. A read replica uses these WAL segments to continuously replicate changes from its primary instance.

In effect in either of the database engine creating a read replica does not impact the performance of the primary instance as the operation doesn’t have to wait for any acknowledgement from the read replicas.

Read Replicas can be used to horizontally scale our read loads and offload the read requests from the primary instance. If we have applications with more reads than writes, then turn on multiple read replica instances and route read traffic to these instances .

Fault Tolerance Replica

Replica for Fault tolerance is same as Read replica, however it’s primary job is to be ready as a primary (or master) instance replacement, reducing downtime and ensuring the data continues to be available to client applications in case of any disaster e.g. either zone where the primary (or master) instance is located experiences an outage or the primary (or master) instance is unresponsive.

CloudSQL continuously checks the health of primary (or master) instance. If the instance becomes unresponsive, an automatic switch over happens to the standby fault tolerant replica for serving data. This entire process is called a failover. Let’s first look at the failover process of MySQL database engine.

MySQL

In MySQL the replica for fault tolerance is called a Failover replica. Semisynchronous replication method is used to sync data between the primary instance and the failover replica. This means every write operation performed on the primary instance waits until the failover replica acknowledges that it has received and logged all events for the operations. This is to ensure that changes are never lost and the order of the operation is also maintained exactly replicating the primary instance. Using this log data, the failover replica then performs its updates. The HA enabled MySQL instance looks as shown in Figure 4-49.

Let’s look at the failover process. As we mentioned above the failover process will be initiated as the primary instance becomes unresponsive as shown in Figure 4-50.

CloudSQL waits for the failover replica to catch up to the primary instance’s state.

Note

This step is affected by replication lag. This is difference in time from when the primary instance makes an update to when the failover replica catches up with that update from its log. Replication lag should be acceptable to initiate the failover.

Once the failover replica catches up, it is promoted to the primary instance role i.e. the primary instance name and IP addresses are moved to the failover replica as shown in Figure 4-51. Doing this ensures that the client application can reconnect to the new primary instance without any change in the connection string.

In effect, from an application perspective there’s no update required after failover. Only for some time during the failover the application won’t be able to connect to the database. As the last step, CloudSQL recreates a failover replica in a healthy zone as shown in Figure 4-52.

Let’s next look at the failover process of PostgreSQL database engine.

PostgreSQL

In PostgreSQL the HA enabled instance is referred to as regional instance and the replica for fault tolerance is called standby instance. The HA configuration is backed by Google’s new Regional disks. Figure 4-53 shows the basic setup.

As with MySQL, the failover process will be initiated as the primary instance becomes unresponsive. During failover the IP address and name of the primary instance is transferred to the standby instance and the instance is restarted. After failover, connected application resumes connection to the new primary instance without noticing any change as the connection details remains the same. Let’s now enable high availability.

Enable High Availability

Navigate to instances page. Figure 4-54 shows the instances page. We can see Add in the High Availability column.

Click on Add next to mysqllive. This takes us to the form shown in Figure 4-55 where we create the failover replica.

As with Instance provisioning, we need to provide the instance id. For this example, we retain the default which comes auto populated. Next we need to specify where the instance is to be located. Notice that though it enables us to choose a zone, but the region is disabled. Figure 4-56 shows the available zones. Note that the zones listed omits the primary instance zone.

This implies that the failover replica will be created in the same region as that of primary instance but in a different zone. By creating the failover replica in a different zone we can be certain that our instance remains available even if a zone outage occurs.

We have an option Any in zone selection. Selecting this implies that we don’t have any zone preference and we leave the decision on GCP to place our replica in any zone listed in the dropdown. Let’s retain the default selection us-central1-b.

The remaining parameters come configured as that of the primary instance. Since the prime responsibility of this instance is to act as a standby for the primary instance, we retain all the configurations as is and click on Create.

Within few minutes the instance is provisioned. The failover replica will be configured with the same configuration as the primary instance. Once provisioned it appear under the primary instance as shown in Figure 4-57.

It will be displayed in the left panel of the instance detail page as well as shown in Figure 4-58.

Let’s also look at enabling HA for a PostgreSQL instance. Like with MySQL instance, click on Add in the High Availability column next to the PostgreSQL instance in the instances page. It just prompts like Figure 4-59 to enable high availability.

Click Enable and High Availability is enabled as shown in Figure 4-60.

As is seen, unlike MySQL there’s no separate instance provisioning being done. Let’s next create Read replicas.

Read Replicas

Navigate to the instances page. Click on the Contextual menu next to mysqllive instance. Figure 4-61 shows the available options. Let’s choose Create Read Replica.

This takes us to the Create Read Replica page as shown in Figure 4-62. This is similar to the failover replica create page.

First we have to specify the Instance Id. As with Failover replica form, the read replica instance id also comes pre-populated. Let’s retain the default ID. Next we specify the location for the replica. Like Failover Replica, region remains disabled, however note, unlike the failover replica the primary instance zone is listed in the Zone drop down. This implies we can create the read replica in any zone in the region including the primary instance zone as well. We retain the primary instance zone.

Like Failover replica, the read replica form also comes prepopulated with the primary instance configuration. However, since the read replicas are not standby for primary instance and will be primarily used to scale read requests, we can customize the read replica.

The primary configuration which we may change is the instance type, this choice depends on whether we require a more powerful (upgrade) or less powerful (downgrade) read replica. This is determined by our application workload, if its read heavy we can opt for a powerful read replica.

For this example, let’s downgrade the instance type back to db-n1-standard-1. With all configuration in place we click Create. Within few minutes the read replica is enabled and like the failover replica is displayed underneath the primary instance as shown in Figure 4-63.

The steps for creating PostgreSQL Read replica is same as above.

In effect, with failover replica and multiple read replicas created, a fault tolerant, high performing, scale out CloudSQL instance in us-cental1 region will look as shown in Figure 4-64.

With the HA enabled system setup, let’s next look at the way we can diagnose issues.

Logging

Logs are critical for identifying and diagnosing issues with the instance. Being managed logs capturing is built-in. Navigate to Operations tab in the Instance detail page. We will be redirected to screen as shown in Figure 4-65 which lists logs of all recent operations being performed on the instance.

However, we need to note that this logs are of operations being performed using Console, gcloud utility or CloudSQL API only.

In order to view error and logs from clients e.g. MySQL Client we can use GCP Stackdriver logging Logs viewer. Stackdriver logging enables us to log and analyze our log data. This also enables us to create a metric wherein we can create notifications as an event occurs for proactive resolutions.

Let’s next look at the log viewer. Navigate to Operations and Logs card underneath Overview tab in the instance detail page. Click on MySQL error logs as shown in Figure 4-66.

This takes us to the Stackdriver Logging Log Viewer as shown in Figure 4-67 with selections in place.

As we can see it enables us to run analysis using Filter. We can also Create a Metric which can be monitored for notification purposes. To learn more on working with Log Viewer and Monitoring, refer Stackdriver Logging.⁴

Stackdriver logging default logs for the ones shown in Figure 4-68.

We can enable the log collection for our instance using the database flags options.

For this example, let’s enable SLOW Queries Logging for our instance, this is important for individuals responsible for managing the MySQL database. This will enable us to identify inefficient or time-consuming which needs optimizations.

Navigate to instance detail page, click on Edit Instance. This opens the Edit Instance page as shown in Figure 4-69.

Figure 4-70 shows the sections with the inputs in place.

Click on Close and Save buttons respectively for the instance to be updated. Navigate to the Log viewer, we will see the Slow Query Log enabled as shown in Figure 4-71.

With this we covered important activities for setting up and managing CloudSQL in a live (production) environment.

Summary

In this chapter we configured CloudSQL to work in a high availability architecture. We also saw how to configure CloudSQL to work in a scale out architecture for high volume read applications using read replicas and also looked at ways of diagnosing the instance. With this we conclude usage of CloudSQL instance. Let’s next begin with CloudSpanner.