Skip to main content

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
FlagWhy
--single-transactionConsistent snapshot of InnoDB tables without locking writers.
--quickStreams rows instead of buffering large tables in memory.
--routinesInclude stored procedures and functions.
--triggersInclude triggers (included by default, shown for clarity).
app_dbThe 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. Construct the SDK client with the API key from step 1 and upload the directory as a snapshot. This reads LH_API_KEY and LH_WORKSPACE_ID from the environment (exported in step 1):

package main

import (
	"log"
	"os"

	sdkclient "github.com/lighthouse-web3/baas-go-sdk/client"
	sdktypes "github.com/lighthouse-web3/baas-go-sdk/types"
)

func main() {
	client, err := sdkclient.NewBackupClient(sdkclient.BackupClientOptions{
		APIURL:      "https://baas-api.lighthouse.storage", // API host, not the portal
		APIKey:      os.Getenv("LH_API_KEY"),
		WorkspaceID: os.Getenv("LH_WORKSPACE_ID"),
	})
	if err != nil {
		log.Fatalf("client init: %v", err)
	}

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

Run it from the directory containing db-dumps:

go run .

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