Connecting to a Local Database
On This Page
We strongly recommend using local databases for testing purposes only. Else, you may experience lower performance even with a low volume of data as compared to a hosted server. Further, with the free version of ngrok, if the utility terminates for any reason, the Pipeline will stop and you might need to update the database host configuration in the Pipeline to run it again.
You can use either of the following methods to connect to your local database in Hevo:
-
Connect via SSH. Read Connecting through SSH for steps to do this.
-
Connect using ngrok. ngrok allows you to expose a web server running on your local machine to the internet. Refer to section, Connecting to the Local Database below for steps to connect using ngrok.
Prerequisites
-
MY-SQL or MS-SQL or Oracle service is running on your local machine.
-
Data to be loaded to the Destination is available in your MY-SQL or MS-SQL or Oracle database.
-
You have an account on ngrok and an installed ngrok utility on your local machine. To run ngrok on your local machine, follow these one-time steps:
-
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.
-
-
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
.
-
Connecting to the Local Database
Perform the following steps to connect to the local database:
-
Log in to your database server.
-
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
-
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 and19789
is the port number. -
Paste the hostname and port number into the Database Host and Database Port fields respectively.
-
Specify all other settings and click TEST & 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.
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:
-
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, andpassword
is your password. -
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. This causes the Pipeline to fail and you need to change the database host and port with the new details received from ngrok to make sure the Pipeline continues to ingest data from the local database.
For steps to edit the database settings, refer to section Modifying the Source and Destination Configuration.
See Also
Revision History
Refer to the following table for the list of key updates made to this page:
Date | Release | Description of Change |
---|---|---|
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 | NA | Added the Limitations section. |
Jul-12-2021 | 1.67 | New document. |