MariaDB/MySQL (database service)

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

Use a framework

If you use one of the following frameworks, follow its guide:

• Hibernate
• Jakarta EE
• Spring

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.

Supported versions on Dedicated environments

oracle-mysql is not yet available for Dedicated Gen 2 environments.

On Dedicated environments, MariaDB is available with Galera for replication. Supported versions are the following:

Dedicated environments only support the InnoDB storage engine. Tables created on Dedicated environments using the MyISAM storage engine don’t replicate between all hosts in the cluster. See how to convert tables to the InnoDB engine.

Deprecated versions

The following versions are deprecated. They’re available, but they don’t receive security updates from upstream and aren’t guaranteed to work. They’ll be removed in the future – consider migrating to a supported version.

Upgrade

When upgrading your service, skipping versions may result in data loss. 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.

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.

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:

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

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:

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

# 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.
# Note that legacy definition of the relationship is still supported.
# More information: https://docs.upsun.com/anchors/fixed/app/reference/relationships/
relationships:
  <RELATIONSHIP_NAME>:
    service: <SERVICE_NAME>
    endpoint: mysql

MariaDB example

Service definition

# The name of the service container. Must be unique within a project.
mariadb:
  type: mariadb:11.8
  disk: 256

App configuration

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

# 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.
# Note that legacy definition of the relationship is still supported.
# More information: https://docs.upsun.com/anchors/fixed/app/reference/relationships/
relationships:
  mariadb:
    service: mariadb
    endpoint: mysql

OracleMySQL example

Service definition

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

App configuration

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

# 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.
# Note that legacy definition of the relationship is still supported.
# More information: https://docs.upsun.com/anchors/fixed/app/reference/relationships/
relationships:
  oraclemysql:
    service: oraclemysql
    endpoint: mysql

Use in app

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

package examples

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	psh "github.com/platformsh/config-reader-go/v2"
	sqldsn "github.com/platformsh/config-reader-go/v2/sqldsn"
)

func UsageExampleMySQL() string {

	// Create a NewRuntimeConfig object to ease reading the Upsun Fixed environment variables.
	// You can alternatively use os.Getenv() yourself.
	config, err := psh.NewRuntimeConfig()
	checkErr(err)

	// The 'database' relationship is generally the name of the primary SQL database of an application.
	// That's not required, but much of our default automation code assumes it.
	credentials, err := config.Credentials("database")
	checkErr(err)

	// Using the sqldsn formatted credentials package.
	formatted, err := sqldsn.FormattedCredentials(credentials)
	checkErr(err)

	db, err := sql.Open("mysql", formatted)
	checkErr(err)

	defer db.Close()

	// Force MySQL into modern mode.
	db.Exec("SET NAMES=utf8")
	db.Exec("SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES," +
		"NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO," +
		"NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY'")

	// Creating a table.
	sqlCreate := "CREATE TABLE IF NOT EXISTS People (" +
		"id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY," +
		"name VARCHAR(30) NOT NULL," +
		"city VARCHAR(30) NOT NULL)"

	_, err = db.Exec(sqlCreate)
	checkErr(err)

	// Insert data.
	sqlInsert := "INSERT INTO People (name, city) VALUES" +
		"('Neil Armstrong', 'Moon')," +
		"('Buzz Aldrin', 'Glen Ridge')," +
		"('Sally Ride', 'La Jolla');"

	_, err = db.Exec(sqlInsert)
	checkErr(err)

	table := "<table>" +
		"<thead>" +
		"<tr><th>Name</th><th>City</th></tr>" +
		"</thead>" +
		"<tbody>"

	var id int
	var name string
	var city string

	rows, err := db.Query("SELECT * FROM People")
	if err != nil {
		panic(err)
	} else {
		for rows.Next() {
			err = rows.Scan(&id, &name, &city)
			checkErr(err)
			table += fmt.Sprintf("<tr><td>%s</td><td>%s</td><tr>\n", name, city)
		}
		table += "</tbody>\n</table>\n"
	}

	_, err = db.Exec("DROP TABLE People;")
	checkErr(err)

	return table
}

package sh.platform.languages.sample;

import sh.platform.config.Config;
import sh.platform.config.MySQL;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.function.Supplier;

public class MySQLSample implements Supplier<String> {

    @Override
    public String get() {
        StringBuilder logger = new StringBuilder();

        // Create a new config object to ease reading the Upsun Fixed environment variables.
        // You can alternatively use getenv() yourself.
        Config config = new Config();

        // The 'database' relationship is generally the name of primary SQL database of an application.
        // That's not required, but much of our default automation code assumes it.
        MySQL database = config.getCredential("database", MySQL::new);
        DataSource dataSource = database.get();

        // Connect to the database
        try (Connection connection = dataSource.getConnection()) {

            // Creating a table.
            String sql = "CREATE TABLE IF NOT EXISTS People (" +
                    " id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY," +
                    "name VARCHAR(30) NOT NULL," +
                    "city VARCHAR(30) NOT NULL)";

            final Statement statement = connection.createStatement();
            statement.execute(sql);

            // Insert data.
            sql = "INSERT INTO People (name, city) VALUES" +
                    "('Neil Armstrong', 'Moon')," +
                    "('Buzz Aldrin', 'Glen Ridge')," +
                    "('Sally Ride', 'La Jolla')";

            statement.execute(sql);

            // Show table.
            sql = "SELECT * FROM People";
            final ResultSet resultSet = statement.executeQuery(sql);
            logger.append("<table><thead><tr><th>Name</th><th>City</th></tr></thhead><tbody>");
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String city = resultSet.getString("city");
                logger.append(String.format("<tr><td>%s</td><td>%s</td></tr>", name, city));
                logger.append('\n');
            }
            logger.append("</tbody></table>");
            statement.execute("DROP TABLE People");
            return logger.toString();
        } catch (SQLException exp) {
            throw new RuntimeException("An error when execute MySQL", exp);
        }
    }
}

const mysql = require("mysql2/promise");
const config = require("platformsh-config").config();

exports.usageExample = async function () {
    const credentials = config.credentials("database");

    const connection = await mysql.createConnection({
        host: credentials.host,
        port: credentials.port,
        user: credentials.username,
        password: credentials.password,
        database: credentials.path,
    });

    // Creating a table.
    await connection.query(
        `CREATE TABLE IF NOT EXISTS People (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(30) NOT NULL,
            city VARCHAR(30) NOT NULL
        )`
    );

    // Insert data.
    await connection.query(
        `INSERT INTO People (name, city)
        VALUES
            ('Neil Armstrong', 'Moon'),
            ('Buzz Aldrin', 'Glen Ridge'),
            ('Sally Ride', 'La Jolla');`
    );

    // Show table.
    const [rows] = await connection.query("SELECT * FROM People");

    // Drop table.
    await connection.query("DROP TABLE People");

    const outputRows = rows
        .map(({ name, city }) => `<tr><td>${name}</td><td>${city}</td></tr>\n`)
        .join("\n");

    return `
    <table>
        <thead>
            <tr>
                <th>Name</th><th>City</th>
            </tr>
        </thhead>
        <tbody>
            ${outputRows}
        </tbody>
    </table>
    `;
};

<?php

declare(strict_types=1);

use Platformsh\ConfigReader\Config;

// Create a new config object to ease reading the Upsun Fixed environment variables.
// You can alternatively use getenv() yourself.
$config = new Config();

// The 'database' relationship is generally the name of primary SQL database of an application.
// That's not required, but much of our default automation code assumes it.
$credentials = $config->credentials('database');

try {
    // Connect to the database using PDO.  If using some other abstraction layer you would
    // inject the values from $database into whatever your abstraction layer asks for.
    $dsn = sprintf('mysql:host=%s;port=%d;dbname=%s', $credentials['host'], $credentials['port'], $credentials['path']);
    $conn = new \PDO($dsn, $credentials['username'], $credentials['password'], [
        // Always use Exception error mode with PDO, as it's more reliable.
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        // So we don't have to mess around with cursors and unbuffered queries by default.
        \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
        // Make sure MySQL returns all matched rows on update queries including
        // rows that actually didn't have to be updated because the values didn't
        // change. This matches common behavior among other database systems.
        \PDO::MYSQL_ATTR_FOUND_ROWS => TRUE,
    ]);

    // Creating a table.
    $sql = "CREATE TABLE People (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL,
      city VARCHAR(30) NOT NULL
      )";
    $conn->query($sql);

    // Insert data.
    $sql = "INSERT INTO People (name, city) VALUES
        ('Neil Armstrong', 'Moon'),
        ('Buzz Aldrin', 'Glen Ridge'),
        ('Sally Ride', 'La Jolla');";
    $conn->query($sql);

    // Show table.
    $sql = "SELECT * FROM People";
    $result = $conn->query($sql);
    $result->setFetchMode(\PDO::FETCH_OBJ);

    if ($result) {
        print <<<TABLE
<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>
TABLE;
        foreach ($result as $record) {
            printf("<tr><td>%s</td><td>%s</td></tr>\n", $record->name, $record->city);
        }
        print "</tbody>\n</table>\n";
    }

    // Drop table
    $sql = "DROP TABLE People";
    $conn->query($sql);

} catch (\Exception $e) {
    print $e->getMessage();
}

import pymysql
from platformshconfig import Config


def usage_example():

    # Create a new Config object to ease reading the Upsun Fixed environment variables.
    # You can alternatively use os.environ yourself.
    config = Config()

    # The 'database' relationship is generally the name of primary SQL database of an application.
    # That's not required, but much of our default automation code assumes it.'
    credentials = config.credentials('database')

    try:
        # Connect to the database using PDO. If using some other abstraction layer you would inject the values
        # from `database` into whatever your abstraction layer asks for.

        conn = pymysql.connect(host=credentials['host'],
                               port=credentials['port'],
                               database=credentials['path'],
                               user=credentials['username'],
                               password=credentials['password'])

        sql = '''
                CREATE TABLE People (
                id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(30) NOT NULL,
                city VARCHAR(30) NOT NULL
                )
                '''

        cur = conn.cursor()
        cur.execute(sql)

        sql = '''
                INSERT INTO People (name, city) VALUES
                ('Neil Armstrong', 'Moon'),
                ('Buzz Aldrin', 'Glen Ridge'),
                ('Sally Ride', 'La Jolla');
                '''

        cur.execute(sql)

        # Show table.
        sql = '''SELECT * FROM People'''
        cur.execute(sql)
        result = cur.fetchall()

        table = '''<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>'''

        if result:
            for record in result:
                table += '''<tr><td>{0}</td><td>{1}</td><tr>\n'''.format(record[1], record[2])
            table += '''</tbody>\n</table>\n'''

        # Drop table
        sql = '''DROP TABLE People'''
        cur.execute(sql)

        # Close communication with the database
        cur.close()
        conn.close()

        return table

    except Exception as e:
        return e

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:

platform relationships

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

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

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

Configuration options

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

Example configuration:

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

Relationship reference

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

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

MariaDB reference

{
  "username": "user",
  "scheme": "mysql",
  "service": "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.8",
  "public": false,
  "host_mapped": false
}

Oracle MySQL reference

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

Access the service directly

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

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

mysql -h HOST -P PORT -u USER PATH

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

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

Available permissions:

Restrict access to database replicas only

Your main database lives on one of the three nodes provided on Grid HA. The two other nodes can each accommodate a replica of your main database.

For security reasons, you can grant your app access to a replica instead of your main database. To do so, when defining the relationship between your app and database, make sure you do the following:

1. Use the explicit endpoint syntax.
2. Add the -replica suffix to the name of the endpoint you want to use.

This results in the following configuration:

relationships:
  RELATIONSHIP_NAME:
    service: SERVICE_NAME
    endpoint: ENDPOINT_NAME-replica

For example, if you define a mariadb database as follows:

mariadb:
  type: mariadb:11.8
  disk: 2048
    configuration:
      schemas:
        - main
    endpoints:
      admin:
        default_schema: main
        privileges:
          main: admin
      reporter:
        privileges:
          main: ro

To create a replica of the mariadb database and allow your app to connect to it through the admin endpoint with admin permissions, use the following configuration:

relationships:
  mariadb:
    service: mariadb
    endpoint: admin-replica

To create a replica of the mariadb database and allow your app to connect to it through the reporter endpoint with read-only permissions instead, use the following configuration:

relationships:
  mariadb:
    service: mariadb
    endpoint: reporter-replica

Grant access to the main database and its replicas

Your main database lives on one of the three nodes provided on Grid HA. The two other nodes can each accommodate a replica of your main database.

You may want to grant access to both your main database and its replicas. To do so, when defining the relationship between your app and database, make sure you do the following:

1. Use the explicit endpoint syntax.
2. Add the -all suffix to the name of the endpoint you want to use.

This results in the following configuration, which creates a replica on each of the secondary nodes:

relationships:
  RELATIONSHIP_NAME:
    service: SERVICE_NAME
    endpoint: ENDPOINT_NAME-all

For example, if you define a mariadb database as follows:

mariadb:
  type: mariadb:11.8
  disk: 2048
  configuration:
    schemas:
      - main
  endpoints:
    admin:
      default_schema: main
      privileges:
        main: admin
    reporter:
      privileges:
        main: ro

To allow your app to connect to your main database and both its replicas through the admin endpoint with admin permissions, use the following configuration:

relationships:
  mariadb:
    service: mariadb
    endpoint: admin-all

To allow your app to connect to your main database and both its replicas through the reporter endpoint with read-only permissions, use the following configuration:

relationships:
  mariadb:
    service: mariadb
    endpoint: reporter-all

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:

# The name of the service container. Must be unique within a project.
mariadb:
  type: mariadb:11.8
  disk: 2048
  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.

# The name of the service container. Must be unique within a project.
mariadb:
  type: mariadb:11.8
  disk: 2048
  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:

name: myapp

[...]

# Relationships enable an app container's access to a service.
relationships:
  # Note that legacy definition of the relationship is still supported.
  # More information: https://docs.upsun.com/anchors/fixed/app/reference/relationships/
  database:
    service: mariadb
    endpoint: admin
  reports:
    service: mariadb
    endpoint: reporter
  imports:
    service: mariadb
    endpoint: importer

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

Configure the database

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

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

An example of setting these properties:

# The name of the service container. Must be unique within a project.
mariadb:
  type: mariadb:11.8
  disk: 2048
  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. You cannot customize these 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.

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.

When you switch from the default configuration with an empty password to custom endpoints, make sure your service name remains unchanged. Changing the service name creates a new service, which removes any existing data from your database.

Password rotation

By default, password rotation is enabled (rotate_passwords=true), enabling Upsun Fixed to automatically change (or rotate) MariaDB passwords each time it updates the MariaDB image. Password rotation also occurs as defined by any password lifetime settings in MariaDB.

Specific scenarios might warrant disabling password rotation ( 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.

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:

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 Fixed CLI. If you have a single SQL database, the following command exports all data to a local file:

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

platform db:dump --relationship RELATIONSHIP_NAME

Compression

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

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

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

Importing data

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

platform sql < my_database_backup.sql

That runs the database backup against the SQL database on Upsun Fixed. 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:

platform 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

In non-Dedicated environments, there is no on-site primary/replica supports. In Dedicated environments, it’s provided automatically as part of the default configuration.

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.