IN THIS ARTICLE
- Whitelist the Magento BI IP address
- Create a MySQL user for Magento BI
- Enter connection info into Magento BI
In this article, we'll walk you through directly connecting your MySQL database to Magento BI. These settings can also be used with Magento EE/CE or any other eCommerce databases that use MySQL.
Whitelisting the Magento BI IP addresses
For the connection to be successful, your must configure your firewall to allow access from our IP addresses. They are 18.104.22.168 and 22.214.171.124, but it's also on the MySQL credentials page:
Create a MySQL user for Magento BI
The simplest way to create a MySQL user for Magento BI is to execute the following query when logged into MySQL with GRANT privileges. Replace <Magento BI IP Address> with the Magento BI IP address and replace <secure password> with a secure password of your choice:
GRANT SELECT ON *.* TO 'magentobi'@'<Magento BI IP address>' IDENTIFIED BY '<secure password>';
To restrict this user from accessing data in specific databases, tables, or columns, you can instead run GRANT queries that only allow access to the data you permit.
Please re-run the GRANT query for all required IPs using the same user and password.
Enter connection info in Magento BI
To wrap things up, we need to enter the connection and user info into Magento BI. Did you leave the MySQL credentials page open? If not, go to Data > Connections and click the Add New Data Source button, then the MySQL icon. Don't forget to toggle the Encrypted button to Yes.
Enter the following info into this page, starting with the Database Connection section:
- Connection Nickname: Enter a name for the integration (ex: Ecommerce Store)
- Username: The username for the Magento BI MySQL user
- Password: The password for the Magento BI MySQL user
- Port: MySQL's port on your server (3306 by default)
- Host: By default, this will be localhost. In general, it will be the bind-address value for your MySQL server, which by default is 127.0.0.1 (localhost), but could also be some local network address (e.g. 192.168.0.1) or your server's public IP address.
The value can be found in your my.cnf file (usually located at "/etc/my.cnf") underneath the line that reads "[mysqld]". If the bind-address line is commented out in that file, your server is secured from outside connection attempts.
That's it! When you're finished, click the Save & Test button to complete the setup.