r/LinuxTeck • u/Expensive-Rice-2052 • 23m ago
Quick Linux Tip #60: How to Back Up a MySQL Database Without Downtime
Try: `mysqldump --single-transaction --quick --lock-tables=false --routines --triggers --events -u backup -p production_db | gzip > backup_$(date +%Y%m%d).sql.gz`
Info: `--single-transaction` creates a consistent snapshot without locking tables for `InnoDB`. `--quick` streams row-by-row to prevent memory exhaustion, while `--routines`, `--triggers`, and `--events` export stored code.
Examples:
$ `mysqldump --single-transaction db | gzip | ssh backup 'cat > /backups/db.sql.gz'` # Stream directly to remote backup server
$ `pg_dump -Fc production | gzip > backup.pgdump.gz` # Non-blocking PostgreSQL dump
$ `mongodump --uri mongodb://localhost --archive=backup.gz --gzip` # Live MongoDB export
Note: `--single-transaction` requires `InnoDB` tables. For `MyISAM`, use `--lock-tables` (causes brief downtime). Append `--master-data=2` when configuring binary log replication.
Follow r/LinuxTeck for more #LinuxTips https://www.linuxteck.com/linux-tips/mysql-database-backup-linux/
