Linux Tips

How to Back Up a MySQL Database Without Downtime

- by John Britto - Leave a Comment - 2 min read
Quick Linux Tip #60:

Question: How do I dump a MySQL database while it's serving traffic?

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 rows instead of loading the entire result into memory, while --routines, --triggers, and --events include stored procedures, triggers, and scheduled events in the backup.

Examples:

  • $ mysqldump --single-transaction db | gzip | ssh backup 'cat > /backups/db.sql.gz'  # Stream directly to a remote backup server
  • $ pg_dump -Fc production | gzip > backup.pgdump.gz  # PostgreSQL database dump
  • $ mongodump --uri mongodb://localhost --archive=backup.gz --gzip  # Compressed MongoDB export

Note: --single-transaction provides a consistent dump for transactional InnoDB tables without holding table locks for the duration of the dump. Non-transactional tables such as MyISAM require different locking considerations. Test your backup and restore process regularly to make sure the dump can actually be recovered when needed.




LinuxTeck.com 
linuxteck@ubuntu:~$ mysqldump --single-transaction --quick --lock-tables=false --routines --triggers --events -u backup -p production_db | gzip > backup_$(date +%Y%m%d).sql.gz
Enter password:

linuxteck@ubuntu:~$ ls -la backup_20260822.sql.gz
-rw-r--r-- 1 linuxteck linuxteck 234567890 Aug 22 10:34 backup_20260822.sql.gz

linuxteck@ubuntu:~$ zcat backup_20260822.sql.gz | head -20
-- MySQL dump 10.13  Distrib 8.0.35, for Linux (x86_64)
--
-- Host: localhost    Database: production_db
--
-- Server version        8.0.35
--
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

linuxteck@ubuntu:~$

PREVIOUS ARTICLE Quick Linux Tip #59: How to Check File Access Time with stat
About John Britto

John Britto Founder & Chief-Editor @LinuxTeck. A Computer Geek and Linux Intellectual having more than 20+ years of experience in Linux and Open Source technologies.

View all posts by John Britto →

Leave a Reply

Your email address will not be published.