Use case – managing replicas

Our database would not be considered managed unless we have figured out how to get replicas created and managed. Cloud SQL by default does not provide replicas, but we can create them. There are four types of replicas to choose from. Despite of all of this, the reason behind creating such replicas is to increase query capacity for read intensive databases:

Type 1: Read Replicas:

These are low cost low availability replicas of the master instance, which contains root password and changes made to the master. The changes are least likely to be real time. The replicas can also face disruptions due to server outage or sudden upgrade:

  • The configuration of the replica instance may be different from the master instance based on the choices we make and it is viable to make read replicas that have lower configurations if the application is not mission critical.
  • They cannot be backed up or restored.
  • They need to be promoted as master before we delete the master instance.
  • Master needs to have binary logging enabled in order to have a replica.
  • Replica cannot have replicas (What would be the point anyway!).
  • Steps to create a read replica:
    1. Check the status of the instance for binary logging:
gcloud sql instances describe bank-balance
    1. If binaryLogEnabled is false, enable it using the following command:
gcloud sql instances patch --enable-bin-log bank-balance
    1. Create the replica:
gcloud sql instances create bank-balance-replica-0 --master-instance-name=bank-balance

Type 2: Failover Replicas:

A second generation replica within a different zone is called a Failover Replica. This allows us the flexibility to treat production level instances and not-so-mission-critical instances differently:

  • In case of outage in master instance, Cloud SQL promptly directs to a failover replica, which would be safe since it would be in another region. The Cloud SQL proxy is failover aware so the application using it doesn’t need to update its configurations. We only need to restart the session.
  • The update window is smaller and having more than one failover replica set up with different update windows can provide complete failover immunity theoretically.
  • To set up failover replica:
    • We can create a master instance along with its failover replica:
gcloud sql instances create bank-balance --backup-start-time 00:00 --failover-replica-name bank-balance-fo --enable-bin-log
    • We can also master a failover separately since the only variations are different arguments of the gcloud command:
gcloud sql instances create bank-balance-fo --master-instance-name=bank-balance--replica-type=FAILOVER

Type 3: External Replicas:

These are external MySQL Read replica instances replicated from Cloud SQL instances. There are a few rules to be followed for external replicas:

  • Binary logging for master
  • Same or higher MySQL version of replica compared to master
  • Replicating to another Cloud platform's MySQL instance is not possible
  • To create an external replica:
    • Connect to your Cloud SQL instance using the Command Prompt:
gcloud sql connect bank-balance --user=root
    • Create a special user for replica:
CREATE USER 'bank-balance-replica-user'@'%' IDENTIFIED BY '********';
    • Provide replication privileges to the user:
GRANT REPLICATION SLAVE ON *.* TO 'bank-balance-replication-user'@'%';
    • Create an export dump file from the master instance for the replica. For this, we will create a Cloud Storage Bucket for the export and add replica's service account in the access control list of the bucket with Writer permission and finally we will export it:
gsutil mb -p loonycorn-project-01 -l asia gs://loonycorn-bucket-00
gcloud sql instances describe bank-balance
gsutil acl ch -u [email protected]:W gs://loonycorn-bucket-00
gcloud sql instances export bank-balance gs://loonycorn-bucket-00/sqldumpfile.gz --database holdersdump.sql
    • Go to the machine where you want to host the replica. It can even be a compute engine instance. Seed the export file we created earlier:
mysql --user=root --password < holdersdump.sql
    • Open the MySQL settings file typically named my.cnf:
      nano /etc/my.cnf
    • Add the following fields to it. Among these fields, server ID is a unique numeric value across all of the servers and replicas, which means no two replicas can have the same server ID:
[mysqld]
server-id=[SERVER_ID]
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
replicate-ignore-db=mysql
binlog-format=ROW
log_bin=mysql-bin
expire_logs_days=1
read_only=ON
Exit and re-enter the mysql process to make sure configurations are updated.
Enter the following command:
CHANGE MASTER TO MASTER_HOST='[IP ADDRESS OF MASTER]', MASTER_USER='bank-balance-replication-user',
MASTER_PASSWORD='********', MASTER_AUTO_POSITION=1;
    • Start the replication process with the following command:
START SLAVE;
    • You can also check the state of replica by entering the following command. Make sure it gives a result as Waiting for master to send event:
 SHOW SLAVE STATUSG;
  • Replica Management: As handy as replicas may be, they need to be managed. Sometimes we need to enable/disable them as per our requirements or we need to change their role as well!
    • Enabling Replicas: If a replication instance is disabled for a longer time interval, replicating updated master may take significant time. In which case, it makes sense to enable a new replication instance:
gcloud sql instances patch <<YOUR REPLICA>> --enable-database-replication
    • Disabling Replicas: This does NOT mean deletion of a replica instance. In other words, even if you don't replicate anything from master, the instance will contribute to the billing. This is only advisable for temporary use such as debugging:
gcloud sql instances patch  <<YOUR REPLICA>> --no-enable-database-replication
    • Promoting Replicas: A replica can be turned into a standalone instance, which means it will stop replicating the master, but the action is irreversible. Thus if you do it by mistake, you will have to delete it and create a new replica:
      gcloud sql instances promote-replica <<YOUR REPLICA>>
    • Maintaining Replicas: Finally, you can check the status of a replica or delete it altogether, which of course would be an irreversible action. For doing so, treat a replica instance just as any other one and use the following command:
      gcloud instance delete <<YOUR INSTANCE>>
      gcloud instance describe <<YOUR REPLICA>>

Type 4: External Masters:

External master is a MySQL master instance, which is not a part of Cloud SQL. This arrangement has multiple uses such as increasing the reliability of your database and thus increasing ACID parameters as well. On another note, sometimes the network is not strong enough to keep updating CLoud SQL, in which case having a local master is helpful. It is important to remember that external masters are only supported by 1st generation of MySQL instances which is not the preferred choice in the most cases:

  • Prerequisites: Before setting up an external master, you must have a working service account, billing, a cloud storage bucket, at least one replica of master instance, and binary logging set up:
  • Procedure: First of all, create a mysqldump file with GTID permissions and triggers turned off:
mysqldump --databases <<DATABASE>> -h <<INSTANCE IP>> -u <<USERNAME>> -p --skip-triggers --set-gtid-purged=OFF
--default-character-set=utf8 > [SQL_FILE].sql
  • Now create a CSV file from your local Cloud SQL server (external) with the following command:
mysql --host=<<IP>> --user=<<USER NAME>> --password <<DATABASE NAME>> -e " SELECT * FROM <<TABLE NAME>> INTO OUTFILE '<<FILENAME>>' CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' "
  • Now, provided that you already have an external SQL server running, create a CLoud SQL internal master with the credentials to be external master. Run the following command on the Cloud Shell command line:
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" 
--header 'Content-Type: application/json' 
--data '{"name": "<<CLOUD SQL MASTER INSTANCE>>",
"region": "<<REGION>>",
"databaseVersion": "DATABASE VERSION OF EXTERNAL MASTER",
"onPremisesConfiguration": {"hostPort": "<<IP>>"}}' -X POST 
https://www.googleapis.com/sql/v1beta4/projects/<<PROJECT ID> /instances
  • Now, the internal master is created and has also been provided the credentials of external master. Once it is fully initialized, create a replica with the user account information of external master and storage details of the dump you created with cloud storage bucket:
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" 
--header 'Content-Type: application/json' 
--data '{"replicaConfiguration":
{"mysqlReplicaConfiguration":
{"username": "[REPLICATION_USER]", "password": "<<REPLICA PASSWORD>>","dumpFilePath": "[BUCKET_LOCATION]" }},
"settings": {"tier": "[TIER]","activationPolicy": "ALWAYS"},
"databaseVersion": "[EXTERNAL_MASTER_DATABASE_VERSION]",
"masterInstanceName": "[INTERNAL_MASTER_INSTANCE_NAME]",
"name": "[REPLICA_NAME]", "region": "[REGION_NAME]"}' -X POST https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instancesas
  • High availability: Highly available instances are the ones created under second generation configuration and a failover replica in a different zone. A new instance can be created with high availability and existing instances can be converted:
    • New Instance: While creating the master instance make sure to also set up the failover replica and configure the root user for the master:
    gcloud sql instances create <<MASTER INSTANCE>> 
     --backup-start-time 00:00 
     --failover-replica-name <<REPLICA INSTANCE>> 
     --tier <<TIER TYPE>> --enable-bin-log
    • For root user configuration:
    gcloud sql users set-password root % 
    --instance <<MASTER>> --password ********
    • Existing Instance: High availability is an option only for master instances, so in case of a replica it wouldn't work. After determining your master instance, enable automatic backup and binary logging on it:
gcloud sql instances patch <<MASTER>> --backup-start-time 00:00
gcloud sql instances patch --enable-bin-log <<MASTER INSTANCE>>
    • Finally, create a failover replica to turn your existing instance into a high availability instance:
    gcloud sql instances create <<REPLICA INSTANCE>> 
    --master-instance-name=<<MASTER INSTANCE>> 
    --replica-type=FAILOVER
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset