> ## Documentation Index
> Fetch the complete documentation index at: https://docs.archil.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL

> Run MySQL in a sandbox with its data directory on an Archil disk, and connect to it from your machine with the mysql client.

This guide runs [MySQL](https://www.mysql.com/) 8.4 in a [persistent sandbox](/compute/sandboxes/introduction) with its data directory on an Archil disk. MySQL runs as a supervised service, listens on a public hostname, and only accepts encrypted connections from a user with a password. You connect from your own machine with the `mysql` client, write some rows, stop the sandbox, and read them back.

MySQL doesn't need to know anything about Archil. It reads and writes files in its data directory as usual, and Archil keeps them durable in object storage, so the database outlives any one sandbox.

## Prerequisites

Export your Archil API key and a region where sandboxes are available (`aws-us-east-1`, `aws-us-west-2`, or `aws-eu-west-1`):

```bash theme={null}
export ARCHIL_API_KEY=key-...
export ARCHIL_REGION=aws-us-east-1
```

Then install the SDK:

<CodeGroup>
  ```bash TypeScript theme={null}
  npm init -y && npm pkg set type=module
  npm install disk tsx
  ```

  ```bash Python theme={null}
  pip install archil
  ```
</CodeGroup>

On your machine you also need the `mysql` client and [`socat`](http://www.dest-unreach.org/socat/), which carries the connection over TLS to the sandbox:

<CodeGroup>
  ```bash macOS theme={null}
  brew install mysql-client socat
  export PATH="$(brew --prefix mysql-client)/bin:$PATH"
  ```

  ```bash Ubuntu theme={null}
  sudo apt-get install -y mysql-client socat
  ```
</CodeGroup>

Finally, generate a password. The setup script gives it to MySQL's `root` user and to an `app` user, and the `mysql` client reads it later:

```bash theme={null}
export MYSQL_PASSWORD=$(openssl rand -hex 24)
```

## Write the setup script

This script runs inside the sandbox. The Archil disk is mounted by root, and MySQL runs as the `mysql` user, so the script hands the data directory over first. On an empty disk it then initializes MySQL with an init file that sets the `root` password, creates an `app` database, and creates an `app` user that can connect from anywhere with the password. Finally, it registers MySQL as a service, which restarts if it crashes and starts again whenever the sandbox boots. `--require-secure-transport` makes MySQL reject network connections that don't use TLS; the server generates its own certificate during initialization.

```bash setup.sh theme={null}
#!/bin/bash
set -euo pipefail

DATADIR=/mnt/archil/mysql
mkdir -p "$DATADIR"
chown mysql:mysql "$DATADIR"

if [ ! -d "$DATADIR/mysql" ]; then
  cat > /tmp/init.sql <<EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY '$MYSQL_PASSWORD';
CREATE DATABASE app;
CREATE USER 'app'@'%' IDENTIFIED BY '$MYSQL_PASSWORD';
GRANT ALL ON app.* TO 'app'@'%';
EOF
  chown mysql /tmp/init.sql
  mysqld --initialize-insecure --datadir="$DATADIR" --init-file=/tmp/init.sql
  rm /tmp/init.sql
fi

archil-sandbox services create mysql --tcp-port 3306 -- \
  mysqld --datadir="$DATADIR" --require-secure-transport=ON
timeout 60 bash -c 'until mysqladmin ping --silent 2>/dev/null; do sleep 1; done'
```

Because the script only initializes an empty data directory, you can run it on a new sandbox that mounts an existing `mysql-data` disk, and it just starts the server on the data that's already there.

## Start the database

The next script creates a disk, creates a sandbox from the official `mysql:8.4` image with the disk mounted at `/mnt/archil`, uploads `setup.sh`, and runs it with your password. Then it exposes port 3306 publicly and prints the hostname.

<CodeGroup>
  ```typescript create.ts theme={null}
  import { readFile } from "node:fs/promises";
  import * as archil from "disk";

  const { disk } = await archil.createDisk({ name: "mysql-data" });
  const sandbox = await archil.createSandbox({
    name: "mysql",
    baseImage: "mysql:8.4",
    vcpuCount: 2,
    memSizeMiB: 4096,
    mounts: [{ disk, path: "/mnt/archil" }],
  });

  await sandbox.files.uploadFile(await readFile("setup.sh"), "/root/setup.sh", { mode: 0o755 });
  const setup = await sandbox.exec("/root/setup.sh", {
    env: { MYSQL_PASSWORD: process.env.MYSQL_PASSWORD! },
  });
  if (setup.exitCode !== 0) throw new Error(setup.stderr);

  console.log(await sandbox.exposePort(3306));
  ```

  ```python create.py theme={null}
  import os

  import archil

  disk = archil.create_disk(name="mysql-data").disk
  sandbox = archil.create_sandbox(
      name="mysql",
      base_image="mysql:8.4",
      vcpu_count=2,
      mem_size_mib=4096,
      mounts=[archil.SandboxMountSpec(disk=disk, path="/mnt/archil")],
  )

  sandbox.files.upload_file("setup.sh", "/root/setup.sh", mode=0o755)
  setup = sandbox.exec("/root/setup.sh", env={"MYSQL_PASSWORD": os.environ["MYSQL_PASSWORD"]})
  if setup.exit_code != 0:
      raise RuntimeError(setup.stderr)

  print(sandbox.expose_port(3306))
  ```
</CodeGroup>

<CodeGroup>
  ```bash TypeScript theme={null}
  npx tsx create.ts
  ```

  ```bash Python theme={null}
  python create.py
  ```
</CodeGroup>

```text theme={null}
3306-agquue2c3jzwhbjukocuwzr7d4.prod-aws-us-west-2-green.archil.app
```

<Warning>
  An exposed port is reachable by anyone who knows the hostname. The `app` user accepts connections from any address,
  so keep its password long and random, as generated above, and never create a remote MySQL user with a weak password
  or none.
</Warning>

## Connect from your machine

Archil serves public ports over TLS on port 443 with a publicly trusted certificate, and forwards the decrypted TCP
stream to the sandbox. The `mysql` client can't open that kind of TLS connection on its own, so start a local `socat`
tunnel that listens on port 13306, wraps each connection in TLS, and verifies Archil's certificate. Use the hostname
that `create` printed:

```bash theme={null}
export MYSQL_HOST=3306-agquue2c3jzwhbjukocuwzr7d4.prod-aws-us-west-2-green.archil.app
socat TCP-LISTEN:13306,fork,reuseaddr OPENSSL:$MYSQL_HOST:443,verify=1 &
```

`socat` may print a warning that it doesn't check certificate revocation lists; the tunnel still verifies the
certificate. Now connect through the tunnel as the `app` user. Use `127.0.0.1` rather than `localhost`, which the
`mysql` client treats as a local socket. `--ssl-mode=REQUIRED` makes MySQL encrypt the stream inside the tunnel, too,
so it stays encrypted all the way to the server:

```bash theme={null}
export MYSQL_PWD=$MYSQL_PASSWORD
mysql --host=127.0.0.1 --port=13306 --user=app --ssl-mode=REQUIRED --table app <<'SQL'
CREATE TABLE notes (id INT AUTO_INCREMENT PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notes (body) VALUES ('hello from my laptop'), ('stored on an Archil disk');
SELECT id, body FROM notes;
SELECT @@datadir;
SQL
```

```text theme={null}
+----+--------------------------+
| id | body                     |
+----+--------------------------+
|  1 | hello from my laptop     |
|  2 | stored on an Archil disk |
+----+--------------------------+
+--------------------+
| @@datadir          |
+--------------------+
| /mnt/archil/mysql/ |
+--------------------+
```

## Stop it and let a connection wake it

A stopped sandbox doesn't use compute, and a connection to its public port starts it again. Stop the sandbox:

<CodeGroup>
  ```typescript stop.ts theme={null}
  import * as archil from "disk";

  const sandbox = (await archil.listSandboxes()).find((s) => s.name === "mysql")!;
  await sandbox.stop();
  console.log("sandbox is", sandbox.status);
  ```

  ```python stop.py theme={null}
  import archil

  sandbox = next(s for s in archil.list_sandboxes() if s.name == "mysql")
  sandbox.stop()
  print("sandbox is", archil.get_sandbox(sandbox.id).status)
  ```
</CodeGroup>

<CodeGroup>
  ```bash TypeScript theme={null}
  npx tsx stop.ts
  ```

  ```bash Python theme={null}
  python stop.py
  ```
</CodeGroup>

```text theme={null}
sandbox is stopped
```

Stopping sends `SIGTERM` to MySQL, which shuts down cleanly. Query the table again through the same tunnel:

```bash theme={null}
mysql --host=127.0.0.1 --port=13306 --user=app --ssl-mode=REQUIRED --table app -e "SELECT id, body FROM notes;"
```

```text theme={null}
+----+--------------------------+
| id | body                     |
+----+--------------------------+
|  1 | hello from my laptop     |
|  2 | stored on an Archil disk |
+----+--------------------------+
```

The connection booted the sandbox, which mounted the disk and started the `mysql` service before answering, in a few seconds. The rows
were on the Archil disk the whole time. With MySQL's default settings, InnoDB flushes its redo log to the Archil disk
on every commit, so a committed transaction survives a crash, too.

## Clean up

Stop the tunnel, then stop and delete the sandbox and delete the disk:

```bash theme={null}
kill %1
```

<CodeGroup>
  ```typescript cleanup.ts theme={null}
  import * as archil from "disk";

  const sandbox = (await archil.listSandboxes()).find((s) => s.name === "mysql");
  await sandbox?.stop();
  await sandbox?.delete();
  const [disk] = await archil.listDisks({ name: "mysql-data" });
  await disk?.delete();
  ```

  ```python cleanup.py theme={null}
  import archil

  for sandbox in archil.list_sandboxes():
      if sandbox.name == "mysql":
          sandbox.stop()
          sandbox.delete()
  for disk in archil.list_disks(name="mysql-data"):
      disk.delete()
  ```
</CodeGroup>

## Next steps

<CardGroup cols={2}>
  <Card title="Persistent sandboxes" icon="box" href="/compute/sandboxes/introduction">
    Lifecycle, services, network access, and forking in detail.
  </Card>

  <Card title="PostgreSQL" icon="elephant" href="/guides/apps/postgres">
    Run PostgreSQL the same way, with its data directory on an Archil disk.
  </Card>

  <Card title="Sharing disks" icon="share-nodes" href="/concepts/sharing-disks">
    Mount modes, and reading a disk from several machines.
  </Card>

  <Card title="TypeScript SDK" icon="js" href="/sdks/typescript">
    Full reference for sandboxes, disks, and serverless execution.
  </Card>
</CardGroup>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.