About a year ago, we discussed how MyDumper refactored its locking mechanisms to move away from old, rigid flags and transitioned towards more flexible, streamlined execution. Since then, the MyDumper community hasn’t stood still.
In recent releases, the locking architecture was further standardized under a single overarching option: --sync-thread-lock-mode. Along with this modernization came a powerful new safety feature designed to give you lock-free thread synchronization without risking silent inconsistency: SAFE_NO_LOCK (merged in PR #2031).
Let’s explore the new thread-synchronization landscape and break down when you should use each mode.
--sync-thread-lock-mode?Previously, flags like -k, --no-locks or --lock-all-tables dictated how MyDumper behaved. These have now been deprecated in favor of --sync-thread-lock-mode, which accepts five core values: AUTO, FTWRL, LOCK_ALL, GTID, NO_LOCK, and the newly added SAFE_NO_LOCK.
As a multi-threaded tool, MyDumper’s main challenge is ensuring that every single worker thread establishes its database snapshot at the exact same point in time. The sync mode you choose completely alters how MyDumper orchestrates this point-in-time synchronization.
MyDumper fires off START TRANSACTION WITH CONSISTENT SNAPSHOT across its threads. It captures the binary log position at the very beginning of the process and compares it after the worker threads have attempted to synchronize.
When using NO_LOCK, if the threads don’t actually hit the same point in time—meaning they fail to synchronize—MyDumper simply logs a warning and continues backing up. This results in an inconsistent backup, which is a massive gamble for production systems.
SAFE_NO_LOCK adds a strict transactional safety net. If MyDumper detects any differences or drift in the binlog position among the threads during the synchronization phase, it immediately stops the backup. This prevents you from generating a corrupted, out-of-sync backup that will fail or cause data anomalies during a later restore.
Depending on your architecture, uptime requirements, and database vendor, here is the breakdown of when to use each mode:
What it does: MyDumper automatically evaluates the database vendor, version, and capabilities to choose the safest, least-intrusive method.
When to use it: The vast majority of standard backups. It removes the guesswork and adapts dynamically if your database infrastructure upgrades.
What it does: It is the traditional method. It issues a global read lock via FLUSH TABLES WITH READ LOCK on the main connection, forces all threads to establish their consistent snapshot at that exact freeze frame, and then releases the lock.
When to use it:
Downside: It blocks writes across the entire instance during synchronization, which can cause a queue cascade on a busy production server.
Leverages a specific server variable in Percona Server called binlog_snapshot_gtid_executed to instantly verify if all threads are watching the exact same transaction state.
When to use it: If you are running Percona Server with GTID enabled and want a lightning-fast, lockless synchronization method that is guaranteed to be transactionally accurate.
What it does: Uses transaction isolation to sync threads without global locks, but immediately aborts the backup if binlog positions diverge during initialization.
When to use it:
Downside: In high-throughput write environments, threads may fail to align within the retry window, causing the backup job to abort. (Though an abort is always preferable to an inconsistent backup!).
What it does: Attempts lockless synchronization but logs a warning and proceeds even if consistency fails.
When to use it: Rarely, if ever, in the production primary server. It is acceptable for staging environments, development seeding, or scratch pads where data accuracy and point-in-time consistency are entirely secondary to getting a quick data dump without locking the server.
What it does: Explicitly issues a LOCK TABLE command for every single table being exported.
When to use it: Primarily a fallback mode. Use this only when FLUSH TABLES WITH READ LOCK is completely unavailable due to restricted cloud permissions (certain restricted PaaS environments) or specific database limitations.
The addition of --sync-thread-lock-mode=SAFE_NO_LOCK bridges a long-standing gap in logical MySQL backups: achieving a completely lockless synchronization state without flying blind. By implementing a strict fail-fast policy, MyDumper ensures that database administrators never have to sacrifice backup integrity for system availability.
