Multiple databases with LAMP app

Overview

Custom PHP apps can be hosted on Cloudron using the LAMP app. This app provides PHP code access to a single MySQL database. Cloudron's isolation and sandboxing practices prevent the LAMP app from creating additional databases.

This guide explains how to create a custom LAMP app that can use multiple MySQL databases.

Prerequisites

For Windows or slow internet connections, use an Ubuntu 18.04 VPS. A small 1GB droplet from Digital Ocean works.

Set up your PC/laptop with:

• Cloudron CLI tool - Install with sudo npm install -g cloudron, then login with cloudron login my.example.com
• Docker
• A free account at Docker Hub or any Docker registry. Login with docker login

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.

The main difference between this app and the LAMP 7.4 app package in the App Store is the multipleDatabases option for the mysql addon in CloudronManifest.json. This option sets CLOUDRON_MYSQL_DATABASE_PREFIX instead of CLOUDRON_MYSQL_DATABASE.

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 the Docker Hub username and lamp-multidb is the repository name. The build command builds the Dockerfile locally and pushes the resulting image to Docker Hub with a tagged timestamp.

Install app

Install the built app 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

With the multipleDatabases option, Cloudron creates a MySQL user with permission to create databases with a prefix, but does not create any databases.

Use cloudron exec to get a shell with the app's context. The env command shows 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

Use the MySQL CLI tool to create 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

Upload the app via SFTP. SFTP credentials are available in the Access Control section of the Cloudron dashboard.

In PHP code, use getenv('CLOUDRON_MYSQL_DATABASE_PREFIX') . db1 instead of hardcoded prefixes. This enables Cloudron's clone feature without code changes.

Update app

All databases are included in the app's backup. The cloned app package includes redis and sendmail addons. Remove them from the manifest and adjust start.sh if not needed.

Since this is a custom-built app, you must keep it updated. Run 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.