Use a MySQL Database

By Chris Fidao

If you just want to run a quick, self-managed MySQL instance on Fly.io, here's how to do it. It's pretty basic, with one caveat around using a Volume to persist data.

Most Fly apps use a Dockerfile to define an application and its dependencies. However, in this case we can use MySQL's official container directly - no need for a custom Dockerfile!

Here's how to run MySQL.

Create the App

We'll run MySQL as a new app:

# Make a directory for the mysql app
mkdir my-mysql
cd my-mysql

# Run `fly launch` to create an app
fly launch

You can name the app whatever you'd like. The name will become a hostname our application uses to connect to the database, such as my-mysql.internal.

Don't deploy the application just yet - we have some work to do first.

Configure the App

Let's create a volume straight-away. If you don't create a volume, you'll lose all of your data on each deployment.

# Create a volume named "mysqldata" within our app "my-mysql"
fly volumes create mysqldata --size 10 # gb

We also need to set some secrets required by the MySQL container:

# Set secrets:
# MYSQL_PASSWORD      - password set for user $MYSQL_USER
# MYSQL_ROOT_PASSWORD - password set for user "root"
fly secrets set MYSQL_PASSWORD=password MYSQL_ROOT_PASSWORD=password

Save these secrets somewhere, because they're not accessible after you set them.

Finally, edit the fly.toml file generated to look something like this:

app = "my-mysql"
kill_signal = "SIGINT"
kill_timeout = 5

[mounts]
  source="mysqldata"
  destination="/data"

[env]
  MYSQL_DATABASE = "cube_theory"
  MYSQL_USER = "hotdogs_are_tacos"

[build]
  image = "mysql:8"

[experimental]
  cmd = [
    "--default-authentication-plugin", 
    "mysql_native_password", 
    "--datadir", 
    "/data/mysql"
  ]

There's a few important things to note:

  1. We deleted the [[services]] block and everything under it. We don't need it!
  2. We added the [build] section to specify an existing Docker image. We don't need to create a Dockerfile of our own.
  3. The [env] section contains two not-so-secret environment variables that MySQL will need to initialize itself.
  4. We added the [experimental] section, which lets us pass a custom command (overriding Docker's CMD).
    1. For MySQL 8, you'll want to use the mysql_native_password password plugin
    2. More importantly, we set MySQL's data directory to a subdirectory of our mounted volume
⚠️ Mounting a disk in Linux usually results in a lost+found directory being created. However, MySQL won't initialize into a data directory unless it's completely empty. Therefore, we use a subdirectory of the mounted location: /data/mysql.

Deploy the App

We are almost ready to deploy the MySQL app!

There's one more detail. MySQL 8+ has higher baseline resource demands than MySQL 5.7.

If you're using MySQL 8, it's best to add some additional RAM to the VM:

# Give the vm 2GB of ram
fly scale memory 2048

And now we can finally deploy it:

fly deploy

You should now have an app running MySQL!

Your other apps can access the MySQL service by its name. In my case, I would use my-mysql.internal as the hostname. Any app that needs to access the database should set the hostname and username as environment variables, and create a secret for the database password.

Backups

We'll take a snapshot of the created volume every day. We retain 7 days of snapshots.

To restore a snapshot, make sure you have the latest version of the fly command, and pass a --snapshot-id flag when creating a new volume.

# Get a volume ID
fly volumes list -a my-mysql

# List snapshots for a volume
fly volumes snapshots list vol_xxx

# Create a new volume from a snapshot
fly volumes create --snapshot-id vs_xxx --size 10