Install MySQL on Always Free GCE and use it from AppSheet

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!:grin:

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 16.04 LTE)
  2. Install MySQL
  3. Setup MySQL
  4. Firewall settings for VPC network
  5. Connecting from AppSheet

GCE setup (Ubuntu 16.04 LTE)

Create a GCE instance from the GCP console.

Create a GCE instance.

  • Select f1-micro in the First Generation.
  • Select us-west1(Oregon) which seems to be close to AppSheet.:yum:
  • Select Ubuntu 16.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.

$ sudo apt-get update
$ sudo apt-get -y 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)

> CREATE DATABASE your_database CHARACTER SET utf8 COLLATE utf8_general_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=utf8

[mysqld]
character-set-server=utf8

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
20.189.130.98/32, 20.189.132.63/32, 40.67.216.152/32, 40.67.216.182/32, 40.67.219.84/32, 138.91.195.239/32, 20.189.139.27/32, 20.189.138. 55/32, 20.189.138.139/32, 20.189.139.109/32, 20.189.139.123/32, 20.189.138.180/32, 20.189.138.40/32, 52.149.110.221/32, 52.149.111.209/32 , 52.149.111.211/32, 52.149.111.248/32, 51.144.56.20/32, 51.144.57.117/32, 52.138.118.136/32, 52.138.118.137/32, 34.87.131.237/32, 34.87. 103.64/32, 35.197.185.203/32, 35.244.126.141/32, 35.204.213.55/32, 34.91.161.74/32, 35.222.253.144/32, 34.71.7.214/32, 35.194.89.186/32, 35.245.209.204/32, 35.203.191.15/32, 35.247.56.116/32, 35.240.247.148/32, 34.87.159.166/32, 34.87.233.115/32, 35.244.107.184/32, 35.204. 102.20/32, 35.204.159.159/32, 35.239.203.99/32, 35.239.112.17/32, 34.86.96.199/32, 35.245.229.252/32, 34.83.247.7/32, 35.247.40.210/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!:hugs:

5 Likes

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

1 Like

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.

1 Like

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

4 Likes