Connect MySQL to RocketBI

Prerequisites:

• MySQL database information includes hostname, port number, username, and password.

• Ensure that MySQL is accessible from the machine running RocketBI. If you use the SaaS version, please configure your server's firewall to allow access from the following IP: 144.217.255.203 . This step is usually necessary if your database is deployed on a cloud provider platform (like GCP or AWS). Feel free to ask your IT/Technical team for assistance.

Setup process:

To set up MySQL database as a connector, follow these steps:

1. Hover over Settings in the top left corner and select Organization Settings. 2. In the right panel, select Data Source Config and click Edit.

3. In the pop-up panel, choose MySQL.

4. In the following panel, fill in the MySQL database connection details:

• Host: IP address or hostname of the MySql database.

• Port: The port number on which MySql is working.

• Username: Name of the database user with read and write permissions for RocketBI to access the data.

• Password: Password of that database user.

SSH tunnel:

In some cases where direct connection to your database is not possible (due to company policies or security concerns), RocketBI provides an option to connect via an SSH tunnel.

This feature requires some additional configurations when setting up the source:

You will need to create a user in the jump server and then grant access to that user by adding the value in the `Public Key` field to the jump server for the SSH service to authorize.

This process varies based on your server operating system, but normally, this should be pasted to a plain text file with any name with the `.pub` extension and saved to folder `/home/<username>/.ssh`, assuming the server is Linux based.

To set up an SSH tunnel in RocketBI, follow these steps:

1. Click the drop-down arrow next to the SSH tunnel.

2. Fill in the following fields:

   • SSH Host: The address of the jump server that has access to the destination database. This should be a hostname or IP address.

   • SSH Port: The port of the SSH service on the jump server. The default is usually 22, but it can be reconfigured.

   • SSH Username: The user's username was created for RocketBI to authorize the connection.

   • Public Key: The key that needs to be added to the jump server for RocketBI to authorize the SSH connection.

Troubleshooting:

After filling in the required field, click the `Test Connection` button to check if RocketBI can connect to your database successfully.

Common Errors

When connecting or syncing data from a data source, if an error message appears something like this, it probably means that you need some additional configurations for the connectors/jobs to work properly.

Connection Time Out:

This is a common error that can be due to the following reasons:

• The cloud provider firewall blocks our connection request to MySQL.

• The hostname or IP address is inaccessible from RocketBI SaaS cloud server. This can be due to company security policies (VPN, private network…).

• The database connection requires special properties to work properly. These properties can be added to the “Properties” section.

Communication failure:

Almost like the previous error, this time RocketBI can access the server that your MySQL hosted on but cannot communicate (read the information) of the Data Warehouse. It usually requires special properties to work properly (for example useSSL, allowPublicKeyRetrieval…)

If either of these cases happens, please show the error message to the IT/ Technical team and ask them to examine the database connection status.

Server time error:

What to do?

1. Check the connector setting:

Go to Settings > Organization Settings > DataSource Config, and click on `Edit` button. Look for the properties section in the pop-up panel, then click `Add`. Depending on your connector type, you may need to configure the timezone of your database by providing a key-value pair setting.

For example, click `Add` in the properties section, enter ‘serverTimezone’ as the key, and ‘UTC’ as the value (assuming your database is running on UTC timezone), and then hit Enter.

Then click `Test Connection` to test if the settings are correct. If the test is successful, click `Apply` to save changes.

Frequently Asking Questions:

1. Why is my system (website/ ERP/ CRM/ any other internal system) slowing down?

NEVER connect your transaction database to RocketBI for business intelligence. A transaction database, or record-keeping system, is where your internal systems store crucial records of everyday activities like sales, purchases, payments, and other transactions. It's similar to how your accountant can't simultaneously file an invoice and present the sales performance on your company's monthly meeting.

2. What should I do if I encounter connection issues or errors when connecting to MySQL?

3. Can I schedule data refreshes or automate data updates from MySQL to RocketBI?

4. What kind of access should I tell my IT/ Technical team to grant RocketBI?

5. Is there a risk of data corruption when connecting MySQL to RocketBI?

6. Can I connect multiple MySQL databases to RocketBI simultaneously?

7. Why does the analyzing process is so slow?

8. Why does RocketBI become slower with time?

9. Which database is recommend for a data warehouse ?