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: 35.185.190.64 . 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:
Click the drop-down arrow next to the SSH tunnel.
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?
Double-check Credentials: Ensure you've entered the correct server address, username, password, and database name. Pay attention to typos and case sensitivity.
Network Connectivity: Ensure you can access your MySQL.
Firewall and Permissions: Make sure your MySQL server allows connections from RocketBI's IP address ( 35.185.190.64 ). Check firewall settings and user permissions in MySQL.
Port Configuration: Verify that MySQL is running on the default port (3306) or the port you specified and that a firewall does not block it.
Security Settings: Check if MySQL's security settings (such as SSL) are configured correctly
3. Can I schedule data refreshes or automate data updates from MySQL to RocketBI?
You don't need to do anything. Your data is sync in real-time.
4. What kind of access should I tell my IT/ Technical team to grant RocketBI?
For a completed experience, the user credential that was used to connect your MySQL and RocketBI must have full Read and Write authority. We don't need your MySQL user creation or full administration rights.
5. Is there a risk of data corruption when connecting MySQL to RocketBI?
No, RocketBI syncs your data and allows you to do exploration, transformation & visualization. To avoid unnecessary errors, create new databases for your ETL & analysis.
6. Can I connect multiple MySQL databases to RocketBI simultaneously?
No, only one data warehouse can be connected to RocketBI at a time. You can unplug and connect to a new MySQL, but all your analysis results would stay in the old warehouse.
7. Why does the analyzing process is so slow?
MySQL was never a database built for analytics. So, the query power, the ability to read data, was never its forte. We recommended consulting with your IT/ Tech team and using several tactics, from moving your MySQL data warehouse to a fully managed cloud solution or migrating to an analytics database (For example, we have native support for Clickhouse)
8. Why does RocketBI become slower with time?
In most cases, it's not RocketBI but your MySQL server. It can become slow for various reasons, ranging from improper index usage to bugs in the storage engine. However, in most cases, they need to be maintain & clean-up, so monitor your data warehouse and keep an eye on their performance.
9. Which database is recommend for a data warehouse ?
For analytical data warehouse, we recommend you to use clickhouse. it's fast & open source.