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 the Oracle MySQL Server documentation for more information.

MariaDB note

The example provided later in this topic is specific to using only a primary MariaDB database. For details about using read-only replicas to improve performance of read-heavy applications, see the MariaDB read-only replication topic.

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.

Both mariadb and mysql service types use MariaDB.
They behave identically, so the information on this page applies to both of them.
The service type oracle-mysql refers to MySQL as released by Oracle, Inc.

mariadb / mysql
oracle-mysql

11.8
11.4
10.11
10.6

Upgrade

Branch locally from production. For example: git branch -c database-upgrade.
Important: Push the branch (git push) before making any changes. This step is required — it ensures your data is copied to a new container before the upgrade begins.
In .upsun/config.yaml, update the service type key to include the target version. You can upgrade directly to any higher supported version — sequential upgrades are not required.

Do not change the service name. Changing the service name deletes the existing container and all its data.

Commit and push the changes.
You can now test the new version on the branch you created in step 1 (e.g. the database-upgrade branch).
Verify the upgrade; then, merge your branch (e.g. database-upgrade) into production. Expect a slightly longer deployment as the database restarts during the upgrade.

Downgrade

Downgrading requires changing the service name, which permanently deletes the existing container and all its data.Before downgrading, back up your environment for rollback, and export your data — you’ll reimport it into the re-created service.

Back up your environment. If you accidentally delete the wrong service (or make an error in your configuration files) and need to revert your entire environment, the backup enables you to do so.
Export the data. Exporting the data to a portable file enables you to import it later. You cannot import data directly from a backup of your environment.
Review the dump file for version compatibility issues.
Your exported data might contain features that don’t exist in the older target version. Review the dump file for compatibility issues such as:
- Lines starting with /*! followed by a number (e.g. /*!100200). These are version-conditional statements — if the number corresponds to a version newer than your target, that SQL may not run correctly.
- Data types introduced after the target version (e.g. JSON columns added in MariaDB 10.2)
- SQL syntax introduced after the target version, such as window functions or CTEs (WITH)
In .upsun/config.yaml, change the service name and replace the version in the service type key with the lower target version.
Commit and push the changes. A new container is created with an empty database.
Import the data into the new service.

Deprecated versions

The following versions are still available in your projects, but they’re at their end of life and are no longer receiving security updates from upstream.

mariadb / mysql
oracle-mysql

No deprecated versions.

To ensure your project remains stable in the future, switch to a supported version.

Retired versions

The following versions have been retired and are no longer available. If your project uses a retired version, you must update to a supported version.

mariadb / mysql
oracle-mysql

11.2
11.0
10.5
10.4
10.3
10.2
10.1
10.0
5.5

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

Service environment variables
`PLATFORM_RELATIONSHIPS` environment variable

You can obtain the complete list of available service environment variables in your app container by running upsun ssh env.Service connection details can change whenever your app restarts or redeploys. To keep your connection stable, use service environment variables rather than hard-coding values.

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.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

Service environment variables
`PLATFORM_RELATIONSHIPS` environment variable

You can obtain the complete list of available service environment variables in your app container by running upsun ssh env.Service connection details can change whenever your app restarts or redeploys. To keep your connection stable, use service environment variables rather than hard-coding values.

.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

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 : 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:

Using default endpoints
Using explicit endpoints

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 SERVICE_NAME and its corresponding service environment variables.

You can define SERVICE_NAME and <RELATIONSHIP_NAME> as you like, so long as it’s unique between all defined services and relationships and matches in both the application and services configuration.The example above leverages explicit endpoint configuration for relationships.Depending on your needs, instead of explicit endpoint configuration, you can use default endpoint configuration.With the above definition, the application container now has access to the service via the relationship <RELATIONSHIP_NAME> and its corresponding service environment variables.

MariaDB example

Use the steps and sample code below if your application will connect to a primary MariaDB database. For details about using read-only replicas to improve performance of read-heavy applications, see the MariaDB read-only replication topic.

OracleMySQL example

Use in app

To use the configured service in your app, add a configuration file similar to the following to your project. 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 /docs/development/variables#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. Service connection details can change whenever your app restarts or redeploys. To keep your connection stable, use service environment variables rather than hard-coding 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:

Name	Type	Version	Description
`schemas`	`string` array	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.
`rotate_passwords`	A boolean	10.3+	Defaults to `true`. When set to `false`, password rotation is disabled.

Example configuration:

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: ` 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:

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 defined schema, specifies the permissions of the endpoint.

Available 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.
Replication admin	`replication-admin`	For managing replicas across projects; can run statements such as SHOW REPLICA STATUS, CHANGE MASTER TO, START REPLICA, and so on (see this related Dev Center article).

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: If either schemas or endpoints are defined, no default is applied and you have to specify the full configuration.

Removing a schema from the list of schemas on further deployments results in the deletion of the schema.

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.

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 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:

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: 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.

MariaDB configuration properties like max_connections and innodb_buffer_pool_size are not directly configurable from configuration.properties in your services configuration. They can, however, be set indirectly, which can be useful for solving Too many connection errors. See the troubleshooting documentation for more details.

Password generation

If your YAML file does not specify a schema and endpoint for the MariaDB or MySQL service, no password is generated. Because the database container is strictly isolated, it remains invisible to any resource until you define an explicit relationship between it and other apps or workers. The container’s “walls” ensure that only authorized applications and workers can reach the data, while all other processes are blocked at the network level. If you prefer to have Upsun generate a password, you must define schemas and custom endpoints in the services configuration – see the example in the multiple databases section of this topic. For each custom endpoint that you define, Upsun generates a password. Note that you cannot customize these generated passwords.

Make sure you don’t change services.<SERVICE_NAME>. Changing the service name creates a new service, which removes existing data from your database.

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. Using this method to retrieve password credentials is considered a best practice: passwords change automatically (or rotate) over time, and using incorrect passwords results in application downtime. Avoid using hard-coded passwords in your application (and code base), which can cause security issues.

Password rotation

For rotation to occur, you must define a schema and endpoint in your service configuration (see Password generation above); otherwise, no password is generated to be rotated.

By default, password rotation is enabled (rotate_passwords: true), which enables Upsun to automatically rotate MariaDB passwords during image updates or as defined by MariaDB lifetime settings. Specific scenarios might warrant disabling password rotation by setting rotate_passwords=false: for example, choosing to accommodate users who access a database via an SSH tunnel and provide a password in their request because they cannot retrieve the database credentials stored in the service or $PLATFORM_RELATIONSHIPS MariaDB environment variables. Passwords do not rotate automatically when you reset this value to true.

Important

Disabling password rotation can jeopardize compliance with security certifications - make sure you weigh this risk alongside the convenience of SSH-tunneling access.

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:

Rename the existing table.
Create a new table from the data in the existing 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:

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:

Importing a database backup is a destructive operation. It overwrites data already in your database. It’s best to run it against an empty database. If not, make a backup or do a database export before importing.

Sanitizing data

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

Large databases

Upsun supports MariaDB and Oracle MySQL databases of any size, including multi-terabyte deployments. The same service configuration applies whether your database holds gigabytes or tens of terabytes. Only the resources you allocate to it differ.

Cloning at any size

When you create a preview environment, Upsun clones the entire parent environment (including its database) at the storage layer. The clone completes in seconds to under a minute regardless of database size. A 10 TB database clones in roughly the same time as a 10 GB one. Service start-up time also stays largely the same with larger datasets on these engines.

What drives performance

Total disk size is rarely the limiting factor for query performance. What matters is the active dataset (or working set), the subset of rows, indexes, and pages your application actually reads and writes. A 10 TB database that serves queries against 5 GB of hot data can outperform a 50 GB database. The smaller database performs worse if it scans entire tables on every request. As long as the active dataset fits in the InnoDB buffer pool, queries are served from memory without touching disk. By contrast, queries that read most of a table become more expensive as the table grows. The same is true of indexes that effectively scan the whole table.

Sizing for large datasets

Memory is the lever that keeps the active dataset hot. MariaDB and Oracle MySQL containers use the HIGH_MEMORY container profile by default. Combined with Guaranteed CPU, you can allocate up to 512 GB of RAM to a single database container. To work efficiently with a large database:

Size memory to fit the active dataset in the InnoDB buffer pool. The buffer pool is derived from the container’s available memory. See the troubleshooting guide for the exact formula.
Make sure queries hit indexes, and avoid full table scans on large tables.
Watch index selectivity. An index that scans most of a large table on each query loads gigabytes into memory, evicting the active dataset.
Use EXPLAIN to verify which indexes a query uses and how many rows it examines.

Disk size alone, even into the multi-terabyte range, does not determine database performance. It determines what data you can store. Memory and query patterns determine how fast you can serve it.

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.

MariaDB note

​Supported versions

​Upgrade

​Downgrade

​Deprecated versions

​Retired versions

​Relationship reference

​MariaDB reference

​Oracle MySQL reference

​Usage example

​1. Configure the service

​2. Define the relationship

​MariaDB example

​OracleMySQL example

​Use in app

​Configure connections

​Configuration options

​Access the service directly

​Define permissions

​Multiple databases

​Multiple databases example

​Configure the database

​Password generation

​Password rotation

Important

​Storage Engine

​Service timezone

​Exporting data

​Compression

​Using the output in bash

​Importing data

​Sanitizing data

​Large databases

​Cloning at any size

​What drives performance

​Sizing for large datasets

​Replication

​Troubleshoot

Supported versions

Upgrade

Downgrade

Deprecated versions

Retired versions

Relationship reference

MariaDB reference

Oracle MySQL reference

Usage example

1. Configure the service

2. Define the relationship

MariaDB example

OracleMySQL example

Use in app

Configure connections

Configuration options

Access the service directly

Define permissions

Multiple databases

Multiple databases example

Configure the database

Password generation

Password rotation

Storage Engine

Service timezone

Exporting data

Compression

Using the output in bash

Importing data

Sanitizing data

Large databases

Cloning at any size

What drives performance

Sizing for large datasets

Replication

Troubleshoot