mysqldumpBack up MySQL databases as SQL
mysqldump connects to a MySQL or MariaDB server and writes a database out as SQL statements (CREATE TABLE, INSERT) that rebuild it when replayed with the mysql client. It's the simplest logical backup, perfect for small and medium databases, migrations and copies to staging.
mysqldump [OPTIONS] DB [TABLE...] | --databases DB... | --all-databasesBefore a risky migration you take a quick copy of the shop database on an Ubuntu server.
mysqldumpThe MySQL client program that reads schema and rows and prints them as SQL.
mysqldump is an ordinary client. For each table it runs SHOW CREATE TABLE and then SELECT * FROM the table, streaming rows one by one (--quick, part of the default --opt) and printing them as multi-row INSERT statements. Consistency is the part to understand. By default it runs LOCK TABLES ... READ for each database, so writes block for the whole dump. --single-transaction instead sets REPEATABLE READ and runs START TRANSACTION WITH CONSISTENT SNAPSHOT: InnoDB's MVCC then shows the dump the data as of that instant while the application keeps writing. That only works for transactional tables (not MyISAM), and DDL such as ALTER TABLE during the dump breaks it. --source-data briefly takes FLUSH TABLES WITH READ LOCK to record the binary log position, which is what you need for point-in-time recovery. Restoring means replaying all the SQL on one thread and rebuilding every index, so a 200 GB database can take many hours; physical tools (Percona XtraBackup, MySQL Shell dump utilities, cloud snapshots) are used at that size.
--single-transactionConsistent InnoDB snapshot without blocking writes--routines / -RInclude stored procedures and functions--events / -EInclude event scheduler events--databases / -BDump several databases, with CREATE DATABASE and USE--all-databases / -AEvery database, including the mysql system schema--no-data / -dSchema only--no-create-info / -tData only--where='cond'Dump only matching rows--source-data=2Record the binlog file and position as a comment (PITR)--no-tablespacesSkip tablespace info; avoids needing the PROCESS privilege--set-gtid-purged=OFFDon't write GTID_PURGED (when loading into a server with its own GTIDs)--defaults-extra-file=FRead user and password from a protected option file--skip-extended-insertOne INSERT per row: bigger, but diff-friendlymysqldump without --single-transaction on a busy InnoDB databaseThe default locks tables for reading, so every write waits until the dump finishes.
mysqldump --single-transaction --routines --events dbmysqldump -u backup -pSecret123 in a cron lineThe password is visible in ps output, process accounting and crontab backups.
--defaults-extra-file=~/.backup.cnf (mode 600)mysqldump db | gzip > file in a script without pipefailThe pipeline reports gzip's success even when mysqldump failed, leaving a tiny useless file.
set -o pipefail, check the exit code and the file sizeRelying on mysqldump for a 500 GB databaseThe dump takes hours and the restore takes far longer, which blows any recovery time objective.
Physical backups (XtraBackup, MySQL Shell dumpInstance with parallel load) or volume/RDS snapshots plus binlogsTry these on a Linux machine of your own. Each task has a hint and a solution.
Write the command that dumps only the schema (no rows) of database 'shop' into schema.sql. (The sandbox has no MySQL server.)
Write a cron-safe backup line that dumps 'shop' consistently with routines, compresses it with a date in the name, reads credentials from ~/.backup.cnf, and fails if mysqldump fails.
Write the commands to restore /backup/shop-2026-10-28.sql.gz into a new database called shop_copy.
Create an option file for credentials in your home directory with the right permissions.
mysqldumpBack up MySQL databases as SQL
mysqldump connects to a MySQL or MariaDB server and writes a database out as SQL statements (CREATE TABLE, INSERT) that rebuild it when replayed with the mysql client. It's the simplest logical backup, perfect for small and medium databases, migrations and copies to staging.
mysqldump [OPTIONS] DB [TABLE...] | --databases DB... | --all-databasesBefore a risky migration you take a quick copy of the shop database on an Ubuntu server.
mysqldumpThe MySQL client program that reads schema and rows and prints them as SQL.
mysqldump is an ordinary client. For each table it runs SHOW CREATE TABLE and then SELECT * FROM the table, streaming rows one by one (--quick, part of the default --opt) and printing them as multi-row INSERT statements. Consistency is the part to understand. By default it runs LOCK TABLES ... READ for each database, so writes block for the whole dump. --single-transaction instead sets REPEATABLE READ and runs START TRANSACTION WITH CONSISTENT SNAPSHOT: InnoDB's MVCC then shows the dump the data as of that instant while the application keeps writing. That only works for transactional tables (not MyISAM), and DDL such as ALTER TABLE during the dump breaks it. --source-data briefly takes FLUSH TABLES WITH READ LOCK to record the binary log position, which is what you need for point-in-time recovery. Restoring means replaying all the SQL on one thread and rebuilding every index, so a 200 GB database can take many hours; physical tools (Percona XtraBackup, MySQL Shell dump utilities, cloud snapshots) are used at that size.
--single-transactionConsistent InnoDB snapshot without blocking writes--routines / -RInclude stored procedures and functions--events / -EInclude event scheduler events--databases / -BDump several databases, with CREATE DATABASE and USE--all-databases / -AEvery database, including the mysql system schema--no-data / -dSchema only--no-create-info / -tData only--where='cond'Dump only matching rows--source-data=2Record the binlog file and position as a comment (PITR)--no-tablespacesSkip tablespace info; avoids needing the PROCESS privilege--set-gtid-purged=OFFDon't write GTID_PURGED (when loading into a server with its own GTIDs)--defaults-extra-file=FRead user and password from a protected option file--skip-extended-insertOne INSERT per row: bigger, but diff-friendlymysqldump without --single-transaction on a busy InnoDB databaseThe default locks tables for reading, so every write waits until the dump finishes.
mysqldump --single-transaction --routines --events dbmysqldump -u backup -pSecret123 in a cron lineThe password is visible in ps output, process accounting and crontab backups.
--defaults-extra-file=~/.backup.cnf (mode 600)mysqldump db | gzip > file in a script without pipefailThe pipeline reports gzip's success even when mysqldump failed, leaving a tiny useless file.
set -o pipefail, check the exit code and the file sizeRelying on mysqldump for a 500 GB databaseThe dump takes hours and the restore takes far longer, which blows any recovery time objective.
Physical backups (XtraBackup, MySQL Shell dumpInstance with parallel load) or volume/RDS snapshots plus binlogsTry these on a Linux machine of your own. Each task has a hint and a solution.
Write the command that dumps only the schema (no rows) of database 'shop' into schema.sql. (The sandbox has no MySQL server.)
Write a cron-safe backup line that dumps 'shop' consistently with routines, compresses it with a date in the name, reads credentials from ~/.backup.cnf, and fails if mysqldump fails.
Write the commands to restore /backup/shop-2026-10-28.sql.gz into a new database called shop_copy.
Create an option file for credentials in your home directory with the right permissions.