MariaDB/MySQL (database service)

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

Use a framework 

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

For more implementation ideas, consult a template.

Supported versions 

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
  • 10.6
  • 10.5
  • 10.4
  • 10.3
  • 10.6
  • 10.5
  • 10.4
  • 10.3
  • 8.0
  • 5.7

Supported versions on Dedicated environments 

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

On Dedicated Gen 3 and Dedicated Gen 2 environments, MariaDB is available with Galera for replication:

  • 10.6 Galera
  • 10.5 Galera
  • 10.4 Galera
  • 10.3 Galera

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 aren’t receiving security updates from upstream and aren’t guaranteed to work. They’ll be removed at some point in the future, so you should migrate to one of the supported versions.

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

Switching type and version 

If you change the service type, your data is removed.

To switch service types:

  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 

Use the mariadb or mysql type for MariaDB or the oracle-mysql type for Oracle MySQL to define the service:

.platform/services.yaml
<SERVICE_NAME>:
    type: mariadb:<VERSION>
    disk: 256

Note that if you later change the name, it’s treated as an entirely new service. This removes all data from your service. Always backup your data before changing the service.

2. Add the relationship 

Use the mysql endpoint to define the relationship (unless you have multiple databases):

.platform.app.yaml
relationships:
    <RELATIONSHIP_NAME>: "<SERVICE_NAME>:mysql"

You can define <SERVICE_NAME> and <RELATIONSHIP_NAME> as you like, but it’s best if they’re distinct.

MariaDB example configuration

Service definition

.platform/services.yaml
db:
    type: mariadb:10.6
    disk: 256

App configuration

.platform.app.yaml
relationships:
    database: "db:mysql"

Oracle MySQL example configuration

Service definition

.platform/services.yaml
dbmysql:
  type: oracle-mysql:8.0
  disk: 256

App configuration

.platform.app.yaml
relationships:
  mysqldatabase: "dbmysql:mysql"

Use in app 

Then use the service in your app with a configuration file like the following:

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 Platform.sh 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(\x60SET 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'\x60)

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

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

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

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

	table := \x60<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>\x60

	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 Platform.sh 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 Platform.sh 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 Platform.sh 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 (replacing <PROJECT_ID> and <ENVIRONMENT_NAME> with your values):

platform relationships -p <PROJECT_ID> <ENVIRONMENT_NAME>

The result is the complete information for all relationships with an additional url property. Use the url property as your connection. Note that url can change if you modify the relationship or add additional databases. So always check it each time your app starts.

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

.platform/services.yaml
db:
    type: mariadb:10.5
    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:

MariaDB reference 

{
    "username": "user",
    "scheme": "mysql",
    "service": "mariadb104",
    "fragment": null,
    "ip": "169.254.255.221",
    "hostname": "e3wffyxtwnrxujeyg5u3kvqi6y.mariadb104.service._.eu-3.platformsh.site",
    "port": 3306,
    "cluster": "rjify4yjcwxaa-master-7rqtwti",
    "host": "mysql.internal",
    "rel": "mysql",
    "path": "main",
    "query": {
        "is_master": true
    },
    "password": "",
    "type": "mariadb:10.4",
    "public": false,
    "host_mapped": false
}

Oracle MySQL reference 

{
    "username": "user",
    "scheme": "mysql",
    "service": "oraclemysql",
    "fragment": null,
    "ip": "169.254.150.190",
    "hostname": "7q5hllmmhoeuthu6th7qovoone.oraclemysql.service._.eu-3.platformsh.site",
    "port": 3306,
    "cluster": "rjify4yjcwxaa-master-7rqtwti",
    "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 Platform 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 mysql.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 mysql.internal -P 3306 -u user main

Multiple databases 

With version 10.0 or later, you can define multiple databases and multiple users with different permissions. 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 schemas defined above to default to. If not specified, the path property of the relationship is null and so tools such as the Platform CLI can’t access the relationship.
privileges A permissions dictionary For each of the defined schemas, what permissions the given endpoint has.

Possible permissions:

  • ro: Only SELECT queries are allowed.
  • rw: SELECT queries and INSERT/UPDATE/DELETE queries are allowed.
  • admin: All queries are allowed including data definition language (DDL) queries (such as CREATE TABLE, DROP TABLE).

If neither schemas nor endpoints is included, it’s equivalent to the following default:

.platform/services.yaml
    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 db 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.
.platform/services.yaml
db:
    type: mariadb:10.5
    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:

.platform.app.yaml
relationships:
    database: "db:admin"
    reports: "db:reporter"
    imports: "db: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:

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 latin1 before February 2020 and utf8mb4 after The default character set. Affects any tables created after it’s set.
default_collation string latin1 before February 2020 and utf8mb4_unicode_ci after The default collation. Affects any tables created after it’s set.

An example of setting these properties:

.platform/services.yaml
db:
    type: mariadb:10.5
    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;

Consult the MySQL documentation for further details.

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> <table_old>;
  2. Create a new table from the data in the existing table.

    CREATE TABLE <existing_table> SELECT * from <table_old>;

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

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.