How to connect Google Data Studio to MySQL?

Team TypeStack
Team TypeStack ...

In this blog, we will look at how we connect MySQL (or 'my-sequel') to Google Data Studio. 

Open your database, set it up, and then move through to Google Data Studio, where you can link your database and pull that data into a new report.

Let's get started.

The first thing we need to do to connect Google Data Studio to MySQL, is to check if you can remotely connect to your database. This is one of the most important steps. Google provides a list of IP addresses that need to be able to access your database.

You can access these from the help page. Go to the help page for connecting to MySQL, and scroll down to ‘Notes.’ You will see the IP addresses that need to be able to access the database. You will probably need to have a VPS (Virtual Private Server), a dedicated server, or dedicated MySQL hosting. If you’re using shared hosting, then you might not be able to connect to your database remotely.

You can use VPS hosting provided by Name Cheap, and this lets you enable remote access to MySQL. However, if you are using their shared hosting, then you will be able to connect MySQL to Google Data Studio.

So you can start by checking with your hosting provider and see if you can enable remote access to your database. 

First, we need to enable remote access to MySQL. To do this, search for ‘MySQL,’ and select ‘Additional MySQL Access Hosts.’ Now enter the Google IP addresses so that they can access our database.

Since there are a number of IP addresses, each have their own range, the laziest way to add them is to make use of the wildcard character in our configuration.

For example, instead of adding the range for the IP address starting with ‘108.177’,

we can simply enter ‘108.177.%.%’. You can find the Google IP addresses you need to add listed on Google’s help page. 

Now click ‘Save,’ and apply these settings to all of the user accounts on your server.

Next, you need to configure the firewall on our server. To do this, search for the server ‘Security and Firewall’ configuration options. Scrolling down, we need to find ‘Firewall Configuration’ under ‘CSF - ConfigServer Firewall.’ Now you need to find ‘TCP In’ which is for incoming connections. Next you need to enter the port for our MySQL databases. It’s important to highlight that this will open incoming connections on this port. You will need to consider the potential security implications of making this change to your server.

When you’re happy, scroll to the very bottom of the page and click ‘Change,’ now you need to click ‘Restart.’ Next, you need to create our database. 

Open a user account, select ‘MySQL Databases,’ create a new database, and name your database. Now create a user and name it. Then give it a password and click ‘Create’. Next you need to add the user to your database so that you can access MySQL.

Now let’s navigate back and select ‘phpMyAdmin.’ Start by uploading your database into MySQL. Then select ‘Import,’ click ‘Choose File,’ and select your database. Now click ‘Go’. You will be able to see your database in Google Data Studio.

Now let’s create a simple dashboard.  Head to Google Data Studio and create a new report. Connect your database to Google Data Studio. To do this select the ‘MySQL’ connector. Enter the details so that Google Data Studio can connect to your database. Enter the hostname or IP address for your database, then the database name, username, and password.

Now click ‘Authenticate’. You can now select the table from your database, click ‘Add,’ and ‘Add to Report.’ You will see that a table is automatically added to your report. You can click the edit icon for the data source and see the fields from your database – these are the dimensions and metrics.

If needed, you can adjust the field ‘Types’ for your report. You can now create different charts using the information from MySQL. For example, you can add a bar chart and change the dimensions and metrics. Also, there are lots of different ways to visualize information from your MySQL database. 

Take some time to explore the options in Google Data Studio.