Add a MySQL data source to connect your MySQL database to Quick BI. Once connected, you can analyze and visualize your data in Quick BI. Quick BI supports connecting to MySQL over the public network or an internal network. This topic describes how to add a user-created MySQL data source.
Limitations
Quick BI supports MySQL 5.5, 5.6, 5.7, and 8.0.
Prerequisites
If you connect Quick BI to your MySQL database over the public network, you must add Quick BI's IP addresses to the database whitelist. For more information, see Add a security group rule.
If you connect to your MySQL database over an internal network, ensure network connectivity between your data source and Quick BI by using one of the following methods:
If your MySQL database is hosted on an Alibaba Cloud ECS instance, you can connect through a VPC.
You can also set up a bastion host and access the database through an SSH tunnel.
You have created a MySQL database and obtained its username and password.
You have obtained an AccessKey ID and an AccessKey Secret for authentication. For more information, see Obtain an AccessKey.
Step 1: Configure the firewall
You must configure the firewall for your user-created database to allow access from external networks.
Run the following command to open the firewall configuration file:
vi /etc/sysconfig/iptablesAdd the following rule to the firewall configuration file:
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPTAfter you add the rule, run the following command to restart iptables:
service iptables restart
Step 2: Add a user-created MySQL data source
Log on to the Quick BI console.
Follow these steps to add a data source:
Go to the data source creation page. For more information, see Create a data source.
On the User-Created Databases tab, click MySQL.
In the Configure Connection dialog box, configure the following parameters.
Parameter
Description
Display name
A name for the data source that appears in your data source list.
The name must not contain special characters or have leading or trailing spaces.
Database address
The IP address or domain name of your MySQL database.
Port
The port the database uses.
Database
The name of the database you want to connect to.
Username and password
The credentials to log on to the MySQL database.
VPC data source
Only if your MySQL database is deployed on an Alibaba Cloud ECS instance and uses an Alibaba Cloud VPC network, select VPC Data Source and configure the following parameters:
Purchaser AccessKey ID and Purchaser AccessKey Secret: The AccessKey ID and AccessKey Secret of the account that was used to purchase the instance.
For more information, see Obtain an AccessKey.
NoteEnsure that the account has read permissions on the target instance. If the account also has write permissions, the system automatically adds the required IP addresses to the whitelist. Otherwise, you must add them manually. For more information, see Create a custom policy.
Instance ID: The ID of the ECS instance. You can find the instance ID on the Instances page in the ECS console.
For more information, see View instance information.
Region: The region where the ECS instance is located. You can find the region in the upper-left corner of the ECS console.
For more information, see View instance information.
SSL
Select this option if your data source requires an SSL connection.
SSH
Select SSH and configure the following parameters:
You can set up a bastion host and use an SSH tunnel to access your database. Contact your O&M engineer or system administrator for the bastion host details.
SSH host: Enter the IP address of the bastion host.
SSH username and SSH password: The credentials to log on to the bastion host.
SSH port: The port of the bastion host. The default value is 22.
For more information, see Log on to a Linux instance by using password authentication.
Initialize SQL
Enable this option to configure SQL statements that run automatically after a connection is established.
You can only use SET statements. Separate multiple statements with semicolons (;).
Enable file upload entry
Enable this option to allow file uploads.
Click Test Connection to verify the connection.
After a successful connection test, click OK to add the data source.
The new data source appears in the data source list.
Next steps
After creating the data source, you can create a dataset and begin analyzing your data.
Create a dataset by adding data tables from your MySQL database or by using custom SQL queries. For more information, see Create a dataset.
Add visualizations to analyze your data. For more information, see Create a dashboard and Overview of visualizations.
Use the drill-down feature for in-depth data analysis. For more information, see Step 3: Configure and use the drill-down feature.