Multiple databases with LAMP app

Overview

Custom PHP apps can be hosted on Cloudron using the LAMP app. This app provides the PHP code access to a single MySQL database. The LAMP app cannot create another database because of Cloudron's isolation and sandboxing practices. In this guide, we will see how to create a custom LAMP app that can use multiple MySQL databases.

Prerequisites

If you are on Windows or have a slow internet connection, we recommend just using a Ubuntu 18.04 VPS. A small 1GB droplet from Digital Ocean will do.

Have your PC/laptop setup with the following tools:

Cloudron CLI tool - You can do this by sudo npm install -g cloudron. Then login to Cloudron using cloudron login my.example.com.
Docker
A free account at Docker Hub or just about any other Docker registry. Use docker login to login into the docker hub account.

Building custom app

Start out by cloning the LAMP 7.4 multidb app package

~$ git clone https://git.cloudron.io/cloudron/lamp7.4-multidb-app.git
Cloning into 'lamp7.4-multidb-app'...
Receiving objects: 100% (841/841), 225.28 KiB | 1.06 MiB/s, done.
Resolving deltas: 100% (552/552), done.

For the curious, the main difference between this app and LAMP 7.4 app package in the App Store is that this uses the multipleDatabases option for mysql addon in CloudronManifest.json. When using this option, an environment variable named CLOUDRON_MYSQL_DATABASE_PREFIX will be set instead of CLOUDRON_MYSQL_DATABASE.

Next, build the app using cloudron build:

~/lamp7.4-multidb-app$ cloudron build --local
Enter repository (e.g registry/username/lamp.cloudronapp.php73): girish/lamp-multidb

Building locally as girish/lamp-multidb:20200518-010956-446902496
...
6597da2e2e52: Layer already exists 
977183d4e999: Layer already exists 
c8be1b8f4d60: Layer already exists 
20200518-012539-407f7a758: digest: sha256:2ed41f543da49d1504fc3994efd107d8a4034dee753731f3073f885cfcf02bed size: 7819

In the example above, girish is my docker hub username and lamp-multidb is the repository name. The build command will build the Dockerfile locally and push the resulting image to Docker Hub with a tagged timestamp (as you can see from the output above).

Install app

The built app can now be installed using cloudron install:

~/lamp-multidb-app$ cloudron install
Location: lamp-multidb
App is being installed.

 => Queued 
 => Registering subdomains 
 => Downloading image ....
 => Setting up addons ..............
 => Creating container 
 => Wait for health check ........

App is installed.

The app is now available at lamp-multidb.example.com! You can use cloudron open to open it in the browser.

Creating databases

When using the multipleDatabases option, Cloudron does not create any databases. Instead, it creates a MySQL user that has the permission to create databases with a prefix.

To create a database, simply use cloudron exec. This gives you a shell which has the same context as the app. By using the env command in the shall, we can see that CLOUDRON_MYSQL_DATABASE_PREFIX is set.

~/lamp-multidb-app$ cloudron exec
root@9bbac34d-452d-49ab-beac-a90f90549085:/app/data# env | grep CLOUDRON_MYSQL_               
CLOUDRON_MYSQL_PORT=3306
CLOUDRON_MYSQL_PASSWORD=cd544c6690c6b6f903b037e24b0f9293d6dfc56fc6c02e05
CLOUDRON_MYSQL_USERNAME=e26ceb5eff7b17b8
CLOUDRON_MYSQL_DATABASE_PREFIX=e26ceb5eff7b17b8_
CLOUDRON_MYSQL_HOST=mysql

We can now use the MySQL CLI tool to create any number of databases with this prefix.

root@9bbac34d-452d-49ab-beac-a90f90549085:/app/data# mysql --user=${CLOUDRON_MYSQL_USERNAME} --password=${CLOUDRON_MYSQL_PASSWORD} --host=${CLOUDRON_MYSQL_HOST}
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2701
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)

mysql> CREATE DATABASE `e26ceb5eff7b17b8_db1`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE `e26ceb5eff7b17b8_db2`;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| e26ceb5eff7b17b8_db1 |
| e26ceb5eff7b17b8_db2 |
+----------------------+
3 rows in set (0.00 sec)

Upload App

The app can be uploaded via SFTP. SFTP credentials are available in the Access Control section of the Cloudron dashboard.

In PHP code, it is recommended to use the database names as getenv('CLOUDRON_MYSQL_DATABASE_PREFIX') . db1 instead of the hardcoded prefix. This makes it possible to use Cloudron's clone feature to clone the app and not make any changes.

Update App

All the databases are part of the app's backup! The app package we cloned also has redis and sendmail addons. You can remove them from the manifest, if you don't intend to use them and adjust start.sh accordingly.

As this app was custom built, you have the responsibility of keeping it up-to-date. This is fairly easy. All you have to do is cloudron build and cloudron update:

~/lamp-multidb-app$ cloudron build
Building locally as girish/lamp-multidb:20200518-014229-318890494

Sending build context to Docker daemon  53.76kB
...

20200518-014229-318890494: digest: sha256:48215586dbcd980abb6e467b353e5bff42285271368d7c7daede24291497af77 size: 7819


~/tmp/lamp-multidb-app$ cloudron update

 => Waiting for app to be updated 
 => Queued 
 => Backup - Snapshotting app lamp-multidb.cloudron.fun .
 => Backup - Copying XJivOprCjge3aQ9woWIDYeyNrZ0m-CuDFbkXW95brWE (lamp-multidb.cloudron.fun) 
 => Downloading image 
 => Updating addons 
 => Creating container 
 => Wait for health check ....

App is updated.