Back up MySQL / MariaDB

Create a consistent dump of a MySQL or MariaDB database, verify it, and prepare it for upload to Lighthouse.

Prerequisites

• The mysqldump client (ships with the MySQL/MariaDB client packages):
  • macOS: brew install mysql-client (or mariadb)
  • Debian/Ubuntu: sudo apt-get install mysql-client (or mariadb-client)
• Credentials for a user with SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, and (for --routines) EVENT privileges.

1. Sign in & create an API key

You need a Lighthouse account, a workspace, and an API key before you can upload a backup.

1. Sign in to the portal and claim your free 5 GB workspace — see Web Portal & Free Workspace.

2. Create an API key scoped backup:write, backup:read, snapshots:read — see API Keys.

3. Export the credentials so the upload step can read them:

   export LH_API_KEY="lh_xxxxxxxxxxxxxxxxxxxxxxxx"
   export LH_WORKSPACE_ID="your-workspace-uuid"

Already have a key? Continue to the dump step below.

2. Create the dump

Create the local dump directory once:

mkdir -p ./db-dumps

mysqldump \
  --host=127.0.0.1 \
  --port=3306 \
  --user=root \
  --password='your_password' \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  app_db > ./db-dumps/app.sql

Flag	Why
--single-transaction	Consistent snapshot of InnoDB tables without locking writers.
--quick	Streams rows instead of buffering large tables in memory.
--routines	Include stored procedures and functions.
--triggers	Include triggers (included by default, shown for clarity).
app_db	The database to dump.

Keep the password out of history

Put credentials in a ~/.my.cnf file ([client] section) and drop --password so it isn't visible in ps or shell history.

Dump all databases

mysqldump --host=127.0.0.1 --user=root --password='your_password' \
  --single-transaction --quick --routines --triggers \
  --all-databases > ./db-dumps/all.sql

3. Verify the dump

ls -lh ./db-dumps/app.sql
tail -n 1 ./db-dumps/app.sql   # a complete dump ends with: -- Dump completed on ...

4. Restore (recovery test)

Restore into a throwaway database:

mysql --host=127.0.0.1 --user=root --password='your_password' \
  -e "CREATE DATABASE app_db_restore"

mysql --host=127.0.0.1 --user=root --password='your_password' \
  app_db_restore < ./db-dumps/app.sql

5. Upload to Lighthouse

Your dump is now in ./db-dumps. After creating an authenticated SDK client with the API key from step 1, upload the directory as a snapshot:

snapshot, err := client.Backup([]string{"./db-dumps"}, &sdktypes.BackupOptions{})
if err != nil {
	log.Fatal(err)
}
log.Printf("snapshotId=%s", snapshot.SnapshotID)

To run this on a schedule, use Automated backup with scheduling.

The dump command for this database, ready to drop into the scheduled job's make_dump():

mysqldump --host=127.0.0.1 --port=3306 --user=root --password="$DB_PASSWORD" \
  --single-transaction --quick --routines --triggers \
  app_db > ./db-dumps/app.sql