Google Cloud Platform: Cloud SQL replication - Readonly and failover replica creation and simulation

A fundamental principle to design a highly available systems is to get rid of any single points of failure. In order to make DB instance fail-safe and efficient, Cloud SQL makes it easy to implement the most basic forms of replication.

It does so by providing two different push-button replica types:

1. Read replicas : Only read is allowed, write operation is refused.
   
2. Failover replicas: Standby or replacement primary instance

A read replica is a clone of your Cloud SQL instance that follows the primary or master instance, pulling in any changes made to the master.

• The read replica is strictly read-only, which means that it will reject any queries that modify data (such as INSERT or UPDATE queries). 
• Read replicas are useful when your application does a lot more reads than writes, because you can turn on a bunch of read replicas and route some of the read-only traffic to those instances. 
• Horizontal scaling is an added advantage

 Read replica creation from Google console:

• From SQL Services dashboard, select MYSQL instance(three dot from Action's column) and "Create read replica". 
• Instance name can be changed, by default it created <primary_instance_name>-replica
  
  
  
  
  
  
  
• Architecture of read replica with primary instance
  
  
  
  No write is allowed from application/client, Only Read operation is allowed.
  

A failover replica is similar to a read replica, except its primary job is to be ready as a replacement or standby instance of primary instance in case of some sort of disaster.

Failover replica creation from Google console:

• From SQL Services dashboard, select MYSQL instance(three dot from Action's column) and "Create clone".
  

• Architecture of failover/standby replica with primary instance:
  
  
  
  
  

 Overview of readonly and failover replica in Google console:

Visualize Read-Only replica feature 

In order to simulate readonly replica feature, lets perform write operation from primary instance and repeat the same from readonly replica using remote MYSQL client.

1. Connect to primary MYSQL instance and execute both read and write operation query.
   
   • Instance ID = devinline-gcp-mysqll 
   • Public IP address = 35.225.161.77
     

       
     n0r0082@m-c02z31rnlvdt ~ % mysql -u root --password=PASSWORD -h 35.225.161.77 \ 
       --ssl-ca=../GCP/certs/server-ca.pem \
       --ssl-cert=../GCP/certs/client-cert.pem \
       --ssl-key=../GCP/certs/client-key.pem
     Your MySQL connection id is 10151
     Server version: 5.7.36-google-log (Google)
     
     Copyright (c) 2000, 2022, Oracle and/or its affiliates.
     
     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> show databases
         -> ;
     +--------------------+
     | Database           |
     +--------------------+
     | information_schema |
     | devInlineTxn       |
     | mysql              |
     | performance_schema |
     | sys                |
     +--------------------+
     5 rows in set (0.05 sec)
     
     
     mysql> CREATE TABLE ORDERS(
         ->    order_id INT NOT NULL AUTO_INCREMENT,
         ->    order_total VARCHAR(100) NOT NULL,
         ->    customer_id VARCHAR(40) NOT NULL,
         ->    order_date DATE,
         ->    PRIMARY KEY ( order_id )
         ->    );
     Query OK, 0 rows affected (0.07 sec)
     
     mysql> show tables;
     +------------------------+
     | Tables_in_devInlineTxn |
     +------------------------+
     | ORDERS                 |
     +------------------------+
     1 row in set (0.05 sec)
     
     mysql> INSERT INTO ORDERS 
         ->    (order_total, customer_id, order_date)
         ->    VALUES
         ->    ("$120.98", "WM-198721", NOW());
     Query OK, 1 row affected, 1 warning (0.05 sec)
     
     mysql> INSERT INTO ORDERS 
         ->    (order_total, customer_id, order_date)
         ->    VALUES
         ->    ("$11.98", "WM-1100", NOW());
     Query OK, 1 row affected, 1 warning (0.05 sec)
     
     mysql> INSERT INTO ORDERS 
         ->    (order_total, customer_id, order_date)
         ->    VALUES
         ->    ("$41.98", "WM-4360", NOW());
     Query OK, 1 row affected, 1 warning (0.06 sec)
     
     mysql> select * from ORDERS;
     +----------+-------------+-------------+------------+
     | order_id | order_total | customer_id | order_date |
     +----------+-------------+-------------+------------+
     |        1 | $120.98     | WM-198721   | 2022-02-26 |
     |        2 | $11.98      | WM-1100     | 2022-02-26 |
     |        3 | $41.98      | WM-4360     | 2022-02-26 |
     +----------+-------------+-------------+------------+
     3 rows in set (0.05 sec)
     
     mysql> 
     
     

2. Connect to read-only MYSQL instance and try to execute write operation query.
   
   • InstanceId = devinline-gcp-mysqll-replica
     
   • public Ip address = 34.72.79.8)
   • This replicas is not SSL enabled so we will login into this instance without SSH keys.
     

     n0r0082@m-c02z31rnlvdt ~ % mysql -u root --password=PASSWORD -h  34.72.79.8  
     Your MySQL connection id is 8088
     Server version: 5.7.36-google (Google)
     
     Copyright (c) 2000, 2022, Oracle and/or its affiliates.
     
     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> clear
     mysql> show databases
         -> ;
     +--------------------+
     | Database           |
     +--------------------+
     | information_schema |
     | devInlineTxn       |
     | mysql              |
     | performance_schema |
     | sys                |
     +--------------------+
     5 rows in set (0.05 sec)
     
     mysql> use devInlineTxn;
     Reading table information for completion of table and column names
     You can turn off this feature to get a quicker startup with -A
     
     Database changed
     mysql> select * from ORDERS;
     +----------+-------------+-------------+------------+
     | order_id | order_total | customer_id | order_date |
     +----------+-------------+-------------+------------+
     |        1 | $120.98     | WM-198721   | 2022-02-26 |
     |        2 | $11.98      | WM-1100     | 2022-02-26 |
     |        3 | $41.98      | WM-4360     | 2022-02-26 |
     +----------+-------------+-------------+------------+
     3 rows in set (0.06 sec)
     
     mysql> 
     mysql> INSERT INTO ORDERS 
         ->    (order_total, customer_id, order_date)
         ->    VALUES
         ->    ("$1.98", "WM-020", NOW());
     ERROR 1290 (HY000): The MySQL server is running with the --read-only option 
                         so it cannot execute this statement
     mysql> 
     
     

     
     
   INSERT query execution throws error on replica instance as read only feature is only enabled.

Fail-over/clone DB Instance: Since fail-over DB instance is clone of primary instance(Instance ID = devinline-gcp-mysqll and Public IP address = 35.225.161.77) so by default when clone was created, fail-over instance created with SSL enabled.

What is replica promotion?

A couple of operations are only possible with read replicas: promoting and disabling replication.

• Promoting a read replica stops replication and converts the instance into a standalone primary instance with read and write capabilities. This can't be undone. 
• To avoid loss of data, before promoting the replica, you should verify that the replica has applied all transactions received from the primary.

-----======----------