PostgreSQL on Google Cloud SQL to MySQL Data Migration: 2 Easy Methods

MySQL has remained the most popularly used open-source relational database for many years and continues to maintain its dominant position in the industry. Its robustness, reliability, and flexibility for a wide range of applications, from small-scale projects to vast enterprise systems, justifies its widespread adoption. Migrating data from PostgreSQL on Google Cloud SQL to MySQL can serve as a strategic solution for high availability and analytics workflows. 

This provides an efficient or safer backup for your data. It will also help optimize database compatibility to ensure a wider interconnected database environment within the organization.

This article lists the different methods to migrate data between both these platforms. While each one has its benefits and challenges, you can make an informed decision as to which is better suited for your data needs.

Methods to Connect PostgreSQL on Google Cloud SQL to MySQL

There are two simple methods that you can use to migrate data from Google Cloud PostgreSQL to MySQL. Let’s look into the details of both approaches.

Method 1: Use Custom Scripts for PostgreSQL on Google Cloud SQL to MySQL Migration

This method involves exporting data from Google Cloud PostgreSQL as SQL dump files and then importing the data to MySQL. Here are the steps involved in this process:

Step 1: Export Data from PostgreSQL on Google Cloud SQL

To export Google Cloud PostgreSQL data as SQL dump files, you can use one of the following methods:

Use Google Cloud Console

When you use the Google Cloud Console to export, the exported data will be stored in a GCS bucket. Before you proceed with this step, you require one of the following sets of roles:

• The Cloud SQL Editor role and the storage.objectAdmin IAM role.

• A custom role including these permissions:
  • cloudsql.instances.export
  • cloudsql.instances.get
  • storage.buckets.create
  • storage.objects.create

Here are the steps involved in this method:

• Log in to your Google Cloud account.

• Navigate to the Google Cloud Console > Cloud SQL Instances page.

• Click on an instance name to open the Overview page of the instance.

• Click on Export.

• For File format, click on SQL to create a SQL dump file.

• Within Data to export, use the drop-down menu and select the database you want to export from.

• For Destination, select Browse and search for a Cloud Storage bucket or folder for the export.

• Click on Export to begin the export.

Use the pg_dump command

The pg_dump command is a useful option to export your PostgreSQL database. Use the pg_dump utility to export a single PostgreSQL database and the pg_dumpall utility to export all PostgreSQL databases of a cluster.

Before you execute the command, ensure that:

• You have the username and password of the PostgreSQL database.
• The PostgreSQL instance is configured to allow access from the machine where you’re executing pg_dump.
• You have the necessary permissions to dump the database.

Run the pg_dump command from Google Cloud Shell or gcloud CLI. Here’s the format for the command:

pg_dump -h PostgreSQL_hostname -U PostgreSQL_user -d PostgreSQL_database -f /path/to/Output_filename.sql

This command uses your Google Cloud PostgreSQL instance’s IP address or hostname, user name, and database name. The output will be an SQL dump file with the provided output filename.

Step 2: Transform the Data (If Needed)

There are syntactical differences between a PostgreSQL database and a MySQL database. So, there may be PostgreSQL-specific SQL commands in the dump that may not be compatible with MySQL. You might need to modify the SQL dump file to remove such commands.

Consider using tools or scripts that are available online to assist with this transformation process.

Step 3: Load the Data into MySQL

Ensure you have a MySQL database to import the PostgreSQL data into. If you don’t already have one, run the following command in the mysql command-line client or any MySQL client tool:

CREATE DATABASE MySQL_database;
USE MySQL_database;

Now, import the SQL dump file into your MySQL database with the following command:

mysql -h MySQL_hostname -u MySQL_user -p MySQL_database < Output_filename.sql

This command uses your MySQL server’s IP address, user name, database name, and name of your SQL dump file. Upon execution of the command, your Google Cloud PostgreSQL data will be loaded into your MySQL database.

There are some benefits associated with using the custom script method for PostgreSQL on Google Cloud SQL to MySQL migration:

• When using the Google Cloud Console method to export data, the data is stored in an intermediary storage solution—GCS buckets—which serves as a buffer. This provides a backup storage. If the data loading process to MySQL encounters any issues, you won’t witness a data loss.

• This method is well-suited for one-time or infrequent data transfers, especially of smaller datasets. The associated latencies of the data migration process won’t significantly impact the operations.

• Using custom scripts and built-in utilities is a cost-effective solution, especially for occasional or one-time tasks, as it includes manual intervention. 

Despite the benefits, this method also has the following limitations:

• When you export data using the Google Cloud Console, the process can consume a significant amount of memory if your data contains large objects. This will impact the performance of the PostgreSQL instance.

• Using GCS storage (in the Google Cloud Console export method) can lead to additional costs.

• Migrating data between the two platforms with custom scripts is an effort-intensive process for large-scale and frequent data migrations.

• Exporting data from Google Cloud PostgreSQL as SQL dump files, performing data transformations, and loading these files into a MySQL database involve considerable time. This prevents real-time or near-real-time data updates in MySQL, resulting in non-availability of up-to-the-second data for critical analysis.

Method 2: Use No-Code Tools to Load Data from PostgreSQL on Google Cloud SQL To MySQL

No-code tools can help overcome the limitations associated with the previous method. Such tools offer a range of benefits, including:

• Real-time Data Transfers: Most no-code tools offer the capability for real-time data synchronization, which helps eliminate delays and ensures that all systems have current and consistent data.

• Scheduling and Automation: No-code tools typically provide users with the option to set schedules for migrations, especially for recurrent data transfers. This makes it easier to keep systems in sync.

• Fully Managed: No-code tools are usually fully managed, with the service providers handling all the updates, backend infrastructure, and security patches. This provides you with a hassle-free experience without having to worry about system maintenance or infrastructure issues.

• Pre-built Connectors: Most no-code tools have pre-built connectors for popular data sources and destinations. These connectors simplify the process of connecting two platforms for data migration, eliminating the need for excessive manual efforts.

• Error Reduction: No-code tools, with in-built connectors and automation capabilities, minimize the chances of human errors, which are common in manual data migrations.

Hevo Data, a fully automated no-code data integration tool, can replicate data from 150+ sources and 15+ destinations. You can load data from PostgreSQL on Google Cloud SQL to MySQL in near-real time with our fully-automated platform. Here are the steps you need to follow to integrate Google Cloud PostgreSQL and MySQL:

Step 1: Configure PostgreSQL on Google Cloud SQL as the Data Source

Step 2: Configure MySQL as the Data Destination

With just two simple steps, Hevo Data simplifies the process of setting up a PostgreSQL on Google Cloud SQL to MySQL ETL pipeline. Here are some other impressive features of Hevo:

• Auto Schema Mapping: Hevo takes away the tedious task of schema management. It automatically maps the schema of the incoming data to match that of the destination. But, you have the flexibility to do it manually, if needed.

• Recoverability: Hevo provides quick support to recover from any issues in the data migration process. If there is an issue at the source end of the data migration pipeline, Hevo keeps re-trying the data ingestion. Similarly, if the destination reports a problem, Hevo re-attempts the data load, ensuring no records are lost.

• Easy Visibility into the Entire Process: Hevo offers various graphs, metrics, and visual UI signals for providing visibility to identify any issues in the process, provide the status of the data replication progress, and more.

• Security: Hevo offers end-to-end encryption in its data integration process. It is SOC II, GDPR, and HIPAA compliant.

• Built to Scale: Hevo’s architecture functions with zero data loss and minimal latency. As the number of sources and data volumes grow, Hevo’s pipelines scale horizontally.

• Transformations: Hevo provides Python code-based and Drag-and-Drop transformations for transforming data before loading it to the warehouse. You can also use Hevo’s Postload transformation capabilities for data loaded in the warehouse.

What Can You Achieve with PostgreSQL on Google Cloud SQL MySQL Integration?

When you integrate Google Cloud PostgreSQL to MySQL, you can find answers to several questions, such as:

• Who are your primary customers in terms of gender, age, location, and other demographic parameters?
• What are the most commonly received feedback or complaints?
• What is the average lifetime value of customers? How does it differ across different customer segments?
• How sensitive are customers to changes in pricing?
• Which marketing methods are most effective?

Conclusion

A PostgreSQL on Google Cloud SQL to MySQL integration provides you with actionable insights for optimizing performance and streamlining operations. This will help your business adapt well to the ever-evolving digital landscape and gain a competitive edge.

There are two methods to move data between both platforms. The first method involves using custom scripts to export data from Google Cloud PostgreSQL and then loading the data into MySQL. This method is associated with limitations, such as being effort-intensive, time-consuming, and lacking real-time capabilities.

You can overcome these limitations by using Hevo Data—a fully managed no-code tool—to build near-real-time data migration pipelines. Its range of readily available connectors and transformations helps simplify the process of data migration between any source and destination.

In case you want to integrate data into your desired Database/destination, then Hevo Data is the right choice for you! It will help simplify the ETL and management process of both the data sources and the data destinations. 

Visit our Website to Explore Hevo

Offering 150+ plug-and-play integrations and saving countless hours of manual data cleaning & standardizing, Hevo Data also offers in-built pre-load data transformations that get it done in minutes via a simple drag-and-drop interface or your custom python scripts. 

Want to take Hevo Data for a ride? SIGN UP for a 14-day free trial and experience the feature-rich Hevo suite first hand. Check out the pricing details to understand which plan fulfills all your business needs