MySQL with GTID replicas on a private network

Deploy

Primary and replica from one bootstrap script

Save one bootstrap script, create two VMs from it, and connect them with GTID replication.

~/infra

$ edge compute scripts create \

--name mysql-8 --file mysql.sh

$ edge compute create --name mysql-1 \

--size s-4vcpu-8gb --image ubuntu-24 \

--region london --script mysql-8

✓ vm-8d2f4b up, private IP 10.185.0.3

$ mysql -h 10.185.0.3 -u app -p \

-e 'SHOW REPLICAS'

✓ mysql-2 at 10.185.0.6, auto-position

IOPS to provisionNone

mysql-1 in LondonPrimary

EngineMySQL 8.0, InnoDB

Replicamysql-2, 0s behind

1. 01

   Save a MySQL bootstrap

   ${server_id} is a script parameter, filled in per VM when it's created. Ubuntu 24.04 ships MySQL 8.0; swap the package for mariadb-server if you prefer.

   mysql.shCopy

   #!/bin/bash
   set -e
   export DEBIAN_FRONTEND=noninteractive
   apt-get update && apt-get install -y mysql-server
   cat > /etc/mysql/mysql.conf.d/edge.cnf <<'EOF'
   [mysqld]
   bind-address               = 0.0.0.0
   server_id                  = ${server_id}
   log_bin                    = mysql-bin
   gtid_mode                  = ON
   enforce_gtid_consistency   = ON
   binlog_expire_logs_seconds = 604800
   innodb_buffer_pool_size    = 5G
   EOF
   systemctl restart mysql

2. 02

   Create the primary and a replica

   Attach both VMs to one private network, and add a security group that allows 3306 from 10.185.0.0/24 only.

   shellCopy

   $ edge compute scripts create --name mysql-8 --file mysql.sh
   
   $ edge compute create --name mysql-1 --size s-4vcpu-8gb \
       --image ubuntu-24 --region london \
       --script mysql-8 --script-param server_id=1
   $ edge compute create --name mysql-2 --size s-4vcpu-8gb \
       --image ubuntu-24 --region london \
       --script mysql-8 --script-param server_id=2

3. 03

   Start GTID replication

   Create a repl user on mysql-1 with REPLICATION SLAVE, then point mysql-2 at it. Seed the replica from a backup first if you already have data.

   mysql-2Copy

   CHANGE REPLICATION SOURCE TO
     SOURCE_HOST = '10.185.0.3',
     SOURCE_USER = 'repl',
     SOURCE_PASSWORD = '<pw>',
     SOURCE_AUTO_POSITION = 1,
     SOURCE_SSL = 1;
   START REPLICA;
   SHOW REPLICA STATUS\G

4. 04

   Ship dumps and binlogs to Edge Storage

   Run both from cron on the replica or the primary. Any S3 tool works; this uses the AWS CLI with the Edge endpoint.

   shellCopy

   $ mysqldump --all-databases --single-transaction \
       --source-data=2 --routines --triggers | gzip | \
     aws s3 cp - s3://mysql-backups/dumps/$(date +%F).sql.gz \
       --endpoint-url https://storage.edge.network
   
   $ aws s3 sync /var/lib/mysql/ s3://mysql-backups/binlogs/ \
       --exclude "*" --include "mysql-bin.*" \
       --endpoint-url https://storage.edge.network

Prefer to hand it off? Give the job to your AI agent or have our engineers do it.