Node.js and MySQL on the Oracle Cloud

Let's explore how to deploy a node.js app with MySQL backend on the Oracle Cloud. I want to cover several aspects:

• How to deploy and initialize a MySQL instance
• How to adapt the source code
• How to deploy the application
• How to scale the application

There are different ways to configure this. I tested the easiest deployment with MySQL Cloud Service and the Application Container Cloud for the node.js part. All configurations are done via the cloud web GUI. There is also a REST interface available. But let's keep that for later.
If you don't have access to the Oracle Cloud you can get a trial access here.

How to deploy a MySQL instance

Once you logged into the Oracle cloud you can create new instances from the dashboard. The following screenshots describe the process.

On the next screen we upload the public key for admin access to the instance. Either upload your own public key or generate a new key pair. (If you generate a new key pair you need to download the private key to your local machine.)

I skipped the backup and monitoring configurations for this demo. Let's focus on the application instead. After creating the instance (approx. 10 min) you can navigate via the dashboard to this instance and get the IP address. This is needed for the next step.
To initialize the database I ran this little script that runs ssh to the instance (using the private key), switch user to "oracle" and then call the MySQL CLI to run a few SQL statements.

How to adapt the source code

The Application Container Cloud sets a few environment variables that should be used inside the application to adapt to the environment. In my case this are the following variables:

• PORT is the port number that the application should listen on
• MYSQLCS_USER_NAME is the MySQL user name for the database backend
• MYSQLCS_USER_PASSWORD is the corresponding password
• MYSQLCS_CONNECT_STRING is the hostname and port of the database backend

I could have hardcoded the database connection parameters but that is inflexible if the architecture changes. So let's use these variables. The Node.js code looks like this:

How to deploy the application

There are two simple steps needed: Creating an application and defining service bindings. In my case the only service binding is the MySQL backend. But one step after the other. First let's create the application. First you need to create a manifest.json file to describe the application. Here is mine:

Ideally you create a zip archive with the source code, the manifest.json file and all other resources that your application needs. If you want to use my zip archive, feel free. You find it on GitHub.

From the Oracle Cloud Dashboard click on "create instance -> application container" and then select "Create Instance" and "Node.js". (Java SE, Java EE, Python, Ruby and PHP are available as well.)

On the pop-up you define the application artifacts, number of application instances and the memory per instance. After you click "create" the application is deployed automatically within a few minutes.
The last step is to connect the application service with the database backend. To achieve that, click on the application in the application overview page. Here you find the URL under which your application will be available. And on the left hand side you see three tabs:

Overview, Deployments and Administration. Click on "Deployments". Here you can add the service binding as described in the following screenshot:

After modifying the service bindings you have to click "Apply changes". This will restart the application instances. Obviously needed because now the environment variables for the database backend are set correctly.
That's it. We have an application. The URL to access the new app is listed in the application overview tab. Because this URL is not so nice for offering a short url service, I registered a new domain and forwarded that to the anota application. Maybe it is still running? Check here.

How to scale the application

This is really easy. On the application overview tab you can just increase the number of instances and the memory per instance. After applying the changes, the Application Container Cloud platform will deploy new instances, stop spare instances or reconfigure the existing instances. If you use my ANOTA application, go to the report page. The last line prints the hostname of the application server. Requests are automatically load balanced between the available application instances. 

Summary

There are some minor changes to the application to run on the Oracle Cloud Platform: Reading the port variable and database connection parameters from the provided environment variables and that's it. Deployment is really easy via the GUI. And scalability is so simple now that the full Oracle Cloud Plattform is available and can be provisioned within minutes. 

MySQL Shell - Easy scripting

With the introduction of MySQL InnoDB Cluster we also got the MySQL Shell (mysqlsh) interface. The shell offers scripting in Javascript (default), SQL or Python. This offers a lot more options for writing scripts on MySQL, for example it is much easier now to use multiple server connections in a single script.
A customer recently asked for a way to compare the transaction sets between servers. That is useful when setting up replication or identifying the server that has most transactions applied already. So I wrote this little script which can be executed from the OS shell:

 #!/usr/bin/mysqlsh -f  
 // it is important to connect to the X protocol port,  
 // usually it is the traditional port + "0"  
 //  
 var serverA="root:root@localhost:40010"  
 var serverB="root:root@localhost:50010"  
 shell.connect(serverA)  
 var gtidA=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]  
 shell.connect(serverB)  
 var gtidB=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]  
 //  
 // If you want to use pure XdevAPI the former statements should be  
 //  
 // gtid = session.getSchema("performance_schema").global_variables.select("VARIABLE_VALUE").where("VARIABLE_NAME='gtid_executed'").execute().fetchOne()[0]  
 //  
 println(" ")  
 println ("Transactions that exist only on "+serverA)  
 println (session.sql("SELECT gtid_subtract('"+gtidA+"','"+gtidB+"')").execute().fetchOne()[0])  
 println(" ")  
 println ("Transactions that exist only on "+serverB)  
 println (session.sql("SELECT gtid_subtract('"+gtidB+"','"+gtidA+"')").execute().fetchOne()[0])