Set the strategy first: logical vs physical backup, when to choose which
mysqldump is a logical backup: it exports SQL, which is portable across versions and platforms and restores partially, but a full restore is slower than a physical backup (xtrabackup tearing real files). For small databases and triage scenarios logical is enough; online databases tens of gigabytes and larger usually need a physical snapshot.
Below is the complete logical chain, assuming you know the schema has no conflicting stored procedures or pass --routines explicitly. The golden rule of backup is: a daily full dump plus binlog archiving so the restore point resolves down to the minute.
# Full single-database dump including events/triggers/stored procedures
mysqldump -u root -p --single-transaction --routines --events \
--databases me_db > /backup/me_db.$(date +%F).sql
# Verify the dump is non-empty and has a header
grep -c "CREATE TABLE" /backup/me_db.$(date +%F).sql
head -5 /backup/me_db.$(date +%F).sqlConsistency is the point: --single-transaction and the MyISAM trade-off
By default mysqldump locks tables; under InnoDB for a lock-free consistent snapshot pass --single-transaction: it exports every table inside one REPEATABLE READ transaction so you see one consistent point in time. That relies on InnoDB MVCC and does not apply to MyISAM, which still needs --lock-tables.
Beware mixed engines: --single-transaction guarantees only InnoDB consistency, MyISAM tables still get briefly locked. If your tables are all InnoDB (the recommended state), export with single-transaction confidently and add --master-data=2 to record the binlog position for later point-restore.
Recommended for InnoDB
mysqldump -u root -p --single-transaction --master-data=2 \
--routines me_db > me_db.$(date +%F).sql
# Use locks at minimum when engines are mixed
mysqldump -u root -p --lock-tables --routines me_db > me_db_lock.sql
# Check the recorded binlog position
grep -m1 "CHANGE MASTER" me_db.$(date +%F).sqlRecovery step one: pre-import discipline and acceleration flags
Before importing do three things: ensure the target database exists (CREATE DATABASE if missing), disable foreign key checks, and disable autocommit. For InnoDB also consider temporarily enlarging the buffer pool so the huge in-batch index work speeds up.
Import with mysql < dump.sql rather than splitting into thousands of single inserts. A flood of one-row INSERTs is the number-one cause of slow restores; export side merges rows via the --opt defaults, and the restore side gains a lot from innodb_flush_log_at_trx_commit=0 plus autocommit off.
# Anti-pattern: one commit per row, unbearably slow
# mysql me_db < dump.sql # No foreign-key disable, no buffer pool
# Fix: prepare the workspace, then import
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS me_db"
mysql -u root -p me_db < dump.sql && echo "seated OK" || echo "import returned non-zero"
# For speed: temporarily reduce flush strength
mysql -u root -p -e "SET GLOBAL innodb_flush_log_at_trx_commit=0; SET autocommit=0;"Recover from accidental deletes: full dump plus binlog back to the moment before
Accidental deletes (an UPDATE missing a WHERE, a DELETE without conditions) are the most painful operational accidents, remedied by full-plus-binlog replay: import the full dump into a staging database, then replay the binlog with --stop-position or --stop-datetime until the transaction just before the mistake, cutting at the right moment.
The precondition is binlog enabled (log_bin=ON) with row or mixed format. The rhythm: locate the offending event coordinates in the binlog (mysqlbinlog to find that DEL/UPDATE), set the stop position, import into a staging db to verify, then promote back to production. Full flow below.
# 1) Locate the accidental delete event
mysqlbinlog --no-defaults --skip-opt --base64-output=decode-rows \
/var/lib/mysql/bin.000044 | grep -n "DELETE FROM" | head
# 2) Replay up to just before the target moment
mysqlbinlog --no-defaults --stop-datetime="2026-09-06 10:15:00" \
/var/lib/mysql/bin.000044 | mysql -u root -p stage_db
# 3) Verify the row count, then switch back
mysql -u root -p -e "SELECT COUNT(*) FROM stage_db.orders"
# Once confirmed, import into the real table in a single transaction:
mysql -u root -p me_db < stage_clean.sqlMistake vs fix: restore "succeeded" yet recovered nothing
It is common for an import script to report success while the target table is empty or missing rows. Three root causes usually: importing the wrong (stale) dump, the target schema already holds same-named tables so the dump without DROP TABLE jumps straight over them, or FK ordering interrupting at a child table that should be inserted first.
Turn "success" into "verifiable success": after import, replay the dump against a fresh database and cross-check row counts, or run the dump with --force once to surface alerts. A robust check compares row counts between two schemas—script and the fix essentials below.
# Anti-pattern: default output auto-DROPs before import, may skip if target tables exist
# Fix: add --add-drop-table to force a rebuild, or clear the old database first
mysqldump --add-drop-table me_db > me_db_clean.sql
# Reconcile by row count after restore
mysql -u root -p -N -e "SELECT (SELECT COUNT(*) FROM src.orders) AS s, (SELECT COUNT(*) FROM stage.orders) AS t"
# Find tables missing from the database
mysql -u root -p -N -e "SELECT table_name FROM information_schema.tables WHERE table_schema='stage'"