MariaDB/MySQL (Database service)
Contents:
Platform.sh supports both MariaDB and Oracle MySQL. While there are some differences at the application level for developers, they function nearly identically from an infrastructure point of view.
See the MariaDB documentation or MySQL documentation for more information.
Supported versions
The service types mariadb
and mysql
both refer to MariaDB for compatibility reasons. The service type oracle-mysql
refers to MySQL as released by Oracle, Inc. Other than the type, MySQL and MariaDB are otherwise identical and the rest of this page refers to both equally.
mariadb |
mysql |
oracle-mysql |
---|---|---|
|
|
|
Only MariaDB is available on Dedicated environments, using Galera for replication:
- 10.0 Galera
- 10.1 Galera
- 10.2 Galera
- 10.3 Galera
- 10.4 Galera
- 10.5 Galera
Note:
Downgrades of MySQL or MariaDB are not supported. Both will update their own datafiles to a new version automatically but cannot downgrade them. If you want to experiment with a later version without committing to it use a non-master environment.
Dedicated environments do not support any storage engine other than InnoDB. Tables created using the MyISAM storage engine on dedicated environments will not replicate between cluster nodes.
Deprecated versions
The following versions are available but are not receiving security updates from upstream, so their use is not recommended. They will be removed at some point in the future.
mariadb |
mysql |
oracle-mysql |
---|---|---|
|
|
|
Relationship
The format exposed in the $PLATFORM_RELATIONSHIPS
environment variable:
{
"username": "user",
"scheme": "mysql",
"service": "mariadb104",
"fragment": null,
"ip": "169.254.135.53",
"hostname": "e3wffyxtwnrxujeyg5u3kvqi6y.mariadb104.service._.eu-3.platformsh.site",
"public": false,
"cluster": "rjify4yjcwxaa-master-7rqtwti",
"host": "mysql.internal",
"rel": "mysql",
"query": {
"is_master": true
},
"path": "main",
"password": "",
"type": "mariadb:10.4",
"port": 3306,
"host_mapped": false
}
Usage example
For MariaDB your .platform/services.yaml
use mariadb
service type:
db:
type: mariadb:10.5
disk: 256
Oracle-mysql uses the oracle-mysql
service type:
dbmysql:
type: oracle-mysql:8.0
disk: 256
Note that the minimum disk size for mysql
/oracle-mysql
is 256MB.
Despite these service type differences, MariaDB and Oracle MySQL both use the mysql
endpoint in their configuration.
Note:
You will need to use
either the mariadb
, mysql
, or oracle-mysql
type
when defining the service
# .platform/services.yaml
service_name:
type: mariadb:version
disk: 256
and the endpoint mysql
when defining the relationship
# .platform.app.yaml
relationships:
relationship_name: “service_name:mysql”
Your service_name
and relationship_name
are defined by you, but we recommend making them distinct from each other.
Exception: This pattern will be the case unless you explictly set additional endpoints for multiple databases, as shown in the section below.
You can then use the service in a configuration file of your application with something like:
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 PeopleGo (
id SERIAL 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 PeopleGo (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 PeopleGo")
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 PeopleGo;")
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 JAVA_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 JAVA_PEOPLE (name, city) VALUES" +
"('Neil Armstrong', 'Moon')," +
"('Buzz Aldrin', 'Glen Ridge')," +
"('Sally Ride', 'La Jolla')";
statement.execute(sql);
// Show table.
sql = "SELECT * FROM JAVA_PEOPLE";
final ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String city = resultSet.getString("city");
logger.append(String.format("the JAVA_PEOPLE id %d the name %s and city %s", id, name, city));
logger.append('\n');
}
statement.execute("DROP TABLE JAVA_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 SERIAL 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
Note:
MySQL schema names can not use system reserved namespace. (mysql, information_schema, etc)
Multiple databases
If you are using version 10.0
or later of this service it is possible to define multiple databases as well as multiple users with different permissions. To do so requires defining multiple endpoints. Under the configuration
key of your service there are two additional keys:
schemas
: This is a YAML array listing the databases that should be created. If not specified, a single database namedmain
will be created.endpoints
: This is a nested YAML array defining different credentials. Each endpoint may have access to one or more schemas (databases), and may have different levels of permission on each. The valid permission levels are:ro
: Using this endpoint only SELECT queries are allowed.rw
: Using this endpoint SELECT queries as well INSERT/UPDATE/DELETE queries are allowed.admin
: Using this endpoint all queries are allowed, including DDL queries (CREATE TABLE, DROP TABLE, etc.).
Consider the following illustrative example:
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
This example creates a single MySQL/MariaDB service named mysqldb
. That server will have two databases, main
and legacy
. There will be three endpoints created. The first, named admin
, will have full access to both databases. The second, reporter
, will have SELECT query access to the main
DB but no access to legacy
at all. The importer
user will have SELECT/INSERT/UPDATE/DELETE access (but not DDL access) to the legacy
database but no access to main
.
If a given endpoint has access to multiple databases you should also specify which will be listed by default in the relationships array. If one isn’t specified the path
property of the relationship will be null. While that may be acceptable for an application that knows the name of the database to connect to, it would mean that automated tools such as the Platform CLI will not be able to access the database on that relationship. For that reason the default_schema
property is always recommended.
Once those endpoints are defined, you need to expose them to your application as a relationship. Continuing with our example, this would be a possible corresponding block from .platform.app.yaml
:
relationships:
database: "db:admin"
reports: "db:reporter"
imports: "db:importer"
This block defines three relationships, database
, reports
, and imports
. They’ll be available in the PLATFORM_RELATIONSHIPS
environment variable and all have the same structure documented above, but with different credentials. You can use those to connect to the appropriate database with the specified restrictions using whatever the SQL access tools are for your language and application.
If no configuration
block is specified at all, it is equivalent to the following default:
configuration:
schemas:
- main
endpoints:
mysql:
default_schema: main
privileges:
main: admin
If either schemas or endpoints are defined, then no default will be applied and you must specify the full configuration.
Adjusting database configuration
For MariaDB 10.1 and later Oracle MySQL 8.0 and later, a select few configuration properties from the my.cnf
file are available for adjustment.
Packet and connection sizing
This value defaults to 16
(in MB). Legal values are from 1
to 100
.
db:
type: mariadb:10.5
disk: 2048
configuration:
properties:
max_allowed_packet: 64
The above code will increase the maximum allowed packet size (the size of a query or response) to 64 MB. However, increasing the size of the maximum packet will also automatically decrease the max_connections
value. The number of connections allowed will depend on the packet size and the memory available to the service. In most cases leaving this value at the default is recommended.
Character encoding
For services created prior to February 2020, the default character set and collation is latin1
, which is the default in most MySQL/MariaDB.
For services created after February 2020, the default character set is utf8mb4
and the default collation is utf8mb4_unicode_ci
.
Both values can be adjusted at the server level in services.yaml
:
db:
type: mariadb:10.5
disk: 2048
configuration:
properties:
default_charset: utf8mb4
default_collation: utf8mb4_unicode_ci
Note that the effect of this setting is to set the character set and collation of any tables created once those properties are set. Tables created prior to when those settings are changed will be unaffected by changes to the services.yaml
configuration. However, you can change your own table’s character set and collation through ALTER TABLE
commands. For example:
# 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
We recommend using the InnoDB storage engine wherever possible. MyISAM is only properly supported in Grid environments. In dedicated cluster environments there is no replication of MyISAM tables.
If MyISAM tables are inadvertently created or imported in a dedicated environment they can be converted to use the InnoDB storage engine using the following procedure:
RENAME TABLE <existing> <old>;
INSERT INTO <existing> SELECT * from <old>;
Access your MariaDB service
Assuming your MariaDB relationship is named database
, the host name and port number obtained from PLATFORM_RELATIONSHIPS
would be database.internal
and 3306
. Open an SSH session and run the MySQL command line client.
mysql -h database.internal -P 3306 -u user main
Outside the application container, you can use Platform CLI platform sql
.
Exporting data
The easiest way to download all data in a MariaDB instance is with the Platform.sh CLI. If you have a single SQL database, the following command will export all data using the mysqldump
command to a local file:
platform db:dump
If you have multiple SQL databases it will prompt you which one to export. You can also specify one by relationship name explicitly:
platform db:dump --relationship database
By default the file will be uncompressed. If you want to compress it, use the --gzip
(-z
) option:
platform db:dump --gzip
You can use the --stdout
option to pipe the result to another command. For example, if you want to create a bzip2-compressed file, you can run:
platform db:dump --stdout | bzip2 > dump.sql.bz2
Importing data
The easiest way to load data into a database is to pipe an SQL dump through the platform sql
command, like so:
platform sql < my_database_backup.sql
That will run the database backup against the SQL database on Platform.sh. That will work for any SQL file, so the usual caveats about importing an SQL dump apply (e.g., it’s best to run against an empty database). As with exporting, you can also specify a specific environment to use and a specific database relationship to use, if there are multiple.
platform sql --relationship database -e master < my_database_backup.sql
Note:
Importing a database backup is a destructive operation. It will overwrite data already in your database. Taking a backup or a database export before doing so is strongly recommended.
Replication
On-site primary/replica support is not available on Grid plans. On a Dedicated environment, it is provided automatically as part of the default configuration.
In abnormal cases you may also enable remote replication to your own replica data. This is an advanced configuration not appropriate for most circumstances (and the replica will not be available to your application), but may be useful for certain backup purposes.