Install MySQL on Always Free GCE and use it from AppSheet

[takuya_miyai]

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!

Google Cloud

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

1. Setup GCE (Ubuntu 22.04 LTE)
2. Install MySQL
3. Setup MySQL
4. Firewall settings for VPC network
5. 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.

Google Cloud

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_0900_ai_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!

[Daniel_Turner]

Are database connections now allowed with AppSheet standard and/or core?

[takuya_miyai]

Hi @Daniel_Turner

For prototypes, SQL server is available in any edition.

As you mentioned, we need a Business or higher plan to deploy it.
“Verify the integration” leads me to what I have written.

[takuya_miyai]

It looks like you now need an Enterprise standard or higher plan to use the Database.

We updated our subscription plans Announcements

Hello everyone, Beginning today, April 12, 2021, you will notice that our Premium and Pro plans have been renamed as Starter and Core, respectively. Your subscription and the included features remain the same - and will even include a few expansions, such as upcoming basic team management in Core. You can refer to our pricing page for details. If you subscribe to one of these plans, there is no action needed on your part - this change will be applied automatically. There are no changes to the P…

[Haduaen123]

---

@takuya_miyai wrote:

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.

---

hi, @takuya_miyai , i've read your post, as i need it too.

thanks for sharing, but i have some questions:

1. is it works from Windows ?

2. are there other tools which paid plan ?

pls your answer.

thanks a lot

[takuya_miyai]

Hi @Haduaen123 

Could it be that you have misunderstood the content of this article?
What specific use case are you trying to address?

[Haduaen123]

Currently im using Spreadseet for my databases, but it seems getting bigger and should be using SQL or something.  do you have idea ? 
as i want to expand using Cloud SQL for free or something. but i dont know the way

please tell me more

[takuya_miyai]

Hi @Haduaen123 

Unfortunately, Cloud SQL does not have a free plan.
However, if you are using it for production apps, I would recommend using Cloud SQL.

If you really want to use free SQL, you should look for such a cloud database service or build a GCE instance of the Free plan and install and use the database as described in this article.

[Rifad]

@Haduaen123 have a look at this post by @SkrOYC 

[Updated] Supabase mets AppSheet - Google Cloud Community  Easier, faster to deploy and use.