To connect your
MySQL database to MBI via an
SSH tunnel, you (or your team, if you are not a techie) will need to do a few things:
Linuxuser for MBI
MySQLuser for MBI
It is not as complicated as it might sound. Let us get started.
public key is used to authorize the MBI
Linux user. In the next section, we will create the user and import the key.
MySQL credentialspage opens, set the
Yes. This will display the SSH setup form.
public keyis 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:
For the connection to be successful, your must configure your firewall to allow access from our IP addresses. They are
220.127.116.11 but they’re also on the
MySQL credentials page. See the blue box in the GIF above? That is it!
Linuxuser 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
adduser rjmetric -p<password> mkdir /home/rjmetric mkdir /home/rjmetric/.ssh
public key we retrieved in the first section? To ensure the user has access to the database, we need to import the key into
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
/home/rjmetricdirectory to allow access via
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 will have server access - this will prevent a successful connection to MBI. In these cases, it is necessary to run a command like
AllowUsers to allow the
rjmetric user access to the server.
MySQLuser 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>';
secure password here with a secure password, which can be different than the
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.
To wrap things up, we 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
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 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 (for example,
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.
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.