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.131.133.218

  • 34.93.42.224

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;

  
  

Note: The retention period should be at least 72 hours to avoid missing any log files during historical data loading.

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.131.133.218, 34.93.42.224

  
  

• 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;

  
  

Note: Replace <database_name> and <schema_name> with the actual database and schema names.

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:

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.