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.

Upgrade

When upgrading your service, skipping versions may result in data loss. Upgrade sequentially from one supported version to another (10.5 -> 10.6 -> 10.11 -> 11.0), and check that each upgrade commit translates into an actual deployment.

To upgrade, update the service version in your service configuration.

Change the service type

To change the service type:

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.

Relationship reference

For each service defined via a relationship to your application, Upsun automatically generates corresponding environment variables within your application container, in the $<RELATIONSHIP-NAME>_<SERVICE-PROPERTY> format.

Here is example information available through the service environment variables themselves, or through the PLATFORM_RELATIONSHIPS environment variable.

MariaDB reference

MARIADB_USERNAME=user
MARIADB_SCHEME=mysql
MARIADB_SERVICE=mariadb
MARIADB_FRAGMENT=
MARIADB_IP=123.456.78.90
MARIADB_HOSTNAME=azertyuiopqsdfghjklm.mariadb.service._.eu-1.platformsh.site
MARIADB_PORT=3306
MARIADB_CLUSTER=azertyuiop-main-afdwftq
MARIADB_HOST=mariadbdatabase.internal
MARIADB_REL=mysql
MARIADB_PATH=main
MARIADB_QUERY={'is_master': True}
MARIADB_PASSWORD=
MARIADB_EPOCH=0
MARIADB_TYPE=mariadb:11.4
MARIADB_PUBLIC=false
MARIADB_HOST_MAPPED=false

{
  "username": "user",
  "scheme": "mysql",
  "service": "mariadb",
  "fragment": null,
  "ip": "123.456.78.90",
  "hostname": "azertyuiopqsdfghjklm.mariadb.service._.eu-1.platformsh.site",
  "port": 3306,
  "cluster": "azertyuiop-main-7rqtwti",
  "host": "mariadb.internal",
  "rel": "mysql",
  "path": "main",
  "query": {
    "is_master": true
  },
  "password": "",
  "type": "mariadb:11.4",
  "public": false,
  "host_mapped": false
}

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

# Set environment variables for individual credentials.
export APP_DATABASE_HOST=$(echo $PLATFORM_RELATIONSHIPS | base64 --decode | jq -r ".mariadb[0].host")

Oracle MySQL reference

ORACLE_MYSQL_USERNAME=user
ORACLE_MYSQL_SCHEME=mysql
ORACLE_MYSQL_SERVICE=oracle-mysql
ORACLE_MYSQL_FRAGMENT=
ORACLE_MYSQL_IP=123.456.78.90
ORACLE_MYSQL_HOSTNAME=azertyuiopqsdfghjklm.oracle-mysql.service._.eu-1.platformsh.site
ORACLE_MYSQL_PORT=3306
ORACLE_MYSQL_CLUSTER=azertyuiop-main-afdwftq
ORACLE_MYSQL_HOST=oraclemysql.internal
ORACLE_MYSQL_REL=mysql
ORACLE_MYSQL_PATH=main
ORACLE_MYSQL_QUERY={'is_master': True}
ORACLE_MYSQL_PASSWORD=
ORACLE_MYSQL_EPOCH=0
ORACLE_MYSQL_TYPE=oracle-mysql:8.0
ORACLE_MYSQL_PUBLIC=false
ORACLE_MYSQL_HOST_MAPPED=false

{
  "username": "user",
  "scheme": "mysql",
  "service": "oracle-mysql",
  "fragment": null,
  "ip": "123.456.78.90",
  "hostname": "azertyuiopqsdfghjklm.oracle-mysql.service._.eu-1.platformsh.site",
  "port": 3306,
  "cluster": "azertyuiop-main-afdwftq",
  "host": "oracle_mysql.internal",
  "rel": "mysql",
  "path": "main",
  "query": {
    "is_master": true
  },
  "password": "",
  "type": "oracle-mysql:8.0",
  "public": false,
  "host_mapped": false
}

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

# Set environment variables for individual credentials.
export APP_ORACLE_HOST="$(echo $RELATIONSHIPS_JSON | jq -r '.oraclemysql[0].host')"

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. Define the relationship

To define the relationship, use the following configuration:

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.
    # The example below shows simplified configuration leveraging a default service
    # (identified from the relationship name) and a default endpoint.
    # See the Application reference for all options for defining relationships and endpoints.
    relationships:
      <SERVICE_NAME>:

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.
    # The example below shows configuration with an explicitly set service name and endpoint.
    # See the Application reference for all options for defining relationships and endpoints.
    relationships:
      <RELATIONSHIP_NAME>:
        service: <SERVICE_NAME>
        endpoint: mysql

MariaDB example

applications:
  # The name of the app container. Must be unique within a project.
  myapp:
    # Relationships enable access from this app to a given service.
    # The example below shows simplified configuration leveraging a default service
    # (identified from the relationship name) and a default endpoint.
    # See the Application reference for all options for defining relationships and endpoints.
    relationships:
      mariadb:
services:
  # The name of the service container. Must be unique within a project.
  mariadb:
    type: mariadb:11.4

applications:
  # The name of the app container. Must be unique within a project.
  myapp:
    # Relationships enable access from this app to a given service.
    # The example below shows configuration with an explicitly set service name and endpoint.
    # See the Application reference for all options for defining relationships and endpoints.
    relationships:
      mariadb:
        service: mariadb
        endpoint: mysql
services:
# The name of the service container. Must be unique within a project.
  mariadb:
    type: mariadb:11.4

OracleMySQL example

applications:
  # The name of the app container. Must be unique within a project.
  myapp:
    # The location of the application's code.
    relationships:
      oracle-mysql:
service:
  # The name of the service container. Must be unique within a project.
  oracle-mysql:
    type: oracle-mysql:8.0

applications:
  # The name of the app container. Must be unique within a project.
  myapp:
    # Relationships enable access from this app to a given service.
    # The example below shows configuration with an explicitly set service name and endpoint.
    # See the Application reference for all options for defining relationships and endpoints.
    relationships:
      oracle-mysql:
        service: oracle-mysql_service
        endpoint: mysql
service:
  # The name of the service container. Must be unique within a project.
  oracle-mysql_service:
    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: "/"

    [...]

    # Relationships enable an app container's access to a service.
    relationships:
      mariadb:
service:
  mariadb:
    type: mariadb:11.4

applications:
  # The name of the app container. Must be unique within a project.
  myapp:
    # The location of the application's code.
    source:
      root: "myapp"

    [...]

    # Relationships enable access from this app to a given service.
    # The example below shows configuration with an explicitly set service name and endpoint.
    # See the Application reference for all options for defining relationships and endpoints.
    relationships:
      mariadb:
        service: mariadb
        endpoint: mysql
service:
  mariadb:
    type: mariadb:11.4

This configuration defines a single application (myapp), whose source code exists in the <PROJECT_ROOT>/myapp directory. myapp has access to the mariadb service, via a relationship whose name is identical to the service name (as per default endpoint configuration for relationships).

From this, myapp can retrieve access credentials to the service through the relationship environment variables.

# Set environment variables for individual credentials.
# For more information, please visit https://docs.upsun.com/development/variables.html#service-environment-variables.
export DB_CONNECTION==${MARIADB_SCHEME}
export DB_USERNAME=${MARIADB_USERNAME}
export DB_PASSWORD=${MARIADB_PASSWORD}
export DB_HOST=${MARIADB_HOST}
export DB_PORT=${MARIADB_PORT}
export DB_DATABASE=${MARIADB_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 MARIADB_URL can be used within the application to connect to the service.

Note that DATABASE_URL, and all Upsun service environment variables like MARIADB_HOST, 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 ssh env

The result is the complete information for all relationships with an additional DATABASE_URL property, defined on step Use in app.
Use the DATABASE_URL property as your connection.

You can obtain the complete list of available service environment variables in your app container by running upsun ssh env.

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 service environment variables 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.
  mariadb:
    type: mariadb:11.4
    configuration:
      schemas:
        - main
      endpoints:
        mysql:
          default_schema: main
          privileges:
            main: admin
      properties:
        max_allowed_packet: 64

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: MARIADB_HOST, MARIADB_PORT, MARIADB_USERNAME, MARIADB_PATH values. Then run the following command:

mysql -h MARIADB_HOST -P MARIADB_PORT -u MARIADB_USERNAME MARIADB_PATH

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

mysql -h mariadb.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 mariadb.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.
  mariadb:
    type: mariadb:11.4
    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 mariadb 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.
  mariadb:
    type: mariadb:11.4
    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"

    [...]

    # Relationships enable access from this app to a given service.
    # The example below shows configuration with explicitly set service names and endpoints.
    # See the Application reference for all options for defining relationships and endpoints.
    relationships:
      database:
        service: mariadb
        endpoint: admin
      reports:
        service: mariadb
        endpoint: reporter
      imports:
        service: mariadb
        endpoint: importer

These relationships are then available in the service environment variables. Each has its own credentials, prefixed with the relationship name, 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.
  mariadb:
    type: mariadb:11.4
    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.