Skip to content

6. Restoring Snapshots

This guide covers basic restoration of database snapshots and user accounts created by docker-mariadb-snapshot.

Test Restorations First

Always test restoration procedures in a non-production environment before performing them on production systems. Verify snapshot integrity and compatibility with your target server version.

6.1 What Can Be Restored

Snapshots include:

  • Database dumps (.gz files) - Database schemas, tables, data, routines, events, and triggers
  • User accounts (users.sql.gz) - User definitions, passwords, and grant permissions (if DB_SNAPSHOT_USERS_GRANTS was enabled)
  • Metadata (snapshot-metadata.json) - Information about the snapshot including server type, version, and configuration

Note: Structure-only tables (like cache or session tables) are restored with schema only - no data.

6.2 Restoring Database Data

6.2.1 Single Database Restoration

To restore a single database from a snapshot:

# Using local mysql client
gunzip -c /path/to/snapshots/hourly.0/mysql/myapp.gz | mysql -h localhost -u root -p

# Using Docker to restore to a containerized MySQL server
gunzip -c /path/to/snapshots/hourly.0/mysql/myapp.gz | \
  docker exec -i mysql-server mysql -u root -p[password]

6.2.2 Multiple Databases

Restore multiple databases by repeating the process for each .gz file:

# Restore app1, app2, app3
for db in app1 app2 app3; do
  echo "Restoring $db..."
  gunzip -c /path/to/snapshots/hourly.0/mysql/${db}.gz | mysql -h localhost -u root -p
done

6.2.3 Combined Snapshot (ALL_DATABASES.gz)

If you used DB_SNAPSHOT_COMBINED=true, restore all databases from a single file:

gunzip -c /path/to/snapshots/hourly.0/mysql/ALL_DATABASES.gz | mysql -h localhost -u root -p

6.3 Restoring Users and Grants

If DB_SNAPSHOT_USERS_GRANTS was enabled, restore user accounts and permissions:

# Using local mysql client
gunzip -c /path/to/snapshots/hourly.0/mysql/users.sql.gz | mysql -h localhost -u root -p

# Using Docker
gunzip -c /path/to/snapshots/hourly.0/mysql/users.sql.gz | \
  docker exec -i mysql-server mysql -u root -p[password]

Important: User restoration requires administrative privileges on the target server (typically root or equivalent).

6.4 Verification

After restoration, verify the data:

# Check databases exist
mysql -h localhost -u root -p -e "SHOW DATABASES;"

# Verify table row counts
mysql -h localhost -u root -p myapp -e "SELECT COUNT(*) FROM users;"

# Check users and grants
mysql -h localhost -u root -p -e "SELECT user, host FROM mysql.user;"

6.5 Examining Snapshots

To inspect snapshot contents before restoring:

# View SQL without executing
gunzip -c myapp.gz | less

# Check file integrity
gunzip -t myapp.gz && echo "File is valid"

# Review metadata
cat snapshot-metadata.json | jq .

6.6 Important Considerations

Server Compatibility:

  • Check snapshot-metadata.json for source server type and version
  • MariaDB and MySQL have some incompatibilities
  • Major version differences may cause issues

Existing Data:

  • The snapshots use --add-drop-table flag, which drops existing tables before recreating them
  • Back up your target database before restoring if it contains data you want to preserve