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`

`mariadb` / `mysql`	`oracle-mysql`

Upgrade, change, or downgrade a service

Caution

Upgrading and downgrading a service version or changing a service type are destructive processes that delete the existing service and its data.A best practice is to first back up your environment and export the data.

To prevent data loss after completing either of these actions, follow these steps:

Back up your environment. If you accidentally delete the wrong service or make an error in your config.yaml file 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.
Change the service type in your service configuration:
- Upgrade: Upgrade sequentially from one supported version to another (10.6 -> 10.11 -> 11.4), and check that each upgrade commit translates into an actual deployment.
- Change or downgrade: Specify the new service type and the desired version.
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

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 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. 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. MyISAM is only properly supported in non-Dedicated environments. In Dedicated environments, there is no replication of MyISAM tables. If MyISAM tables have been inadvertently created or imported in a Dedicated environment (if you see ENGINE=MyISAM in the response to SHOW CREATE TABLE EXISTING_TABLE), convert them to use the InnoDB storage engine as follows: 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.

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.

Get Started

Core Concepts

Configure Apps & Services

Build, Deploy & Run

Manage Projects

Manage Environments

Manage Resources

Observability & Metrics

Integrations

Troubleshooting

Resources

MariaDB note

​Supported versions

​Upgrade, change, or downgrade a service

Caution

​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

​Replication

​Troubleshoot

Supported versions

Upgrade, change, or downgrade a service

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

Replication

Troubleshoot