> ## Documentation Index
> Fetch the complete documentation index at: https://developer.upsun.com/llms.txt
> Use this file to discover all available pages before exploring further.
# MariaDB/MySQL (database service)
> See how to configure a MariaDB/MySQL server to store your data.
export const DynamicCodeBlock = ({language = 'yaml', filename, icon, lines, wrap, expandable, highlight, focus, children}) => {
const STORAGE_KEY = 'upsun_versions_cache';
const COMPOSABLE_STORAGE_KEY = 'upsun_composable_cache';
const CACHE_TTL = 5 * 60 * 1000;
const API_URL = 'https://meta.upsun.com/images';
const COMPOSABLE_API_URL = 'https://meta.upsun.com/composable';
const DEBUG_PREFIX = '[DynamicCodeBlock cache]';
const [versionData, setVersionData] = useState(null);
const [versionError, setVersionError] = useState(false);
const [composableData, setComposableData] = useState(null);
const [composableError, setComposableError] = useState(false);
useEffect(() => {
const fetchData = async () => {
let cachedData = null;
let cachedEtag = null;
if (typeof localStorage !== 'undefined') {
try {
const cached = localStorage.getItem(STORAGE_KEY);
if (cached) {
const parsed = JSON.parse(cached);
cachedData = parsed?.data || null;
cachedEtag = parsed?.etag || null;
if (cachedData && Date.now() - parsed.timestamp < CACHE_TTL) {
return cachedData;
}
}
} catch (err) {
console.error('Failed to load from cache:', err);
}
}
const requestHeaders = cachedEtag ? {
'If-None-Match': cachedEtag
} : {};
console.debug(`${DEBUG_PREFIX} revalidating`, {
storageKey: STORAGE_KEY,
hasCachedData: Boolean(cachedData),
hasCachedEtag: Boolean(cachedEtag)
});
const response = await fetch(API_URL, {
headers: requestHeaders
});
if (response.status === 304 && cachedData) {
console.debug(`${DEBUG_PREFIX} revalidated (304)`, {
storageKey: STORAGE_KEY
});
if (typeof localStorage !== 'undefined') {
try {
const etag = response.headers.get('etag') || cachedEtag;
localStorage.setItem(STORAGE_KEY, JSON.stringify({
data: cachedData,
etag,
timestamp: Date.now()
}));
} catch (err) {
console.error('Failed to refresh cache metadata:', err);
}
}
return cachedData;
}
if (!response.ok) throw new Error(`API request failed: ${response.statusText}`);
const data = await response.json();
const etag = response.headers.get('etag');
console.debug(`${DEBUG_PREFIX} refreshed (200)`, {
storageKey: STORAGE_KEY,
etag
});
if (typeof localStorage !== 'undefined') {
try {
localStorage.setItem(STORAGE_KEY, JSON.stringify({
data,
etag,
timestamp: Date.now()
}));
} catch (err) {
console.error('Failed to cache data:', err);
}
}
return data;
};
fetchData().then(data => setVersionData(data)).catch(err => console.error('Failed to fetch version data:', err));
}, []);
const findHighestVersion = versionsMap => {
if (!versionsMap || Object.keys(versionsMap).length === 0) return null;
const entries = Object.entries(versionsMap);
const active = entries.filter(([, v]) => v.upsun && v.upsun.status === 'supported' || v.upsun && v.upsun.status === 'deprecated');
const candidates = active.length > 0 ? active : entries;
let [highestName] = candidates[0];
for (let i = 1; i < candidates.length; i++) {
const [currentName] = candidates[i];
const cp = currentName.split('.').map(Number);
const hp = highestName.split('.').map(Number);
for (let j = 0; j < Math.max(cp.length, hp.length); j++) {
if ((cp[j] || 0) > (hp[j] || 0)) {
highestName = currentName;
break;
} else if ((cp[j] || 0) < (hp[j] || 0)) {
break;
}
}
}
return highestName;
};
const getVersion = (lang, requestedVersion = 'latest') => {
if (lang === 'composable') {
if (!composableData || !composableData.versions || Object.keys(composableData.versions).length === 0) return null;
if (requestedVersion && requestedVersion !== 'latest') {
return (requestedVersion in composableData.versions) ? requestedVersion : null;
}
return findHighestVersion(composableData.versions);
}
if (!versionData) return null;
const imageData = versionData[lang];
if (!imageData || !imageData.versions || Object.keys(imageData.versions).length === 0) {
return null;
}
if (requestedVersion && requestedVersion !== 'latest') {
return (requestedVersion in imageData.versions) ? requestedVersion : null;
}
return findHighestVersion(imageData.versions);
};
let code = typeof children === 'string' ? children : String(children || '');
const codeLines = code.split('\n');
while (codeLines.length > 0 && codeLines[0].trim() === '') codeLines.shift();
while (codeLines.length > 0 && codeLines[codeLines.length - 1].trim() === '') codeLines.pop();
if (codeLines.length > 0) {
const indents = codeLines.filter(line => line.trim().length > 0).map(line => line.match(/^[ \t]*/)[0].length);
const minIndent = Math.min(...indents);
code = codeLines.map(line => line.slice(minIndent)).join('\n');
}
code = code.replace(/\{\{version:(.*?)\}\}/g, (match, params) => {
const parts = params.split(':');
const lang = parts[0];
const ver = parts[1] || 'latest';
const isComposable = lang === 'composable';
const hasError = isComposable ? composableError : versionError;
const dataReady = isComposable ? composableData !== null : versionData !== null;
if (hasError) return '(unavailable)';
if (dataReady) {
const resolvedVersion = getVersion(lang, ver);
return resolvedVersion || match;
}
return '...';
});
const codeBlockProps = {
language,
...filename && ({
filename
}),
...icon && ({
icon
}),
...lines !== undefined && ({
lines
}),
...wrap !== undefined && ({
wrap
}),
...expandable !== undefined && ({
expandable
}),
...highlight && ({
highlight
}),
...focus && ({
focus
})
};
return {code};
};
export const VersionDeprecatedBlock = () => <>
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.
;
export const VariableBlock = ({name}) => {
return {name};
};
export const MetaImageVersionList = ({language, status}) => {
const [versions, setVersions] = useState([]);
const [loading, setLoading] = useState(true);
const [error, setError] = useState(null);
const STORAGE_KEY = 'upsun_versions_cache';
const CACHE_TTL = 5 * 60 * 1000;
const API_URL = 'https://meta.upsun.com/images';
useEffect(() => {
if (!language) {
setLoading(false);
return;
}
setLoading(true);
setError(null);
const fetchData = async () => {
let cachedData = null;
let cachedEtag = null;
if (typeof localStorage !== 'undefined') {
try {
const cached = localStorage.getItem(STORAGE_KEY);
if (cached) {
const parsed = JSON.parse(cached);
cachedData = parsed?.data || null;
cachedEtag = parsed?.etag || null;
if (cachedData && Date.now() - parsed.timestamp < CACHE_TTL) return cachedData;
}
} catch (error_) {
console.error('Failed to load from cache:', error_);
}
}
const requestHeaders = cachedEtag ? {
'If-None-Match': cachedEtag
} : {};
const response = await fetch(API_URL, {
headers: requestHeaders
});
if (response.status === 304 && cachedData) {
if (typeof localStorage !== 'undefined') {
try {
const etag = response.headers.get('etag') || cachedEtag;
localStorage.setItem(STORAGE_KEY, JSON.stringify({
data: cachedData,
etag,
timestamp: Date.now()
}));
} catch (error_) {
console.error('Failed to refresh cache metadata:', error_);
}
}
return cachedData;
}
if (!response.ok) throw new Error(`API request failed: ${response.statusText}`);
const data = await response.json();
const etag = response.headers.get('etag');
if (typeof localStorage !== 'undefined') {
try {
localStorage.setItem(STORAGE_KEY, JSON.stringify({
data,
etag,
timestamp: Date.now()
}));
} catch (error_) {
console.error('Failed to cache data:', error_);
}
}
return data;
};
fetchData().then(data => {
if (!data || !data[language]) {
setVersions([]);
setLoading(false);
return;
}
const imageData = data[language];
if (!imageData.versions) {
setVersions([]);
setLoading(false);
return;
}
let versionList = Object.entries(imageData.versions).map(([name, v]) => ({
name,
status: v.upsun?.status || v.status
})).sort((a, b) => {
const aParts = a.name.split('.').map(Number);
const bParts = b.name.split('.').map(Number);
const max = Math.max(aParts.length, bParts.length);
for (let i = 0; i < max; i++) {
const av = aParts[i] || 0;
const bv = bParts[i] || 0;
if (av !== bv) return bv - av;
}
return 0;
});
if (status) {
versionList = versionList.filter(v => v.status === status);
}
setVersions(versionList);
setLoading(false);
}).catch(error_ => {
console.error('MetaImageVersionList error:', error_);
setError(error_.message);
setLoading(false);
});
}, [language, status]);
if (loading) return Loading...
;
if (error) return Error: {error}
;
if (!versions || versions.length === 0) {
if (status === 'incoming') return null;
return No versions available! Contact support.
;
}
let incomingBlock = null;
if (status === 'incoming' && versions.length > 0) {
incomingBlock = `These versions are not yet available but are expected to be released soon.`;
}
return incomingBlock ?
{incomingBlock}
{versions.map(version => -
{version.name} {version.status === 'beta' && Beta}
)}
:
{versions.map(version => -
{version.name} {version.status === 'beta' && Beta}
)}
;
};
export const MetaImageVersion = ({language, version}) => {
const [selectedVersion, setSelectedVersion] = useState(null);
const [loading, setLoading] = useState(true);
const [error, setError] = useState(null);
const isComposable = language === 'composable';
const STORAGE_KEY = isComposable ? 'upsun_composable_cache' : 'upsun_versions_cache';
const CACHE_TTL = 5 * 60 * 1000;
const API_URL = isComposable ? 'https://meta.upsun.com/composable' : 'https://meta.upsun.com/images';
const findHighestVersion = versionsMap => {
if (!versionsMap || Object.keys(versionsMap).length === 0) return null;
const entries = Object.entries(versionsMap);
const active = entries.filter(([, v]) => v.upsun && v.upsun.status === 'supported' || v.upsun && v.upsun.status === 'deprecated');
const candidates = active.length > 0 ? active : entries;
let [highestName] = candidates[0];
for (let i = 1; i < candidates.length; i++) {
const [currentName] = candidates[i];
const cp = currentName.split('.').map(Number);
const hp = highestName.split('.').map(Number);
for (let j = 0; j < Math.max(cp.length, hp.length); j++) {
if ((cp[j] || 0) > (hp[j] || 0)) {
highestName = currentName;
break;
} else if ((cp[j] || 0) < (hp[j] || 0)) {
break;
}
}
}
return highestName;
};
useEffect(() => {
if (!language) {
setLoading(false);
return;
}
setLoading(true);
setError(null);
const fetchData = async () => {
let cachedData = null;
let cachedEtag = null;
if (typeof localStorage !== 'undefined') {
try {
const cached = localStorage.getItem(STORAGE_KEY);
if (cached) {
const parsed = JSON.parse(cached);
cachedData = parsed?.data || null;
cachedEtag = parsed?.etag || null;
if (cachedData && Date.now() - parsed.timestamp < CACHE_TTL) return cachedData;
}
} catch (error_) {
console.error('Failed to load from cache:', error_);
}
}
const requestHeaders = cachedEtag ? {
'If-None-Match': cachedEtag
} : {};
const response = await fetch(API_URL, {
headers: requestHeaders
});
if (response.status === 304 && cachedData) {
if (typeof localStorage !== 'undefined') {
try {
const etag = response.headers.get('etag') || cachedEtag;
localStorage.setItem(STORAGE_KEY, JSON.stringify({
data: cachedData,
etag,
timestamp: Date.now()
}));
} catch (error_) {
console.error('Failed to refresh cache metadata:', error_);
}
}
return cachedData;
}
if (!response.ok) throw new Error(`API request failed: ${response.statusText}`);
const data = await response.json();
const etag = response.headers.get('etag');
if (typeof localStorage !== 'undefined') {
try {
localStorage.setItem(STORAGE_KEY, JSON.stringify({
data,
etag,
timestamp: Date.now()
}));
} catch (error_) {
console.error('Failed to cache data:', error_);
}
}
return data;
};
fetchData().then(data => {
if (!data) {
setSelectedVersion(null);
setLoading(false);
return;
}
const imageData = isComposable ? data : data[language];
if (!imageData || !imageData.versions || Object.keys(imageData.versions).length === 0) {
setSelectedVersion(null);
setLoading(false);
return;
}
let versionName = null;
if (version && version !== 'latest') {
versionName = (version in imageData.versions) ? version : null;
} else {
versionName = findHighestVersion(imageData.versions);
}
setSelectedVersion(versionName);
setLoading(false);
}).catch(error_ => {
console.error('MetaImageVersion error:', error_);
setError(error_.message);
setLoading(false);
});
}, [language, version]);
if (loading) return …;
if (error) return ⚠ unavailable;
if (!selectedVersion) return No version found;
return {selectedVersion};
};
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](https://mariadb.org/documentation/)
or the Oracle [MySQL Server documentation](https://dev.mysql.com/doc/refman/en/) for more information.
MariaDB note
The [example](#mariadb-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](/docs/add-services/mysql/mysql-readonly-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`** |
| --------------------------------------------------------------- | -------------------------------------------------------------------- |
| | |
## Retired versions
The following versions have been retired and are no longer available.
If your project uses a retired version, you must update to a [supported version](#supported-versions).
| **`mariadb` / `mysql`** | **`oracle-mysql`** |
| ------------------------------------------------------------ | ----------------------------------------------------------------- |
| | |
### Upgrade a service version or change a service type
Caution
Upgrading 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, as described in the following steps.
To prevent data loss after a service upgrade or change, follow these steps:
1. [Back up your environment](/docs/environments/backup#create-a-manual-backup). 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.
2. [Export the data](#exporting-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.
3. Change the `.services..type` key in your [service configuration](/docs/add-services):
* **Upgrade the version:** Specify the desired supported version.
* **Change the type:** Specify the new service type and the desired supported version.
4. [Import your data](#importing-data) into the new service.
### Downgrade a service
You cannot downgrade MariaDB by lowering the service version in your `config.yaml` file. Doing so will cause your deployment to fail with an `Invalid deployment` error.
To downgrade, you must manually delete the service and then re-create it at the lower version, as described in the following steps.
This is an advanced process — consider testing it on a [preview environment](/docs/environments) to catch any problems before deploying to production.
1. [Back up your environment](/docs/environments/backup#create-a-manual-backup).
2. [Export the data](#exporting-data). Your exported data might contain features that don't exist in the older target version. **Review the dump file for compatibility issues such as:**
* Lines starting with `/*!` followed by a number (e.g. `/*!100200`). These are version-conditional statements — if the number corresponds to a version newer than your target, that SQL may not run correctly.
* Data types introduced after the target version (e.g. `JSON` columns added in MariaDB 10.2)
* SQL syntax introduced after the target version, such as window functions or CTEs (`WITH`)
3. In your `config.yaml` file, delete the `` block (see this [example](#usage-example)) for your MariaDB service and then deploy. This removes the service and permanently deletes its data.
4. Re-add the `` block with the lower target version and deploy to recreate the service.
5. [Import your data](#importing-data) into the new service.
## Relationship reference
For each service [defined via a relationship](#usage-example) to your application,
Upsun automatically generates corresponding environment variables within your application container,
in the `$_` format.
Here is example information available through the [service environment variables](/docs/development/variables#service-environment-variables) themselves,
or through the [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables).
### MariaDB reference
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](/docs/development/variables#service-environment-variables) rather than hard-coding values.
{`
MARIADB_USERNAME=user
MARIADB_SCHEME=mysql
MARIADB_SERVICE=mariadb
MARIADB_FRAGMENT=
MARIADB_IP=123.456.78.90
MARIADB_HOSTNAME=azertyuiopqsdfghjklm.mariadb.service._.eu-1.platformsh.site
MARIADB_PORT=3306
MARIADB_CLUSTER=azertyuiop-main-afdwftq
MARIADB_HOST=mariadbdatabase.internal
MARIADB_REL=mysql
MARIADB_PATH=main
MARIADB_QUERY={'is_master': True}
MARIADB_PASSWORD=
MARIADB_EPOCH=0
MARIADB_TYPE=mariadb:{{version:mariadb:latest}}
MARIADB_PUBLIC=false
MARIADB_HOST_MAPPED=false
`
}
For some advanced use cases, you can use the [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables).
The structure of the `PLATFORM_RELATIONSHIPS` environment variable can be obtained by running `upsun relationships` in your terminal.
{`
{
"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:{{version:mariadb:latest}}",
"public": false,
"host_mapped": false
}
`
}
Example on how to gather [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables) information in a [`.environment` file](/docs/development/variables/set-variables#when-to-use-env-files):
```bash .environment theme={null}
# 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
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](/docs/development/variables#service-environment-variables) rather than hard-coding values.**
{`
ORACLE_MYSQL_USERNAME=user
ORACLE_MYSQL_SCHEME=mysql
ORACLE_MYSQL_SERVICE=oracle-mysql
ORACLE_MYSQL_FRAGMENT=
ORACLE_MYSQL_IP=123.456.78.90
ORACLE_MYSQL_HOSTNAME=azertyuiopqsdfghjklm.oracle-mysql.service._.eu-1.platformsh.site
ORACLE_MYSQL_PORT=3306
ORACLE_MYSQL_CLUSTER=azertyuiop-main-afdwftq
ORACLE_MYSQL_HOST=oraclemysql.internal
ORACLE_MYSQL_REL=mysql
ORACLE_MYSQL_PATH=main
ORACLE_MYSQL_QUERY={'is_master': True}
ORACLE_MYSQL_PASSWORD=
ORACLE_MYSQL_EPOCH=0
ORACLE_MYSQL_TYPE=oracle-mysql:{{version:oracle-mysql:latest}}
ORACLE_MYSQL_PUBLIC=false
ORACLE_MYSQL_HOST_MAPPED=false
`
}
For some advanced use cases, you can use the [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables).
The structure of the `PLATFORM_RELATIONSHIPS` environment variable can be obtained by running `upsun relationships` in your terminal.
{`
{
"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": "oracle_mysql.internal",
"rel": "mysql",
"path": "main",
"query": {
"is_master": true
},
"password": "",
"type": "oracle-mysql:{{version:oracle-mysql:latest}}",
"public": false,
"host_mapped": false
}
`
}
Example on how to gather [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables) information in a [`.environment` file](/docs/development/variables/set-variables#when-to-use-env-files):
```bash .environment theme={null}
# 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 :
{`
services:
# The name of the service container. Must be unique within a project.
:
type: mariadb:
`
}
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:
{`
applications:
# The name of the app container. Must be unique within a project.
:
# 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:
:
`}
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](/docs/configure-apps/image-properties/relationships) configuration for relationships.
That is, it uses default endpoints behind the scenes,
providing a [relationship](/docs/configure-apps/image-properties/relationships) (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](/docs/configure-apps/image-properties/relationships).
With the above definition, the application container (`APP_NAME`) now has [access to the service](#use-in-app) via the relationship `SERVICE_NAME` and its corresponding [service environment variables](/docs/development/variables#service-environment-variables).
{`
applications:
# The name of the app container. Must be unique within a project.
:
# 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.
relationships:
:
service:
endpoint: mysql
`
}
You can define `SERVICE_NAME` and `` 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](/docs/configure-apps/image-properties/relationships) configuration for relationships.
Depending on your needs, instead of explicit endpoint configuration,
you can use [default endpoint configuration](/docs/configure-apps/image-properties/relationships).
With the above definition, the application container now has [access to the service](#use-in-app) via the relationship `` and its corresponding [service environment variables](/docs/development/variables#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](/docs/add-services/mysql/mysql-readonly-replication) topic.
{`
applications:
# The name of the app container. Must be unique within a project.
myapp:
# 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:
services:
# The name of the service container. Must be unique within a project.
mariadb:
type: mariadb:{{version:mariadb:latest}}`
}
{`
applications:
# The name of the app container. Must be unique within a project.
myapp:
# 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.
relationships:
mariadb:
service: mariadb
endpoint: mysql
services:
# The name of the service container. Must be unique within a project.
mariadb:
type: mariadb:{{version:mariadb:latest}}`
}
### OracleMySQL example
{`
applications:
# The name of the app container. Must be unique within a project.
myapp:
# The location of the application's code.
relationships:
oracle-mysql:
services:
# The name of the service container. Must be unique within a project.
oracle-mysql:
type: oracle-mysql:{{version:oracle-mysql:latest}}`
}
{`
applications:
# The name of the app container. Must be unique within a project.
myapp:
# 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.
relationships:
oracle-mysql:
service: oracle-mysql_service
endpoint: mysql
services:
# The name of the service container. Must be unique within a project.
oracle-mysql_service:
type: oracle-mysql:{{version:oracle-mysql:latest}}`
}
### Use in app
To use the configured service in your app, add a configuration file similar to the following to your project.
{`
applications:
# The name of the app container. Must be unique within a project.
myapp:
# The location of the application's code.
source:
root: "/"
[...]
# Relationships enable an app container's access to a service.
relationships:
mariadb:
services:
mariadb:
type: mariadb:{{version:mariadb:latest}}`
}
{`
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 an explicitly set service name and endpoint.
# See the Application reference for all options for defining relationships and endpoints.
relationships:
mariadb:
service: mariadb
endpoint: mysql
services:
mariadb:
type: mariadb:{{version:mariadb:latest}}`
}
This configuration defines a single application (`myapp`), whose source code exists in the `/myapp` directory.
`myapp` has access to the `mariadb` service, via a relationship whose name is [identical to the service name](#2-define-the-relationship)
(as per [default endpoint](/docs/configure-apps/image-properties/relationships) configuration for relationships).
From this, `myapp` can retrieve access credentials to the service through the [relationship environment variables](#relationship-reference).
```bash myapp/.environment theme={null}
# Set environment variables for individual credentials.
# For more information, please visit /docs/development/variables#service-environment-variables.
export DB_CONNECTION="${MARIADB_SCHEME}"
export DB_USERNAME="${MARIADB_USERNAME}"
export DB_PASSWORD="${MARIADB_PASSWORD}"
export DB_HOST="${MARIADB_HOST}"
export DB_PORT="${MARIADB_PORT}"
export DB_DATABASE="${MARIADB_PATH}"
# Surface connection string variable for use in app.
export DATABASE_URL="${DB_CONNECTION}://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}"
```
The above file — `.environment` in the `myapp` directory — is automatically sourced by Upsun into the runtime environment, so that the variable `MARIADB_URL` can be used within the application to connect to the service.
Note that `DATABASE_URL`, and all Upsun [service environment variables](/docs/development/variables#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](/docs/get-started).
### 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:
```bash theme={null}
upsun ssh env
```
The result is the complete [information for all relationships](#relationship-reference) with an additional `DATABASE_URL` property, defined on step [Use in app](#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](/docs/development/variables#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](https://support.platform.sh/hc/en-us/community/posts/16439596373010).
## 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](#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](#configure-the-database). |
| `rotate_passwords` | A boolean | 10.3+ | Defaults to `true`. When set to `false`, [password rotation](#password-rotation) is disabled. |
Example configuration:
{`
services:
# The name of the service container. Must be unique within a project.
mariadb:
type: mariadb:{{version:mariadb:latest}}
configuration:
schemas:
- main
endpoints:
mysql:
default_schema: main
privileges:
main: admin
properties:
max_allowed_packet: 64`
}
## 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](/docs/development/ssh).
From your [relationship data](#relationship-reference), you need: `MARIADB_HOST`, `MARIADB_PORT`, `MARIADB_USERNAME`, `MARIADB_PATH` values.
Then run the following command:
\`
{`
mysql -h -P -u
`}
Assuming the values from the [MariaDB reference](#mariadb-reference), that would be:
```bash theme={null}
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:
```bash theme={null}
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](#configuration-options).
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](/docs/add-services/mysql/mysql-replication). 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](https://devcenter.upsun.com/posts/connect-multiple-projects-applications-or-services-together/)). |
## Multiple databases
With version `10.0` or later, you can define multiple databases.
To do so, define multiple `schemas` in your [service configuration](#configuration-options).
You can also specify multiple `endpoints` for [permissions](#define-permissions).
If neither `schemas` nor `endpoints` is included, it's equivalent to the following default:
{`
services:
# The name of the service container. Must be unique within a project.
mariadb:
type: mariadb:{{version:mariadb:latest}}
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.
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`.
{`
services:
# The name of the service container. Must be unique within a project.
mariadb:
type: mariadb:{{version:mariadb:latest}}
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](/docs/configure-apps):
```yaml .upsun/config.yaml theme={null}
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](#relationship-reference).
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](https://mariadb.com/kb/en/optimizer-switch/). |
| `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](https://mariadb.com/kb/en/server-system-variables/#optimizer_use_condition_selectivity). |
| `innodb_adaptive_hash_index` | `integer` | `0` in version 10.5+ and `1` before that | Enable/Disable InnoDB Hash Index. See the [MariaDB documentation](https://mariadb.com/kb/en/innodb-system-variables/#innodb_adaptive_hash_index). |
| `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](https://mariadb.com/kb/en/server-system-variables/#table_definition_cache). |
| `table_open_cache` | `integer` | `400` | The maximum number of open tables cached in one table cache instance. See the [MariaDB documentation](https://mariadb.com/kb/en/server-system-variables/#table_open_cache). |
| `wsrep_sync_wait` | `integer` | `0` (Disabled) | Ensure execution of statements in fully synced nodes. See the [MariaDB documentation](https://mariadb.com/kb/en/galera-cluster-system-variables/#wsrep_sync_wait). |
An example of setting these properties:
{`
services:
# The name of the service container. Must be unique within a project.
mariadb:
type: mariadb:{{version:mariadb:latest}}
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:
```sql theme={null}
-- 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](https://mariadb.com/kb/en/character-set-and-collation-overview/).
MariaDB configuration properties like [`max_connections`](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#max_connections) and [`innodb_buffer_pool_size`](https://mariadb.com/kb/en/innodb-buffer-pool/#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](/docs/add-services/mysql/troubleshoot#too-many-connections) 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](/docs/add-services/mysql#2-define-the-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`](#1-configure-the-service) in the `services` configuration – see the example in the [multiple databases](#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.`. **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](/docs/configure-apps),
you can retrieve the password for each endpoint
through the `PLATFORM_RELATIONSHIPS` [environment variable](/docs/development/variables/use-variables#use-provided-variables)
within your [application containers](/docs/development/variables/use-variables#access-variables-in-your-app).
Using this method to retrieve password credentials is considered a best practice: passwords change automatically (or [*rotate*](#password-rotation)) 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](#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`](/docs/add-services/mysql#configuration-options): 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](#mariadb-reference).
Passwords do **not** rotate automatically when you reset this value to `true`.
Important
Disabling password rotation can jeopardize compliance with security certifications - make sure you weigh this risk alongside the convenience of SSH-tunneling access.
## Storage Engine
It's best to use the InnoDB storage engine wherever possible instead of MyISAM.
If MyISAM tables have been inadvertently created or imported in your environments
(if you see `ENGINE=MyISAM` in the response to `SHOW CREATE TABLE EXISTING_TABLE`),
convert them to use the InnoDB storage engine as follows:
1. Rename the existing table.
{`
RENAME TABLE ;
`}
2. Create a new table from the data in the existing table.
{`
CREATE TABLE SELECT * from ;
`}
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:
```bash theme={null}
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:
{`
upsun db:dump --relationship
`}
### Compression
By default, the file is uncompressed.
To compress it, use the `--gzip` (`-z`) option:
```bash theme={null}
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:
```bash theme={null}
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:
```bash theme={null}
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:
{`
upsun sql --relationship -e < my_database_backup.sql
`}
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](/docs/development/sanitize-db).
## Large databases
Upsun supports MariaDB and Oracle MySQL databases of any size, including multi-terabyte deployments. The same service configuration applies whether your database holds gigabytes or tens of terabytes. Only the resources you allocate to it differ.
### Cloning at any size
When you create a [preview environment](/docs/environments), Upsun clones the entire parent environment (including its database) at the storage layer. The clone completes in seconds to under a minute regardless of database size. A 10 TB database clones in roughly the same time as a 10 GB one. Service start-up time also stays largely the same with larger datasets on these engines.
### What drives performance
Total disk size is rarely the limiting factor for query performance. What matters is the **active dataset** (or working set), the subset of rows, indexes, and pages your application actually reads and writes.
A 10 TB database that serves queries against 5 GB of hot data can outperform a 50 GB database. The smaller database performs worse if it scans entire tables on every request. As long as the active dataset fits in the [InnoDB buffer pool](https://mariadb.com/kb/en/innodb-buffer-pool/), queries are served from memory without touching disk.
By contrast, queries that read most of a table become more expensive as the table grows. The same is true of indexes that effectively scan the whole table.
### Sizing for large datasets
Memory is the lever that keeps the active dataset hot. MariaDB and Oracle MySQL containers use the [`HIGH_MEMORY` container profile](/docs/manage-resources/adjust-resources#advanced-container-profiles) by default. Combined with [Guaranteed CPU](/docs/manage-resources/guaranteed-resources), you can allocate up to **512 GB of RAM** to a single database container.
To work efficiently with a large database:
* Size memory to fit the active dataset in the InnoDB buffer pool. The buffer pool is derived from the container's available memory. See the [troubleshooting guide](/docs/add-services/mysql/troubleshoot#too-many-connections) for the exact formula.
* Make sure queries hit indexes, and avoid full table scans on large tables.
* Watch index selectivity. An index that scans most of a large table on each query loads gigabytes into memory, evicting the active dataset.
* Use [`EXPLAIN`](https://mariadb.com/kb/en/explain/) to verify which indexes a query uses and how many rows it examines.
Disk size alone, even into the multi-terabyte range, does not determine database performance. It determines what data you can store. Memory and query patterns determine how fast you can serve it.
## Replication
There is no on-site primary/replica support in your environments.
In rare cases (such as for certain backup purposes),
you can also enable [remote replication](/docs/add-services/mysql/mysql-replication) to your own replica data.
The replica isn't available to your application.
## Troubleshoot
If you run into issues, [troubleshoot MySQL](/docs/add-services/mysql/troubleshoot).