Set Up MySQL Integration with Edilitics

MySQL is a leading open-source relational database management system (RDBMS) known for its high performance, reliability, and ease of use. It powers numerous web applications and offers robust features for managing data efficiently across businesses of all sizes. Whether dealing with large datasets or high-traffic applications, MySQL provides the scalability and speed required. To explore more about MySQL, visit the official MySQL website.

Within the Edilitics platform, MySQL 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's strengths for data storage and retrieval within your Edilitics environment.

This step-by-step guide equips you with the knowledge to configure your MySQL Database instance and integrate it with Edilitics for data analysis.

Before You Begin

Make sure you have the following:

• The IP address and hostname for your MySQL server.
• MySQL version 5.5 or higher.
• Binary logging enabled if using BinLog replication.
• Edilitics IP addresses whitelisted on your MySQL platform:
  • 34.14.190.234
  • 34.14.190.234

Setting Up MySQL for Edilitics Integration

Step 1: Enable Binary Log Replication

Verify BinLog Status

Access the MySQL database to check if BinLog is active:

mysql -h hostname -u user -p database

Open a secure shell:

ssh user@localhost

Enter the command:

SELECT @@log_bin;

If the value returned is 1, BinLog is active. If 0, follow the steps below to enable it.

Enable BinLog Replication

Log in to your MySQL Server and edit the MySQL configuration file:

sudo nano /etc/mysql/my.cnf

(In some cases, the path may be /etc/my.cnf)

Add or verify the following configurations:

[mysqld]
binlog_format=ROW
binlog_row_image=FULL
expire_logs_days=3
log_bin=mysql-binlog
server-id=1
log_slave_updates=1
max_allowed_packet=16777216

Restart your MySQL Server and confirm BinLog activation:

SELECT @@log_bin;

Step 2: Whitelist Edilitics’ IP Addresses

Grant Edilitics access to your MySQL data port to ensure a real-time connection. Edit the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add Bind Address:

bind-address = 0.0.0.0

or specific IPs:

bind-address = 34.14.190.234, 34.14.190.234

Save the configuration.

Step 3: Create a Database User (Optional)

Create a database user (replace <password> with your desired password):

CREATE USER edilitics_user WITH PASSWORD '<password>';

Grant the necessary permissions to the user:

GRANT CONNECT ON DATABASE <database_name> TO edilitics_user;
GRANT USAGE ON SCHEMA <schema_name> TO edilitics_user;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO edilitics_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO edilitics_user;

Connecting MySQL to Edilitics

Step 4: Add the MySQL Connector in Edilitics

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

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

Step 5: Configure the MySQL 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:

mysql://<username>:<password>@<host>:<port>/<database_name>

Step 6: 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 7: 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.

Need help? Email support@edilitics.com with your workspace, job ID, and context. We reply within one business day.