How to create MySQL or MariaDB database backup

A recent schema change, an accidental delete, or a disk failure becomes recoverable when a database backup exists. Regular dumps reduce downtime by providing a known-good restore point for incidents and upgrades.

Logical backups for MySQL and MariaDB are commonly produced with mysqldump, which connects to the server and writes SQL statements for schema and data. Streaming the output through gzip compresses the dump during creation and results in a single .sql.gz file that is easy to move and archive.

Consistency and privileges affect the result: InnoDB tables can be dumped as a point-in-time snapshot with --single-transaction, while non-transactional tables may require locks or a quiet write window. Backup files can contain sensitive data, so restrictive filesystem permissions and a quick integrity check help prevent leaks and surprises during restore.

1. Create a directory for backups.

   $ mkdir -p "$HOME/backups/mysql"

2. Restrict the backup directory permissions.

   $ chmod 700 "$HOME/backups/mysql"

   A private directory such as /$HOME/backups/mysql prevents other local users from reading backup files even when file permissions are permissive.

3. Generate a timestamp string for the backup filename.

   $ date +%F_%H%M%S
   2025-12-12_223015

   Reuse the timestamp output in the filenames below to keep backups sortable.

4. Create a compressed dump of a single database.

   $ mysqldump --user=backup --password --single-transaction --quick --routines --events --triggers --databases example_db | gzip --stdout > "$HOME/backups/mysql/example_db-2025-12-12_223015.sql.gz"
   Enter password:

   Avoid putting passwords on the command line with --password=secret or -psecret, since process listings and shell history can expose them.

   --single-transaction produces a consistent snapshot for InnoDB without locking tables, and --quick streams rows to reduce memory usage.

   If a MySQL 8 client errors with Unknown table 'COLUMN_STATISTICS' against an older server, add --column-statistics=0 to the mysqldump command.

5. Create a compressed dump of a single table.

   $ mysqldump --user=backup --password --single-transaction --quick example_db example_table | gzip --stdout > "$HOME/backups/mysql/example_db.example_table-2025-12-12_223015.sql.gz"
   Enter password:

   Table-level dumps typically omit CREATE DATABASE statements; use the target database explicitly during restore.

6. Create a single compressed file containing multiple databases.

   $ mysqldump --user=backup --password --single-transaction --quick --routines --events --triggers --databases db1 db2 | gzip --stdout > "$HOME/backups/mysql/multiple-databases-2025-12-12_223015.sql.gz"
   Enter password:

   --databases inserts CREATE DATABASE and USE statements for each named database.

7. Create a full instance backup containing all databases.

   $ mysqldump --user=backup --password --single-transaction --quick --routines --events --triggers --all-databases | gzip --stdout > "$HOME/backups/mysql/all-databases-2025-12-12_223015.sql.gz"
   Enter password:

   --all-databases includes system schemas and user accounts, so treat the resulting .sql.gz as highly sensitive.

8. List the backup directory to confirm the dump file exists.

   $ ls -lh "$HOME/backups/mysql"
   total 62M
   -rw-r--r-- 1 user user  12M Dec 12 22:30 example_db-2025-12-12_223015.sql.gz
   -rw-r--r-- 1 user user 1.1M Dec 12 22:31 example_db.example_table-2025-12-12_223015.sql.gz
   -rw-r--r-- 1 user user  49M Dec 12 22:32 all-databases-2025-12-12_223015.sql.gz

9. Test the compressed dump for corruption.

   $ gzip --test "$HOME/backups/mysql/example_db-2025-12-12_223015.sql.gz"

   No output indicates the gzip stream is valid, and a non-zero exit status indicates a truncated or corrupt backup.

10. Preview the start of the dump to confirm expected content.

    $ gzip --decompress --stdout "$HOME/backups/mysql/example_db-2025-12-12_223015.sql.gz" | head -n 20
    -- MySQL dump 10.13  Distrib 8.0.36, for Linux (x86_64)
    --
    -- Host: localhost    Database: example_db
    -- ------------------------------------------------------
    -- Server version       8.0.36
     
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
     
    CREATE DATABASE IF NOT EXISTS `example_db`;
    USE `example_db`;
    ##### snipped #####