aamir814

Multi-AZ DB Cluster Binlog Persistence Solution

Problem Statement

Issue: AWS RDS Multi-AZ DB Clusters lose binlog files during maintenance operations (resizes, failovers, patches), causing CDC tools (DMS, Fivetran) to fail with Error 1236 "Could not find first log file name in binary log index file".

Root Cause: Multi-AZ DB Clusters store newest binlog files in ephemeral local storage that is wiped during host replacements. This is documented AWS behavior per: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-configuring.html

"If binary logs on the DB cluster exceed half of the total local storage space, Amazon RDS automatically moves stale logs to the EBS volume. However, the newest logs remain in local storage, so they're subject to be lost if there's a failure that requires a host replacement, or if you scale the database up or down."

aamir814

Introduction

The following steps show how to upgrade Cloud SQL PostgreSQL to a newer version using Google Cloud DMS. A few interesting observations that were made are:

• You can write to the destination database, but the cloudsqlexternalsync user owns all objects. You must create users/roles and manually run grant statements to match the source.
• During the promotion, the target database restarts.
• The drop/create/grant statements ran during promotion [1] are here.
• This screenshot shows grants after promotion [2]. They don't match the source instance. Here is a good blog post that talks about this [3].
• In optimal settings, DMS creates two subscriptions per database (max of 10 subscriptions). The more subscriptions you have, the longer it can take to clean up during the promotion.
• This screenshot [4] shows that I was able to insert data into the destination database after applying appropriate grants while the migration job was in the CDC phase.

[1]

aamir814

#!/usr/bin/env bash

#####################################################################
# Purpose: 
#   The following steps show how to use the new PostgreSQL parameter
# sync_replication_slot introduced in version 17 to keep logical 
# replication running during a failover or major upgrade.
#
#   The setup includes three RDS PostgreSQL instances: Primary (PRI), 
# Read Replica (RR), and Logical Replica (LR). After successfully 

aamir814

#!/usr/bin/env bash

#####################################################################
# Purpose: Following steps show how to connect datastream to a Cloud
#          SQL instance with private IP. These steps can be used to
#          quickly demo data transfer from Cloud SQL to BigQuery.
#
# Assumption:
#         * Google cloud project exists with network setup as desired.
#         * all the necessary privileges have been granted to create

aamir814

#####################################################################
# Purpose: Following is an example on how to use Google Cloud Monitoring 
#          Metric Service Client using timeseries to get CPU metric
#          for specified Cloud SQL instances in a project with aggregated
#          mean value over the last five minuets.
#          
#          The Service account must have following permissions:
#          * roles/monitoring.viewer
#          * roles/cloudsql.viewer
#          * Service Account Token Creator

aamir814

#!/usr/bin/env bash

#####################################################################
# Purpose: Following is an example on how to restore AlloyDB Cluster 
#          cross project
#          For CLOUD SQL backup/restore cross project, please see:
#          https://gist.github.com/anorth848/724808332e4d3e699eb9a05411e59e5e 
#
# Assumption:
#         Source and target Google Projects exist

aamir814

#!/usr/bin/env bash

#####################################################################
# Purpose: Following are commands needed to install Percona tool kit on
#   Amazon Linux 2023 AMI. It also shows how to export data from MySQL table
#   into CSV format.
#
# Assumption: You have EC2 instance (Amazon Linux 2023 AMI) up and 
#     running and you can ssh into it.
#     You have RDS MySQL (or Aurora MySQL) up and running

aamir814

#!/usr/bin/env bash

#####################################################################
# Purpose: Following is an example on how to connect to Cloud SQL instance
#          from different VPC using Private Service Connect. I followed
#          the instructions mentioned in Google document 
#          (1st URL in refrence section). Created two VPCs
#
# REFERENCES
# - https://cloud.google.com/sql/docs/postgres/configure-private-service-connect

aamir814

MySQL Multi-source Replication POC

• Overview
• Deploy MySQL 8 (Codelab) VM - T1
  • Make sure master_info_repository and relay_log_info_repository are set to TABLE
  • Turn ON GTID_MODE
  • Change GTID_MODE to ON
  • Update my.cnf file
  • Restart mysql as root user

• Verify GTID_MODE is ON