MySQL/MariaDB Backup & Restore Guide

Backup

# Single database
mysqldump -u root -p mydb > backup.sql

# All databases
mysqldump -u root -p --all-databases > all_backup.sql

# Compressed
mysqldump -u root -p mydb | gzip > backup.sql.gz

Restore

mysql -u root -p mydb < backup.sql

# From compressed
gunzip < backup.sql.gz | mysql -u root -p mydb

Automated Backup Script

#!/bin/bash
DATE=$(date +%Y%m%d)
mysqldump -u root -pSECRET mydb | gzip > /backups/mydb_$DATE.sql.gz
find /backups -mtime +30 -delete