ÃÛ¶¹ÊÓƵ

DocumentationCommerceCommerce Intelligence User Guide

Connect MySQL via direct connection

Last update: Tue Jul 25 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User

In this topic

Jump to

NOTE
ÃÛ¶¹ÊÓƵ recommends you use SSH or some other form of encryption to secure your data! If this is not an option, you can still directly connect Commerce Intelligence to your database using the instructions in this topic.

This topic walks you through directly connecting your MySQL database to Commerce Intelligence. These settings can also be used with ÃÛ¶¹ÊÓƵ Commerce or any other eCommerce databases that use MySQL.

Allow access to the Commerce Intelligence IP addresses allowlist

For the connection to be successful, you must configure your firewall to allow access from your IP addresses. They are 54.88.76.97 and 34.250.211.151, but it is also on the MySQL credentials page:

MBI_Allow_Access_IPs.png

Create a MySQL user for Commerce Intelligence

The simplest way to create a MySQL user for Commerce Intelligence is to execute the following query when logged into MySQL with GRANT privileges. Replace Commerce Intelligence IP Address with the Commerce Intelligence IP address and replace secure password with a secure password of your choice:

    GRANT SELECT ON *.* TO 'magentobi'@'<Commerce Intelligence 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.

Rerun the GRANT query for all required IPs using the same user and password.

Enter connection info in Commerce Intelligence

To wrap things up, you need to enter the connection and user info into Commerce Intelligence. Did you leave the MySQL credentials page open? If not, go to Data > Connections and click Add New Data Source, then click the MySQL icon. Do not forget to change the Encrypted toggle to Yes.

Enter the following info into this page, starting with the Database Connection section:

  • Connection Nickname: Enter a name for the integration (for example, Ecommerce Store)

  • Username: The username for the Commerce Intelligence MySQL user

  • Password: The password for the Commerce Intelligence MySQL user

  • Port: MySQL’s port on your server (3306 by default)

  • Host: By default, this is localhost. In general, it is 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 (for example, 192.168.0.1) or your server’s public IP address.

    The value can be found in your my.cnf file (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.

When you are finished, click Save & Test to complete the setup.

recommendation-more-help
e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc