This article contains general steps for enabling remote access to MySQL database that is hosted on AWS EC2 instance, and connecting to it using Supermetrics for Google Sheets. Please note that this set of instructions is not exhaustive, and your own setup might need additional configuration as well.
Please note, that you should always consult your system administrator first before doing any changes!
Step 1: Update the security group of the EC2 instance by going to your AWS console and selecting the instance where you are hosting your MySQL database. After this you will have to select the security group, and add a rule in the "Inbound" section for MySQL by selecting "MySQL/Aurora". Add relevant IP address(es) from IP Whitelist List for Database Connections.
Step 2: Update the MySQL binding address by editing the file /etc/mysql/my.cnf, and change the binding address to 0.0.0.0. The file may also be /etc/mysql/conf.d/mysql.cnf for certain versions of MySQL.
bind-address = 0.0.0.0
And then restart your MySQL server:
$ sudo /etc/init.d/mysql restart
Step 3: Create a new user for any host in MySQL. You could use command like this:
CREATE USER 'foo'@'%' IDENTIFIED BY 'your_strong_password';
And grant privileges to relevant table(s):
GRANT SELECT PRIVILEGES ON bar.* TO 'foo'@'%';
And updates to the user privileges:
You may also consider limiting the user privileges on CREATE and GRANT calls by determining specific IP address(es) instead of using '%'.
Step 4: Open Supermetrics for Google Sheets sidebar from Add-ons -> Supermetrics -> Launch sidebar, choose "Database" data source and fill in the necessary info.
After this you can "Test connection" to ensure that everything works, finally click 'Save'.
Please note, that you will have to choose the "Request origin" based on the IP address(es) you have whitelisted on the first step.
Step 5: Write your SQL statement to the "Query SQL" input box, and hit "Get Data to Table" to display the results within your spreadsheet file.