MariaDB/MySQL (database service)

Upsun supports both MariaDB and Oracle MySQL to manage your relational databases. Their infrastructure setup is nearly identical, though they differ in some features. See the MariaDB documentation or MySQL documentation for more information.

Supported versions

You can select the major and minor version.

Patch versions are applied periodically for bug fixes and the like. When you deploy your app, you always get the latest available patches.

The service types mariadb and mysql both refer to MariaDB. The service type oracle-mysql refers to MySQL as released by Oracle, Inc. Other than the value for their type, MySQL and MariaDB have the same behavior and the rest of this page applies to both of them.

Deprecated versions

The following versions are deprecated. They’re available, but they aren’t receiving security updates from upstream and aren’t guaranteed to work. They’ll be removed in the future, so migrate to one of the supported versions.

Switching type and version

If you change the service type, your data is removed.

To switch service types:

1. Export your data.
2. Remove the old service from your service configuration.
3. Specify a new service type.
4. Import your data into the new service.

Downgrade

You can’t downgrade to a previous version and retain your data. To downgrade your database, follow these steps:

1. Export your data.
2. Remove the old service from your service configuration.
3. Add a new service with a different name and your desired version.
4. Import your data into the new service.

Usage example

Configure your service with at least 256 MB in disk space.

1. Configure the service

To define the service, use the mariadb or mysql type for MariaDB or the oracle-mysql type for Oracle MySQL :

services:
    # The name of the service container. Must be unique within a project.
    <SERVICE_NAME>:
        type: mariadb:<VERSION>

Note that changing the name of the service replaces it with a brand new service and all existing data is lost. Back up your data before changing the service.

2. Add the relationship

To define the relationship, use the mysql endpoint (unless you have multiple databases):

applications:
    # The name of the app container. Must be unique within a project.
    <APP_NAME>:
        # Relationships enable access from this app to a given service.
        relationships:
            <RELATIONSHIP_NAME>: "<SERVICE_NAME>:mysql"

services:
    # The name of the service container. Must be unique within a project.
    <SERVICE_NAME>:
        type: mariadb:<VERSION>

You can define <SERVICE_NAME> and <RELATIONSHIP_NAME> as you like, but it’s best if they’re distinct. With this definition, the application container (<APP_NAME>) now has access to the service via the relationship <RELATIONSHIP_NAME>.

MariaDB example

App and Service configuration

applications:
    # The name of the app container. Must be unique within a project.
    myapp:
        # Relationships enable access from this app to a given service.
        relationships:
            database: "db:mysql"

services:
    # The name of the service container. Must be unique within a project.
    db:
        type: mariadb:11.2

OracleMySQL example

App and Service configuration

applications:
    # The name of the app container. Must be unique within a project.
    myapp:
        relationships:
            mysqldatabase: "dbmysql:mysql"

services:
    # The name of the service container. Must be unique within a project.
    dbmysql:
        type: oracle-mysql:8.0

Use in app

To use the configured service in your app, add a configuration file similar to the following to your project.

applications:
    # The name of the app container. Must be unique within a project.
    myapp:
        # The location of the application's code.
        source:
            root: "myapp"
        # Other options...
        
        # Relationships enable an app container's access to a service.
        relationships:
            database: "db:mysql"
services:
    # The name of the service container. Must be unique within a project.
    db:
        type: mariadb:11.2

This configuration defines a single application myapp, whose source code exists in the directory <PROJECT_ROOT>/myapp, and has been provided access to the service (db) via the relationship database.

From this, myapp can retrieve access credentials to the service through the environment variable PLATFORM_RELATIONSHIPS. That variable is a base64-encoded JSON object, but can be decoded at runtime (using the built-in tool jq) to provide more accessible environment variables to use within the application itself:

# Decode the built-in credentials object variable.
export RELATIONSHIPS_JSON=$(echo $PLATFORM_RELATIONSHIPS | base64 --decode)

# Set environment variables for individual credentials.
export DB_CONNECTION=="$(echo $RELATIONSHIPS_JSON | jq -r '.database[0].scheme')"
export DB_USERNAME="$(echo $RELATIONSHIPS_JSON | jq -r '.database[0].username')"
export DB_PASSWORD="$(echo $RELATIONSHIPS_JSON | jq -r '.database[0].password')"
export DB_HOST="$(echo $RELATIONSHIPS_JSON | jq -r '.database[0].host')"
export DB_PORT="$(echo $RELATIONSHIPS_JSON | jq -r '.database[0].port')"
export DB_DATABASE="$(echo $RELATIONSHIPS_JSON | jq -r '.database[0].path')"

# Surface connection string variable for use in app.
export DATABASE_URL="${DB_CONNECTION}://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}"

The above file — .environment in the myapp directory — is automatically sourced by Upsun into the runtime environment, so that the variable DATABASE_URL can be used within the application to connect to the service.

Note that DATABASE_URL, and all Upsun-provided environment variables like PLATFORM_RELATIONSHIPS, are environment-dependent. Unlike the build produced for a given commit, they can’t be reused across environments and only allow your app to connect to a single service instance on a single environment.

A file very similar to this is generated automatically for your when using the upsun ify command to migrate a codebase to Upsun.

Configure connections

There may be cases where you want to configure a database connection manually.

To get the URL to connect to the database, run the following command:

upsun relationships

The result is the complete information for all relationships with an additional url property. Use the url property as your connection.

Note that the information about the relationship can change when an app is redeployed or restarted or the relationship is changed. So your apps should only rely on the PLATFORM_RELATIONSHIPS environment variable directly rather than hard coding any values.

You can also see a guide on how to convert the PLATFORM_RELATIONSHIPS environment variable to a different form.

Configuration options

You can configure your MySQL service in the services configuration with the following options:

Example configuration:

services:
    # The name of the service container. Must be unique within a project.
    db:
        type: mariadb:11.2
        configuration:
            schemas:
                - main
            endpoints:
                mysql:
                    default_schema: main
                    privileges:
                        main: admin
            properties:
                max_allowed_packet: 64

Relationship reference

Example information available through the PLATFORM_RELATIONSHIPS environment variable or by running upsun relationships.

Note that the information about the relationship can change when an app is redeployed or restarted or the relationship is changed. So your apps should only rely on the PLATFORM_RELATIONSHIPS environment variable directly rather than hard coding any values.

MariaDB reference

{
    "username": "user",
    "scheme": "mysql",
    "service": "mariadb104",
    "fragment": null,
    "ip": "169.254.255.221",
    "hostname": "e3wffyxtwnrxujeyg5u3kvqi6y.mariadb104.service._.eu-3.upsunapp.com",
    "port": 3306,
    "cluster": "rjify4yjcwxaa-master-7rqtwti",
    "host": "mysql.internal",
    "rel": "mysql",
    "path": "main",
    "query": {
        "is_master": true
    },
    "password": "",
    "type": "mariadb:11.2",
    "public": false,
    "host_mapped": false
}

Oracle MySQL reference

{
    "username": "user",
    "scheme": "mysql",
    "service": "oraclemysql",
    "fragment": null,
    "ip": "169.254.150.190",
    "hostname": "7q5hllmmhoeuthu6th7qovoone.oraclemysql.service._.eu-3.upsunapp.com",
    "port": 3306,
    "cluster": "rjify4yjcwxaa-master-7rqtwti",
    "host": "oraclemysql.internal",
    "rel": "mysql",
    "path": "main",
    "query": {
        "is_master": true
    },
    "password": "",
    "type": "oracle-mysql:8.0",
    "public": false,
    "host_mapped": false
}

Access the service directly

You can access the service using the Upsun CLI by running upsun sql.

You can also access it from you app container via SSH. From your relationship data, you need: host, port, user, path. Then run the following command:

mysql -h HOST -P PORT -u USER PATH

Assuming the values from the MariaDB reference, that would be:

mysql -h mysql.internal -P 3306 -u user main

If your database relationship has a password, pass the -p switch and enter the password when prompted:

mysql -p -h mysql.internal -P 3306 -u user main

Define permissions

With version 10.0 or later, you can define multiple users with different permissions for your database. To do so, define multiple endpoints in your service configuration.

For each endpoint you add, you can define the following properties:

Possible permissions:

Multiple databases

With version 10.0 or later, you can define multiple databases. To do so, define multiple schemas in your service configuration.

You can also specify multiple endpoints for permissions. If neither schemas nor endpoints is included, it’s equivalent to the following default:

services:
    # The name of the service container. Must be unique within a project.
    db:
        type: mariadb:11.2
        configuration:
            schemas:
                - main
            endpoints:
                mysql:
                    default_schema: main
                    privileges:
                        main: admin

If either schemas or endpoints are defined, no default is applied and you have to specify the full configuration.

Multiple databases example

The following configuration example creates a single MariaDB service named db with two databases, main and legacy. Access to the database is defined through three endpoints:

• admin has full access to both databases.
• reporter has SELECT query access to main but no access to legacy.
• importer has SELECT/INSERT/UPDATE/DELETE (but not DDL) access to legacy but no access to main.

services:
    # The name of the service container. Must be unique within a project.
    db:
        type: mariadb:11.2
        configuration:
            schemas:
                - main
                - legacy
            endpoints:
                admin:
                    default_schema: main
                    privileges:
                        main: admin
                        legacy: admin
                reporter:
                    privileges:
                        main: ro
                importer:
                    default_schema: legacy
                    privileges:
                        legacy: rw

Expose these endpoints to your app as relationships in your app configuration:

applications:
    # The name of the app container. Must be unique within a project.
    myapp:
        # The location of the application's code.
        source:
            root: "myapp"
        # Other options...
        
        # Relationships enable an app container's access to a service.
        relationships:
            database: "db:admin"
            reports: "db:reporter"
            imports: "db:importer"

These relationships are then available in the PLATFORM_RELATIONSHIPS environment variable. Each has its own credentials you can use to connect to the given database.

Configure the database

For MariaDB 10.1 and later and Oracle MySQL 8.0 and later, you can set some configuration properties (equivalent to using a my.cnf file).

In your settings, add the properties key to the configuration key. It offers the following properties:

An example of setting these properties:

services:
    # The name of the service container. Must be unique within a project.
    db:
        type: mariadb:11.2
        configuration:
            properties:
                max_allowed_packet: 64
                default_charset: utf8mb4
                default_collation: utf8mb4_unicode_ci

You can also change a table’s character set and collation through ALTER TABLE commands:

-- To change defaults when creating new tables:
ALTER DATABASE main CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- To change defaults when creating new columns:
ALTER TABLE table_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- To convert existing data:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For further details, see the MariaDB documentation.

Password generation

When you connect your app to a database, an empty password is generated for the database by default. This can cause issues with your app.

To generate real passwords for your database, define custom endpoints in your service configuration. For each custom endpoint you create, you get an automatically generated password, similarly to when you create multiple databases. Note that you can’t customize these automatically generated passwords.

After your custom endpoints are exposed as relationships in your app configuration, you can retrieve the password for each endpoint through the PLATFORM_RELATIONSHIPS environment variable within your application containers. The password value changes automatically over time, to avoid downtime its value has to be read dynamically by your app. Globally speaking, having passwords hard-coded into your codebase can cause security issues and should be avoided.

When you switch from the default configuration with an empty password to custom endpoints, make sure your service name remains unchanged. Failure to do so results in the creation of a new service, which removes any existing data from your database.

Storage Engine

It’s best to use the InnoDB storage engine wherever possible instead of MyISAM. If MyISAM tables have been inadvertently created or imported in your environments (if you see ENGINE=MyISAM in the response to SHOW CREATE TABLE EXISTING_TABLE), convert them to use the InnoDB storage engine as follows:

1. Rename the existing table.

   RENAME TABLE EXISTING_TABLE OLD_TABLE;

2. Create a new table from the data in the existing table.

   CREATE TABLE EXISTING_TABLE SELECT * from OLD_TABLE;

Now when you run SHOW CREATE TABLE EXISTING_TABLE, you see ENGINE=InnoDB.

Service timezone

To change the timezone for a given connection, run SET time_zone = TIMEZONE;.

Exporting data

To download all data from your SQL database, use the Upsun CLI. If you have a single SQL database, the following command exports all data to a local file:

upsun db:dump

If you have multiple SQL databases, you are prompted for which one to export. You can also specify a database by its relationship name:

upsun db:dump --relationship RELATIONSHIP_NAME

Compression

By default, the file is uncompressed. To compress it, use the --gzip (-z) option:

upsun db:dump --gzip

Using the output in bash

To pipe the result to another command, use the --stdout option. For example, to create a bzip2-compressed file, run:

upsun db:dump --stdout | bzip2 > dump.sql.bz2

Importing data

To load data into a database, pipe an SQL dump through the upsun sql command, like so:

upsun sql < my_database_backup.sql

That runs the database backup against the SQL database on Upsun. That works for any SQL file, so the usual caveats about importing an SQL dump apply (for example, it’s best to run against an empty database).

As with exporting, you can specify a specific environment and a specific database relationship to use:

upsun sql --relationship RELATIONSHIP_NAME -e BRANCH_NAME < my_database_backup.sql

Sanitizing data

To ensure people who review code changes can’t access personally identifiable information stored in your database, sanitize your preview environments.

Replication

There is no on-site primary/replica support in your environments.

In rare cases (such as for certain backup purposes), you can also enable remote replication to your own replica data. The replica isn’t available to your application.

Troubleshoot

If you run into issues, troubleshoot MySQL.