Upsun User Documentation

MariaDB/MySQL (database service)

Try Upsun for 15 days
After that, enjoy the same, game-changing Upsun features for less with the First Project Incentive!¹ A monthly $19 perk!
¹Terms and conditions apply
Activate your 15-day trial

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 Anchor to this heading

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.

mariadb mysql oracle-mysql
  • 11.4
  • 11.2
  • 11.0
  • 10.11
  • 10.6
  • 10.5
  • 10.4
  • 11.0
  • 10.11
  • 10.6
  • 10.5
  • 10.4
  • 10.3
  • 8.0
  • 5.7

Deprecated versions Anchor to this heading

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.

mariadb mysql oracle-mysql
  • 10.2
  • 10.1
  • 10.3
  • 10.0
  • 5.5
  • 10.2
  • 10.1
  • 10.3
  • 10.0
  • 5.5

Upgrade Anchor to this heading

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 Anchor to this heading

To change the service type:

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

Downgrade Anchor to this heading

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 Anchor to this heading

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 Anchor to this heading

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.

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

For some advanced use cases, you can use the PLATFORM_RELATIONSHIPS environment variable. The structure of the PLATFORM_RELATIONSHIPS environment variable can be obtained by running upsun relationships in your terminal.

{
    "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
}

Example on how to gather PLATFORM_RELATIONSHIPS environment variable information in a .environment file:

.environment
# 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 Anchor to this heading

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.

ORACLEMYSQL_USERNAME=user
ORACLEMYSQL_SCHEME=mysql
ORACLEMYSQL_SERVICE=oracle-mysql
ORACLEMYSQL_FRAGMENT=
ORACLEMYSQL_IP=123.456.78.90
ORACLEMYSQL_HOSTNAME=azertyuiopqsdfghjklm.oracle-mysql.service._.eu-1.platformsh.site
ORACLEMYSQL_PORT=3306
ORACLEMYSQL_CLUSTER=azertyuiop-main-afdwftq
ORACLEMYSQL_HOST=oraclemysql.internal
ORACLEMYSQL_REL=mysql
ORACLEMYSQL_PATH=main
ORACLEMYSQL_QUERY={'is_master': True}
ORACLEMYSQL_PASSWORD=
ORACLEMYSQL_EPOCH=0
ORACLEMYSQL_TYPE=oracle-mysql:8.0
ORACLEMYSQL_PUBLIC=false
ORACLEMYSQL_HOST_MAPPED=false

For some advanced use cases, you can use the PLATFORM_RELATIONSHIPS environment variable. The structure of the PLATFORM_RELATIONSHIPS environment variable can be obtained by running upsun relationships in your terminal.

{
    "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": "oraclemysql.internal",
    "rel": "mysql",
    "path": "main",
    "query": {
        "is_master": true
    },
    "password": "",
    "type": "oracle-mysql:8.0",
    "public": false,
    "host_mapped": false
}

Example on how to gather PLATFORM_RELATIONSHIPS environment variable information in a .environment file:

.environment
# 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 Anchor to this heading

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

1. Configure the service Anchor to this heading

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

.upsun/config.yaml
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 Anchor to this heading

To define the relationship, use the following configuration:

.upsun/config.yaml
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>: 
services:
    # The name of the service container. Must be unique within a project.
    <SERVICE_NAME>:
        type: mariadb:<VERSION>

You can define <SERVICE_NAME> as you like, so long as it’s unique between all defined services and matches in both the application and services configuration.

The example above leverages default endpoint configuration for relationships. That is, it uses default endpoints behind-the-scenes, providing a relationship (the network address a service is accessible from) that is identical to the name of that service.

Depending on your needs, instead of default endpoint configuration, you can use explicit endpoint configuration.

With the above definition, the application container (<APP_NAME>) now has access to the service via the relationship <RELATIONSHIP_NAME> and its corresponding service environment variables.

MariaDB example Anchor to this heading

.upsun/config.yaml
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

OracleMySQL example Anchor to this heading

.upsun/config.yaml
applications:
    # The name of the app container. Must be unique within a project.
    myapp:
        # The location of the application's code.
        relationships:
            oraclemysql:

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

Use in app Anchor to this heading

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

.upsun/config.yaml
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

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.

myapp/.environment
# 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 MARIADB_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 Anchor to this heading

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 Anchor to this heading

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

Name Type Version Description
schemas An array of strings 10.0+ All databases to be created. Defaults to a single main database.
endpoints An endpoints dictionary 10.0+ Endpoints with their permissions. See multiple databases.
properties A properties dictionary MariaDB: 10.1+; Oracle MySQL: 8.0+ Additional properties for the database. Equivalent to using a my.cnf file. See property options.

Example configuration:

.upsun/config.yaml
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 Anchor to this heading

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 Anchor to this heading

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:

Name Type Required Description
default_schema string Which of the defined schemas to default to. If not specified, the path property of the relationship is null and so tools such as the Upsun CLI can’t access the relationship.
privileges A permissions dictionary For each of the defined schemas, what permissions the given endpoint has.

Possible permissions:

Name Type Description
Read-only ro Can select, create temporary tables, and see views.
Read-write rw In addition to read-only permissions, can also insert, update, delete, manage and execute events, execute routines, create and drop indexes, manage and execute triggers, and lock tables.
Admin admin In addition to read-write permissions, can also create, drop, and alter tables; create views; and create and alter routines.
Replication replication For replicating databases. In addition to read-only permissions, can also lock tables.

Multiple databases Anchor to this heading

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:

.upsun/config.yaml
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 Anchor to this heading

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.
.upsun/config.yaml
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:

.upsun/config.yaml
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 an app container's access to a service.
        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 Anchor to this heading

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:

Name Type Default Description
max_allowed_packet integer 16 The maximum size for packets in MB. Can be from 1 to 100.
default_charset string utf8mb4 after February 2020 and latin1 before The default character set. Affects any tables created after it’s set.
default_collation string utf8mb4_unicode_ci after February 2020 and latin1 before The default collation. Affects any tables created after it’s set.
optimizer_switch string A place to set various server optimization variables. See the MariaDB documentation.
optimizer_use_condition_selectivity integer 4 in version 10.4.1+ and 1 before that Which statistics are used by the optimizer. From 1 to 5. See the MariaDB documentation.
innodb_adaptive_hash_index integer 0 in version 10.5+ and 1 before that Enable/Disable InnoDB Hash Index. See the MariaDB documentation.
max_heap_table_size integer 32 The maximum size for user-created MEMORY tables in MB. Can be from 1 to 4096.
table_definition_cache integer 400 The number of table definitions that can be cached. See the MariaDB documentation.
table_open_cache integer 400 The maximum number of open tables cached in one table cache instance. See the MariaDB documentation.
wsrep_sync_wait integer 0 (Disabled) Ensure execution of statements in fully synced nodes. See the MariaDB documentation.

An example of setting these properties:

.upsun/config.yaml
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 Anchor to this heading

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 Anchor to this heading

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 Anchor to this heading

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

Exporting data Anchor to this heading

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 Anchor to this heading

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

upsun db:dump --gzip

Using the output in bash Anchor to this heading

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 Anchor to this heading

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 Anchor to this heading

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

Replication Anchor to this heading

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 Anchor to this heading

If you run into issues, troubleshoot MySQL.

Is this page helpful?