FreeBSD-friendly Makefile for mysql

Mekefie_mysql.mk

# --- Configuration Constants ---
DB_HOST ?= mysql.example.com
DB_NAME ?= dbname
DB_USER ?= dbuser
BACKUP_DIR ?= ./backups
DATE := $(shell date +%Y-%m-%d_%H-%M-%S)

# Restore settings
# Usage: make restore RESTORE_FILE=./backups/xxx.sql.gz
RESTORE_FILE ?=
MYSQL := mysql -h $(DB_HOST) -u $(DB_USER) -p $(DB_NAME)

.DEFAULT_GOAL := help
.PHONY: help mysql backup restore restore-latest

help: ## Show this help message
	@echo "Usage: make [target]"
	@echo ""
	@echo "Targets:"
	@echo "  help            Show this help message"
	@echo "  mysql           Connect to $(DB_NAME) on $(DB_HOST)"
	@echo "  backup          Safely dump $(DB_NAME) to $(BACKUP_DIR)"
	@echo "  restore         Restore from a .sql.gz (set RESTORE_FILE=...)"
	@echo "  restore-latest  Restore from the newest dump in $(BACKUP_DIR)"

mysql: ## Connect to the MySQL database
	mysql -h $(DB_HOST) -u $(DB_USER) -p $(DB_NAME)

backup: ## Safe backup bypassing tablespace privilege requirements
	@mkdir -p $(BACKUP_DIR)
	mysqldump -h $(DB_HOST) -u $(DB_USER) -p \
		--single-transaction \
		--quick \
		--lock-tables=false \
		--no-tablespaces \
		$(DB_NAME) | gzip > $(BACKUP_DIR)/$(DB_NAME)_$(DATE).sql.gz
	@echo "Backup saved to $(BACKUP_DIR)/$(DB_NAME)_$(DATE).sql.gz"

restore: ## Restore from a .sql.gz (set RESTORE_FILE=...)
	@if [ -z "$(RESTORE_FILE)" ]; then \
		echo "ERROR: RESTORE_FILE is required."; \
		echo "Example: make restore RESTORE_FILE=$(BACKUP_DIR)/$(DB_NAME)_YYYY-MM-DD_HH-MM-SS.sql.gz"; \
		exit 2; \
	fi
	@if [ ! -f "$(RESTORE_FILE)" ]; then \
		echo "ERROR: File not found: $(RESTORE_FILE)"; \
		exit 2; \
	fi
	@echo "Restoring into $(DB_NAME) on $(DB_HOST) from: $(RESTORE_FILE)"
	@gzip -dc "$(RESTORE_FILE)" | $(MYSQL)
	@echo "Restore completed."

restore-latest: ## Restore from the newest dump in $(BACKUP_DIR)
	@latest="$$(ls -1t "$(BACKUP_DIR)"/$(DB_NAME)_*.sql.gz 2>/dev/null | head -n 1)"; \
	if [ -z "$$latest" ]; then \
		echo "ERROR: No dumps found in $(BACKUP_DIR) for $(DB_NAME)."; \
		exit 2; \
	fi; \
	$(MAKE) restore RESTORE_FILE="$$latest"