> ## 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 external replication

> In rare cases, it may be useful to maintain a replica instance of your MySQL/MariaDB database outside of Upsun on a remote server.

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>;
};

export const VariableBlock = ({name}) => {
  return <var spellCheck={false} title={`Replace '${name}' with your own data`}>{name}</var>;
};

Typically, an automated backup is better for short-term usage and a `mysqldump` for longer term storage, but in some cases the data set is large enough that `mysqldump` is prohibitive.
In that case, you can enable external replication using an extra permission.

This guide explains replication on the Upsun side only; you must also set up and maintain your own replica instance. Consult the MySQL or MariaDB documentation for details.

## Create a replication user

To set up replication you need to create a replication-enabled user.
For each database that you'd like to replicate, you need to assign a `replication` permission/role, under a corresponding `endpoint`:

<DynamicCodeBlock language="yaml">
  {`
      services:
        # The name of the service container. Must be unique within a project.
        mariadb:
          type: mariadb:{{version:mariadb:latest}}
          configuration:
            schemas:
              - main
            endpoints:
              # Restate the default user to be used by your application.
              mysql:
                default_schema: main
                privileges:
                  main: admin
              replicator:
                privileges:
                  main: replication`
  }
</DynamicCodeBlock>

The preceding example:

* Creates a `replicator` user.
* Grants read-only and table locking rights on the `main` database (namely `Select_priv`, `Show_view_priv`, `Create_tmp_table_priv`, `Lock_tables_priv` privileges).
* Grants global replication rights (namely `Repl_slave_priv` and `Repl_client_priv` privileges).
* Grants flushing rights (`Reload_priv` used for flushing before reading the binary log position).
* If at least one `replication` permission is defined, the bin-logging is enabled on the primary server, which is essential for the replication.

## Define a relationship for the new endpoint

Even if your application won’t access the replication endpoint, you still need to expose it to an application as a relationship so that you can connect to it over SSH.
Add a new relationship to your application container:

```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: "/"

    [...]

    # Relationships enable an app container's access to a service.
    relationships:
      database:
        service: mariadb
        endpoint: mysql
      replication:
        service: mariadb
        endpoint: replicator

```

## Get the primary's binary log co-ordinates

Open the MySQL CLI to the `replication` relationship, either by accessing the credentials while on the app container or using the following command.

```bash theme={null}
upsun sql -r replication
```

Now you need to prevent any changes to the data while you view the binary log position. You'll use this to tell the replica at exactly which point it should start replicating from. On the primary server, flush and lock all tables by running `FLUSH TABLES WITH READ LOCK`. Keep this session running - exiting it releases the lock. Get the current position in the binary log by running `SHOW MASTER STATUS`:

```sql theme={null}
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.016 sec)

mysql> SHOW MASTER STATUS;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| binlogs.000002  |  1036    | dflt         |                  |
+-----------------+----------+--------------+------------------+
```

Record the `File` and `Position` details. If binary logging has just been enabled, these are blank. Now, with the lock still in place, copy the data from the primary to the replica.

Login to the app container, then run:

```sh theme={null}
# Dump the data from primary. Note that it dumps only the databases, which "replicator" user has access to.
$ mysqldump --all-databases --single-transaction -h database.internal -P 3306 -u replicator -p > /path/to/dump.sql
```

Download the dump file, then move it to the server where your replica lives so that the replica can import it.

```bash theme={null}
# Copy the dump to your replica
$ mysql -u root < /path/to/dump.sql
```

Note for live databases: It is sufficient to make a local copy of the data; you don’t need to keep the primary locked until the replica has imported the data. Once the `mysqldump` has completed, you can release the lock on the primary by running `UNLOCK TABLES`.

```sql theme={null}
mysql> UNLOCK TABLES;
```

## Set up the replica

### Configure the replica

As mentioned above you have to set up a replica on your own. Assuming that you have a running MariaDB/MySQL replica instance, give the replica a unique `server_id` (distinct from primary). You can find out primary's `server_id` by running:

```sql theme={null}
mysql> SHOW VARIABLES LIKE 'server_id';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
```

Then set a distinct `server_id` number (e.g. server\_id+1) in your replica config (e.g. `my.cnf`) under:

```
[mysqld]
server_id=2
```

Next, reload the replica instance for the changes to take effect.

### Set up SSH tunneling

You need to set up an SSH tunnel from the replica server to the primary, tunneled through the application.

<Note>
  The SSH tunnel is interrupted every time the environment redeploys. For replication to continue you must set up an auto-restart for the tunnel. There are many ways to do so that are out of the scope of this documentation.
</Note>

You can set up an SSH tunnel using one of the following methods:

* Manually using SSH. Using this method makes it easier to set up an auto-restart for the tunnel. Consult the SSH documentation for details on setting up the tunnel and the auto-restart.

* Run the following Upsun CLI command
  (replacing `<BRANCH_NAME>` with the name of your production branch):

  ```bash theme={null}
    upsun tunnel:open --project <PROJECT_ID> --environment <BRANCH_NAME>
  ```

  This command opens local SSH tunnels to all services accessible from the application.

To configure an auto-restart, you need the project's SSH address, which you can retrieve by running:

```bash theme={null}
upsun ssh --pipe --project <PROJECT_ID>
```

For details about this command, see the [Upsun CLI reference](/cli/reference#usage-54).

### Binary log retention and cleanup

When replication is disabled, the MariaDB service stops managing the binary logs and they remain on the file system. **You must delete these logs manually**. If the remote replica has been unreachable for some time, these logs can consume a significant amount of storage.

### Start the Replica

Once the data has been imported, you are ready to start replicating. Begin by running a `CHANGE MASTER TO`, making sure that `MASTER_LOG_FILE` matches the file and `MASTER_LOG_POS` the position returned by the earlier `SHOW MASTER STATUS` on the Upsun database. For example:

```sql theme={null}
mysql> CHANGE MASTER TO
  MASTER_HOST='<REPLICATION_HOST>',
  MASTER_USER='replicator',
  MASTER_PASSWORD='<REPLICATION_PASSWORD>',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='binlogs.000002',
  MASTER_LOG_POS=1036,
  MASTER_CONNECT_RETRY=10;
```

Where `<REPLICATION_HOST>` varies depending on the SSH tunneling configuration you have, and the `<REPLICATION_PASSWORD>` can be obtained by running `upsun ssh env`.

Now start the replica with the `START SLAVE` command:

```sql theme={null}
mysql> START SLAVE;
```

Check that the replication is working by running the `SHOW SLAVE STATUS` command:

```sql theme={null}
mysql> SHOW SLAVE STATUS \G
```

If replication is working correctly, the values of both `Slave_IO_Running` and `Slave_SQL_Running` should be `Yes`:

```sql theme={null}
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```

### \[Optional/Troubleshooting] Skipping invalid binary log queries

In some cases, after applying primary's dump to the replica and starting the replica, you might experience replication errors (`Slave_SQL_Running: No` and `Error:` in the output of `SHOW SLAVE STATUS \G` above). Each of such errors needs a careful inspection, but you might be able to just skip some of them. For example:

```sql theme={null}
mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
mysql> SHOW SLAVE STATUS \G
```

In case you have multiple errors you would need to repeat the steps above (preferred) or set `SQL_SLAVE_SKIP_COUNTER` (which corresponds to skipping the next N events from the primary) to a larger value.