> ## 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 read-only replication

> Configure and access read-only MariaDB replicas to ease the load on a primary database.

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 <CodeBlock {...codeBlockProps}>{code}</CodeBlock>;
};

You can improve the performance of read-heavy applications by defining read-only replicas of your MariaDB database and then connecting your applications to those replicas.

Examples of read-heavy applications include:

* Listing pages or dashboards
* Reporting or analytics jobs
* Background jobs that frequently query data

<Info>
  <h4>Note</h4>

  * **Replication is asynchronous**: Delays of a few milliseconds might occur between writes on the primary database and reads on the replica database.
  * **Replicas are read-only**: This restriction ensures data consistency and integrity. Attempts to modify data will result in an SQL error.
</Info>

### Read-only vs. external replicas

Read-only replicas are used primarily to improve application performance by distributing database read requests from read-heavy applications.

Other common use cases for read-only replicas include:

* Cross-region backup: Replicating data to different geographical regions
* Data warehousing: Extracting data from production to analytics projects

[External replicas](/docs/add-services/mysql/mysql-replication) reside on remote servers and have different use cases, including disaster recovery.

### Replica scope and sharing services

MariaDB services (which provide access to databases and replicas) defined in a project cannot be accessed by or shared with applications in other projects.

<Info>
  <h4>Important</h4>

  * **Replication is asynchronous**: Delays of a few milliseconds might occur between writes on the primary database and reads on the replica database.
  * **Replicas are read-only**: This restriction ensures data consistency and integrity. Attempts to modify a replica's data will result in an SQL error.
</Info>

## 1. Configure the primary and replica services

The following code fragment defines two MariaDB services: a primary and a replica. You can use this fragment as a template by copying it into your `.upsun/config.yaml` file.

Be sure to:

* Replace `VERSION` with the [supported MariaDB version](/docs/add-services/mysql#supported-versions) that you need. Use the same version number for the primary and replica services.
* **Important:** Use `replicator` as the endpoint name when you define the replica service. This is a special endpoint name that the replica service uses to connect to the database.

<DynamicCodeBlock language="yaml" filename=".upsun/config.yaml">
  {`
    services:
      db:
        type: mariadb:<VERSION>
        configuration:
          schemas:
            - main
          endpoints:
            main:
              default_schema: main
              privileges:
                main: admin
            replicator:
              privileges:
                main: replication

      db-replica1:
        type: mariadb-replica:<VERSION>
        configuration:
          schemas:
            - main
          endpoints:
            main:
              default_schema: main
              privileges:
                main: admin
        relationships:
          primary: db:replicator # Do not change the name 'primary'. The service expects to receive this name.

      db-replica2:
        type: mariadb-replica:<VERSION>
        configuration:
          schemas:
            - main
          endpoints:
            main:
              default_schema: main
              privileges:
                main: admin
        relationships:
          primary: db:replicator # Do not change the name 'primary'. The service expects to receive this name.
    `
  }
</DynamicCodeBlock>

### How it works

Using the sample code fragment above:

1. The primary service (`db`) defines an additional `replicator` endpoint, granting the `replication` privilege. This enables a replica to connect and continuously replicate data from the primary database.

   ```yaml theme={null}
   replicator:
     privileges:
       main: replication
   ```

2. The replica services (`db-replica1` and `db-relica2`) use the `mariadb-replica` image type and connect back to the primary database service through the primary relationship. This establishes a replication link from `db` (the source) to `db-replica` (the target).

   ```yaml theme={null}
   relationships:
     primary: db:replicator
   ```

The `db-replica1` and `db-replica2` replica services continuously stream data from the primary endpoint, maintaining a read-only copy of the primary database content. Write operations are not permitted on the replicas.

## 2. Define the relationship between the application and the replicas

Even if your application won't access the replication endpoint, you must expose the endpoint to an application as a relationship so that you can connect to it over SSH.

Define a new relationship in your application container, as shown below:

```yaml .upsun/config.yaml theme={null}
name: myapp

[...]

# Relationships enable an app container's access to a service.
relationships:
  # More information: https://developer.upsun.com/docs/configure-apps/image-properties/relationships
  database:
    service: db
    endpoint: main
  database-readonly:
    service: db-replica
```

If your application's performance is still not what you expect, you can configure additional replicas as described above.