Google Cloud MySQL Integration with Edilitics

Leveraging MySQL for Google Cloud Platform (GCP) grants you the benefits of a familiar MySQL experience with the added advantages of scalability, high availability, and automated management offered by GCP. This empowers you to migrate existing MySQL workloads or establish new databases with regional deployments for optimal performance. Edilitics, a powerful data analytics platform, integrates effortlessly with your MySQL GCP database, unlocking valuable insights from your data for informed decision-making.

Within the Edilitics platform, MySQL GCP can be used both as a source and destination for data, making it a versatile tool for data analysis workflows. This enables you to leverage MySQL GCP's strengths for data storage and retrieval within your Edilitics environment.

This step-by-step guide equips you with the knowledge to configure your Google Cloud MySQL instance and integrate it with Edilitics for data analysis. To learn more about Google Cloud MySQL, visit the official Google Cloud MySQL website.

Before You Begin#

Make sure you have the following:

• A functional Google Cloud MySQL instance (version 5.6 or higher).

• Binary Log (BinLog) replication enabled if using Change Data Capture (CDC) in Edilitics Replication.

• Edilitics IP addresses whitelisted on your MySQL platform:

  • 34.131.133.218

  • 34.93.42.224

Setting Up MySQL on GCP for Edilitics Integration#

Step 1: Enable Binary Log Replication#

Binary logs act as chronological records of data modifications within your MySQL instance. Edilitics leverages BinLog replication for efficient data synchronization.

• Access your Google Cloud MySQL instance through the Google Cloud Console.

• In the left navigation pane, under Databases, locate and select Backups.

• If Automated backups are disabled, follow these steps to enable BinLog replication:

  • Activate Automate backups and Enable point-in-time recovery options.

  • Save changes to confirm.

• Verify that the Automated backups status now reflects Enabled.

Step 2: Whitelist Edilitics’ IP Addresses#

To establish a connection, Edilitics requires whitelisting of its IP addresses for your specific region within Google Cloud.

• Access the Google Cloud SQL Instances page and select the desired instance ID.

• In the left navigation pane, choose Connections.

• Navigate to the Networking tab and activate the Public IP checkbox.

• Click ADD A NETWORK and provide the following details:

  • Name: Edilitics_1

  • Network: 34.131.133.218

• Click Done and repeat for the second IP:

  • Name: Edilitics_2

  • Network: 34.93.42.224

• Click Save to finalize the network configuration.

Step 3: Create a Database User and Grant Privileges#

MySQL allows you to connect to Edilitics using an existing user or a new user configured within your Google Cloud MySQL instance.

Option 1: Using Google Cloud Console#

• Click on your primary Google Cloud MySQL instance.

• In the left navigation pane, under Primary Instance, select Users, and then click + Add User Account.

• Choose the authentication method, host name, and ensure a strong password is selected for the Edilitics user.

• Click ADD.

Option 2: Using MySQL Client#

• Connect to your Google Cloud MySQL database as a root user with an SQL client tool, such as MySQL Workbench.

• Create a database user:

  
  

  CREATE USER 'edilitics_user'@'%' IDENTIFIED BY '<password>';

  
  

• Grant SELECT, REPLICATION CLIENT, and REPLICATION SLAVE privileges to the user:

  
  

  GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'edilitics_user'@'%';

  
  

Connecting Google Cloud MySQL to Edilitics#

Step 4: Retrieve the Configuration Details#

The following information is required to configure the MySQL GCP connector in Edilitics:

• Hostname: Retrieved from the Google Cloud SQL Instances page under the Public IP address column for the master instance (or replica instance, depending on your setup).

• Port: The port number used by your MySQL server (usually the default 3306).

• Username and Password: Refer to the user creation step above.

• Database Names: Accessible directly within the Google Cloud Console by navigating to the Databases tab in your MySQL instance.

Step 5: Add the MySQL GCP Connector in Edilitics#

• In Edilitics, navigate to the integrations module and locate the New Integration option.

• Choose the MySQL GCP connector from the list or use the search bar to find it.

Step 6: Configure the MySQL GCP Connection#

To connect, enter the following information on the next screen:

Alternatively, you can connect using a connection string. Replace the placeholders with your details:

Step 7: Test the Connection#

Once you've entered your credentials, click the Test Connection button. Edilitics will attempt to connect to your MySQL database. You'll see a confirmation message if the connection is successful. If there's a problem, Edilitics will provide details to help you troubleshoot.

Step 8: Verify & Finalize Integration#

Upon successful database connection, you can:

• Preview (Optional): Confirm accuracy by retrieving a sample of tables and data.

• Update (Optional): If the preview deviates from expectations, modify connection details for correct data retrieval.

• Save: Finalize by encrypting and storing connection details within Edilitics for future use.