README

MySQL/MariaDB backup, restore, and recovery toolkit.

Installation

composer require amitdugar/db-tools

Quick Start

# See all available commands
vendor/bin/db-tools

# Get help for any command
vendor/bin/db-tools backup --help
vendor/bin/db-tools collation --help

Setup

If your project already has a `.env` file (Laravel, Symfony, etc.)

You're done! db-tools automatically reads DB_* variables from your .env file.

# Just works - no additional config needed
vendor/bin/db-tools backup --output-dir=/backups

If your .env doesn't have database variables yet, run setup:

vendor/bin/db-tools setup

This will prompt for credentials and add them to your .env file.

If you're starting fresh

vendor/bin/db-tools setup

Choose where to save config:

.env file (recommended) - adds DB_HOST, DB_DATABASE, etc.
Profile file - saves to ~/.config/db-tools/profiles.php

Verify your setup

# Test database connectivity
vendor/bin/db-tools db:test

# Show current configuration
vendor/bin/db-tools config:show --validate

Commands

Backup & Restore

# Create compressed backup (zstd/pigz/gzip)
vendor/bin/db-tools backup --output-dir=/backups
vendor/bin/db-tools backup --output-dir=/backups --note=before-deploy
vendor/bin/db-tools backup --output-dir=/backups --encrypt          # GPG encrypted
vendor/bin/db-tools backup --output-dir=/backups --compression=zstd # specific compression

# Restore from backup
vendor/bin/db-tools restore /backups/mydb-20240501-0100.sql.zst
vendor/bin/db-tools restore /backups/mydb.sql.zst.gpg              # auto-decrypts

# Import SQL file (supports .sql, .gz, .zst, .zip, .gpg)
vendor/bin/db-tools import /path/to/dump.sql.zst
vendor/bin/db-tools import /path/to/dump.sql.gpg -e secretpassword

# Verify archive integrity
vendor/bin/db-tools verify /backups/mydb-20240501-0100.sql.zst

# Export plain SQL (no compression)
vendor/bin/db-tools export mydb /backups/mydb.sql

Backup Management

# List backup files in directory
vendor/bin/db-tools show --output-dir=/backups

# Show database/table sizes
vendor/bin/db-tools size                           # all databases
vendor/bin/db-tools size mydb                      # specific database
vendor/bin/db-tools size mydb --tables             # show table breakdown

# Clean old backups (keep last N)
vendor/bin/db-tools clean --output-dir=/backups --retention=7

# Clean backups older than N days
vendor/bin/db-tools clean --output-dir=/backups --days=30

Database Maintenance

# Fix collation (convert to utf8mb4)
vendor/bin/db-tools collation mydb                           # convert all tables
vendor/bin/db-tools collation mydb --dry-run                 # preview changes
vendor/bin/db-tools collation mydb --table=users             # specific table only
vendor/bin/db-tools collation mydb --skip-columns            # tables only, skip columns
vendor/bin/db-tools collation mydb --collation=utf8mb4_unicode_ci  # specific collation

# Run mysqlcheck operations
vendor/bin/db-tools mysqlcheck mydb                          # check tables
vendor/bin/db-tools mysqlcheck mydb --analyze                # update index statistics
vendor/bin/db-tools mysqlcheck mydb --optimize               # optimize tables
vendor/bin/db-tools mysqlcheck mydb --repair                 # repair tables

# Test database connectivity
vendor/bin/db-tools db:test

Point-in-Time Recovery

# View available recovery points
vendor/bin/db-tools pitr-info --meta=/backups/mydb.meta.json

# Restore to specific point in time
vendor/bin/db-tools pitr-restore --meta=/backups/mydb.meta.json --to="2024-05-10 12:00:00"

# Purge old binary logs
vendor/bin/db-tools purge-binlogs --before="2024-05-01"

Configuration

# List available profiles
vendor/bin/db-tools config:list

# Show current configuration
vendor/bin/db-tools config:show
vendor/bin/db-tools config:show --validate    # test connection

# Interactive setup
vendor/bin/db-tools setup

# Non-interactive setup (for deploy scripts)
vendor/bin/db-tools setup --no-prompt --database=mydb --user=root --password=secret
vendor/bin/db-tools setup --no-prompt --database=mydb --user=root -o config  # output to db-tools.php

Profiles (Multiple Databases)

If you manage multiple databases, use profiles. Each profile is a named database configuration that you can switch between.

When to use profiles

You have separate databases (e.g., main app + analytics)
You need different backup settings per database
You want to manage multiple projects from one machine

Quick setup with profiles

# Add your main database (uses "default" profile)
vendor/bin/db-tools setup --no-prompt --database=myapp --user=root --password=secret

# Add an analytics database as a separate profile
vendor/bin/db-tools setup --no-prompt --profile=analytics --database=analytics_db --user=root --password=secret

# Add a legacy database
vendor/bin/db-tools setup --no-prompt --profile=legacy --database=old_app --user=root --password=secret

Using profiles

# Commands use "default" profile by default
vendor/bin/db-tools backup --output-dir=/backups

# Specify a profile with --profile
vendor/bin/db-tools backup --profile=analytics --output-dir=/backups
vendor/bin/db-tools restore --profile=legacy /backups/old_app.sql.zst
vendor/bin/db-tools size --profile=analytics

# List all configured profiles
vendor/bin/db-tools config:list

How profiles are stored

In .env file (default) - profiles use prefixed variable names:

# Default profile
DB_HOST=localhost
DB_DATABASE=myapp
DB_USERNAME=root
DB_PASSWORD=secret

# Analytics profile (note the _ANALYTICS_ prefix)
DB_ANALYTICS_HOST=localhost
DB_ANALYTICS_DATABASE=analytics_db
DB_ANALYTICS_USERNAME=root
DB_ANALYTICS_PASSWORD=secret

In db-tools.php - profiles are array keys:

<?php
return [
    'default' => [
        'host' => 'localhost',
        'database' => 'myapp',
        'user' => 'root',
        'password' => 'secret',
    ],
    'analytics' => [
        'host' => 'localhost',
        'database' => 'analytics_db',
        'user' => 'root',
        'password' => 'secret',
    ],
];

Cron Jobs

db-tools is designed for cron. Just cd to your project and run:

# Nightly backup at 2am
0 2 * * * cd /var/www/myapp && vendor/bin/db-tools backup --output-dir=/backups --retention=7

# Weekly full backup with note
0 3 * * 0 cd /var/www/myapp && vendor/bin/db-tools backup --output-dir=/backups --note=weekly

# Daily cleanup - keep last 7 backups
0 4 * * * cd /var/www/myapp && vendor/bin/db-tools clean --output-dir=/backups --retention=7

# Monthly - delete backups older than 30 days
0 5 1 * * cd /var/www/myapp && vendor/bin/db-tools clean --output-dir=/backups --days=30

No environment setup needed in cron - db-tools reads directly from your project's .env file.

Non-Interactive Setup

For deploy scripts and CI/CD pipelines, use --no-prompt mode:

# Basic - adds DB variables to .env file
vendor/bin/db-tools setup --no-prompt --database=mydb --user=root --password=secret

# With all options
vendor/bin/db-tools setup --no-prompt \
  --database=mydb \
  --host=localhost \
  --port=3306 \
  --user=root \
  --password=secret \
  --output-dir=./backups \
  --retention=7

# Output to db-tools.php instead of .env
vendor/bin/db-tools setup --no-prompt --database=mydb --user=root -o config

# Output to user profile (~/.config/db-tools/profiles.php)
vendor/bin/db-tools setup --no-prompt --database=mydb --user=root -o profile

Option	Default	Description
`--no-prompt`		Run without prompts (required)
`-o, --output`	`env`	Output format: `env`, `config`, or `profile`
`-p, --profile`	`default`	Profile name for multiple databases
`--database`		Database name (required)
`--host`	`localhost`	Database host
`--port`	`3306`	Database port
`--user`	`root`	Database user
`--password`		Database password
`--output-dir`	`./backups`	Backup directory
`--retention`	`7`	Retention count

Config Files

Most projects don't need a config file - db-tools reads your .env automatically. Use config files when you need:

Multiple databases or profiles
Custom output_dir, retention, or other options
Non-standard environment variable names

`db-tools.php` (commit to repo)

<?php
// db-tools.php - shared project config
return [
    'default' => [
        'host'       => $_ENV['DB_HOST'] ?? 'localhost',
        'port'       => (int) ($_ENV['DB_PORT'] ?? 3306),
        'database'   => $_ENV['DB_DATABASE'],
        'user'       => $_ENV['DB_USERNAME'],
        'password'   => $_ENV['DB_PASSWORD'],
        'output_dir' => __DIR__ . '/backups',
        'retention'  => 7,
        'compression' => 'zstd',
    ],
];

`db-tools.local.php` (gitignore, local overrides)

<?php
// db-tools.local.php - local dev overrides (add to .gitignore)
return [
    'default' => [
        'host'     => '127.0.0.1',
        'database' => 'myapp_dev',
        'user'     => 'root',
        'password' => 'localpass',
    ],
];

Configuration Reference

Auto-detected environment variables

db-tools automatically reads these from your .env file:

DB_HOST        (or MYSQL_HOST)
DB_PORT        (or MYSQL_PORT)
DB_DATABASE    (or MYSQL_DATABASE)
DB_USERNAME    (or MYSQL_USER)
DB_PASSWORD    (or MYSQL_PASSWORD)

Config file locations (first found wins)

DBTOOLS_CONFIG env var
db-tools.local.php (project root)
db-tools.php (project root)
~/.config/db-tools/profiles.php

Profile options

Option	Type	Description
`host`	string	Database host (default: localhost)
`port`	int	Database port (default: 3306)
`database`	string	Database name
`user`	string	Database user
`password`	string	Database password
`output_dir`	string	Backup directory
`retention`	int	Keep last N backups
`compression`	string	zstd, pigz, gzip, or zip
`encryption_password`	string	Encrypt backups (AES-256)
`label`	string	Filename prefix

Command-line overrides

Any option can be overridden via CLI or environment:

# CLI flags
vendor/bin/db-tools backup --host=otherhost --database=otherdb

# Environment variables (prefix with DBTOOLS_)
DBTOOLS_HOST=otherhost DBTOOLS_DATABASE=otherdb vendor/bin/db-tools backup

MySQL and MariaDB

Both are supported. Two things adapt automatically:

Client binaries. MariaDB 11.0 renamed its client tools (mysql → mariadb, mysqldump → mariadb-dump, and so on) and deprecated the old mysql* symlinks. db-tools looks for the classic name first and falls back to the MariaDB name, so it works on either install without configuration. Override any of them if your tools live somewhere unusual:

DBTOOLS_MYSQL_BIN=/opt/mariadb/bin/mariadb
DBTOOLS_MYSQLDUMP_BIN=/opt/mariadb/bin/mariadb-dump
DBTOOLS_MYSQLBINLOG_BIN=/opt/mariadb/bin/mariadb-binlog
DBTOOLS_MYSQLCHECK_BIN=/opt/mariadb/bin/mariadb-check

Server flavour. db:test reports which server it reached, and the collation command picks its default collation accordingly — see Collation Conversion.

Note: a MySQL 8.4 client cannot authenticate against a MariaDB server at all (they no longer share an auth plugin). Use the MariaDB client for MariaDB servers; the fallback above picks it up automatically when it is installed.

Host option files

When you configure a password, db-tools passes --no-defaults to the client tools so that ~/.my.cnf and /etc/my.cnf cannot override the credentials you gave it.

Without it, a password in an option file silently wins. MySQL ranks option files above environment variables, and MYSQL_PWD is where a password belongs because it stays out of ps. The connection is then attempted with the user you configured and a password you did not, and it is refused.

Configure no password and nothing changes. Option files are still read, which is what socket authentication and operator-maintained ~/.my.cnf setups depend on.

--no-defaults also drops non-credential tuning such as max_allowed_packet and default-character-set. If your option file carries settings you need:

DBTOOLS_ALLOW_HOST_DEFAULTS=1 vendor/bin/db-tools backup

That reads option files as before, credentials included, so use it only where the option file is the intended source.

Encrypted Backups

All compression backends support encryption using GPG with AES-256. There are two ways to encrypt:

Auto-generated password (recommended)

Use --encrypt to automatically generate a secure password. The password is derived from your database password plus a random string embedded in the filename:

# Create encrypted backup (password auto-generated)
vendor/bin/db-tools backup --encrypt --output-dir=/backups

# Output: mydb-20240501-0100-x7Kp9mQrAbCd1234XyZ.sql.zst.gpg
#                            └──────────────────┘
#                            32-char random string

# Restore - password is derived from DB_PASSWORD + random string from filename
vendor/bin/db-tools restore /backups/mydb-20240501-0100-x7Kp9mQrAbCd1234XyZ.sql.zst.gpg

How it works: The encryption password is DB_PASSWORD + randomString. Since the random string is embedded in the filename, restore automatically derives the correct password when you provide your database credentials.

Manual password

Use --encryption-password to specify your own password:

# Create encrypted backup with custom password
vendor/bin/db-tools backup --encryption-password=secret --output-dir=/backups

# Restore encrypted backup
vendor/bin/db-tools restore /backups/mydb.sql.zst.gpg --encryption-password=secret

# Verify encrypted backup
vendor/bin/db-tools verify /backups/mydb.sql.zst.gpg --password=secret

Store the password securely (e.g., in environment variable):

// db-tools.php
return [
    'default' => [
        // ...
        'encryption_password' => getenv('BACKUP_ENCRYPTION_KEY'),
    ],
];

How encryption works

ZIP (--compression=zip): Native ZIP encryption (AES-256)
Other backends (zstd, gzip, pigz): File is compressed, then encrypted with GPG symmetric AES-256 → .sql.zst.gpg

Collation Conversion

The collation command converts database tables and columns to utf8mb4, picking the default collation from the detected server flavour and version:

MySQL 8+: Uses utf8mb4_0900_ai_ci
MySQL 5.x: Uses utf8mb4_unicode_ci
MariaDB (all versions): Uses utf8mb4_unicode_ci

MariaDB is detected from VERSION() rather than inferred from the version number, because MariaDB 10.x and 11.x both compare greater than 8.0 while only MariaDB 11.4+ recognises utf8mb4_0900_ai_ci — on 10.5/10.6/10.11 it fails with ERROR 1273 Unknown collation. MariaDB's own utf8mb4_uca1400_ai_ci (10.10+) is not selected automatically, since adopting it changes sort order; pass --collation utf8mb4_uca1400_ai_ci if you want it.

# Preview what would be converted (no changes)
vendor/bin/db-tools collation mydb --dry-run

# Convert all tables and columns
vendor/bin/db-tools collation mydb

# Convert specific table only
vendor/bin/db-tools collation mydb --table=users

# Convert tables only, skip individual column conversion
vendor/bin/db-tools collation mydb --skip-columns

# Specify custom collation
vendor/bin/db-tools collation mydb --collation=utf8mb4_general_ci

# Verbose output (shows column-level details)
vendor/bin/db-tools collation mydb -v

The command preserves all column attributes: NULL/NOT NULL, DEFAULT values, AUTO_INCREMENT, COMMENT, etc.

Import Formats

The import command handles various file formats:

Extension	Description
`.sql`	Plain SQL file
`.sql.gz`	Gzip compressed
`.sql.zst`	Zstandard compressed
`.zip`	ZIP archive (with optional password)
`.sql.gpg`	GPG encrypted
`.sql.zst.gpg`	Compressed + encrypted

# Import plain SQL
vendor/bin/db-tools import /path/to/dump.sql

# Import compressed
vendor/bin/db-tools import /path/to/dump.sql.zst

# Import encrypted (auto-derives password from filename if possible)
vendor/bin/db-tools import /path/to/dump.sql.gpg

# Import encrypted with explicit password
vendor/bin/db-tools import /path/to/dump.sql.gpg -e mypassword

Requirements

PHP 8.4+ (tested on 8.4 and 8.5)
mysqldump, mysql CLI tools
Compression: zstd (preferred), pigz, or gzip
Encryption: gpg (for non-ZIP encrypted backups)

License

MIT

amitdugar / db-tools

Maintainers

Package info

Statistics

Security