MySQL

The first step in querying your MySQL database is to connect FactBranch to your MySQL instance. You do this by creating a MySQL Data Source in FactBranch.

Once you've entered your credentials in the authentication, they are stored on FactBranch's servers, encrypted-at-rest and the password will never be shown again anywhere in your FactBranch account.

You can, however, use the authentication in your nodes. This allows you to save the credentials once and then re-use it in several nodes. You can even let another team member create nodes and use the data source without them seeing the credentials.

In this article you'll learn:

Creating a read-only user in MySQL

We recommend creating a dedicated user for FactBranch with only the permissions it needs to run your queries. Typically this means giving read-only access on the tables you will query.

This is a basic example of how to create an appropriate user in your MySQL database. Use this as a starting point depending on your own requirements.

Creating the user

Create a database user for FactBranch. Replace all values surrounded by <...> with your own values.

CREATE USER 'factbranch'@'%' IDENTIFIED BY '<PASSWORD>';

Granting read-only permissions

Grant the user read-only access to the databases and tables you want to query:

-- Grant read access to a specific database
GRANT SELECT ON <your_database>.* TO 'factbranch'@'%';

-- Or grant read access to specific tables
GRANT SELECT ON <your_database>.<your_table> TO 'factbranch'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

For Amazon RDS MySQL

If you're using Amazon RDS, you can create the user through the AWS console or using the same SQL commands above. Make sure your RDS security group allows connections from FactBranch's static IP address.

Connecting FactBranch to your MySQL database

To create a MySQL authentication in FactBranch, click on Add data source in your Data Sources dashboard, then select MySQL. You'll be forwarded to the credentials form.

TODO: SCREENSHOT

Host address

The hostname or IP address of your MySQL server. For cloud databases, this is typically provided by your hosting provider. For Amazon RDS, it looks like your-instance.region.rds.amazonaws.com

Port

The port your MySQL server is running on. The default port for MySQL is 3306. If you're using a non-standard port, enter it here.

Database name

The name of the database you want to query and where you've created the user above.

Username and Password

The credentials for the user you've created in the previous steps. If you are following this tutorial, the username will be factbranch.

Use a static IP when making database requests

If you select this checkbox, FactBranch will use a single IP to query your MySQL database. In the yellow box below the checkbox you'll see the IP address. In your firewall or cloud security group, allow access from this IP address.

TODO: SCREENSHOT

Once you've filled out everything, click Connect. FactBranch will test the credentials you've entered by attempting to connect to your database. If it was successful, you'll be forwarded to the list of data sources and you'll see your newly created MySQL authentication in the list.

You can now use this authentication in a MySQL node to run SQL queries in your MySQL database.

Renaming the data source

To rename the data source, either click on Rename next to the title, or double-click on the title itself. Then enter the new name and click on Save or hit Enter on your keyboard. To revert to the old name, hit the Escape key.

Using the authentication in a MySQL node

First create a MySQL node in one of your Flows. Edit the node by clicking Edit next to the node in the Flow Editor. Select the Authentication tab on the left side of the screen. Then click on Select an authentication... - or on change... if that node already has an authentication associated. Select the authentication you'd like to use and from now on this node will use for all its requests the credentials you've stored in the authentication.

Troubleshooting connection issues

Connection refused

If you get a "connection refused" error, check that: - Your MySQL server is running and accessible - The host address and port are correct - Your firewall allows connections from FactBranch's static IP address

Access denied

If you get an "access denied" error, verify that: - The username and password are correct - The user has been granted SELECT permissions on the database/tables - The user is allowed to connect from external hosts (using 'factbranch'@'%')

SSL connection issues

FactBranch connects using SSL by default. If your MySQL server doesn't support SSL or has SSL configured differently, this might cause connection issues. Most modern MySQL installations support SSL connections out of the box.