Guide: Using MySQL with Databox

HOW TO

    MySQL connections are available in Plus and Business Accounts. Agency Basic Accounts have the ability to use MySQL in the Agency Account only.

How to prepare your MySQL Database and Host

IN THIS SECTION

How to prepare your MySQL Database and Host

To prepare our MySQL Database and Host, we'll need to allow remote connections from Databox’s public IP (52.4.198.118) to our Database. The necessary steps to do that will depend on your database, server infrastructure, and firewall. VPC IP must be able to connect to the database port directly.

1
 First, we will  create a user named  "User" with permissions to remotely access the Database called "mydb" 
GRANT SELECT ON mydb.* TO 'user'@'52.4.198.118' IDENTIFIED BY 'securePassword';
	

To keep this simple, we have given this user SELECT permission for all Tables in our database. Permission could be given to select from one Table only, or we could create a custom view and give permissions to select from this view only. A basic example of how to achieve this can be found here.

2
 By default, our MySQL server will be set to only listen to the local interface. We must configure our MySQL server to listen to all IPs. To do this, we'll open  /etc/my.cnf (or   /etc/mysql/my.cnf, depending on your Linux distribution). Check to ensure it contains the following:  
#skip-networking # commented out! bind-address = 0.0.0.0 # Will listen on all IPs
	
3
 If you’ve made any changes, restart your MySQL server to make sure it's ready to accept remote connections

4
 Find  Port 3306/TCP, the default MySQL port, from our VPC IP mentioned above. This must be done on your firewall. Below is an example for Linux iptables:
iptables -A FORWARD -s 52.4.198.118/32 -p tcp --dport 3306 -j ACCEPT
	

You may have to replace  FORWARD with INPUT if your Linux box has a public IP and the Database runs on the Host itself.

Your server is now set up to accept requests from our IP to your Database/ Table.

How to set up your SSL connection

It’s a good practice to secure the connection with an SSL certificate that is generated and installed on the server. Follow the steps below to do this on any recent MySQL version. 

We’ll use the OpenSSL command line tools and our certificate will be self-signed, but feel free to use certificates from any certificate authority that is widely known and acknowledged.

1
 First, we'll generate a new CA private key
openssl genrsa 2048 > ca-key.pem
	
2
 Next, we’ll generate a certificate. You will need to answer some questions and, when complete, you will have a CA key and a CA certificate
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
	
3
 Create a new signing request and private key
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem
	
4
 Export the private key into a RSA private key
openssl rsa -in server-key.pem -out server-key.pem
	
5
 The server certificate can now be created and signed using our CA 
openssl x509 -sha1 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
	
6
 Next, we'll copy the created files to our MySQL directory
cp ca-cert.pem server-cert.pem server-key.pem /etc/mysql/
	
7
 Open the   /etc/mysql/my.cnf file and add the following lines to the MySQLd  section: 
ssl-ca=/etc/mysql/ca-cert.pem ssl-cert=/etc/mysql/server-cert.pem ssl-key=/etc/mysql/server-key.pem
	
8
  Restart the server to apply the new settings

9
 We now want to create a user that will be allowed to only connect via SSL connection
GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘SecurePassword’ REQUIRE SSL;
	

Feel free to restrict the user access further as you see fit. In most cases, user access is restricted to our IP only with SELECT permissions.

10
 At this stage, we should test the SSL connection from a MySQL client. To do this, we'll paste the code below to  /etc/mysql/my.cnf, but this time in the Client section.  
ssl-ca=/etc/mysql/ca-cert.pem ssl-cert=/etc/mysql/server-cert.pem ssl-key=/etc/mysql/server-key.pem
	
11
    Connect to the server. If the connection is successful, we'll need to confirm we're connected via SSL. This can be done by running the following: 
\s
	
12
 The output will show many lines of code. If Cipher in use is returned, the SSL connection is working. 
SSL: Cipher in use is DHE-RSA-AES256-SHA
	

If SSL: Not in use is returned, the SSL isn't active. Please go back and re-check all steps above, or contact Support at [email protected] for additional assistance.

SSL: Not in use

How to connect MySQL to Databox

Once our Database is properly prepared, we'll connect it to Databox.

To connect MySQL to Databox, go to Data Sources > + New connection. Type MySQL in the search bar and click on the green Connect button. 

This will open the  Activation window, which is where you will be prompted to enter your connection data and authorize the Databox app. 

add_user_popup@2xIf you have set up a SSL certificate, be sure to check the SSL checkbox and paste the certificates in the corresponding fields. SSL CA is not mandatory. If you're using a self-signed certificate, be sure to leave the SSL checkbox un-checked or the connection will fail. 

Click the green Activate button to complete the connection process. If MySQL was successfully connected, the Activation window will close shortly and you'll see a Connected notice. 

If you see a notice that says Wrong Credentials, doublecheck your user data before re-entering them in the Activation window. 

If the Activation window doesn't close for over a minute, there may be difficulty connecting to your Database Host due to firewall, server, or networking issues. 

If you have difficulty connecting, be sure the IP or hostname entered is publicly available or access is granted to Databox's public IP (52.4.198.118). 

How to use the Query Builder for MySQL

The Query Builder for MySQL allows you to create Custom Metrics using data from your Database to use in Databoards, Alerts, Goals, etc. Learn more about the Query Builder for MySQL here

Additional Information

  • Query results are limited to 1000 rows. If you expect your query to return over 1000 rows or if you're not sure how many rows will be returned, be sure to LIMIT the query
  • The query must contain a column named "Date." If you need to, use AS in your SELECT statement to satisfy this requirement
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us