mysql
Tech

A Guide to Integrate MySQL with Other Tools

Hrvoje Š.
Hrvoje Š.
8 Min Read

Very often, it’s essential to connect databases to other applications or cloud tools for better productivity and operativity. Integrating MySQL with other tools often involves connectors, APIs, or middleware. The latter option incredibly facilitates connection and communication between the database and the preferred tool. Most use cases, including Salesforce MySQL integration, presented in this article rely on middleware for connection and data exchange.

This article focuses on popular integration scenarios involving the MySQL database. In particular, you’ll learn how to bring Salesforce and MySQL together and discover the benefits of such integration. Moreover, this article explains how to connect MySQL with other tools and what benefits businesses can obtain from that.

MySQL and Salesforce Integration

Bringing together MySQL database and Salesforce might be necessary for a number of reasons, starting from customer profile enrichment to data consolidation. There are multiple different ways and methods to connect MySQL to Salesforce, in fact. However, we’d like to present the Skyvia data integration tool for building data pipelines between Salesforce and MySQL.

Skyvia is a universal data platform designed not only for data integration but also for a variety of data-related tasks. However, in this article, we focus on its Data Integration product as it’s designed for connecting different tools and data exchange between those.

With Skyvia’s Data Integration, you can:

  • Import data from MySQL to Salesforce or vice versa.
  • Export Salesforce data into a CSV file and upload it to other apps.
  • Synchronize MySQL database with Salesforce tables.
  • Replicate Salesforce data to MySQL for data storage and consolidation.
  • Build complex data integration pipelines and scenarios.

Skyvia has a range of benefits:

  • Powerful data transformation options.
  • Data mapping settings.
  • No-code interface.
  • 170+ supported connectors (cloud apps, databases, and data warehouses).
  • Suitable plans for any kind of company.

Now, let’s have a look at a concrete example of how to establish the connection between MySQL and Salesforce using Skyvia.

  1. Log into your Skyvia account or create a new one.
  2. Go to Create New -> Import in the top menu.
  3. Select MySQL as a source and Salesforce as a target.
  4. Click Add task to configure import integration.
  5. Specify the object to transfer and define the DML operation.
  6. Set the mapping between MySQL and Salesforce, and click Save.
  7. Create the task and click Run to start the integration immediately. Otherwise, indicate scheduling parameters to start integration at a definite point in time.

You can use such integration to transfer data from MySQL about purchases on the website to the corresponding customer profile on Salesforce, for instance. There are many other use cases where the import scenario works best. You can also use other integration tools of Skyvia to perform the needed operations.

Connecting MySQL to BI Tools

Given the popularity of MySQL database due to its numerous features, many popular tools have native connectors to it. Especially, this concerns the BI and analytics tools, such as Tableau, Looker, and Power BI. While the first two options have built-in connectors, it’s possible to establish the connection between a BI tool and MySQL server in several clicks. Meanwhile, Power BI needs additional solutions for connecting and data exchanging with MySQL.

Below, find the step-by-step instructions for integrating Power BI and MySQL.

First steps

  1. Make sure that the Power BI and the MySQL ODBC driver are installed on the same computer.
  2. Check whether the MySQL server is configured correctly for remote connections.
  3. In the MySQL configuration file (my.cnf or my.ini), find the bind-address directive and set it to 0.0.0.0 to allow connections from any IP address.
  4. Restart MySQL server to apply changes.
  5. Create a dedicated MySQL user for Power BI with the necessary permissions:
CREATE USER 'powerbi_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON your_database_name.* TO 'powerbi_user'@'%';
FLUSH PRIVILEGES;

Configure ODBC Data Source

  1. Open ODBC Data Source Administrator on your computer.
  2. Go to the User DSN tab and click Add.
  3. Select MySQL ODBC Driver from the list and click Finish.
  4. Configure the ODBC connection by indicating the data source name, TCP/IP server, port, users, and password.
  5. Click Test to ensure that the connection was established successfully.
  6. Click OK to save the ODBC data source.

Connect Power BI to MySQL

  1. In Power BI Desktop, go to Home > Get Data > More from the top menu.
  2. In the Get Data window, select ODBC and click Connect.
  3. Choose the ODBC data source you created for MySQL from the list and click OK.
  4. Enter the MySQL user credentials when prompted and click Connect.
  5. In the Navigator window, select the database and tables you want to import.
  6. Click Load or Transform Data to start loading the data into Power BI.

Integrating MySQL Database in Custom Applications

If you’re developing your custom application for desktop or mobile, you’ll definitely need a database to rely on. As MySQL is one of the popular solutions for that, find the steps on how to connect it to custom applications.

  1. Install MySQL connector: download mysql-connector-python package for Python or include MySQL JDBC driver in your project with Java.
  2. Specify the connection parameters (hostname or IP address, port, database name, and credentials).
  3. Connect to MySQL database. The example using Python code is provided below.
import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

cursor = conn.cursor()
  1. You may try different DML operations for inserting, updating, or deleting data. Otherwise, you may simply start querying the database.

The steps provided above are just a base of the MySQL integration in the custom applications. in reality, there might be many more procedures for each particular module in the application. Everything is up to the database administrator, developer, project managers, and other specialists working on the application.

Summary

Being a popular database, MySQL is used in many applications, including custom ones. Some popular cloud applications have built-in connectors to MySQL, while others require third-party tools or ODBC drivers. Meanwhile, custom applications use Python or Java to connect to MySQL databases.

Share This Article