IN THIS ARTICLE
In this article, we'll walk you through directly connecting your MySQL database to Magento BI using cPanel. This process can also be used to connect Magento and any other MySQL-based eCommerce databases to Magento BI.
- Create a Magento BI MySQL user in cPanel
- Enter connection and user info into Magento BI
Let's get started.
Creating a Magento BI MySQL user in cPanel
- Login to cPanel via your hosting provider.
- Click MySQL Databases, located in the Database section.
- Scroll down to the Add New User section and create a user for Magento BI:
- Click the Create User button.
- Now that you've created the user, you need to associate it to a database. Go back to the Add New User section - see the settings for Add User to Database? That's what we need.
- In the User dropdown of this section, select the user you created.
- In the Database dropdown of this section, select the database you want to connect to Magento BI.
- Click the Add button.
- When the checklist of privileges appears, check the box next to SELECT - this is all Magento BI needs to connect to your database.
Entering the connection and user info into 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 Manage Data > Connections and click the Add New Data Source button, then the MySQL icon.
Enter the following info into this page in the Database Connection section:
- 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: The public address of the MySQL server Magento BI will connect to. This is usually the URL you use to log into cPanel.
If you're using an SSH tunnel, you'll also need to enter the encryption information. Toggle the Encrypted button to Yes to display the form.
- Connection Type: Set this to SSH Tunnel
- Remote Address: The IP address or hostname of the server Magento BI will tunnel into
- Username: The username for the Magento BI SSH (Linux) user (click here for instructions on how to do this, if you haven't already)
- SSH Port: SSH port on your server (22 by default)
That's it! When you're finished, click the Save & Test button to complete the setup.