GCP PostgreSQL MySQL Connection: 6 Easy Steps

Businesses are updating their IT infrastructure with managed cloud services. They want to take advantage of the reliability, safety, and price-effectiveness of fully managed cloud databases like Cloud SQL.

Using Cloud SQL for popular relational databases like PostgreSQL and MySQL is an efficient hassle-free database management solution. You can also set up the GCP PostgreSQL MySQL Connection using Google Cloud’s Database Migration Service (DMS) supporting MySQL and PostgreSQL migrations from on-premises and other clouds to Cloud SQL. 

In this article, you will learn how to effectively set up the GCP PostgreSQL MySQL Connection in 6 easy steps.

What is PostgreSQL?

PostgreSQL, also known as Postgres, is a robust open-source relational database that employs and extends the SQL language and incorporates numerous features to store and scale the most complex data workloads safely. It dates back to 1986 and has started as part of the POSTGRES project at the University of California at Berkeley; PostgreSQL currently holds an active and growing ecosystem of developers and tools. 

An enterprise-class database, Postgre works on the most widespread operating systems – such as Linux and Unix distributions, Windows, Mac OS X.SQL. PostgreSQL enables its users to manage data, regardless of its extensive database.  

One major difference between PostgreSQL and standard relational database systems that make it highly extensible is that PostgreSQL stores vast amounts of data in its catalogs. It keeps the information about data types, functions, access methods, etc.

Key Features of PostgreSQL

PostgreSQL has become one of the most sought-after Databases Management Systems due to the following eye-catching features:

• Enhanced Data Integrity: PostgreSQL assures Data Integrity via Primary Keys, Foreign Keys, Explicit Locks, Advisory Locks, & Exclusion Constraints.
• Multiple Data Types: PostgreSQL allows you to work with a broad range of datasets. It is compatible with several data types such as INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, TIMESTAMP, etc.
• Data Security: You can rest assured with several layers of Data Authentication and Protection layers. It provides different authentications such as Lightweight Directory Access Protocol(LDAP), Generic Security Service Application Program Interface (GSSAPI), SCRAM-SHA-256, and Security Support Provider Interface (SSPI), etc. It also provides a robust Access Control System along with Column & Row-level security. 
• Reliability: PostgreSQL provides a reliable environment with special features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, Asynchronous Replication, online/hot backups, and write-ahead logging.

What is MySQL?

MySQL is a freely available open-source Relational Database Management System (RDBMS) that is based on Structured Query Language (SQL). It has been developed and is supported by the Oracle Corporation. It is typically used to create and manage web databases, however, its application has a wide range of purposes, including data warehousing, e-commerce, and logging applications.

MySQL is ideal for both small and large applications as it can store anything from single records to a large inventory of products. It is very fast, reliable and delivers efficient features to meet its users’ requirements, and holds a user-friendly interface compared to other software, making it popular.

Key Features of MySQL

MySQL is widely used by organizations like Facebook, Flickr, Twitter, Wikipedia, and YouTube. This is due to the following set of MySQL’s eye-catching features: 

• Ease of Operation: With MySQL, you can work with a diverse set of programming languages ​​such as PHP, PERL, C, C ++, and JAVA. It also provides a set of intuitive tools to simplify time-consuming tasks such as Server Management, Reporting, and Data Analysis. Allowing you to work with a broader range of datasets, MySQL offers full support for several Data structures, JSON and Geospatial data, as well as Logical, Numeric, Alphanumeric, Date, and Time data types.
• First-rate Performance: MySQL delivers best-in-class query performance across a variety of clustered servers. It provides a fast-loading utility with a separate memory cache and table index partitioning. MySQL can also efficiently handle fluctuating workloads and process large volumes of data at optimal speeds. This allows you to store more than 50 million rows of data in a table with a table’s default file size limit of 4 GB. However, depending on the configuration of the operating system, this can be increased to a theoretical limit of 8 million terabytes (TB). 
• Open-Source: MySQL is under the GNU General Public License (GPL), which means it will always be freely available. It provides you the freedom to customize it on Oracle’s Open-Source MySQL codebase based on your needs. Since it is an Open-Source software, a large community has grown that regularly enriches MySQL’s documentation and its online support culture.
• Localization: To make it user-friendly, MySQL supports multiple character sets, including latin1 (cp1252), german, big5, ujis, and more. You can also set the error message language that the server provides to the client. The MySQL Server Time can also be dynamically changed with the ability to set specific time zones for individual clients.
• Data Security: MySQL provides you full control over the Data Access Control settings. Robust mechanisms such as Access Privilege Systems and User Account Management allow you to regulate who can view or use your MySQL data. MySQL sets the highest standard with Host-based Verification and Password Encryption.

Prerequisites

• You need to have a Google Cloud account. If you don’t have one, you will need to have one created. 
• Open the project selector page from the Google Cloud Console and either select or create a Google Cloud project.
• Ensure that billing is enabled for your Cloud project. Even if you’re using the Free option, billing must be enabled for the project, or you might encounter connection issues. 
• Enable the Database Migration Service API. Refer to Google’s documentation for more info.
• Furthermore, you will need to have the Database Migration Admin role assigned to your user account.

How to set up the GCP PostgreSQL MySQL Connection?

There are multiple ways to for setting up the GCP PostgreSQL MySQL Connection. Here are a few options:

• Using Google Cloud’s Database Migration Service (DMS)
• using Foreign Data Wrapper, 
• using Federated Tables, 
• exporting, 
• using CSV files, 
• using pgLoader

Google Cloud’s Database Migration Service (DMS) can be used for GCP PostgreSQL MySQL Connection from on-premises and other clouds to Cloud SQL.

Cloud SQL for PostgreSQL assures that your databases are dependable, safe, and scalable so your company can keep working without disruption. Flexible instance shapes will enable you to optimize the compute, storage, and memory ratios for every deployment. 

The underlying Google Cloud infrastructure is highly optimized for predictable, high-performance operations such as storage-based HA. Developers can focus on shipping features in your applications with seamless integrations to Google Kubernetes Engine and dozens of other Google Cloud services. 

Strong security and enhanced compliance controls have been put in place. In addition, it offers automated backups, capacity increases, replication, encryption patches, and 99.95% availability so you can easily scale if needed.

Here are the steps you’ll need to take to set up the GCP PostgreSQL MySQL Connection using Google Cloud’s Database Migration Service: 

• GCP PostgreSQL MySQL Connection Step 1: Set up a Connection Profile
• GCP PostgreSQL MySQL Connection Step 2: Create a Migration Job
• GCP PostgreSQL MySQL Connection Step 3: Set up Connectivity
• GCP PostgreSQL MySQL Connection Step 4: Test your Migration Job
• GCP PostgreSQL MySQL Connection Step 5: Start the Migration
• GCP PostgreSQL MySQL Connection Step 6: Promote Cloud SQL for MySQL 

GCP PostgreSQL MySQL Connection Step 1: Set up a Connection Profile

The completion of a GCP PostgreSQL MySQL connection profile will create a record that collects information related to the source database. DMS will make use of this info to move data from your GCP PostgreSQL database to the Cloud SQL for MySQL database instance.

• Step 1: Open the Database Migration Service Connection profile page in the Google Cloud Console and click on the CREATE PROFILE button.
• Step 2: Open the Source database engine drop-down list and choose the classification type of your source database. In our case, select the A standalone MySQL instance (MySQL) option.
• Step 3: Provide the following details:
  • Under the Connection profile name field, provide a name for the connection profile for your source database.
  • Keep the auto-generated Connection profile ID.
  • Type the Connectivity info:
    • In case you are replicating from a self-hosted database, you will have to supply the Hostname or IP address, as well as the Port, to access the host. (MySQL’s default port is 3306.)
    • In case you’re replicating from a Cloud SQL database, just pick the Cloud SQL instance from the drop-down list.
  • Provide the Username and Password so you can connect to the source instance.
  • (Optional) In case you are looking to move sensitive data over a public network (by using IP allowlists), it is highly recommended to use SSL/TLS encryption; otherwise, you can just maintain the default value of None.
• Step 4: Under the Connection profile region section of the page, choose the region where you like to have your connection profile saved and click on the CREATE button.

GCP PostgreSQL MySQL Connection Step 2: Create a Migration Job

• Step 1: In the Google Cloud Console, open the Database Migration Service Migration jobs page. 
• Step 2: Click on CREATE MIGRATION JOB, and in the Migration job name field, write a name for the job.
• Step 3: Maintain the auto-generated Migration job ID.
• Step 4: From the Source database engine drop-down list, select PostgreSQL.
• Step 5: Determine the Region where the destination instance will be initiated.
• Step 6: The Migration job type needs to be set to “Continuous” if you would like the changes in your source database to also be reflected in the destination Cloud SQL for the MySQL database instance.
• Step 7: Go over the list of mandatory prerequisites that have automatically been generated. It’s best to review these prerequisites during this stage; however, you can complete them any time before you start the migration job.
• Step 8: Just click the SAVE & CONTINUE button to move to the next step.

Provide the details related to the source connection profile

• Step 1: Click on the Select source connection profile, and a drop-down list will open. Choose the connection profile that you set up.
• Step 2: Click SAVE & CONTINUE.

Specify the settings of the destination Cloud SQL for MySQL instance

• Step 1: Supply a Destination Instance ID for the Cloud SQL for MySQL instance. You have the option to keep the name that has been automatically allocated or you can enter another name.
• Step 2: Choose a password for the destination Cloud SQL for the MySQL instance. The new setup password will also be the one for the root administrator account. You can have a password generated automatically or manually enter one.
• Step 3: Open the list of supported Cloud SQL versions and choose the Database version.
• Step 4: Determine the desired Google Cloud Zone for the new Cloud SQL for MySQL instance (alternatively, you can also leave it as “Any”).
• Step 5: Check the Public IP box because, for this quickstart, you’re using IP allowlist as the networking method. 
• Step 6: Pick the Machine type for the Cloud SQL for MySQL instance. Ensure that there is enough space on the disk for the migration. 
• Step 7: Specify both the Storage type and Storage capacity for the Cloud SQL for MySQL instance.
• Step 8: Click CREATE & CONTINUE to institute a new instance., the, in the subsequent dialog box. Click on the CREATE DESTINATION & CONTINUE 

GCP PostgreSQL MySQL Connection Step 3: Set up Connectivity

During this GCP PostgreSQL MySQL Connection step, you will need to choose the networking method that you’ll use to connect the source and destination databases. 

• Step 1: Click on the Connectivity method to open a drop-down list and choose the IP allowlist as the networking method.
• Step 2: In case the IP address isn’t already pre-populated, you will need to copy the Cloud SQL for MySQL’s outgoing IP address and use it to set up the network firewall. Skipping this step will prevent the source database server from accepting connections from this IP address.
• Step 3: Under the firewall rules, you will need to set up an Inbound rule that will permit connections on port 3306 coming from the destination Cloud SQL for MySQL  IP address.
• Step 4: Click on the CONFIGURE & CONTINUE button to finish the connection profile setup.

GCP PostgreSQL MySQL Connection Step 4: Test your Migration Job 

• Step 1: Click TEST JOB to check whether the migration job is valid. If it was successful, you should get a “Tests passed successfully!” notification.
• Step 2: In case the test did not pass, you can address the problem and return to re-test.

GCP PostgreSQL MySQL Connection Step 5: Start the Migration 

• Step 1: Click on the CREATE & START JOB button and click START to start the migration.
• Step 2: Under the Migration jobs page, you can check the status of your migration job.
• Step 3: Confirm the migration job. 
• Step 4: During this stage, you will ensure that Database Migration Service used the migration job to move data from the source database instance to the destination Cloud SQL for MySQL database instance.
• Step 5: Proceed to the SQL Instances page in the Google Cloud Console and click the read replica of the migration task..
• Step 6: In the upper-right corner of the page, there is an Activate Cloud Shell icon. Just click on it 
• Step 7: Next, a Google Cloud Shell will be prompted, and you have to click on Enter.
• Step 8: Connect to the destination Cloud SQL database instance for MySQL. 
• Step 9: An enter password page will be prompted during the next step. Here, you will have to enter the password you set up earlier or the one automatically generated.
• Step 10: At the MySQL prompt, ensure that you will
  • Enter show databases; to get a list of the databases and confirm if the source database instance is listed
  • Use the source_database_instance_name; as you will want to be able to see the tables related to this database instance.
  • Enter show tables; to review the tables of this instance.
  • Enter select * from table_name; to go over the information that’s replicated in the source database instance.
• Step 11: Next, check that the information in the table is correct, which confirms that the DMS has migrated the data.
• Step 12: When the replication delay moves toward zero, you can stop all writes.

GCP PostgreSQL MySQL Connection Step 6: Promote Cloud SQLfor MySQL  

In this GCP PostgreSQL MySQL Connection step, Access the Navigation menu from the Google Cloud Console and click on Database Migration, then Migration jobs.

• Step 1: Click the migration job name to view the details page.
• Step 2: Next click the Promote button. When the promotion is done, the status of the job will be changed to Completed. This completes the GCP PostgreSQL MySQL Connection process.

Conclusion

In this article, you have learned what is PostgreSQL, and MySQL, and how to effectively set up the GCP PostgreSQL MySQL Connection in 6 easy steps. It talks about using the Database Migration Service (DMS), a serverless tool that takes care of the underlying server infrastructure provisioning and operation during data migration. DMS opens possibilities and offers reduced downtime for customers, as well as a secure and cost-effective solution for setting up the GCP PostgreSQL MySQL Connection.

As you collect and manage your data across several applications and databases in your business, it is important to consolidate it for a complete performance analysis of your business. However, it is a time-consuming and resource-intensive task to monitor the Data Connectors continuously. To achieve this efficiently, you need to assign a portion of your engineering bandwidth to Integrate data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-based ETL tool such as Hevo Data.   

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 100+ sources like PostgreSQL, and Google Cloud SQL for PostgreSQL to a Data Warehouse, Databases like MySQL, or a Destination of your choice. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

If you are using CRMs, Sales, HR, and Marketing applications and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 100+ sources(Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of setting up the GCP PostgreSQL MySQL Connection! Share your thoughts with us in the comments section below.