Connect MySQL via SSH Tunnel
JUMP TO
To connect your MySQL database to Commerce Intelligence via an SSH tunnel
, you must do a few things:
- Retrieve the Commerce Intelligence
public key
- Allow access to the Commerce Intelligence
IP address
- Create a
Linux
user for Commerce Intelligence - Create a
MySQL
user for Commerce Intelligence - Enter the connection and user info into Commerce Intelligence
Retrieving the Commerce Intelligence public key
The public key
is used to authorize the Commerce Intelligence Linux
user. In the next section, you will create the user and import the key.
- Go to Manage Data > Connections and click Add New Data Source.
- Click the
MySQL
icon. - After the
MySQL credentials
page opens, set theEncrypted
toggle toYes
. This displays the SSH setup form. - The
public key
is located underneath this form.
Leave this page open throughout the tutorial - you will need it in the next section and at the end.
Here’s how to navigate through Commerce Intelligence to retrieve the key:
Allow access to the Commerce Intelligence IP address
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 they are also on the MySQL credentials
page. See the blue box in the GIF above.
Creating a Linux user for Commerce Intelligence
This can be a production or secondary 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.
- 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
-
Remember the
public key
you retrieved in the first section? To ensure that the user has access to the database, you need to import the key intoauthorized\_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
- To finish creating the user, alter the permissions on the
/home/rjmetric
directory to allow access viaSSH
:
chown -R rjmetric:rjmetric /home/rjmetric
chmod -R 700 /home/rjmetric/.ssh
chmod 400 /home/rjmetric/.ssh/authorized_keys
sshd\_config
file associated with the server is not set to the default option, only certain users have server access - this prevents a successful connection to Commerce Intelligence. In these cases, it is necessary to run a command like AllowUsers
to allow the rjmetric
user access to the server.Creating a MySQL user for Commerce Intelligence
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 from 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 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 the MySQL icon. Do not forget to set the Encrypted
toggle to Yes
.
Enter the following info into this page, starting with the Database Connection
section:
-
Username
: The username for the Commerce Intelligence MySQL user -
Password
: The password for the Commerce Intelligence MySQL user -
Port
: MySQL 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 is127.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.
In the SSH Connection
section:
Remote Address
: The IP address or hostname of the server Commerce Intelligence will tunnel intoUsername
: The username for the Commerce Intelligence SSH (Linux) userSSH Port
: SSH port on your server (22 by default)
When you are finished, click Save & Test to complete the setup.