For production, we should use Cloud SQL. However, if we just want to verify the integration with MySQL, we may want to use Always Free GCE.
Because it’s always free!
Free Trial and Free Tier | Google Cloud
Start building on GCP with a Free Trial that includes $300 in credits. Plus, enjoy access to 20+ select products, like Compute Engine, free of charge.
In this case, I have restricted the connection with the Firewall of the VPC network, but if you want to use it in a formal production environment, please ensure security by yourself.
Working Procedure
- Setup GCE (Ubuntu 22.04 LTE)
- Install MySQL
- Setup MySQL
- Firewall settings for VPC network
- Connecting from AppSheet
GCE setup (Ubuntu 22.04 LTE)
Create a GCE instance from the GCP console.
Create a GCE instance.
- Select e2-micro in the First Generation.
- Select us-west1(Oregon) which seems to be close to AppSheet.
- Select Ubuntu 22.04 LTE.
- Select HDD Standard for storage.
- Capacity up to 30GB is free.
Assign a static IP address
- Change from VPC network to static IP address.
Install MySQL
Install MySQL with reference to the following page.
How to Set Up MySQL on Google Compute Engine | Solutions
$ sudo apt-get update
$ sudo apt upgrade
$ sudo apt install mysql-server
MySQL setup
Create database and tables
https://dev.mysql.com/doc/refman/5.7/en/charset-database.html
https://dev.mysql.com/doc/refman/5.7/en/creating-tables.html
ex)
$ sudo mysql -u root
> CREATE DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
> CREATE TABLE your_database.task (id VARCHAR(255) NOT NULL, name VARCHAR(255), description TEXT, priority VARCHAR(20), expirationdate DATE, PRIMARY KEY (id));
Create user
https://dev.mysql.com/doc/refman/5.7/en/create-user.html
ex)
> CREATE USER 'your_user'@'%'IDENTIFIED BY 'your_password';
> GRANT ALL PRIVILEGES ON *. * TO 'your_user'@'%';
> FLUSH PRIVILEGES;
Edit mysqld.cnf
Change MySQL settings.
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
IP limit release
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
Change character encoding
Add the following to the end of the file
[client]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
Restart MySQL
$ sudo service mysql restart
Firewall settings for VPC network
Create Firewall configuration with VPN network
Label Value
| name | mysql-3306-open |
|---|---|
| Target tags | mysql-3306-open |
| Enforcement | Enabled |
| Protocols and ports | tcp:3306 |
- Add the following for IP ranges
34.71.7.214/32, 34.82.138.241/32, 34.83.247.7/32, 34.86.96.199/32, 34.87.102.230/32, 34.87.103.64/32, 34.87.131.237/32, 34.87.159.166/32, 34.87.233.115/32, 34.91.142.99/32, 34.91.161.74/32, 34.116.117.132/32, 34.123.81.112/32, 34.141.206.242/32, 34.145.159.146/32, 35.189.26.70/32, 35.194.89.186/32, 35.197.185.203/32, 35.203.191.15/32, 35.204.102.20/32, 35.204.159.159/32, 35.204.213.55/32, 35.222.253.144/32, 35.230.32.44/32, 35.232.30.149/32, 35.233.206.57/32, 35.239.112.17/32, 35.239.203.99/32, 35.240.241.182/32, 35.240.247.148/32, 35.244.107.184/32, 35.244.126.141/32, 35.245.45.144/32, 35.245.209.204/32, 35.245.229.252/32, 35.247.40.210/32, 35.247.56.116/32
Attach Firewall in Networktqag in VM instance details
AppSheet settings
Add the above MySQL information to the Datasouce in AppSheet
Finally
Let me know if you have any more suggestions for configuring this.
Let’s have some fun with AppSheet using GCP!