Connecting to a Local Database

Last updated on Jan 05, 2024

We recommend using local databases for testing purposes, or you may experience lower performance even with a low volume of data. Further, with the free version of ngrok, if the utility terminates for any reason, the Pipeline stops and you need to update the database host setup in the Pipeline to run it again.

To connect to your localhost database, you can use either of the following methods:


Prerequisites

  • MySQL/MSSQL service is running on your local machine.

  • You have an account on ngrok and an ngrok utility installed on your local machine. To run ngrok on your local machine, follow these one-time steps:

    1. Extract the ngrok utility:

      • On Linux or macOS, unzip ngrok from a terminal:

        unzip /path/to/ngrok.zip
        
      • On Windows, double-click ngrok.zip to extract it.

    2. Authenticate ngrok in your local machine:

      ./ngrok authtoken <your_auth_token>
      

      You can get the auth token from your ngrok dashboard. For example, in the image below, the auth_token starts with 1t7uGaJshUvnh2Fg1MApl.

      Auth token

  • You are assigned the Team Collaborator, or any administrator role except the Billing Administrator role in Hevo to create the Destination.


Connecting to the Local Database

Perform the following steps to connect to the local database:

  1. Log in to your database server.

  2. Start a TCP tunnel forwarding to your database port.

    ./ngrok tcp <your_database_port>
    

    For example, the port address for MySQL is 3306. Therefore, the command would be:

    ./ngrok tcp 3306
    
  3. Copy the public IP address (hostname and port number) for your local database and port. For example, in the image below, 8.tcp.ngrok.io is the database hostname and 19789 is the port number.

    Getting hostname and port

  4. Paste the hostname and port number into the Database Host and Database Port fields, respectively.

    MySQL settings

  5. Specify all the other settings and click SAVE & CONTINUE.


Troubleshooting

Refer to this section to resolve typical errors encountered while connecting the localhost database to Hevo.

java.sql.SQLNonTransientConnectionException

Whitelist the IP address, 0.0.0.0 to grant Hevo permission to access your server.

To do this in SQL, add the following code block in the my.cnf file and restart the SQL server.

[mysqld]
bind-address = 0.0.0.0

The my.cnf file is generally found at:

  • macOS: /usr/local/mysql/etc/my.cnf

  • Linux: /etc/mysql/my.cnf

  • Windows: C:\Program Files\MySQL\MySQL Server 5.5\my.cnf

MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

To resolve this error, follow these steps:

  1. Run the following query in the MySQL Workbench:

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
    

    where root is your user, localhost is your URL, and password is your password.

  2. Refresh your privileges by running this query:

     flush privileges;
    

    If this does not resolve the issue, try the SQL query without the @'localhost' part.


Limitations

  • In the free account, ngrok generates a new URL and port every time, which causes the Pipeline to fail. You need to update the database host and port to make sure the Pipeline continues to ingest data from the local database. Refer to section Modifying the Source and Destination Configuration for the steps to do this.



See Also


Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Jul-25-2023 NA Revised the page to enhance its clarity.
Sep-07-2022 NA Re-organized the content and updated the screenshots.
Sep-09-2021 1.71 Updated the page overview to mention different options that Hevo supports for connecting to the local database.
Jul-26-2021 1.68 New document.

Tell us what went wrong