If you have a MySQL database hosted on an AWS EC2 instance, you can connect it to Google Sheets with Supermetrics. Set up a connection to your database and query your data directly in Google Sheets.
Before you begin
These instructions include general steps for enabling remote access to your database. They are not exhaustive, and your setup might require additional configuration.
Please be sure to consult your system administrator before making any changes to your database.
Instructions
Step 1: Network access
- Go to the AWS console.
- Navigate to Security Groups.
- Select the security group of the EC2 instance where you are hosting your MySQL database.
- Add a rule in the Inbound section by selecting MySQL/Aurora and add the relevant IP addresses.
See AWS documentation to learn more about DB security groups.
Step 2: MySQL binding address
- Edit the file /etc/mysql/my.cnf. This could also be /etc/mysql/conf.d/mysql.cnf for certain versions of MySQL.
- Change the binding address to 0.0.0.0:
bind-address = 0.0.0.0 - Restart your MySQL server:
sudo /etc/init.d/mysql restart
Step 3: User and privileges
- Create a new user for any host in MySQL. You could use a command like this:
CREATE USER 'foo'@'%' IDENTIFIED BY 'your_strong_password'; - Grant the user privileges to the relevant tables:
GRANT SELECT PRIVILEGES ON bar.* TO 'foo'@'%'; - Update the user privileges:
FLUSH PRIVILEGES;
- You might also consider limiting the user’s privileges on CREATE and GRANT calls by determining specific IP address(es) instead of using '%'.
Step 4: Supermetrics sidebar
- Open the Supermetrics sidebar in Google Sheets.
- Under Data source, select Database.
- Fill in the requested details.
- The Request origin should be selected based on the IP addresses you allowlisted in step 1.
- Click Test connection to verify the configuration is correct and Save the data source.
- Write your SQL statement to the Query SQL input box.
- Click Get Data to Table to create your query and pull your data.