Hey there.
I'm Benjamin from Love's Data.
In this video, we're going to look at how we connect MySQL (or 'my-sequel') to Google
Data Studio.
This is a topic I've had requested from a number of people, so it's great to be covering
it.
Normally, I cover Google Data Studio with connectors like the Google Analytics connector
and the Google Sheets connector.
So what we will do in today's video – is we'll begin by looking at a demo database
and you can actually grab a link to download the sample database in the description below
this video.
Then what we'll do is we'll head to the database, we will set it up, and then we'll move through
to Google Data Studio, where we'll actually link it up and pull that data into a new report.
Okay, let's get started.
If you’ve tried to connect MySQL to Google Data Studio you may have encountered issues.
For example, you might have entered all of the details for your database...
Hit ‘Authenticate’...
And then seen an error like this one...
Okay, so 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 probably the most important step.
Google provides a list of IP addresses that need to be able to access your database.
Let’s take a look...
We’re now on the help page for connecting to MySQL, and if we scroll down to ‘Notes’...
We can 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.
Today I’m going to use VPS hosting provided by Name Cheap, and this lets me let enable
remote access to MySQL.
However, if I was using their shared hosting, then I wouldn’t be able to connect MySQL
to Google Data Studio.
So start by checking with your hosting provider and see if you can enable remote access to
your database.
Let’s head to my server to see how we can enable remote access...
First, we need to enable remote access to MySQL.
To do this, I’m going to search for ‘MySQL’...
And I’m going to select ‘Additional MySQL Access Hosts’...
Now we need to enter the Google IP addresses so that they can access our database.
Since there are a number of IP addresses that each have their own range, the easiest 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.
I’ve included a link in the description below this video, along with a preformatted
list using the wildcards.
Now we need to click ‘Save’...
And I’m going to apply these settings to all of the user accounts on my server...
Next, we need to configure the firewall on our server.
To do this, let’s search for the server ‘Security and Firewall’ configuration
options...
Scrolling down, we need to find ‘Firewall Configuration’ under ‘CSF - ConfigServer
Firewall’...
Let’s click this button...
And now we need to find ‘TCP In’ which is for incoming connections...
Now we need to enter the port for our MySQL databases.
We need to add ‘3306’...
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 we need to click ‘Restart’...
Next, we need to create our database.
Let’s open a user account that I’ve already created on my server...
I’m going to select ‘MySQL Databases’...
And I’m going to create a new database.
I’m going to name my database ‘lovesdata_example’...
And
now I’m going to create a user.
I’m going to name my database user ‘lovesdata_user’...
And I’m going to enter a password for my user...
And I’m going to click ‘Create’...
Now I need to add the user to my database to that I can access MySQL...
Now let’s navigate back...
And select ‘phpMyAdmin’...
I’m going to start by uploading my example database into MySQL.
If you want to follow along using my example, you can find a link to download my example
database in the description below this video.
I’m going to select my database...
And then I’m going to select ‘Import’...
Now I’m going to click ‘Choose File’...
And select my example database...
Now I’m going to click ‘Go’...
I can now see that my ‘Students’ table has been added.
Let’s select the table...
We can see that this is a simple example, it includes some example student names, student
IDs, and the courses they are taking on my website.
We can use this to create a simple dashboard in Google Data Studio that includes these
basic student details.
Okay, so let’s head to Google Data Studio...
Let’s create a new report...
Now we need to connect our database to Google Data Studio, so I’m going to select the
‘MySQL’ connector...
Now I need to enter the details so that Google Data Studio can connect to my database.
I need to enter the hostname or IP address for my database, there’s an optional port,
then the database name, username, and password.
Now I’m going to click ‘Authenticate’...
I can now select the table from my database...
And click ‘Add’...
And ‘Add to Report’...
We can see a table is automatically added to our report...
And we can click the edit icon for the data source...
We can now see the fields from our database – these are the dimensions and metrics.
If needed, you can adjust the field ‘Types’ for your report.
We can now create different charts using the information from MySQL.
For example, we can add a bar chart...
And change the dimensions and metrics...
Now I can visualize the number of students in each course.
And remember, there are lots of different ways to visualize the information from your
MySQL database.
Take some time to explore the options in Google Data Studio and check out my extra resources
in the description below this video.
That’s it!
So that's taken connect your own MySQL (or 'sequel') database through to your reports
in Google Data Studio.
Don't forget, I do cover the Google Analytics connector in some of my other videos.
You can find them in the description below this video.
What database are you going to connect to Google Data Studio?
I'd love to know!
Let me know in the comments below, and if you've found this video helpful, please subscribe,
share it with your friends and hit the like button so I know to make more videos like
this.
See you next time!