How to Connect Google DataStudio to MySQL Database (cPanel Flavor)

How to Connect Google DataStudio to MySQL Database (cPanel Flavor)

Ok, so say you have a cPanel database (say you’ve got some of your company’s data in a self-hosted app you’ve made like the link building database we use in connection with our SEO Services and our DataStudio consulting services. If you want to connect your Google DataStudio reports and use your database as a DataStudio Data Source, we can do this in a few steps, here’s the overview:

1. We need to whitelist some IP addresses so Google’s DataStudio servers can get into your database.
2. We need to open a port on our hosting account.
3. From there, it’s easy, we just make a Data Source Connection

Step 1: Let’s Whitelist Google’s IP Addresses

Hosting accounts (and we are using cPanel here, which is ubiquitous) are going to block random MySQL connections–the need for this security precaution is obvious. But we can open up external connections to friendly IP address. So, luckily, Google has a tutorial that gives us basic instructions and provides some IP ranges where DataStudio will initiate requests to connect:

64.18.0.0/20
64.233.160.0/19
66.102.0.0/20
66.249.80.0/20
72.14.192.0/18
74.125.0.0/16
108.177.8.0/21
173.194.0.0/16
207.126.144.0/20
209.85.128.0/17
216.58.192.0/19
216.239.32.0/19

Step 1, Part 2, Let’s Open Up Remote MySQL Connections

Now we have our friendly Google IP ranges, we need to set our hosting server to accept these IP addresses. Now, here is where your exact steps will depend on your hosting. If you have a dedicated server or VPS server with WHM/cPanel, you are golden. If you have just cPanel, you are still okay, you just need to ask your web host admin to allow these remote connections.

But let’s start with WHM instructions:

• Log in to WHM
• Navigate/Search for Additional MySQL Access Hosts, and you want to enter your IP addresses from the previous step, but note how the syntax has to change:

• Note how 64.18.0.0/20 we had to rewrite as 64.18.%.%. This is because WHM doesn’t understand CIDR notation. So, a word of warning, our IP ranges are wider than those specifically noted by Google, but with crazy-ass passwords, we aren’t horrified.
• Save your list.
• Now, if you don’t have WHM access (sometimes you won’t with cPanel shared hosting), then you have to ask your hosting company to make this change for you. But if you don’t have WHM, you probably should because it’s freaking awesome and you can work faster and with more power.

Step 2: Open a Port, Port 3306

So now we need to consider a firewall port. Keep in mind, that whenever you open a port, you are allowing, potentially, remote access. Should you allow a port to be open to access your Bitcoin private keys? No way. But just keep in mind that remote access like we are doing here does carry security risks.

With that warning, let’s open a common port for MySQL connections, port 3306:

• In WHM, navigate/search for ConfigServer Security & Firewall, also called CSF.
• Find/Hunt for (there are a LOT of settings in CSF, just be patient and look) the section that says Allow Incoming TCP Ports it looks like this, and you can see where I just added 3306 at the end of the list:

• That should be the only firewall setting you need to make. I’ve seen some tutorials that say you need to open UDP ports as well as TCP ports, but I didn’t need to.
• You need to restart CSF, which I was prompted to do, super easy.
• Now, again, if you don’t have full WHM access, you can ask your hosting company to open the port. If they don’t want to open the port, dump them and get better hosting!

That should be enough to prepare your server to receive MySQL connection from DataStudio, so our next step is within the DataStudio interface.

Step 3: Make the DataStudio Data Source Connection

For this step, you’ll want to gather all your database information, database name, user name, and database password. Here’s what we want to connect to, as seen in PHPMyAdmin:

• So let’s head over to Google DataStudio and log in.
• Let’s start a new DataStudio document, or open an existing document.
• A new document will prompt us to add a new data source, or with an existing document open, get into Edit mode and go to Resource on the menu bar, and then Manage Added Data Sources and click Add a New Data Source
• From these choices, select MySQL.
• Then input your MySQL credentials, like so….

• Click “Authenticate” button to connect. If all goes well, you should see your table names in the window like on the right side of the screenshot above.
• If DataStudio won’t connect, the most common culprit is going to be either the firewall or remote SQL connection from Steps 1 and 2.
• Once you make the connection, your database is simply another data source that you can include in reports, like so:

If any of this doesn’t work for you, comment on this post and we’ll see if we can help.