Magento Business Intelligence Help Center

Connecting MySQL via SSH tunnel

IN THIS ARTICLE

JUMP TO

BROUGHT TO YOU BY

Avatar

To connect your MySQL database to Magento BI via an SSH tunnel, you (or your team, if you're not a techie) will need to do a few things:

  1. Retrieve the Magento BI public key
  2. Whitelist the Magento BI IP address
  3. Create a Linux user for Magento BI
  4. Create a MySQL user for Magento BI
  5. Enter the connection and user info into Magento BI

It's not as complicated as it might sound. Let's get started.

Retrieving the Magento BI public key

The public key is used to authorize the Magento BI Linux user. In the next section, we'll create the user and import the key.

  1. Go to Manage Data > Connections and click the Add New Data Source button.
  2. Click the MySQL icon.
  3. After the MySQL credentials page opens, toggle the Encrypted button to Yes. This will display the SSH setup form.
  4. The public key is located underneath this form.

Leave this page open throughout the tutorial - you'll need it in the next section and at the end.

If you're a bit lost, here's how to navigate through Magento BI to retrieve the key:

Whitelisting the Magento BI IP address

For the connection to be successful, your must configure your firewall to allow access from our IP address. It's 54.88.76.97/32, but it's also on the MySQL credentials page. See the blue box in the GIF above? That's it!

Creating a Linux user for Magento BI

This can be a production or slave machine, as long as it contains real-time (or frequently updated) data. You may restrict this user any way you like, as long as it retains the right to connect to the MySQL server.

  1. To add the new user, run the following commands as root on your Linux server:

    adduser rjmetric -p<password>
    mkdir /home/rjmetric
    mkdir /home/rjmetric/.ssh
  2. Remember the public key we retrieved in the first section? To ensure the user has access to the database, we need to import the key into authorized_keys.

    Copy the entire key into the authorized_keys file as follows:

    touch /home/rjmetric/.ssh/authorized_keys
    "<PASTE KEY HERE>" >> /home/rjmetric/.ssh/authorized_keys 
  3. To finish creating the user, alter the permissions on the /home/rjmetric directory to allow access via SSH:

    chown -R rjmetric:rjmetric /home/rjmetric
    chmod -R 700 /home/rjmetric/.ssh 

Important!

If the sshd_config file associated with the server is not set to the default option, only certain users will have server access - this will prevent a successful connection to Magento BI. In these cases, it's necessary to run a command like AllowUsers to allow the rjmetric user access to the server.

Creating a MySQL user for Magento BI

Your organization may require a different process, but the simplest way to create this user is to execute the following query when logged into MySQL as a user with the right to grant privileges:

GRANT SELECT ON *.* TO 'rjmetric'@'localhost' IDENTIFIED BY '<secure password here>';

Replace <secure password here> with a secure password, which can be different than the SSH 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.

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 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:

  • 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.

In the SSH Connection section:

  • 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
  • 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.

Related:

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk