<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Saurabh Gupta on Medium]]></title>
        <description><![CDATA[Stories by Saurabh Gupta on Medium]]></description>
        <link>https://medium.com/@saurabh8774?source=rss-2a8e05ac1cf7------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*KNuvUSbsIIAY_xKAO1NPQQ.png</url>
            <title>Stories by Saurabh Gupta on Medium</title>
            <link>https://medium.com/@saurabh8774?source=rss-2a8e05ac1cf7------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 09:27:02 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@saurabh8774/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Zero-Code CDC: Replicating MongoDB to BigQuery Without the Bloat]]></title>
            <link>https://saurabh8774.medium.com/zero-code-cdc-replicating-mongodb-to-bigquery-without-the-bloat-fb4989c4092f?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/fb4989c4092f</guid>
            <category><![CDATA[datastream]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[cdc]]></category>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Thu, 16 Jul 2026 11:09:07 GMT</pubDate>
            <atom:updated>2026-07-16T11:10:30.411Z</atom:updated>
            <content:encoded><![CDATA[<p>A step-by-step guide to building a zero-code MongoDB to BigQuery pipeline that filters massive Base64 payloads in-flight.</p><p>This guide outlines the step-by-step procedure to establish a zero-code, real-time Change Data Capture (CDC) replication pipeline from a self-managed MongoDB database (running on a Google Compute Engine VM) to Google BigQuery using Google Cloud Datastream. To maximize cost efficiency for analytics workloads, this architecture excludes heavy binary payloads (such as Base64 image strings) at the database boundary during transit. This reduces data replication volume and saves substantial pipeline and storage costs.</p><h3>Architecture Overview</h3><ul><li><strong>Source Database:</strong> MongoDB on Compute Engine (asia-south1)</li><li><strong>Transport Mechanism:</strong> CDC via Oplog over Port 27017, Authenticated &amp; TLS Securable</li><li><strong>Replication Engine:</strong> Google Cloud Datastream (asia-south1) automatically merges Upserts/Deletes and drops the base64 field (kyc_documents.selfie_verification.base64_data payload) in-flight.</li><li><strong>Target Destination:</strong> Google BigQuery</li></ul><h3>Prerequisites &amp; Project Settings</h3><ul><li><strong>Region:</strong> asia-southX</li><li><strong>Dataset ID:</strong> analytics</li><li><strong>Target Table</strong>: testchangestreams_employee</li><li><strong>MongoDB Version:</strong> 6.0 (running on Ubuntu 22.04 LTS)</li><li><strong>MongoDB Connection Port:</strong> 27017</li></ul><h3>Phase 1: Provisioning the Compute Engine VM</h3><p>We begin by spinning up a lightweight Ubuntu virtual machine inside your GCP project to host our source MongoDB instance, and configure the firewall to securely allow Datastream’s inbound connections.</p><h3>Step 1.1: Create the Startup Script (startup.sh)</h3><p>In your local environment or Google Cloud Shell, write the MongoDB 6.0 installation script to a file named startup.sh to bypass multi-line CLI parsing limitations:</p><pre>cat &lt;&lt; &#39;EOF&#39; &gt; startup.sh<br>#!/bin/bash<br># Install MongoDB 6.0<br>curl -fsSL https://www.mongodb.org/static/pgp/server-6.0.asc | gpg - dearmor -o /usr/share/keyrings/mongodb-se echo &quot;deb [ arch=amd64,arm64 signed-by=/usr/share/keyrings/mongodb-server-6.0.gpg ] https://repo.mongodb.org/a apt-get update<br>apt-get install -y mongodb-org<br>systemctl start mongod<br>systemctl enable mongod<br>EOF</pre><h3>Step 1.2: Spin Up the GCE VM</h3><p>Execute the following command in Google Cloud Shell to provision the database VM, feeding in our startup script:</p><pre>gcloud compute instances create mongodb-demo-vm \<br> - zone=asia-south1-a \<br> - machine-type=e2-medium \<br> - image-family=ubuntu-2204-lts \<br> - image-project=ubuntu-os-cloud \<br> - tags=mongodb-port \<br> - metadata-from-file=startup-script=startup.sh</pre><h3>Step 1.3: Configure the VPC Firewall</h3><p>Open standard port 27017 specifically on the target instance to allow external incoming traffic from Datastream:</p><pre>gcloud compute firewall-rules create allow-mongodb-ingress \<br> - allow=tcp:27017 \<br> - target-tags=mongodb-port \<br> - direction=INGRESS</pre><p><strong>Note:</strong> For production environments, restrict the source IP ranges in this rule strictly to Datastream’s regional public IP blocks.</p><h3>Phase 2: Database Hardening &amp; Replica Set Configuration</h3><p>Datastream is a Change Data Capture (CDC) tool that streams real-time updates by tailing MongoDB’s Oplog (Operations Log). MongoDB only writes to the Oplog if it is running as a Replica Set. Therefore, we will configure a single-node replica set and enable database security.</p><h3>Step 2.1: Establish a Shared Replica Set KeyFile</h3><p>When authorization is active on a MongoDB replica set, nodes require a shared keyFile to authenticate with one another. Even on a single-node setup, MongoDB’s parser mandates this file.</p><p>SSH into your newly created VM:</p><pre>gcloud compute ssh mongodb-demo-vm - zone=asia-south1-a</pre><p>Generate a secure, random key file and lock down its Linux file permissions:</p><pre>sudo mkdir -p /var/lib/mongodb/security <br>openssl rand -base64 756 | sudo tee /var/lib/mongodb/security/mongo-keyfile &gt; /dev/null <br>sudo chown -R mongodb:mongodb /var/lib/mongodb/security <br>sudo chmod 400 /var/lib/mongodb/security/mongo-keyfile</pre><h3>Step 2.2: Create the Administrative User</h3><p>Launch the Mongo Shell to create an admin user on the default local loopback interface before we lock down database permissions: mongosh</p><p>Switch to the admin database and run the creation script in mongosh:</p><pre>use admin <br>db.createUser({ <br> user: &quot;user&quot;, <br> pwd: &quot;password&quot;, <br> roles: [ { role: &quot;root&quot;, db: &quot;admin&quot; } ] <br>})</pre><p>Verify that the output returns { ok: 1 } and type exit to close the shell.</p><h3>Step 2.3: Standardize the Configuration File (/etc/mongod.conf)</h3><p>MongoDB configuration files use strict YAML formatting (requiring exactly 2 spaces for nested elements). Run this command in your VM terminal to overwrite the config file with perfect formatting, binding MongoDB to all networks (0.0.0.0), pointing to our keyFile, and declaring our replica set name (rs0):</p><pre>sudo tee /etc/mongod.conf &lt;&lt; &#39;EOF&#39; <br># mongod.conf <br>storage: <br> dbPath: /var/lib/mongodb <br>systemLog: <br> destination: file <br> logAppend: true <br> path: /var/log/mongodb/mongod.log <br>net: <br> port: 27017 <br> bindIp: 0.0.0.0 <br>processManagement: <br> timeZoneInfo: /usr/share/zoneinfo <br>security: <br> authorization: &quot;enabled&quot; <br> keyFile: /var/lib/mongodb/security/mongo-keyfile <br>replication: <br> replSetName: &quot;rs0&quot; <br>EOF</pre><h3>Step 2.4: Restart the Database and Initialize the Replica Set</h3><p>Apply the configuration changes by restarting the service and running initialization:</p><pre>sudo systemctl restart mongod <br>sudo systemctl status mongod</pre><p>Verify that it shows ‘active (running)’ in green</p><p>Initialize the single-node replica set with the created admin user:</p><pre>mongosh -u &quot;user&quot; -p &quot;password&quot; - authenticationDatabase &quot;admin&quot; - eval &quot;rs.initiate()&quot;</pre><p>This will output confirmation that rs0 has been successfully initiated.</p><h3>Phase 3: Resolving Split-Horizon DNS / Replica Set Networking</h3><p>By default, when a replica set is initialized via rs.initiate(), MongoDB records the machine’s local OS hostname (e.g., mongodb-demo-vm:27017) in its internal configuration. When Datastream makes its initial handshake using the public IP , MongoDB instructs the client: “I am a replica set! Now connect directly to the primary node at mongodb-demo-vm:27017.” Because Datastream is on an external network, it cannot resolve that internal hostname, producing an UnknownHostException timeout. We must reconfigure MongoDB to advertise its public IP address instead.</p><h3>Step 3.1: Bind the Public IP Locally on the VM</h3><p>To prevent the OS from rejecting connections destined for its external NAT address, run this command in your VM terminal to map the public IP to your local loopback interface:</p><pre>sudo ip addr add &lt;public-ip&gt;/32 dev lo</pre><p>Step 3.2: Reconfigure the Replica Set Members in MongoDB</p><p>Launch the shell and update the replica set configuration to advertise the public IP address:</p><p>Open the Mongo Shell:</p><pre>mongosh -u &quot;user&quot; -p &quot;password&quot; - authenticationDatabase &quot;admin&quot;</pre><p>Run the reconfiguration script:</p><pre>cfg = rs.conf() <br>cfg.members[0].host = &quot;&lt;public-ip&gt;:27017&quot; <br>rs.reconfig(cfg)</pre><p>Verify that the shell prints { ok: 1 } and exit.</p><h3>Phase 4: Establishing Datastream Connection Profiles</h3><p>We now establish our Source and Destination profiles in Datastream.</p><h3>Step 4.1: Source Profile (mongoonvm)</h3><p>Create the Source Connection Profile inside the Google Cloud Console or via gcloud.</p><p><strong>If using the Console UI:</strong></p><ol><li>Navigate to Datastream &gt; Connection profiles and click Create Profile.</li><li>Select MongoDB as your database type.</li><li>Use the following configuration:</li></ol><ul><li><strong>Connection profile ID:</strong> mongoonvm</li><li><strong>Hostname:</strong> &lt;public-ip&gt;</li><li><strong>Port:</strong> 27017</li><li><strong>Username:</strong> user</li><li><strong>Password:</strong> password</li><li><strong>Replica Set Name:</strong> rs0 (Mandatory!)</li></ul><ol><li>Under Connectivity method, select IP allowlist (simply click continue).</li><li>Under Encryption, select None (for sandbox verification purposes).</li><li>Click Run Test to verify connectivity, then click Create.</li></ol><h3>Step 4.2: Destination Profile (bigquery-destination)</h3><p>Run this command in your Google Cloud Shell terminal to instantly provision the target BigQuery destination profile:</p><pre>gcloud datastream connection-profiles create bigquery-destination \<br> - location=asia-south1 \<br> - display-name=&quot;BigQuery Destination&quot; \<br> - type=bigquery</pre><h3>Phase 5: CLI-Based Stream Deployment for Nested Exclusions</h3><p>Because MongoDB is a schema-less NoSQL database, the Google Cloud Console GUI only displays and allows selections for top-level keys. To target deeply nested attributes — specifically dropping the massive base64_data block while preserving the outer selfie_verification metrics — we must bypass the browser UI and deploy our stream configuration via the Google Cloud CLI utilizing nested dot-notation paths.</p><h3>Step 5.1: Create the Source Configuration File (source_config.json)</h3><p>In Cloud Shell, write the following configuration file. This explicitly targets the nested base64_data field within our collection schema:</p><pre>cat &lt;&lt; &#39;EOF&#39; &gt; source_config.json <br>{ <br> &quot;excludeObjects&quot;: { <br> &quot;databases&quot;: [ <br> { <br> &quot;database&quot;: &quot;testchangestreams&quot;, <br> &quot;collections&quot;: [ <br> { <br> &quot;collection&quot;: &quot;employee&quot;, <br> &quot;fields&quot;: [ <br> { <br> &quot;field&quot;: &quot;kyc_documents.selfie_verification.base64_data&quot; <br> } <br> ] <br> } <br> ] <br> } <br> ] <br> } <br>} <br>EOF</pre><h3>Step 5.2: Create the Destination Configuration File (dest_config.json)</h3><p>In Cloud Shell, configure the target BigQuery dataset mapping (including your Project ID prefix):</p><pre>cat &lt;&lt; &#39;EOF&#39; &gt; dest_config.json <br>{ <br> &quot;singleTargetDataset&quot;: { <br> &quot;datasetId&quot;: &quot;my-project-XXXXX–XXXXX:analytics&quot; <br> } <br>} <br>EOF</pre><h3>Step 5.3: Provision the Datastream Replication Stream</h3><p>Run this command in Cloud Shell to deploy the stream instance:</p><pre>gcloud datastream streams create los-sync-nested-exclude \<br> - location=asia-south1 \<br> - display-name=&quot;los-sync-nested-exclude&quot; \ - source=&quot;projects/my-project-XXXXX–XXXXX/locations/asia-southX/connectionProfiles/mongoonvm&quot; \<br> - mongodb-source-config=source_config.json \ - destination=&quot;projects/my-project-XXXXX–XXXXX/locations/asia-southX/connectionProfiles/bigquery-destination&quot; \<br> - bigquery-destination-config=dest_config.json \<br> - backfill-all</pre><h3>Phase 6: Live Validation Test and Side-by-Side Verification</h3><p>With the pipeline actively running, we will write a rich nested document to MongoDB containing a heavy Base64 image payload, trigger an update to emit a Change Data Capture (CDC) event, and verify the resulting table structure in BigQuery.</p><h3>Step 6.1: Write the Source Document to MongoDB</h3><p>In your VM SSH terminal, paste and execute this command to insert our initial mock loan application:</p><pre>mongosh -u &quot;user&quot; -p &quot;password&quot; - authenticationDatabase &quot;admin&quot; - eval &quot; <br>db = db.getSiblingDB(&#39;testchangestreams&#39;); <br>db.employee.insertOne({ <br> loan_id: &#39;LN-BNPL-99882731&#39;, <br> customer_id: &#39;CUST-5510294&#39;, <br> loan_status: &#39;APPROVED&#39;, <br> requested_amount: 75000, <br> tenure_months: 12, <br> created_at: &#39;2026–07–15T08:30:00Z&#39;, <br> updated_at: &#39;2026–07–15T13:10:00Z&#39;, <br> applicant_details: { <br> first_name: &#39;John&#39;, <br> last_name: &#39;Doe&#39;, <br> email: &#39;john.doe@gmail.com&#39;, <br> phone: &#39;+919999999999&#39;, <br> monthly_income: 125000, <br> cibil_score: 765 <br> }, <br> kyc_documents: { <br> pan_card_number: &#39;ABCDE1234F&#39;, <br> aadhaar_last_four: &#39;5678&#39;, <br> aadhaar_front_scan: { <br> file_name: &#39;aadhaar_front.png&#39;, <br> mime_type: &#39;image/png&#39; <br> }, <br> selfie_verification: { <br> file_name: &#39;customer_selfie.jpg&#39;, <br> file_size_bytes: 2621440, <br> mime_type: &#39;image/jpeg&#39;, <br> match_confidence_score: 0.98, <br> verified_at: &#39;2026–07–15T08:32:00Z&#39;, <br> base64_data: &#39;/9j/4AAQSkZJRgABAQEASABIAAD/2wBDAAMCAgMCAgMDAwMEAwMEBQgFBQQEBQoHBwYIDAoMDAsKCwsNDhIQDQ4RDg&#39;<br> } <br> } <br>}); <br>print(&#39;✅ Test document inserted into MongoDB!&#39;); <br>&quot;</pre><h3>Step 6.2: Start the Stream</h3><p>Once the stream’s background deployment operations succeed, transition the stream’s state to RUNNING:</p><pre>gcloud datastream streams update los-sync-nested-exclude \<br> - location=asia-southX \<br> - state=RUNNING</pre><h3>Step 6.3: Emulate status progression to trigger CDC Update</h3><p>Run the status transition statement in your VM SSH terminal:</p><pre>mongosh -u &quot;user&quot; -p &quot;password&quot; - authenticationDatabase &quot;admin&quot; - eval &quot; <br>db = db.getSiblingDB(&#39;testchangestreams&#39;); <br>db.employee.updateOne( <br> { loan_id: &#39;LN-BNPL-99882731&#39; }, <br> { \$set: { loan_status: &#39;COMPLETED&#39;, updated_at: &#39;2026–07–15T18:10:00Z&#39; } } <br>); <br>print(&#39;✅ Status updated! Replicating change stream to BigQuery…&#39;); <br>&quot;</pre><h3>Step 6.4: Query BigQuery to Prove Selective Extraction</h3><p>Open the Google BigQuery Console and execute the SQL query to verify that the target metadata has been fully preserved while the heavy binary was stripped from the JSON payload in transit:</p><pre>SELECT * FROM `my-project-XXXXX–XXX.analytics.testchangestreams_employee` LIMIT 10</pre><pre>[{<br> &quot;_id&quot;: &quot;{\&quot;$oid\&quot;: \&quot;6a57cca9fcb5811d3bf8635f\&quot;}&quot;,<br> &quot;data&quot;: &quot;{\&quot;_id\&quot;:{\&quot;$oid\&quot;:\&quot;6a57cca9fcb5811d3bf8635f\&quot;},\&quot;applicant_details\&quot;:{\&quot;cibil_score\&quot;:765,\&quot;email\&quot;:\&quot;john.doe@gmail.com\&quot;,\&quot;first_name\&quot;:\&quot;John\&quot;,\&quot;last_name\&quot;:\&quot;doe\&quot;,\&quot;monthly_income\&quot;:125000,\&quot;phone\&quot;:\&quot;+919999999999\&quot;},\&quot;created_at\&quot;:\&quot;2026–07–15T08:30:00Z\&quot;,\&quot;customer_id\&quot;:\&quot;CUST-5510294\&quot;,\&quot;kyc_documents\&quot;:{\&quot;aadhaar_front_scan\&quot;:{\&quot;file_name\&quot;:\&quot;aadhaar_front.png\&quot;,\&quot;mime_type\&quot;:\&quot;image/png\&quot;},\&quot;aadhaar_last_four\&quot;:\&quot;5678\&quot;,\&quot;pan_card_number\&quot;:\&quot;ABCDE1234F\&quot;,\&quot;selfie_verification\&quot;:{\&quot;file_name\&quot;:\&quot;customer_selfie.jpg\&quot;,\&quot;file_size_bytes\&quot;:2621440,\&quot;match_confidence_score\&quot;:0.98,\&quot;mime_type\&quot;:\&quot;image/jpeg\&quot;,\&quot;verified_at\&quot;:\&quot;2026–07–15T08:32:00Z\&quot;}},\&quot;loan_id\&quot;:\&quot;LN-BNPL-99882731\&quot;,\&quot;loan_status\&quot;:\&quot;COMPLETED\&quot;,\&quot;requested_amount\&quot;:75000,\&quot;tenure_months\&quot;:12,\&quot;updated_at\&quot;:\&quot;2026–07–15T18:10:00Z\&quot;}&quot;,<br> &quot;datastream_metadata&quot;: {<br> &quot;uuid&quot;: &quot;e7339606–7231–47c8-abf9-fcc23b5fd0af&quot;,<br> &quot;source_timestamp&quot;: &quot;1784141822000&quot;<br> }<br>}]</pre><p><em>Till next time , Happy Learning !</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fb4989c4092f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Scaling the Unscalable: A “Replica Pivot” Strategy for Online Schema Changes on multi-TB MySQL…]]></title>
            <link>https://medium.com/google-cloud/scaling-the-unscalable-a-replica-pivot-strategy-for-online-schema-changes-on-multi-tb-mysql-dff6ee96f9eb?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/dff6ee96f9eb</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[schemachange]]></category>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Mon, 16 Mar 2026 11:30:25 GMT</pubDate>
            <atom:updated>2026-03-25T01:15:15.204Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Scaling the Unscalable: A “Replica Pivot” Strategy for Online Schema Changes on multi-TB MySQL Instances</strong></h3><p>In the world of high-scale database management, some requirements keep DBAs up at night. These are the “structural emergencies” — the realization that your most critical table, the one handling thousands of transactions per second, needs a fundamental architectural change.</p><p>Whether you’ve finally hit the 2.14 billion row limit of an INT primary key and need to move to BIGINT, or you’ve realized that your XTB “orders” table desperately needs partitioning to stay performant, you face the same wall: <strong>The Scale.</strong></p><p>When you are dealing with multi-terabyte tables in a MySQL, standard operations aren’t just slow; they are service-ending events. This post details the “Replica Pivot” strategy — a battle-tested approach to performing massive schema changes without taking your business offline.</p><h3>The High-Stakes Scenarios</h3><p>These are the two most common “nightmare” scenarios where traditional DDL fails:</p><ol><li><strong>The INT Overflow:</strong> Your primary key is an INT. You’ve hit 2 billion rows. If you don’t convert to BIGINT immediately, your database will stop accepting new writes.</li><li><strong>The Partitioning Pivot:</strong> A massive legacy table is becoming unmanageable. To improve maintenance windows and query performance, you need to implement Range or Hash partitioning on a table that is already several terabytes in size.</li></ol><p>In both cases, a standard ALTER TABLE will lock your production environment for days.</p><h3>The Live Production Dilemma: Why isn’t this easy?</h3><p>Performing schema changes on a live, multi-terabyte production environment is fraught with complexity. When you run a heavy operation on a “live” master, you aren’t just changing a definition; you are fighting for resources against your own users.</p><h3>The Complexity Factors</h3><ol><li><strong>Metadata Locking:</strong> Even “Online DDL” in MySQL requires brief metadata locks at the start and end of the operation. In a high-TPS environment, waiting for these locks can cause a “pile-up” of application connections, leading to a site-wide outage.</li><li><strong>I/O and CPU Saturation:</strong> Rewriting a XTB table is an I/O-intensive nightmare. It consumes massive amounts of IOPS and throughput, which can starve your production read/write queries, leading to increased latency.</li><li><strong>The “Copy” Problem:</strong> Most schema changes create a hidden temporary table. This means for a XTB table, you suddenly need another XTB of free space instantly, and your database has to manage double the writes.</li></ol><h3>Common Approaches &amp; Their Impact</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/625/1*VKpmEpr3GkW5KKBSmLYL7A.png" /></figure><h3>The Strategy: The “Replica Pivot”</h3><p>The core philosophy here is to perform the heavy, blocking I/O operations on a standalone instance, then use MySQL’s native replication to bridge the gap back to production.</p><p>Lets use this strategy to address the INT overflow problem.</p><h3>Phase 1: The Transformation (Offline)</h3><ol><li><strong>Clone the Environment:</strong> Create a MySQL Read Replica in the same zone/region with the identical configuration as your Master. Let’s call this <strong>Replica-A</strong>.</li><li><strong>Break the Chain:</strong> Promote <strong>Replica-A</strong> to a standalone instance. It is now a point-in-time snapshot of your production data, but it is no longer receiving live updates from Master.</li><li><strong>Perform the Surgery:</strong> Run your massive DDL statement on the promoted instance.</li></ol><ul><li><em>For Datatype Change:</em> ALTER TABLE orders MODIFY id BIGINT;</li><li><em>For Partitioning:</em> ALTER TABLE orders PARTITION BY RANGE…;</li></ul><p><em>Note:</em> This will take hours or even days. Since this instance is isolated, your production Master remains fast and healthy.</p><ol><li><strong>Configure Type Conversion Flags:</strong> This is the most critical technical step. To ensure that the promoted instance can eventually talk to the original Master (which still has the old schema), you must set specific flags on the promoted instance:</li></ol><ul><li>slave_type_conversions = ALL_LOSSY</li><li>slave_type_conversions = ALL_NON_LOSSY</li></ul><p><em>Why?</em> This tells MySQL to allow replication even if the datatypes or structures don’t match perfectly, as long as the data is compatible.</p><h3>Phase 2: The Re-Sync (Online)</h3><ol><li><strong>Re-attach to Master:</strong> Use the external master configuration or MySQL’s replication tools to point your modified instance back to the original Master.</li><li><strong>Catching Up:</strong> The modified instance will begin reading the Binary Logs from the Master. It will replay every transaction that happened while the “surgery” was taking place.</li><li><strong>Monitor Lag:</strong> Wait for the replication lag to reach zero. Because the schema change is already done, the replica is simply inserting new data into your new BIGINT or Partitioned table structure.</li></ol><p>Note: For detailed steps, you can refer <a href="https://medium.com/google-cloud/hybrid-cloud-replicating-to-cloudsql-mysql-from-an-external-server-12ffe5235f5c">this</a> blog</p><h3>Phase 3: The Precision Cutover</h3><p>Once the lag is zero, your modified instance is essentially a “Future Version” of your production database.</p><ol><li><strong>Validation:</strong> Perform functional testing on the replica. Ensure the application can read the new BIGINT range or query the partitions correctly.</li><li><strong>The Maintenance Window: </strong>Stop traffic to the original Master. Wait for the final few transactions to sync (lag = 0).</li><li>Remove *LOSSY* flags and edit database parameters like original source.</li><li><strong>The Promotion:</strong> Promote the replica to be the new Master.</li><li><strong>DNS Update:</strong> Change your application’s database endpoint (or update your Load Balancer) to point to the new Master.</li></ol><h3>Summary of Benefits</h3><p>By using the <strong>Replica Pivot</strong>, you shift 99% of the performance degradation and locking issues to an isolated instance. The only downtime your users experience is the few minutes it takes to swap the connection strings and promote the instance.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dff6ee96f9eb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/scaling-the-unscalable-a-replica-pivot-strategy-for-online-schema-changes-on-multi-tb-mysql-dff6ee96f9eb">Scaling the Unscalable: A “Replica Pivot” Strategy for Online Schema Changes on multi-TB MySQL…</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Upgrade Cloud SQL MySQL with Minimal Downtime: A Read Replica Strategy]]></title>
            <link>https://saurabh8774.medium.com/upgrade-cloud-sql-mysql-with-minimal-downtime-a-read-replica-strategy-d4f1505adbab?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/d4f1505adbab</guid>
            <category><![CDATA[cloud-sql]]></category>
            <category><![CDATA[read-replica]]></category>
            <category><![CDATA[mysql-upgrade]]></category>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Tue, 22 Apr 2025 11:05:44 GMT</pubDate>
            <atom:updated>2025-04-22T11:05:44.330Z</atom:updated>
            <content:encoded><![CDATA[<p>Tired of long maintenance windows for MySQL version upgrades? Learn our practical, proven method that leverages read replicas to shift the heavy lifting and drastically shrink downtime to just the final switch.</p><h3>Introduction:</h3><h4><em>The Upgrade Headache</em></h4><p>Upgrading an existing Cloud SQL MySQL instance version is a critical activity for security patching, performance improvements, and accessing new features. However, these upgrades typically require downtime, which can significantly impact your application’s availability and lead to stressful maintenance windows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OYy1SlYL5IQs8I3L_7v80g.png" /></figure><p><strong>**Note:**</strong> This blog post outlines an approach for Cloud SQL MySQL upgrade with minimal downtime. It does not cover the core MySQL upgrade steps and potential issues encountered during the upgrade process. For detailed information on MySQL upgrades, please refer to this <a href="https://cloud.google.com/sql/docs/mysql/upgrade-major-db-version-inplace">link</a>.</p><h3>Solution:</h3><blockquote><em>Leveraging Read Replicas for Minimal Downtime Upgrades</em></blockquote><p>To drastically reduce downtime to mere seconds or minutes (depending on application cutover), I propose an alternative strategy: by creating a Cloud SQL MySQL Read Replica, promote it and upgrade to a higher version and attach is back as a replica to the old master. This approach will drastically reduces the overall downtime time to few seconds or few minutes depends on the application cutover time.</p><h3>Steps:</h3><blockquote><strong>Provision a Cloud SQL Instance:</strong> Set up your primary Cloud SQL instance with data. [Name : testupgrade] [Version 5.7].Ensure binary logging is enabled on your source instance, which is required for replication. Cloud SQL typically handles this transparently, but it’s good to verify.</blockquote><blockquote><strong>Establish a Read Replica:</strong> Add a read replica to your newly created Cloud SQL instance. [Name : testupgrade-replica] [Version 5.7]</blockquote><blockquote><strong>Verify Replication Status (Zero Lag):</strong> Ensure the read replica is synchronized with the master instance and that the replication lag is zero.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/771/1*4fEokGaHTClPZ8xDEO-yxw.png" /></figure><pre>You can typically check replication status via the Google Cloud Console <br>monitoring interface or by connecting to the replica instance with the MySQL <br>client and running `SHOW SLAVE STATUS;`. Look for `Seconds_Behind_Master` <br>to be 0 (or very close to it) and confirm `Slave_IO_Running: Yes` and <br>`Slave_SQL_Running: Yes`.</pre><blockquote><strong>Record Master GTID:</strong> Note down the Global Transaction Identifier (GTID) of the master instance [testupgrade] for potential future reference.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ass63ATQNHzbfO8Z" /></figure><pre>select @@global.gtid_executed</pre><blockquote><strong>Initiate Replica Promotion:</strong> Promote the read replica [testupgrade-replica] to become the new primary instance.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*p7TOIO-Iju88_fpP" /></figure><blockquote><strong>Insert Records into the old Master (Intentional Divergence):</strong> Insert few records in a table to the <em>old</em> master instance [testupgrade]. <em>(These records are expected to sync later once the old master is re-attached as a replica.)</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*eh0sVZQTn9WY2WqEQ3Ssfg.png" /></figure><blockquote><strong>Perform In-Place Upgrade of Promoted Replica:</strong> Upgrade the newly promoted Cloud SQL instance [testupgrade-replica] to MySQL 8.0 using the instance editing feature.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*cejTS5HAFTo3sJdF" /></figure><blockquote><strong>Conduct Functional Testing:</strong> Verify the core functionality of your application against the upgraded Cloud SQL instance [testupgrade-replica].</blockquote><blockquote><strong>Execute Performance Testing:</strong> Assess the performance of the upgraded Cloud SQL instance [testupgrade-replica] under your typical workload.</blockquote><p>With the upgrade complete and functional and performance tests passing, proceed with the following steps to configure the upgraded MySQL 8.0 instance as a replica of the existing MySQL 5.7 master.</p><blockquote><strong>Create a Source Representation Instance:</strong> The source representation instance is a Cloud SQL instance that represents the source database server to the Cloud SQL replica. [Name : sourceinstance-testupgrade]</blockquote><pre>gcloud auth login <br>ACCESS_TOKEN=&quot;$(gcloud auth print-access-token)&quot; <br>curl --header &quot;Authorization: Bearer ${ACCESS_TOKEN}&quot; \<br>--header &#39;Content-Type: application/json&#39; \<br> --data @/home/source.json \<br>-X POST \<br>https://sqladmin.googleapis.com/sql/v1beta4/projects/&lt;myprojectid/instances</pre><pre>vi source.json<br>    {<br>      &quot;name&quot;: &quot;sourceinstance-testupgrade&quot;,<br>      &quot;region&quot;: &quot;us-central1&quot;,<br>      &quot;databaseVersion&quot;: &quot;MYSQL_8_0&quot;,<br>      &quot;onPremisesConfiguration&quot;: {<br>        &quot;hostPort&quot;: &quot;&lt;ipofmaster&gt;:3306&quot;,<br>        &quot;username&quot;: &quot;username&quot;,<br>        &quot;password&quot;: &quot;password&quot;<br>      }<br>    }</pre><blockquote><strong>Demote and Re-attach Replica to Source Representation:</strong> Demote the upgraded instance [testupgrade-replica]</blockquote><pre>  gcloud auth login<br>  ACCESS_TOKEN=&quot;$(gcloud auth print-access-token)&quot;<br>  curl --header &quot;Authorization: Bearer ${ACCESS_TOKEN}&quot; \<br>    --header &#39;Content-Type: application/json&#39; \<br>    --data @/home/demote.json \<br>    -X POST \<br>  https://sqladmin.googleapis.com/sql/v1beta4/projects/&lt;myprojectid&gt;/instances/&lt;replicainstancename&gt;/demoteMaster</pre><pre>vi demote.json<br> {<br>    &quot;demoteMasterContext&quot;: {<br>      &quot;masterInstanceName&quot;: &quot;sourceinstance-testupgrade&quot;,<br>      &quot;skipReplicationSetup&quot;: true<br>      }<br> }</pre><blockquote><strong>Switch Master and Await Replica Catch-Up:</strong> Reconfigure the demoted Cloud SQL replica [testupgrade-replica] to the old Cloud SQL master [testupgrade] and allow the replication to catch up.</blockquote><pre>call mysql.resetMaster();<br>call mysql.skipTransactionWithGtid(‘GTID-noted-in-step4’);<br>call mysql.setupExternalSourceAutoPosition(&#39;&lt;ipofmaster&gt;&#39;, 3306, \<br>   &#39;username&#39;, &#39;password&#39;, \<br>   /* master_auto_position= */ 1,false, false); \<br>call mysql.startReplication();</pre><blockquote><strong>Confirm Zero Replication Lag:</strong> Ensure the replication lag between the Cloud SQL master [testupgrade] and replica [testupgrade-replica] is zero.Inserted records in step 2 should be visible in the replica [testupgrade- replica] once synced.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/861/1*ZqbCXsfZsOT43hSJ-asnvQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/809/1*I5ilDgdbkn5xkT8CRf50NA.png" /></figure><blockquote><strong>Validate Replication Post-Switch:</strong> Insert 100 new records into the Cloud SQL master [testupgrade] and confirm they are successfully replicated to the Cloud SQL replica [testupgrade-replica].</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/864/1*SRb8c3MFjClYCFQJMvyrfQ.png" /></figure><blockquote><strong>Confirm Zero Replication Lag:</strong> Ensure the replication lag between the Cloud SQL master [testupgrade] and replica [testupgrade-replica] is zero.</blockquote><p>With the Replica in sync and data validation completed , proceed with the following steps to promote the replica and direct the applications to point to Cloud SQL MySQL 8.0.</p><blockquote><strong>Halt Traffic to Source:</strong> Stop all application traffic directed towards the original source database [testupgrade]</blockquote><blockquote><strong>Promote Cloud SQL Replica (Final Switch):</strong> Promote the Cloud SQL replica [testupgrade-replica] to become the final primary instance.</blockquote><blockquote><strong>Direct Application Traffic to New Master:</strong> Update your application to point to the newly promoted Cloud SQL instance [testupgrade-replica].</blockquote><blockquote><strong>Resume Application Traffic:</strong> Re-enable application traffic, now served by the new upgraded Cloud SQL master [testupgrade-replica] [Version MySQL 8.0].</blockquote><h3>Advantages of This Method</h3><p>This read replica promotion method offers several benefits compared to traditional in-place upgrades or risky manual migration steps:</p><ul><li>Drastically Reduced Overall Downtime: The primary benefit. The outage is limited to the final application switchover, which is typically much shorter than a full in-place major version upgrade or complex data migration downtime.</li><li>More Predictable Downtime:The duration is primarily tied to your application’s cutover procedure, which is often within your control, rather than an opaque database utility runtime.</li><li>Lower Risk Profile (Pre-Promotion): If any issues arise during the replica creation or syncing phase, production source instance is completely unaffected.</li><li>Relatively Straightforward Implementation: Leverages native Cloud SQL features (read replicas and promotion).</li></ul><h3>Important Considerations</h3><p>Like any technical strategy, this method has points to consider:</p><ul><li>Downtime During Switch: While drastically reduced, there is still a required downtime window when application traffic is redirected to the new primary. This method is “minimal downtime,” not strictly “zero downtime” in all scenarios.</li><li>Needs Additional Infrastructure and Cost Till Cutover: You will be temporarily running two Cloud SQL instances (the source and the replica/new primary), incurring double the instance cost for that period.</li><li>Need to Monitor Replication: It’s critical to ensure the replica is fully caught up before promotion. Failure to do so could lead to data loss.</li></ul><h3>Conclusion</h3><p>Upgrading your Cloud SQL MySQL database version doesn’t have to be a major source of anxiety and prolonged downtime. By implementing a read replica promotion strategy, you effectively move the time-consuming version upgrade process to a background task, reducing your critical outage window to the brief period required for application cutover. While not strictly “zero downtime” due to the required switch, this method offers a practical and significant reduction in disruption, making essential database maintenance much more manageable.</p><h3><em>References</em></h3><p><a href="https://medium.com/google-cloud/hybrid-cloud-replicating-to-cloudsql-mysql-from-an-external-server-12ffe5235f5c">https://medium.com/google-cloud/hybrid-cloud-replicating-to-cloudsql-mysql-from-an-external-server-12ffe5235f5c</a></p><p><em>Till next time , Happy Learning !</em></p><p><em>Saurabh !</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d4f1505adbab" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Binary Logs in Cloud SQL MySQL: A Time Traveler’s Guide to Recovering Database Operations]]></title>
            <link>https://saurabh8774.medium.com/binary-logs-in-cloud-sql-mysql-a-time-travelers-guide-to-recovering-database-operations-f53d8bc6088c?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/f53d8bc6088c</guid>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Mon, 23 Oct 2023 14:19:34 GMT</pubDate>
            <atom:updated>2023-10-23T14:24:18.877Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Uncover the power of binary logs to recover database operations within a specified time frame, bridging the gap between past and present.</strong></p><h3><strong>Understanding CloudSQL MySQL</strong></h3><p>CloudSQL MySQL is a fully managed MySQL database service provided by Google Cloud Platform. It offers a scalable, reliable, and secure solution for hosting MySQL databases in the cloud. CloudSQL MySQL takes care of database management tasks like patching, backups, and replication, allowing you to focus on developing and deploying applications.</p><h3>Unraveling Binary Logs in MySQL</h3><p>Binary logs, also known as binlogs, are essential components of MySQL’s replication and point-in-time recovery mechanisms. They contain a record of all data modifications made to the database, including INSERT, UPDATE, and DELETE statements. Binary logs are stored in a binary format, making them unreadable by humans. However, they can be decoded using specific tools to extract meaningful information.</p><h3>Common Use Cases for Binary Log Analysis</h3><ul><li>Point-in-time recovery: Binary logs play a crucial role in restoring a database to a specific point in time, allowing you to recover from data loss or corruption.</li><li>Replication troubleshooting: Analyzing binary logs can help identify and resolve replication issues, ensuring data consistency across replicas.</li><li>Auditing and forensic analysis: Binary logs can be used to track changes made to the database, providing valuable insights for auditing purposes or investigating security incidents.</li></ul><h3>How do we use Binary logs to recover database operations within a timeframe?</h3><p>MySQL binary logs are a type of log file that records all changes made to a database. If binary logging was enabled on the old database, it is possible to use the following tools to extract all of the SQL statements that were executed between a given start and end time:</p><ol><li><a href="https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html"><strong>Mysqlbinlog</strong></a></li><li><a href="https://github.com/danfengcao/binlog2sql"><strong>Binlog2SQL</strong></a></li></ol><p>Mysqlbinlog is a native mysql tool and can convert the binary logs from binary to text format. We can also generate the SQL statements using the mysqlbinlog utility.</p><p>Binlog2SQL is an open source tool designed by Cao Danfeng to parse the binary logs.It has the feature to extract the raw SQL statements from binary logs and can be used to generate the ROLLBACK SQL from binary logs for point-in-time recovery.</p><h3>Let’s experience both the tools with an example:</h3><p>Install a CloudSQL MySQL with the below database parameters:</p><pre>| binlog_format | ROW |<br>| binlog_row_image | FULL |<br>| binlog_rows_query_log_events | OFF</pre><p>Connect to MySQL and execute the following statements:</p><pre>Create database test;<br>Use test;<br>CREATE TABLE sales(<br>   ID INT,<br>   ProductName VARCHAR(255),<br>   CustomerName VARCHAR(255),<br>   DispatchDate date,<br>   DeliveryTime time,<br>   Price INT,<br>   Location VARCHAR(255)<br>);<br><br><br>insert into sales values(3, &#39;Mouse&#39;, &#39;Puja&#39;, DATE(&#39;2019-03-01&#39;), TIME(&#39;10:59:59&#39;), 3000, &#39;Vijayawada&#39;);<br>insert into sales values(4, &#39;Mobile&#39;, &#39;Vanaja&#39;, DATE(&#39;2019-03-01&#39;), TIME(&#39;10:10:52&#39;), 9000, &#39;Chennai&#39;);<br>insert into sales values(5, &#39;Headset&#39;, &#39;Jalaja&#39;, DATE(&#39;2019-04-06&#39;), TIME(&#39;11:08:59&#39;), 6000, &#39;Goa&#39;);</pre><p>Get the current binlog file name using</p><pre>show binary logs;</pre><h3><strong>MySQLbinlog</strong></h3><p>Installation</p><p><strong>mysqlbinlog</strong> utility is in mysql server package. You can install mysql server to use it.</p><p>Use the following mysqlbinlog command to read the current binary log for a duration of last 1hr:</p><pre>mysqlbinlog -R --protocol TCP --host &lt;hostip&gt; --user &lt;username&gt; --password --port 3306 -v --base64-output=DECODE-ROWS  mysql-bin.000404 --start-datetime &#39;2023–10–23 12:00:00&#39; --stop-datetime &#39;2023–10–23 13:00:00&#39;</pre><p>Output:</p><pre>....<br>### INSERT INTO `test`.`sales`<br>### SET<br>###   @1=3<br>###   @2=&#39;Mouse&#39;<br>###   @3=&#39;Puja&#39;<br>###   @4=&#39;2019:03:01&#39;<br>###   @5=&#39;10:59:59&#39;<br>###   @6=3000<br>###   @7=&#39;Vijayawada&#39;<br>....<br><br>### INSERT INTO `test`.`sales`<br>### SET<br>###   @1=4<br>###   @2=&#39;Mobile&#39;<br>###   @3=&#39;Vanaja&#39;<br>###   @4=&#39;2019:03:01&#39;<br>###   @5=&#39;10:10:52&#39;<br>###   @6=9000<br>###   @7=&#39;Chennai&#39;<br>....<br><br>### INSERT INTO `test`.`sales`<br>### SET<br>###   @1=5<br>###   @2=&#39;Headset&#39;<br>###   @3=&#39;Jalaja&#39;<br>###   @4=&#39;2019:04:06&#39;<br>###   @5=&#39;11:08:59&#39;<br>###   @6=6000<br>###   @7=&#39;Goa&#39;<br>....</pre><h3>Binlog2sql</h3><p>Installation:</p><pre>git clone https://github.com/danfengcao/binlog2sql.git &amp;&amp; cd binlog2sql<br>pip install -r requirements.txt</pre><p>Use the following mysqlbinlog command to read the current binary log for a duration of last 1hr:</p><pre>binlog2sql.py --host &lt;hostip&gt; -uroot -p --start-file mysql-bin.000404 --start-datetime &#39;2023–10–23 12:00:00&#39; --stop-datetime &#39;2023–10–23 13:00:00&#39;</pre><p>Output:</p><pre>....<br>INSERT INTO `test`.`sales`(`ID`, `ProductName`, `CustomerName`, `DispatchDate`, `DeliveryTime`, `Price`, `Location`) VALUES (3, &#39;Mouse&#39;, &#39;Puja&#39;, &#39;2019-03-01&#39;, &#39;10:59:59&#39;, 3000, &#39;Vijayawada&#39;); #start 100808 end 101016 time 2023-10-23 12:29:05<br>....<br>INSERT INTO `test`.`sales`(`ID`, `ProductName`, `CustomerName`, `DispatchDate`, `DeliveryTime`, `Price`, `Location`) VALUES (4, &#39;Mobile&#39;, &#39;Vanaja&#39;, &#39;2019-03-01&#39;, &#39;10:10:52&#39;, 9000, &#39;Chennai&#39;); #start 101112 end 101320 time 2023-10-23 12:29:05<br>....<br>INSERT INTO `test`.`sales`(`ID`, `ProductName`, `CustomerName`, `DispatchDate`, `DeliveryTime`, `Price`, `Location`) VALUES (5, &#39;Headset&#39;, &#39;Jalaja&#39;, &#39;2019-04-06&#39;, &#39;11:08:59&#39;, 6000, &#39;Goa&#39;); #start 101416 end 101621 time 2023-10-23 12:29:07</pre><h3>Conclusion</h3><p>mysqlbinlog and binlog2sql both tools can be used to read binary logs.</p><p>mysqlbinlog generates the output in SQL and text format. Output can be used to generate exact SQL statements with some manual intervention.</p><p>binlog2sql generates the output in a consumable SQL format that was executed on the database.</p><h3>Summary</h3><p>In general, mysqlbinlog is a more versatile tool that can be used for a wider range of purposes. However, binlog2sql can be a more convenient option for tasks that require converting binary log events to SQL statements.</p><h3>Comparison</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aclefeSkKsgnBpofb-1Zsw.png" /></figure><h3>Reference:</h3><p><a href="https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html">mysqlbinlog</a> <a href="https://github.com/danfengcao/binlog2sql">binlog2sql</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f53d8bc6088c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hybrid Cloud : Replicating from CloudSQL MySQL to an external server]]></title>
            <link>https://medium.com/google-cloud/hybrid-cloud-replicating-from-cloudsql-mysql-to-an-external-server-f03be818d582?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/f03be818d582</guid>
            <category><![CDATA[cloud-sql]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-replication]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Mon, 17 Apr 2023 21:18:38 GMT</pubDate>
            <atom:updated>2024-02-07T09:41:08.102Z</atom:updated>
            <content:encoded><![CDATA[<h3>Hybrid Cloud : Replicating data from Cloud SQL MySQL to a Self Managed MySQL server</h3><blockquote>In the earlier <a href="https://saurabh8774.medium.com/hybrid-cloud-replicating-to-cloudsql-mysql-from-an-external-server-12ffe5235f5c">blog</a> we setup a replication from a self-managed/managed MySQL to Cloud SQL MySQL.</blockquote><blockquote>In this blog ,we would promote the CloudSQL as master and demote the self managed MySQL as Replica.</blockquote><h3>Use cases for setting up Replication to an external MySQL server</h3><p>Replicating to an external server can help you achieve the following goals:</p><ol><li>Hybrid Cloud : Replicate data to a self managed MySQL in the same/other cloud.</li><li>Minimal downtime fallback strategy with RPO=0 while migrating from a self managed mysql to Cloud SQL .</li><li>Retain colocation and control of your replicas.</li></ol><h3>Replicating to a external MySQL includes the following instances:</h3><ul><li>CloudSQL Instance as a Master.</li><li>A self managed MySQL on GCE.</li></ul><h3>Overview:</h3><p>At the end of earlier blog , we had the following setup:</p><pre>Master : Self managed mysql with GTID on GCE<br>Replica : CloudSQL MySQL with GTID</pre><p>Using the below steps , we will promote the CloudSQL MySQL as master and convert self managed MySQL as replica</p><pre>Master : CloudSQL MySQL with GTID<br>Replica : Self managed mysql with GTID on GCE</pre><ol><li>Stop writes on the master and let replication lag settle down.</li><li>Promote CloudSQL</li><li>Create Replication user and grant privileges on CloudSQL MySQL</li><li>Execute change master on self managed MySQL</li></ol><h3>Lets start !</h3><blockquote>1. Status check on Master and Slave</blockquote><p>Master : Self managed MySql on GCE</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E35QNNfuvUD7ei9lyJ7pkg.png" /></figure><p>Slave : CloudSQL MySQL</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fv04-Lxa1S_u_eUPK3knuA.png" /></figure><blockquote>2. Stop writes on the master and let Replication lag settle down.</blockquote><blockquote>3. Promote CloudSQL</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QCoDGYJWHAfx3p-94h6v4Q.png" /></figure><p>After promotion:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OPDcAbZrQGoiD80e4uXXww.png" /></figure><blockquote>4. Create Replication user and grant privileges on CloudSQL MySQL</blockquote><pre>CREATE USER &#39;replicationUser&#39;@&#39;%&#39; IDENTIFIED BY &#39;****&#39;;<br>GRANT SELECT, SHOW VIEW ON *.* TO &#39;replicationUser&#39;@&#39;%&#39;;<br>GRANT REPLICATION SLAVE, EXECUTE ON *.* TO &#39;replicationUser&#39;@&#39;%&#39;;<br>grant FLUSH_TABLES ON *.* TO &#39;replicationUser&#39;@&#39;%&#39;;     <br>grant process ON *.* TO &#39;replicationUser&#39;@&#39;%&#39;;<br>FLUSH PRIVILEGES;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M4uj_J9BGbO1L3Wl9mfqIA.png" /></figure><blockquote>5. Execute change master on self managed MySQL</blockquote><pre>CHANGE MASTER TO MASTER_HOST=&#39;&lt;cloudsql-ip&gt;&#39;,<br>MASTER_USER=&#39;replicationUser&#39;,<br>MASTER_PASSWORD=&#39;****&#39;,<br>MASTER_AUTO_POSITION=1;<br>start slave;</pre><blockquote>6. Status check on Master and Slave</blockquote><p>Master : CloudSQL MySQL</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*50kZc386K25T2qMbC8BOlg.png" /></figure><p>Slave : Self managed MySQL on GCE</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cnvWI_yYxPFtzNCUbSNHEQ.png" /></figure><blockquote>7. Validation</blockquote><p>Insert a record on Master</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zAC-Zisz9_soAoGsWlpCeg.png" /></figure><p>Validate the data replication to Slave</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XSm9HaCtcCx1-h56tOxJdA.png" /></figure><h3>Learnings :</h3><blockquote>Error 1: Start slave fails with the following error:</blockquote><pre>2023-04-16T21:01:25.174361Z 77 [ERROR] [MY-010584] [Repl] Slave SQL for channel<br> &#39;&#39;: Worker 1 failed executing transaction &#39;3a9a32a7-dc82-11ed-83b6-42010a31e0e3:902&#39; <br>at master log mysql-bin.000002, end_log_pos 278330; <br>Could not execute Update_rows event on table mysql.heartbeat; <br>Can&#39;t find record in &#39;heartbeat&#39;, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; <br>the event&#39;s master log FIRST, end_log_pos 278330, Error_code: MY-001032</pre><p>Fix:</p><pre>Step 1: Take a dump of mysql.heartbeat table from CloudSQL MySQL:<br><br>      mysqldump - host=**.**.**.** - port=3306 - user=replicationUser - password=****<br>       - databases mysql - tables heartbeat - hex-blob - no-autocommit - default-character-set=utf8mb4 <br>      - single-transaction - set-gtid-purged=on - add-drop-table &gt; mysql_dump_fromcloudsql.sql<br>      <br>Step 2: On Slave <br><br>reset master;<br><br>Step 3: Import the dump into self managed mysql:<br><br>    mysql -u root -p mysql &lt; mysql_dump_fromcloudsql.sql<br><br>Step 4 : start slave;<br><br>Step 5 : SHOW SLAVE STATUS\G;</pre><p>Till next time , Happy Learning !</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f03be818d582" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/hybrid-cloud-replicating-from-cloudsql-mysql-to-an-external-server-f03be818d582">Hybrid Cloud : Replicating from CloudSQL MySQL to an external server</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hybrid Cloud : Replicating to CloudSQL MySQL from an external server]]></title>
            <link>https://medium.com/google-cloud/hybrid-cloud-replicating-to-cloudsql-mysql-from-an-external-server-12ffe5235f5c?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/12ffe5235f5c</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[cloud-sql]]></category>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Mon, 17 Apr 2023 14:07:29 GMT</pubDate>
            <atom:updated>2024-02-25T20:51:40.626Z</atom:updated>
            <content:encoded><![CDATA[<h3>Hybrid Cloud : Replicating data to CloudSQL MySQL from an external MySQL server</h3><h4>This is a step by step guide to replicate data from a self-managed/managed MySQL to CloudSQL MySQL.</h4><p><strong>Cloud SQL for MySQL</strong> is a fully-managed database service that helps you set up, maintain, manage, and administer your MySQL relational databases on Google Cloud Platform. With Cloud SQL for MySQL, you can spend less time on your database operations and more time on your applications. CloudSQL MySQL offers some great <a href="https://cloud.google.com/sql#section-8">features</a> and differentiators which are getting a lot of attention and making CloudSQL has the first choice for MySQL enthusiasts.</p><p>GCP <a href="https://cloud.google.com/database-migration">Database Migration Service</a> makes it easy to migrate databases from on-premises, Compute Engine, and other clouds to Cloud SQL with minimal downtime.</p><p>Alternatively, if you need more control while migrating to CloudSQL you can create CloudSQL MySQL as a native replica from an external server and replicate data. Replication between your self-managed/managed mysql server and the Cloud SQL replica continues indefinitely.</p><h3>Use cases for external server configuration</h3><p>External server configuration helps you achieve the following goals:</p><ol><li>Migrate your data from your self-managed MySQL server to Google Cloud with minimum downtime.</li><li>Use native tools like mydumper/myloader to exponentially increase the export and import performance.</li><li>Retain colocation and control of your server while off-loading the administration of the replicas to Cloud SQL.</li></ol><h3><strong>External replication configuration includes the following instances:</strong></h3><ul><li>The MySQL server that you manage, also called the <em>source database server</em>.</li><li>The Cloud SQL replica.</li><li>The <em>source representation instance</em>. The source representation instance is a Cloud SQL instance that represents the source database server to the Cloud SQL replica. It is visible in the Google Cloud console and appears the same as a regular Cloud SQL instance, but it contains no data, requires no configuration or maintenance, and does not affect billing.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/562/1*W5wVH4J90dyjmiI8xCU87w.png" /></figure><h3><strong>Overview</strong>:</h3><ol><li>Create a GCE instance , install and configure MySQL 8(with GTID)</li><li>Create a dummy database &amp; table and insert few records</li><li>Prerequisites on source DB — Enable binary logging and log retention , create replication user &amp; grant privileges</li><li>Set up a source representation instance</li><li>Set up a Cloud SQL replica &amp; add users to the Cloud SQL replica</li><li>Get the Cloud SQL replica’s outgoing IP address and Allow incoming connections on the external server</li><li>Promote CloudSQL Replica</li><li>Perform a custom dump and import</li><li>Demote the Cloud SQL instance</li><li>Start replication on the Cloud SQL instance</li><li>Replication validation</li></ol><h3>Lets Start !</h3><blockquote>1. Create a GCE instance , install and configure MySQL 8(with GTID)</blockquote><p>Login into the GCE instance:</p><pre>sudo apt-get install wget<br>wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb<br>sudo apt install ./mysql-apt-config_0.8.22–1_all.deb<br>sudo apt update<br>sudo apt install mysql-server<br>sudo service mysql status</pre><p>Add the following lines in /etc/mysql/mysql.cnf</p><pre>[mysqld]<br><br>pid-file        = /var/run/mysqld/mysqld.pid<br>socket          = /var/run/mysqld/mysqld.sock<br>datadir         = /var/lib/mysql<br>log-error       = /var/log/mysql/error.log<br><br><br>bind-address=**.**.**.**<br>log_bin=/var/log/mysql/mysql-bin.log<br>server_id=11111<br>gtid_mode=ON<br>enforce_gtid_consistency=ON</pre><p>Restart mysql using below commands:</p><pre>sudo service mysql status<br>sudo service mysql stop<br>/usr/sbin/mysqld - defaults-file=/etc/mysql/mysql.cnf</pre><p>Please make sure the mysql user has R/W permissions on /var/run/mysqld , /var/lib/mysql , /var/log/mysql. For any errors , please check and fix /var/log/mysql/error.log</p><p>Validate GTID config on source MySQL :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QbHhMdZ_IZdDkx0P1PWP4A.png" /></figure><p>2. Create a dummy database &amp; table and insert few records</p><pre>Create database sample;<br>Use sample;<br><br>CREATE TABLE employees ( <br>emp_no      INT             NOT NULL,  -- UNSIGNED AUTO_INCREMENT??<br>birth_date  DATE            NOT NULL,<br>first_name  VARCHAR(14)     NOT NULL,<br>last_name   VARCHAR(16)     NOT NULL,<br>gender      ENUM (&#39;M&#39;,&#39;F&#39;)  NOT NULL,  -- Enumeration of either &#39;M&#39; or &#39;F&#39;  <br>hire_date   DATE            NOT NULL,<br>PRIMARY KEY (emp_no)                   -- Index built automatically on primary-key column<br>);<br><br><br><br>insert into employees values (1,now(),&#39;aaaa&#39;,&#39;bbbb&#39;,&#39;M&#39;,now());<br>insert into employees values (2,now(),&#39;aaaa&#39;,&#39;bbbb&#39;,&#39;M&#39;,now());<br>insert into employees values (3,now(),&#39;aaaa&#39;,&#39;bbbb&#39;,&#39;M&#39;,now());<br>insert into employees values (4,now(),&#39;aaaa&#39;,&#39;bbbb&#39;,&#39;M&#39;,now());<br>insert into employees values (5,now(),&#39;aaaa&#39;,&#39;bbbb&#39;,&#39;M&#39;,now());<br>insert into employees values (6,now(),&#39;aaaa&#39;,&#39;bbbb&#39;,&#39;M&#39;,now());</pre><blockquote>3. Prerequisites on source DB — Enable binary logging and log retention , create replication user &amp; grant privileges</blockquote><p>Enable binary logging and log retention</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u8QkQZ0YQB32iyK_bPQnOw.png" /></figure><p>Create replication user &amp; grant privileges on the source DB:</p><pre>CREATE USER &#39;replicationUser&#39;@&#39;%&#39; IDENTIFIED BY &#39;******&#39;;<br>GRANT SELECT, SHOW VIEW ON *.* TO &#39;replicationUser&#39;@&#39;%&#39;;<br>FLUSH PRIVILEGES;<br>GRANT REPLICATION SLAVE, EXECUTE ON *.*<br>TO &#39;replicationUser&#39;@&#39;%&#39;;<br>grant FLUSH_TABLES ON *.* TO &#39;replicationUser&#39;@&#39;%&#39;;<br>grant process ON *.* TO &#39;replicationUser&#39;@&#39;%&#39;;</pre><blockquote>4. Set up a source representation instance</blockquote><pre>vi Source.json<br><br><br>    {<br>      &quot;name&quot;: &quot;clousql-custom&quot;,<br>      &quot;region&quot;: &quot;us-central1&quot;,<br>      &quot;databaseVersion&quot;: &quot;MYSQL_8_0&quot;,<br>      &quot;onPremisesConfiguration&quot;: {<br>        &quot;hostPort&quot;: &quot;*.*.*.*:3306&quot;,<br>        &quot;username&quot;: &quot;replicationUser&quot;,<br>        &quot;password&quot;: &quot;****&quot;<br>      }<br>    }</pre><pre> gcloud auth login<br>    ACCESS_TOKEN=&quot;$(gcloud auth print-access-token)&quot;<br>    curl --header &quot;Authorization: Bearer ${ACCESS_TOKEN}&quot; \<br>         --header &#39;Content-Type: application/json&#39; \<br>         --data @/home/source.json \<br>         -X POST \<br>        https://sqladmin.googleapis.com/sql/v1beta4/projects/&lt;project-name&gt;/instances</pre><blockquote>5. Set up a Cloud SQL replica &amp; add users to the Cloud SQL replica</blockquote><pre>vi replica.json<br><br> {<br>         &quot;settings&quot;: {<br>             &quot;tier&quot;: &quot;db-custom-4-15360&quot;,<br>             &quot;dataDiskSizeGb&quot;: &quot;50&quot;,<br>             &quot;ipConfiguration&quot;: {<br>                 &quot;privateNetwork&quot;: &quot;projects/&lt;project-name&gt;/global/networks/&lt;netwrorkname&gt;&quot;<br>              },<br>              &quot;availabilityType&quot;: &quot;ZONAL&quot;<br>         },<br>         &quot;masterInstanceName&quot;: &quot;cloudsql-custom&quot;,<br>         &quot;region&quot;: &quot;us-central1&quot;,<br>         &quot;databaseVersion&quot;: &quot;MYSQL_8_0&quot;,<br>         &quot;name&quot;: &quot;cloudsql-custom-replica&quot;<br>     }</pre><pre> gcloud auth login<br>      ACCESS_TOKEN=&quot;$(gcloud auth print-access-token)&quot;<br>      curl --header &quot;Authorization: Bearer ${ACCESS_TOKEN}&quot; \<br>           --header &#39;Content-Type: application/json&#39; \<br>           --data @/home/replica.json \<br>           -X POST \ https://sqladmin.googleapis.com/sql/v1beta4/projects/&lt;project-name&gt;/instances</pre><p>To ensure your instances were set up correctly, go to the <a href="https://console.cloud.google.com/sql/instances">Cloud SQL Instances page</a>.</p><p>You should see your source representation instance and the Cloud SQL replica, in a listing similar to the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zbgktQ2huFQvM0eqFYkGjw.png" /></figure><p>Add users to the CloudSQL Replica</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RiZ6yYMyklpjcS6bMNm26w.png" /></figure><blockquote>6. Get the Cloud SQL replica’s outgoing IP address and allow incoming connections on the external server</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y8jTJzCm-8dTLYxjgYh_3w.png" /></figure><p>Outgoing IP address to be whitelisted = 34.31.123.60</p><p>The Cloud SQL replica needs to connect to the external server for replication to succeed. You must configure the network firewall for your external server to accept connections from the Cloud SQL replica’s <a href="https://cloud.google.com/sql/docs/mysql/replication/configure-replication-from-external#outgoing-ip">outgoing IP address</a> if the following conditions apply:</p><ul><li>The external server is behind a firewall or some other network restriction.</li><li>Your Cloud SQL replica is using a public IP.</li></ul><blockquote>7. Promote CloudSQL Replica</blockquote><p>Because Cloud SQL replica instances are read-only, in order to perform a custom import, you need to <a href="https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#promote-replica">promote the Cloud SQL replica</a> to a standalone instance. Once the initial data import is complete, you demote the instance back to a replica.</p><blockquote>8. Perform a custom dump from source and import it into cloudSQL Replica</blockquote><pre>mysqldump     --host=&lt;source-hostip&gt;     --port=3306     --user=replicationUser  <br>  --password=*****    --databases sample      --hex-blob     --no-autocommit <br>    --default-character-set=utf8mb4     --single-transaction   <br>  --set-gtid-purged=on     --add-drop-table &gt; sample_dump.sql</pre><p>Import in the promoted CloudSQL Replica</p><pre>mysql -h &lt;cloudsql replicaip&gt; -u root -p  &lt; sample_dump.sql</pre><p>In case the database size is big , then you can use mydumper and myloader to tune the export and import process and documented <a href="https://cloud.google.com/sql/docs/mysql/replication/custom-import-replication-from-external#perform_a_custom_dump_and_import">here.</a></p><p>Write down the GTID or binlog information of the data dump. You need this information to configure the replication with the Cloud SQL stored procedures.</p><pre>  example:<br>SET @@GLOBAL.GTID_PURGED=&#39;32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496&#39;;</pre><p>Remove the following lines in the dump file that require super privileges. Since Cloud SQL users don’t have super privileges, these lines cause the import to fail.</p><p>For GTID-based replication: Remove the SET GTID_PURGED statement along with the session variable setting statement in the dump. For example:</p><pre>   ...<br>   SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;<br>   SET @@SESSION.SQL_LOG_BIN= 0;<br>   ...<br>   SET @@GLOBAL.GTID_PURGED=&#39;32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496&#39;;<br>   ...<br>   SET @@SESSION.SQL_LOG_BIN=@MYSQLDUMP_TEMP_LOG_BIN;</pre><blockquote>9. Demote the Cloud SQL instance</blockquote><pre>vi demotemaster.json<br><br>{<br>        &quot;demoteMasterContext&quot;: {<br>          &quot;masterInstanceName&quot;: clousql-custom,<br>          &quot;skipReplicationSetup&quot;: true<br>          }<br>     }</pre><pre>  gcloud auth login<br>     ACCESS_TOKEN=&quot;$(gcloud auth print-access-token)&quot;<br>     curl --header &quot;Authorization: Bearer ${ACCESS_TOKEN}&quot; \<br>       --header &#39;Content-Type: application/json&#39; \<br>       --data @/home/demotemaster.json \<br>       -X POST \     https://sqladmin.googleapis.com/sql/v1beta4/projects/&lt;project-name&gt;/instances/cloudsql-custom-replica/demoteMaster</pre><blockquote>10. Start replication on the Cloud SQL instance</blockquote><p>Log on to the replica instance.</p><p>Use the mysql.resetMaster stored procedure to reset replication settings.</p><pre> mysql&gt; call mysql.resetMaster();</pre><p>Configure the replication</p><pre>    mysql&gt; call mysql.skipTransactionWithGtid(&#39;32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496&#39;);<br>    mysql&gt; call mysql.setupExternalSourceAutoPosition(&#39;&lt;externalmysqlip&gt;&#39;, 3306, \<br>    &#39;USERNAME&#39;, &#39;PASSWORD&#39;, \<br>    /* master_auto_position= */ 1,false, false); \<br>   mysql&gt; call mysql.startReplication();<br>   mysql&gt; show slave status\G</pre><p>Once you start replication from the external server, you need to monitor replication and then complete your migration. To learn more, see <a href="https://cloud.google.com/sql/docs/mysql/replication/configure-replication-from-external#monitoring_replication">Monitoring replication</a>.</p><blockquote>11. Replication Validation</blockquote><p>Insert records on the source DB:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q0YnCNgaI50tV2A1iRtC5A.png" /></figure><p>Validate on CloudSQL Replica:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iZTduJEozKPjz8SNur5hmw.png" /></figure><p><em>In the next </em><a href="https://saurabh8774.medium.com/hybrid-cloud-replicating-from-cloudsql-mysql-to-an-external-server-f03be818d582"><em>blog</em></a><em> ,we promote the CloudSQL as master and demote the self managed MySQL as Replica.</em></p><p>Till next time , Happy Learning !</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=12ffe5235f5c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/hybrid-cloud-replicating-to-cloudsql-mysql-from-an-external-server-12ffe5235f5c">Hybrid Cloud : Replicating to CloudSQL MySQL from an external server</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Near Real Time Data Replication using Debezium]]></title>
            <link>https://medium.com/google-cloud/near-real-time-data-replication-using-debezium-4da77cef67ab?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/4da77cef67ab</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-sql]]></category>
            <category><![CDATA[data-replication]]></category>
            <category><![CDATA[debezium]]></category>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Fri, 11 Feb 2022 06:02:57 GMT</pubDate>
            <atom:updated>2022-02-15T05:45:09.739Z</atom:updated>
            <content:encoded><![CDATA[<h4>Part 2 : A detailed guide to build a data replication pipeline from GCP CloudSQL Postgres to GCP BigQuery</h4><p>In <a href="https://medium.com/@saurabhgupt_85545/near-real-time-data-replication-using-debezium-fd32be6d192a">Part 1</a> of this series we implemented Debezium in a distributed mode.In Part 2 we will install and configure source and sink connectors to replicate data from CloudSQL Postgres to BigQuery.</p><h3>What is CloudSQL Postgres ?</h3><p>PostgreSQL is the industry’s leading open source relational database with an active and growing ecosystem of developers and tools. With Cloud SQL for PostgreSQL, you can now spend less time on your PostgreSQL database operations, and more time on your applications.</p><h4>CloudSQL Postgres features:</h4><blockquote>Full compatibility for your PostgreSQL applications on major versions: 14, 13, 12, 11, 10, 9.6<br>Support for the most popular PostgreSQL extensions and over 100 database flags<br>Integrated database observability for DevOps with Cloud SQL Insights<br>Integration with key services like Google Kubernetes Engine (GKE), BigQuery, and Cloud Run<br>Simplified and secure migrations with Database Migration Service</blockquote><h3>What is BigQuery ?</h3><p><strong>BigQuery</strong> is a serverless, cost-effective and multicloud data warehouse designed to help you turn big data into valuable business insights.</p><h4>BigQuery features:</h4><blockquote>Democratize insights with a secure and scalable platform with built-in machine learning<br>Power business decisions from data across clouds with a flexible, multicloud analytics solution<br>Run analytics at scale with 26%–34% lower three-year TCO than cloud data warehouse alternatives<br>Adapting to your data at any scale, from bytes to petabytes, with zero operational overhead</blockquote><h3>Why is it needed to replicate data from OLTP databases like Postgres to DWH like BigQuery ?</h3><p>The operational data needs of an organization are addressed by the online transaction processing (OLTP) systems which is important to the day-to-day running of its business. Nevertheless, they are not perfectly suitable for sustaining decision-support queries or business questions that managers normally needs to address. Such questions involve analytics including<br>aggregation, drilldown, and slicing/dicing of data, which are best supported by online analytical processing (OLAP) systems. Data warehouses like BigQuery support OLAP applications by storing and maintaining data in multidimensional format. Data in an OLAP warehouse is extracted and loaded from multiple OLTP data sources (including Postgres, MySQL,DB2, Oracle, SQL Server and flat files).</p><h3>Architecture:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v5epjUO8cu8bowts_6vgQg.png" /></figure><h3>Requirements:</h3><blockquote>CloudSQL Postgres Instance</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Jpi-MCECcV2axNIluO6zmQ.png" /></figure><blockquote>BigQuery Instance</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AxYWaWxtyaP5fTz6TksLZg.png" /></figure><blockquote>Debezium Service [as detailed in <a href="https://medium.com/@saurabhgupt_85545/near-real-time-data-replication-using-debezium-fd32be6d192a">Part 1</a>]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mO46leZ6uvYcJQLjDR3YNw.png" /></figure><h3>Connector Installation</h3><p>Execute the below installation steps on all 3 Debezium nodes:</p><h4>Install Source Connector : Debezium Postgres to Kafka Connector</h4><blockquote>confluent-hub install debezium/debezium-connector-postgres:latest</blockquote><h4>Install Sink Connector : Kafka to BigQuery Connector</h4><blockquote>confluent-hub install wepay/kafka-connect-bigquery:latest</blockquote><h3>Connector Configuration</h3><h4>Source connector configuration — CloudSQL Postgres</h4><blockquote>cat ~/source.json</blockquote><blockquote>{<br> “name”: “sourcecloudsqlpg”,<br> “config”: {<br> “connector.class”: “io.debezium.connector.postgresql.PostgresConnector”,<br> “database.hostname”: “XX.XX.XX.XX”,<br> “database.port”: “5432”,<br> “database.user”: “postgres”,<br> “database.password”: “****”,<br> “database.dbname”: “dvdrental”,<br> “database.server.name”:”my-server”,<br> “transforms”: “route”,<br> “transforms.route.type”: “org.apache.kafka.connect.transforms.RegexRouter”,<br> “transforms.route.regex”:”([^.]+)\\.([^.]+)\\.([^.]+)”,<br> “transforms.route.replacement”:”$3&quot;,<br> “plugin.name”: “wal2json”,<br> “transforms.unwrap.add.source.fields”: “ts_ms”,<br> “tombstones.on.delete”: “false”,<br> “include.schema.changes”: “true”,<br> “inconsistent.schema.handling.mode”:”warn”,<br> “database.history.skip.unparseable.ddl”: “true”,<br> “table.whitelist”: “public.accounts”,<br> “snapshot.mode”: “initial”,<br> “snapshot.locking.mode”: “none”,<br> “time.precision.mode”: “connect”,<br> “decimal.handling.mode”: “double”<br> }<br>}</blockquote><h4>Target connector configuration — BigQuery</h4><blockquote>cat ~/target.json</blockquote><blockquote>{<br> “name”: “targetbigquery”,<br> “config”: {<br> “connector.class”: “com.wepay.kafka.connect.bigquery.BigQuerySinkConnector”,<br> “tasks.max” : “1”,<br> “errors.log.enable”: true,<br> “errors.log.include.messages”: true,<br> “topics” : “accounts”,<br> “sanitizeTopics” : “true”,<br> “autoCreateTables” : “true”,<br> “autoUpdateSchemas” : “true”,<br> “bufferSize”: “1000”,<br> “maxWriteSize”:”100&quot;,<br> “tableWriteWait”: “10”,<br> “project” : “&lt;GCP-PROJECT-ID&gt;”,<br> “defaultDataset” : “debezium”,<br> “threadPoolSize”: 10,<br> “keyfile” : “&lt;absolute path of service account key file&gt;”,<br> “upsertEnabled”: true,<br> “bigQueryPartitionDecorator”: false,<br> “kafkaKeyFieldName”: “kafkakey”,<br> “transforms” : “HoistFieldKey”,<br> “transforms.HoistFieldKey.type”: “org.apache.kafka.connect.transforms.HoistField$Key”,<br> “transforms.HoistFieldKey.field”: “user_id”,<br> “allowNewBigQueryFields”: “true”,<br> “allowBigQueryRequiredFieldRelaxation”: “true”,<br> “schemaRetriever” : “com.wepay.kafka.connect.bigquery.retrieve.IdentitySchemaRetriever”,<br> “key.converter”: “org.apache.kafka.connect.storage.StringConverter”,<br> “allBQFieldsNullable”: true<br> }<br>}</blockquote><h4>BQ Service Account and key creation steps:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SOQiYCeXAB6Iyxt9X46AVA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_jvAPL0asJ5l8d7BkvfUwg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*64aDsqRan4XSJW7zYEEHFw.png" /></figure><p>Download and paste the key as defined in the BigQuery sink connector configuration file keyfile.</p><h3>Connector Registration:</h3><h4>Register and start Source CloudSQL PG connector:</h4><blockquote>curl -l -X POST -H “Accept:application/json” -H “Content-Type:application/json” localhost:8083/connectors/ -d <a href="http://twitter.com/bt">@</a>source.json</blockquote><p>Above command will register and start the source CloudSQL PG connector. Once the connector starts up it connects with the source i.e CloudSQL PG and take snapshot of the whitelisted table defined in the connector config file and push all the records in the kafka topic with the same name as database table name. As per the above config file , snapshot of <strong>accounts</strong> table is taken and all 58 records are pushed to kafka topic with name <strong>accounts</strong>. Streaming of changed records automatically starts once the snapshot is completed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7YmZOC2epXGzq8u1MypMuw.png" /><figcaption>Snapshot of debezium logs</figcaption></figure><p>List kafka topic and make sure a kafka topic with same name as postgres table being replicated is created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2gYaN70_D2NkEwMlN8v1Ng.png" /><figcaption>Kafka topic with the same name as Postgres table name is created</figcaption></figure><p>List content of kafka topic and make sure the records are streamed to the kafka topic. No. of records in the kafka topic should be equal to no. of records in the postgres table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*az_FbdRe21zolQNKgHo3oQ.png" /></figure><h4>Register and start BigQuery sink connector:</h4><blockquote>curl -l -X POST -H “Accept:application/json” -H “Content-Type:application/json” localhost:8083/connectors/ -d <a href="http://twitter.com/bt">@</a>target.json</blockquote><p>Above command will register and start the sink BigQuery connector. Once the connector starts up it connects with the kafka schema registry and fetches the schema of the source table i.e. accounts and creates the table in the BQ dataset. BQ sink connector also created a account_tmp* table. All the records read from kafka topic is first pushed to account_temp* table and then merged with parent account table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2nkYK8dlCQdFtFoC6eP0qg.png" /><figcaption>Accounts table created automatically in BQ</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DDE7c6VvwT7asheMo6eWiw.png" /><figcaption>Accounts and Accounts_tmp* table created in Debezium dataset in BQ</figcaption></figure><h3>Data validation</h3><h4>Initial snapshot validation<br>Changed data replication validation</h4><blockquote>Case 1: Insert a record</blockquote><blockquote>Case 2 : Delete 2 records</blockquote><blockquote>Case 3: Update a record</blockquote><blockquote>Case 4: Alter table to add a column and insert a record with new structure</blockquote><blockquote>Case 5: Alter table to drop a column and insert a record with new structure</blockquote><h4>Initial snapshot validation</h4><blockquote>Source : CloudSQL Postgres table — Accounts [58 records]:</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mILV6NKQmUOPOLGPnKtBHQ.png" /></figure><blockquote>Kafka Topic — Accounts [58 records]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hbU9i6_fl-2zaYmXrXp4xw.png" /></figure><blockquote>Bigquery table — Accounts [58 records]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YonhBFM-YDBFFyBqgOgEFQ.png" /></figure><h4>Changed data replication validation</h4><blockquote>Case 1: Insert a record</blockquote><p>Insert a record in source cloudsql pg accounts table . [59 records]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1mWUSR6Okqy--HwbKzl-AQ.png" /></figure><p>Kafka Topic — Accounts [59 Records] . Last rows shows the same values as inserted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dg6H1XTIEdt8DhOAW7RUkQ.png" /></figure><p>BigQuery table — Accounts [59 records].Last rows shows the same values as inserted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1Pf7m_-Z0fYhyFgZi-8bxA.png" /></figure><blockquote>Case 2 : Update a record</blockquote><p>Delete 2 records from source cloudsql pg accounts table . [57 records]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rCovPCRY0CdFnJm-XHUrdA.png" /></figure><p>Kafka topic [61 records with the latest entries as delete operations]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TtRzkqKlVe8pnfrLl0F5Lg.png" /></figure><p>BigQuery table — Accounts [57 records].Deletes are treated as soft deletes in BQ so use op &lt;&gt; ‘d’ in the where clause of the SQL statement while fetching the total count.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a8GBYt7vRaix0bskLceWqA.png" /></figure><blockquote>Case 3: Update a record</blockquote><p>Update a record in source cloudsql Postgres[57 records]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ai4fmxe3uV6FoPEj2tDcNA.png" /></figure><p>Kafka topic [62 records with the latest entries as Update operation]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hJ8h5182rkc9qI2CRjI0UQ.png" /></figure><p>BigQuery table [62 records with the updated data]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JgCvivhWERshI2W_t7NJaw.png" /></figure><blockquote>Case 4: Alter table to add a column and insert a record with new structure</blockquote><p>Alter the table structure by adding a column. Add a column <strong>city </strong>in cloudsql PG account table and insert a record.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b2skRPzN3eJvlq6VPXIphA.png" /></figure><p>Kafka topic [New record with New column]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VmeMiVgbbgLg1nMBBU8cjw.png" /></figure><p>Debezium logs indicating the auto schema change</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yIikWexVTBqdRZRg02QVMw.png" /></figure><p>Bigquery table [New column with latest record]:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8cpur9qoQKwiEE9RdSolLg.png" /></figure><blockquote>Case 5: Alter table to drop a column and insert a record with new structure</blockquote><p>Alter the table structure by dropping a column. Drop column <strong>City </strong>in cloudsql pg table and insert a record.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JbyJ5dIDTpkS-WuB3Zgx9Q.png" /></figure><p>Debezium logs indicating the auto schema change</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WEZ7keRDTJDFiTsvFxojmQ.png" /></figure><p>kafka topic Kafka topic [New record with dropped column]:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o2sr39_3LjW_290PInpHnA.png" /></figure><p>Bigquery table [City Column is dropped and latest record added with New structure]:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v01THZyBHWkwZ1i0kKS-FQ.png" /></figure><p>We have successfully installed installed and configured the source and sink connectors and have also replicated the data with seamless schema evolution.</p><h4>Useful Commands</h4><blockquote>Connector Registration Validation:</blockquote><blockquote>curl -k -H “Content-Type: application/json” <a href="https://ssh.cloud.google.com/devshell/proxy?authuser=0&amp;devshellProxyPath=%2Fconnectors%2F&amp;port=8083&amp;environment_name&amp;environment_id">http://localhost:8083/connectors/</a></blockquote><blockquote>[“sourcecloudsqlpg”,”targetbigquery&quot;]</blockquote><blockquote>List Kafka Topics</blockquote><blockquote>usr/bin/kafka-topics — list — zookeeper 10.128.0.14:2181</blockquote><blockquote>Read a kafka topic</blockquote><blockquote>/usr/bin/kafka-console-consumer — topic accounts — from-beginning — bootstrap-server 10.128.0.14:9092</blockquote><blockquote>Delete a connector from Debezium</blockquote><blockquote>curl -X DELETE <a href="http://localhost:8083/connectors/sourcecloudsqlpggg">http://localhost:8083/connectors/</a>&lt;connectorname&gt;</blockquote><p>We were able to successfully setup a data replication pipeline from CloudSQL PG to BigQuery. This data replication pipeline also seamlessly pushed the schema changes from the source database[CloudSQL PG] to the target database[BQ].</p><p>I see you when I see you , Happy Learning !</p><p>Saurabh !</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4da77cef67ab" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/near-real-time-data-replication-using-debezium-4da77cef67ab">Near Real Time Data Replication using Debezium</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Near Real Time Data Replication using Debezium]]></title>
            <link>https://medium.com/google-cloud/near-real-time-data-replication-using-debezium-fd32be6d192a?source=rss-2a8e05ac1cf7------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd32be6d192a</guid>
            <category><![CDATA[data-replication]]></category>
            <category><![CDATA[database-migration]]></category>
            <category><![CDATA[debezium]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Saurabh Gupta]]></dc:creator>
            <pubDate>Thu, 10 Feb 2022 19:09:06 GMT</pubDate>
            <atom:updated>2022-02-15T05:44:51.626Z</atom:updated>
            <content:encoded><![CDATA[<h4>Part 1 : <a href="https://medium.com/@saurabhgupt_85545/near-real-time-data-replication-using-debezium-fd32be6d192a">Introduction and Implementation</a><br>Part 2 : <a href="https://medium.com/@saurabhgupt_85545/near-real-time-data-replication-using-debezium-4da77cef67ab">A detailed guide to build a data replication pipeline from GCP CloudSQL Postgres to BigQuery</a></h4><h4><strong>What is Debezium ?</strong></h4><p>Debezium is an open source distributed platform for change data capture. Start it up, point it at your databases, and your apps can start responding to all of the inserts, updates, and deletes that other apps commit to your databases. Debezium is durable and fast, so your apps can respond quickly and never miss an event. Debezium continuously monitors your databases and lets any of your applications stream every row-level change in the same order they were committed to the database.</p><h4>What does it do ?</h4><p>Once configured , Debezium connects to the source database and takes a consistent snapshot of all or selective tables. After that snapshot is complete, the connector continuously captures row-level changes that insert, update, and delete database content that were committed to the source database. The connector generates data change event records and streams them to Kafka topics. For each table, the default behavior is that the connector streams all generated events to a separate Kafka topic for that table. Applications and services can consume data change event records from that topic.</p><h4>Benefits:</h4><blockquote>Cost effective<br>Support to replicate DDL statements*<br>Selective replication<br>Data replication among heterogeneous databases</blockquote><h4>UseCases:</h4><blockquote>AWS RDS to GCP CloudSQL continuous replication<br>Azure [MySQL Or Postgres] to GCP CloudSQL [MySQL Or Postgres] Replication<br>Cross Cloud DR Setup : Replication from RDS Postgres in Mumbai to CloudSQL Postgres in Delhi Region</blockquote><h3><strong>Architecture:</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zm1L4ncQt-mGRdFk5q1sWw.png" /></figure><h4><strong>Hardware Requirements</strong></h4><blockquote>4 GCE Instances to host:<br>Zookeeper<br>Kafka<br>Confluent platform with source and sink debezium connectors<br>Monitoring Setup with Grafana + Prometheus</blockquote><h4>Port Configuration</h4><p>The following ports must be open:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/848/1*0wQ4NPFS8SeE0gcw-FxSpQ.png" /></figure><h3><strong>Installation:</strong></h3><h4>Install JRE</h4><blockquote>sudo apt-get -y install default-jre</blockquote><h4>Install kafka &amp; Zookeeper using Confluent Kafka Platform</h4><p>Install the Confluent public key. This key is used to sign the packages in the APT repository.</p><blockquote>wget -qO — <a href="https://packages.confluent.io/deb/4.0/archive.key">https://packages.confluent.io/deb/4.0/archive.key</a> | sudo apt-key add -</blockquote><p>Add the repository to your /etc/apt/sources.list by running this command:</p><blockquote>sudo apt install software-properties-common<br>$ sudo add-apt-repository “deb [arch=amd64] <a href="https://packages.confluent.io/deb/4.0">https://packages.confluent.io/deb/4.0</a> stable main”</blockquote><p>Update apt-get and install the Confluent Platform platform.</p><blockquote>sudo apt-get update &amp;&amp; sudo apt-get install confluent-platform</blockquote><p>Confluent Platform using only Confluent Community components:</p><blockquote>$ sudo apt-get update &amp;&amp; sudo apt-get install confluent-platform-oss-2.11</blockquote><blockquote>sudo apt-get update &amp;&amp; sudo apt-get install confluent-hub-client confluent-common confluent-kafka-2.11</blockquote><h3><strong>Configuration</strong></h3><h4>Configure Zookeeper:</h4><p>Its recommended to implement ZooKeeper in a replicated mode. A minimum of three servers are needed for replicated mode, and you must have an odd number of servers for failover. For more information, see the <a href="https://zookeeper.apache.org/doc/current/zookeeperStarted.html#sc_RunningReplicatedZooKeeper">ZooKeeper documentation</a>.</p><p>Navigate to the ZooKeeper properties file (/etc/kafka/zookeeper.properties) file and modify as shown.</p><h4>Node 1: 10.128.0.14</h4><blockquote>tickTime=2000<br>dataDir=/var/lib/zookeeper/<br>clientPort=2181<br>initLimit=5<br>syncLimit=2<br>server.1=10.128.0.14:2888:3888<br>server.2=10.128.15.236:2888:3888<br>server.3=10.128.0.13:2888:3888<br>autopurge.snapRetainCount=3<br>autopurge.purgeInterval=24</blockquote><h4>Node 2: 10.128.15.236</h4><blockquote>tickTime=2000<br>dataDir=/var/lib/zookeeper/<br>clientPort=2181<br>initLimit=5<br>syncLimit=2<br>server.1=10.128.0.14:2888:3888<br>server.2=10.128.15.236:2888:3888<br>server.3=10.128.0.13:2888:3888<br>autopurge.snapRetainCount=3<br>autopurge.purgeInterval=24</blockquote><h4>Node 3: 10.128.0.13</h4><blockquote>tickTime=2000<br>dataDir=/var/lib/zookeeper/<br>clientPort=2181<br>initLimit=5<br>syncLimit=2<br>server.1=10.128.0.14:2888:3888<br>server.2=10.128.15.236:2888:3888<br>server.3=10.128.0.13:2888:3888<br>autopurge.snapRetainCount=3<br>autopurge.purgeInterval=24</blockquote><p>This configuration is for a three node ensemble. This configuration file should be identical across all nodes in the ensemble.</p><p>Navigate to the ZooKeeper log directory (e.g., /var/lib/zookeeper/) and create a file named myid. The myid file consists of a single line that contains the machine ID in the format &lt;machine-id&gt;. When the ZooKeeper server starts up, it knows which server it is by referencing the myid file. For example, server 1 will have a myid value of 1.</p><h4>Node 1: 10.128.0.14</h4><blockquote>echo “1” &gt; /var/lib/zookeeper/myid</blockquote><h4>Node 2: 10.128.15.236</h4><blockquote>echo “2” &gt; /var/lib/zookeeper/myid</blockquote><h4>Node 3: 10.128.0.13</h4><blockquote>echo “3” &gt; /var/lib/zookeeper/myid</blockquote><h4>Configure Kafka</h4><p>Navigate to the Apache Kafka properties file (/etc/kafka/server.properties) and customize the following:</p><h4>Node 1: 10.128.0.14</h4><blockquote>broker.id.generation.enable=true<br>delete.topic.enable=true<br>listeners=PLAINTEXT://:9092<br>zookeeper.connect=10.128.0.14:2181,10.128.15.236:2181,10.128.0.13:2181<br>log.dirs=/var/lib/kafka<br>log.retention.hours=168<br>num.partitions=1</blockquote><h4>Node 2: 10.128.15.236</h4><blockquote>broker.id.generation.enable=true<br>delete.topic.enable=true<br>listeners=PLAINTEXT://:9092<br>zookeeper.connect=10.128.0.14:2181,10.128.15.236:2181,10.128.0.13:2181<br>log.dirs=/var/lib/kafka<br>log.retention.hours=168<br>num.partitions=1</blockquote><h4>Node 3: 10.128.0.13</h4><blockquote>broker.id.generation.enable=true<br>delete.topic.enable=true<br>listeners=PLAINTEXT://:9092<br>zookeeper.connect=10.128.0.14:2181,10.128.15.236:2181,10.128.0.13:2181<br>log.dirs=/var/lib/kafka<br>log.retention.hours=168<br>num.partitions=1</blockquote><h3>Startup Confluent Platform</h3><h4>Enable and Start ZooKeeper</h4><blockquote>sudo systemctl enable confluent-zookeeper<br>sudo systemctl start confluent-zookeeper</blockquote><blockquote>sudo systemctl status confluent-zookeeper</blockquote><h4>Enable and Start Kafka</h4><blockquote>sudo systemctl start confluent-kafka<br>sudo systemctl enable confluent-kafka</blockquote><h4>Enable and Start Schema Registry</h4><blockquote>sudo systemctl start confluent-schema-registry<br>sudo systemctl enable confluent-schema-registry</blockquote><h4>Validate the services are up and running</h4><blockquote>sudo systemctl status confluent-zookeeper</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SY8rg1ya6fWLP9DWH5rg0A.png" /></figure><blockquote>sudo systemctl status confluent-kafka</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MuWnne9AYd7Kdgg9HQlAAQ.png" /></figure><blockquote>sudo systemctl status confluent-schema-registry</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9_bAtTjgeCNVreAxdqEgQA.png" /></figure><h4>Configure Debezium service in Distributed mode</h4><p>Navigate to the following file (/etc/kafka/connect-distributed.properties) and customize the following:</p><p>— On all three nodes:</p><blockquote>bootstrap.servers=10.128.0.14:9092,10.128.15.236:9092,10.128.0.13:9092<br>group.id=connect-cluster<br>plugin.path=/usr/share/java,/usr/share/confluent-hub-components</blockquote><h4>Manually Startup the Debezium service:</h4><blockquote>/usr/bin/connect-distributed /etc/kafka/connect-distributed.properties &amp;</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mO46leZ6uvYcJQLjDR3YNw.png" /></figure><p>We have successfully installed debezium in a distributed mode on 3 nodes.</p><p><a href="https://medium.com/@saurabhgupt_85545/near-real-time-data-replication-using-debezium-4da77cef67ab"><strong>Part2</strong></a> A detailed guide to build a data replication pipeline from GCP CloudSQL Postgres to BigQuery</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*v5epjUO8cu8bowts_6vgQg.png" /></figure><h4>References:</h4><blockquote><a href="https://docs.confluent.io/4.0.3/installation/installing_cp.html">Install Confluent Platform</a></blockquote><p>Till next time , Happy Learning !</p><p>Saurabh !</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd32be6d192a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/near-real-time-data-replication-using-debezium-fd32be6d192a">Near Real Time Data Replication using Debezium</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>