巧匠Developer Cheatsheet
Databases

MySQL Logical Backup and Restore: mysqldump, Faster Imports, and Recovering from Accidental Deletes

Exporting with mysqldump is easy; the hard parts are whether the dump restores cleanly, whether the restores fast enough, and whether an accidental delete comes back. This guide covers consistency flags, single-transaction isolation, import-acceleration knobs, and a binlog-based recovery flow.

By LaoHand Team·8 min read·Updated 2026-09-06

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.

# 单库全量并带 事件/触发器/存储过程
mysqldump -u root -p --single-transaction --routines --events \
  --databases me_db > /backup/me_db.$(date +%F).sql
# 校验导出非空且有 HEADER
grep -c "CREATE TABLE" /backup/me_db.$(date +%F).sql
head -5 /backup/me_db.$(date +%F).sql

Consistency 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.

InnoDB 推荐
mysqldump -u root -p --single-transaction --master-data=2 \
  --routines me_db > me_db.$(date +%F).sql
# 混用引擎时至少带上锁
mysqldump -u root -p --lock-tables --routines me_db > me_db_lock.sql
# 看记录下的 binlog 位点
grep -m1 "CHANGE MASTER" me_db.$(date +%F).sql

Recovery 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.

# 错误示范：每行一条提交，慢到怀疑人生
# mysql me_db < dump.sql          # 无外键关闭也无缓存池

# 修复对照：先做工作区再导
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"
# 加速用：临时降低 flush 强度
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) 找到误删事件
mysqlbinlog --no-defaults --skip-opt --base64-output=decode-rows \
  /var/lib/mysql/bin.000044 | grep -n "DELETE FROM" | head
# 2) 重放到指定时刻之前
mysqlbinlog --no-defaults --stop-datetime="2026-09-06 10:15:00" \
  /var/lib/mysql/bin.000044 | mysql -u root -p stage_db
# 3) 核对 row count 后切回
mysql -u root -p -e "SELECT COUNT(*) FROM stage_db.orders"
# 确认无误再用一片事务导入真正表：
mysql -u root -p me_db < stage_clean.sql

Mistake 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.

# 错误示范：默认产出会在导入前自动 DROP，若目标已有表则可能跳过

# 修复对照：追加 --add-drop-table 保证重建，或先清空旧库
mysqldump --add-drop-table me_db > me_db_clean.sql
# 还原后用行数对账
mysql -u root -p -N -e "SELECT (SELECT COUNT(*) FROM src.orders) AS s, (SELECT COUNT(*) FROM stage.orders) AS t"
# 找出库里缺失的表
mysql -u root -p -N -e "SELECT table_name FROM information_schema.tables WHERE table_schema='stage'"

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

More guides

Git Accident Recovery: Fix Wrong Commits, Wrong Branches and Deleted BranchesThe scariest moment in daily work is "oh no, I messed up a commit or branch". This guide gives copy-ready recovery commands for real accident scenarios, with clear safety boundaries for each.Docker Image Slimming: 5 Steps from 1.2GB to 120MBBigger images mean slower pulls, larger attack surface and higher storage cost. This guide gives 5 immediately applicable slimming steps, each with a reusable Dockerfile snippet and a verification command.Nginx 502 Bad Gateway? Locate It in 5 StepsA 502 means Nginx itself is fine but got an invalid response from the upstream (backend/proxy). This guide gives the shortest path: process → config → timeout → resources → logs.Linux Disk Full: Locate the Real Culprit with df, du and lsofA "No space left on device" error does not mean you should delete files at random. This guide walks through the full path — filesystem water level, directory drill-down, and "deleted but still held" files — plus a safe cleanup order that will not destroy data.Linux CPU at 100%: the Shortest Path from top to the Thread Stack"Restart fixes everything" is the most expensive option when CPU is pegged. This guide gives a reusable path: classify the load, pin the thread, capture what it is executing, then act — with dedicated tools for Java, Python and Go.MySQL Slow Query Tuning: Slow Log, EXPLAIN and Indexing"The database is slow" usually means a handful of slow SQL statements. This guide offers a repeatable flow: use the slow log to circle the most expensive queries, read the plan with EXPLAIN, decide whether an index pays off, and spot the most common index-defeating patterns.Redis Slowdown Playbook: Big Keys, Blocking Commands and EvictionRedis processes commands on a single thread, so one blocking hotspot slows the whole instance. This guide follows the order "memory level → big keys → blocking commands → eviction and persistence", each step with copy-ready commands.Kubernetes CrashLoopBackOff: Diagnose a Restart-Looping Pod in 5 StepsCrashLoopBackOff is not one error but a state: the container dies on start, and Kubernetes retries with backoff. This guide gives the shortest path — exit code → previous logs → probes → resources → dependencies.SSH Passwordless Login: from ssh-keygen to Permission deniedPasswordless SSH boils down to one sentence: put the public key into the remote authorized_keys. What blocks you is usually the three hidden gates — file permissions, the home directory, and SELinux. This guide covers the full setup and a step-by-step debugging path.Python Virtual Environments in Practice: venv, Dependency Pinning and Cross-Platform Gotchas"Works on my machine" usually means mixed environments. This guide builds a clean flow with the stdlib venv: create, activate, pin and reproduce — plus the three frequent pitfalls: Windows activation, version drift and cross-platform dependencies.VSCode Remote Development: Remote-SSH Setup and Connection DebuggingA weak laptop, code on an intranet server, GPUs in the cluster — Remote-SSH keeps the UI local while code and terminal run remotely, feeling native. This guide covers setup, passwordless auth, common connection failures and experience tuning.Bash Script Safety: set -euo pipefail and trap for Trustworthy ScriptsA Bash script that "finishes" is not a script that "worked": failed commands do not stop it, empty variables pass silently, and pipes hide upstream crashes. This guide fixes all of them with a copy-ready skeleton (set -euo pipefail + trap + quoting discipline).YAML Gotchas: Type Coercion, Indentation and Multiline StringsYAML files that "look right but run wrong" usually fail in three places: silently coerced scalars, misplaced indentation, and ambiguous multiline strings. This guide dissects each trap with detection methods and a toolchain.HTTP Caching in Practice: Configuring Cache-Control and ETag CorrectlyMisconfigured caching ends one of two ways: users never see updates, or origin traffic explodes. This guide covers the split between freshness and validation caching, the hashed-asset vs HTML strategy divide, plus ready-to-use Nginx config and verification commands.Git Merge and Rebase Conflict Resolution in PracticeFor developers branching daily, this guide reads merge conflict markers end to end with reproducible cases and shows how to handle merge, rebase, and rename conflicts.Bisecting: Pinpoint the Commit That Introduced a BugFor anyone tired of hunting commits by gut feel, this is the binary-search way to find the commit that broke something, including making the check scriptable so bisect runs hands-free.Git Submodule Management and Its Common TrapsA field guide to when a submodule actually makes sense, how to add, clone, and update it, and the standard escape hatches for empty dirs after clone, branch drift, and nesting.Parallel Development with git worktree: Multiple Branches at OnceFor devs juggling several branches who fear stash chaos while switching, here is how to keep one clean worktree per task and never stash-swap again.docker compose Multi-Service Integration and Startup TroubleshootingGetting to the bottom of ordering, environment variables, port collisions, and service-name DNS inside the compose network, with copyable yaml and debugging commands.Kubernetes HPA: Configuring and Verifying AutoscalingFor engineers comfortable with Deployments but new to autoscaling: from installing metrics-server, writing an HPA, to load-testing and actually watching replicas scale out.Dockerfile Security Hardening: Shrinking the Attack SurfaceA landing checklist for image maintainers and service containerizers: non-root user, read-only root FS, dropping capabilities, layer hygiene, and image scanning.JavaScript Error Handling: From try/catch to Global Fallbacks and RetryA practical catalog for backend and full-stack JS: catching sync and async errors, Promise unhandledrejection, global fallbacks, and exponential-backoff retries.Python Packaging and Dependency Locking: From pyproject.toml to PyPIFor turning scripts into publishable packages: pyproject.toml for metadata, venv isolation, lock files for reproducible environments, and releasing to PyPI with twine.Linux Memory Exhaustion and OOM Hunting: From dmesg to the Process Likely Out of ControlWhen a server claims it is out of memory it rarely tells the whole truth; this guide shows how to use free, /proc/meminfo, cgroups, and dmesg to close in on the process actually eating memory, and how to configure against recurring OOM kills.Linux Network Troubleshooting Field Manual: Connectivity, Packet Loss, Latency, and DNSFrom unreachable pings to crawling slowness and flaky domain resolution, this guide walks layer by layer—link, IP, transport, application—showing which command and which field to look at, with copy-run experiments along the way.Debugging systemd Service Startup Failures: From status and Dependencies to Ordering Deadlockssystemctl is-failed is just the first step; this guide splits failures into four kinds—launcher error, process instantly exiting, dependency not ready, and port never listening—with the right log entry and fix for each.Linux File Permissions and sudo in Practice: chmod/ACL/setuid and Privilege BoundariesFrom bit permissions to ACLs to why setuid exists, this guide clarifies exactly how read/write/execute are computed, when the special bits backfire, and why sudo should not be a blanket ALL—plus a safer habit before you break something.Nginx Reverse Proxy in Practice: location Priority, proxy_pass, and Upstream Balancing Done RightMisconfigured proxies usually come down to location matching order and trailing-slash traps; this guide corrects the most commonly botched configs line by line and walks you through wiring a real Node backend into a load-balanced reverse proxy.HTTPS Certificates and Let's Encrypt Auto-Renewal: From Issuance to a cron FallbackManually requesting a cert is easy; the hard part is renewing it before expiry automatically. This guide walks certbot through webroot and HTTP-01 validation, configures renewal on port 80, and pairs it with two fallbacks so missing a renewal still does not take the site down.Debugging REST APIs End-to-End with curl: Methods, Headers, Auth, Upload, and Diagnosiscurl is the first scene of most API debugging; this guide starts from "see exactly what a request sent", covering GET/POST, query vs body, Bearer/JWT auth, Multipart uploads, and reading status code plus timing from one response.PostgreSQL Slow-Query Diagnosis and Tuning: From EXPLAIN to shared_buffersSlow query tuning is a three-step ladder: find the query, read the plan, tune the config. This guide covers enabling the slow log, telling Seq Scan from Index Scan in EXPLAIN, and the core knobs that give the best bang for buck on buffers and concurrency.Cache Penetration, Breakdown and Avalanche: Identify and Protect Three Redis FaultsWhen your API suddenly slows down or your database collapses, three kinds of cache faults are usually to blame. This guide uses realistic log symptoms to help you tell apart penetration, breakdown and avalanche, then lays out practical defenses including mutex locks, null-caching and never-expiring hot keys.MySQL Index Design and EXPLAIN Analysis: From Wasteful to Zero Slow QueriesYou keep adding indexes yet slow queries do not go away — that usually means the indexes themselves are misdesigned. Starting from real business SQL, this guide covers composite index column order, covering indexes and index-failure scenarios, then walks through EXPLAIN output section by section so you can actually read a plan.tmux Session Management and Reuse: Remote Dev That Survives SSH DropsOne dropped SSH and a half-run build, open files and a dozen panes are all gone. This guide shows how tmux named sessions, detach/attach reuse and window/pane layout let you reconnect after a network drop and pick up exactly where you left off.VSCode Debugging Primer: From launch.json to Hovering Over a VariableCompared with sprinkling console.log everywhere, debugging lets you pause and inspect the call stack and every variable at each step. Starting from editing launch.json, this guide gets Node and front-end projects debugging, then walks through the sidebar, watch expressions and conditional breakpoints.Environment Variables and .env Management: Practical Details Beyond 12-FactorSecrets in code, environment variables drifting across environments, and .env committed to git — almost every project trips on these. Following the 12-factor principle, this guide clarifies precedence, injection methods, and how to manage local and CI config safely.Prettier and ESLint Together: Formatting and Linting That Do Not FightPrettier handles formatting and ESLint enforces quality, but when their rules overlap they fight each other and pre-commit runs get messy. This guide explains how to divide responsibilities, turn off conflicting rules, unify config, and enforce everything in CI.Take Claude Code Through a Real Bug-Fix FlowAn AI coding assistant is not "let it auto-fix" but a disciplined workflow. Using a real project bug, this guide walks the full loop from reproduction, adding a test, surgical fixes, to regression verification, with the exact instructions to issue at each step.Prompt Engineering for Coding Agents: Make Claude Code / Codex a Reliable CollaboratorGetting a coding agent right on the first try is not about the model but the instruction quality. This guide explains context constraints, minimal-change rules, test-first practice and review prompts, so you can craft reproducible prompts with fewer hallucinations.
← Back to Guides