Connect MySQL via SSH Tunnel

• Retrieve the MBI public key
• Allow access to the MBI IP address
• Create a Linux® user for MBI
• Create a MySQL user for MBI
• Enter the connection and user info into MBI

JUMP TO

• MySQL via SSH tunnel
• MySQL via direct connection
• MySQL via cPanel

To connect your MySQL database to MBI via an SSH tunnel, you (or your team, if you are not a techie) must do a few things:

1. Retrieve the MBI public key
2. Allow access to the MBI IP address
3. Create a Linux user for MBI
4. Create a MySQL user for MBI
5. Enter the connection and user info into MBI

Get started.

Retrieving the MBI public key

The public key is used to authorize the MBI Linux user. In the next section, you will create the user and import the key.

1. Go to Manage Data > Connections and click Add New Data Source.
2. Click the MySQL icon.
3. After the MySQL credentials page opens, set the Encrypted toggle to Yes. This displays the SSH setup form.
4. 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.

If you are a bit lost, here’s how to navigate through MBI to retrieve the key:

Allow access to the MBI 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’re also on the MySQL credentials page. See the blue box in the GIF above? That is it!

Creating a Linux user for MBI

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.

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

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

1. 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
        chmod 400 /home/rjmetric/.ssh/authorized_keys

Creating a MySQL user for MBI

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 MBI

To wrap things up, you need to enter the connection and user info into MBI. 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 MBI MySQL user

• Password: The password for the MBI 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.

In the SSH Connection section:

• Remote Address: The IP address or hostname of the server MBI will tunnel into
• Username: The username for the MBI SSH (Linux®) user
• SSH Port: SSH port on your server (22 by default)

That is it! When you are finished, click Save & Test to complete the setup.

Related:

• Reauthenticating integrations

Business.Adobe.com resources