MySQL FLUSH Commands: Complete Reference for Database Administrators

MySQL's `FLUSH` statement forces the server to reload internal caches, close and reopen log files, reset status counters, and synchronize in-memory state with on-disk structures — all without requiring a server restart. This makes it one of the most operationally critical command families available to a database administrator.

Understanding each variant, its precise scope, and its side effects is not optional knowledge for production environments. Misusing `FLUSH TABLES WITH READ LOCK` on a busy OLTP system, for example, can cause application-wide write stalls lasting minutes. This reference covers every significant `FLUSH` variant, including behavior differences across MySQL 5.7 and 8.x, InnoDB-specific implications, replication hazards, and privilege requirements.

Why FLUSH Commands Matter in Production

The MySQL server maintains numerous in-memory structures to accelerate operations: the host connection cache, grant table caches, open table descriptors, query result caches, and storage engine buffer pools. These caches are authoritative during runtime. When an administrator makes out-of-band changes — editing grant tables directly with `INSERT`/`UPDATE`, rotating log files at the OS level, or moving `.ibd` files — the server's in-memory view becomes stale. `FLUSH` commands reconcile that divergence.

Key operational categories where `FLUSH` is indispensable:

• Privilege propagation without restarting `mysqld`
• Consistent online backups using lock-based snapshots
• Log rotation integrated with `logrotate` or custom scripts
• Performance baseline resets before benchmarking
• Host cache invalidation after network topology changes
• Storage engine durability enforcement before maintenance windows

Required Privileges

Most `FLUSH` variants require the `RELOAD` privilege. `FLUSH TABLES WITH READ LOCK` additionally requires `LOCK TABLES`. In MySQL 8.0+, fine-grained dynamic privileges (`FLUSH_OPTIMIZER_COSTS`, `FLUSH_STATUS`, `FLUSH_TABLES`, `FLUSH_USER_RESOURCES`) were introduced, allowing more granular access control without granting the broad `RELOAD` privilege. Always apply the principle of least privilege when assigning these to application or monitoring accounts.

Complete Reference: MySQL FLUSH Commands

1. FLUSH PRIVILEGES

“`sql

FLUSH PRIVILEGES;

“`

This command reloads the in-memory grant tables from the `mysql` system database (`mysql.user`, `mysql.db`, `mysql.tables_priv`, `mysql.columns_priv`, `mysql.procs_priv`). The server reads these tables at startup and caches them. Any direct DML (`INSERT`, `UPDATE`, `DELETE`) against those tables bypasses the normal `GRANT`/`REVOKE` mechanism, leaving the cache stale until `FLUSH PRIVILEGES` is executed.

When to use:

• After manually editing grant tables with raw SQL rather than `GRANT`/`REVOKE` statements
• After importing a mysqldump that includes direct inserts into `mysql.user`
• After restoring a partial backup of the `mysql` schema

Critical nuance: When you use the `GRANT`, `REVOKE`, `CREATE USER`, or `DROP USER` statements, MySQL automatically reloads the grant tables. `FLUSH PRIVILEGES` is only necessary when you bypass those statements entirely. Running it unnecessarily is harmless but adds a brief lock on the grant table cache.

Replication note: `FLUSH PRIVILEGES` is written to the binary log and replicated to replicas by default. This is generally the desired behavior when managing users across a replication topology.

2. FLUSH TABLES

“`sql

FLUSH TABLES;

FLUSH TABLES tbl1, tbl2;

“`

This command closes all currently open table file descriptors and removes them from the table definition cache (TDC). On the next access, MySQL reopens the table files from disk. This is essential after any out-of-band file manipulation.

When to use:

• After copying or replacing `.frm`, `.ibd`, or `.MYD`/`.MYI` files at the OS level
• To release table cache memory on servers with a very large `table_open_cache` value
• As a prerequisite before using `IMPORT TABLESPACE` in InnoDB transportable tablespace operations

Performance consideration: On a server with thousands of open tables, `FLUSH TABLES` acquires a global lock briefly. On high-concurrency systems, this can cause a noticeable latency spike. Prefer the table-specific form (`FLUSH TABLES tbl1, tbl2`) to minimize impact.

3. FLUSH TABLES WITH READ LOCK (FTWRL)

“`sql

FLUSH TABLES WITH READ LOCK;

— perform backup operations

UNLOCK TABLES;

“`

This is one of the most powerful and potentially disruptive `FLUSH` variants. It closes all open tables, flushes the query cache, and acquires a global read lock that prevents any write operations across all databases. The lock persists until `UNLOCK TABLES` is issued or the session ends.

When to use:

• Before taking a physical backup with tools like `mysqldump –single-transaction` (for InnoDB-only databases, this is unnecessary — see below)
• Before using `mysqlpump` or `xtrabackup` in non-InnoDB environments
• To create a point-in-time consistent snapshot across mixed storage engines (InnoDB + MyISAM)

Critical pitfall — InnoDB-only databases: For databases using exclusively InnoDB tables, `FTWRL` is almost never necessary. `mysqldump –single-transaction` opens a repeatable-read transaction that provides a consistent snapshot without blocking writes. Using `FTWRL` in this scenario causes unnecessary write stalls.

Replication hazard: If `FTWRL` is executed on a replica, it blocks the SQL applier thread, causing replication lag to accumulate for the duration of the lock. Always monitor `Seconds_Behind_Master` after releasing the lock.

Metadata lock interaction: In MySQL 5.7+, `FTWRL` waits for all active transactions to complete before acquiring the global lock. On a busy server with long-running transactions, this wait can be indefinite. Use `SHOW PROCESSLIST` to identify blocking transactions before executing `FTWRL`.

4. FLUSH HOSTS

“`sql

FLUSH HOSTS;

“`

MySQL maintains a host cache that records connection attempt history, including failed authentication counts. When a host accumulates more than `max_connect_errors` consecutive failed connections, MySQL blocks all subsequent connections from that host until the cache entry is cleared.

When to use:

• When a legitimate client host is blocked due to exceeding `max_connect_errors`
• After resolving a network issue that caused repeated TCP connection failures
• After changing DNS records that affect how the server resolves client hostnames

MySQL 8.0 alternative: In MySQL 8.0+, you can also truncate the host cache table directly:

“`sql

TRUNCATE TABLE performance_schema.host_cache;

“`

This achieves the same result and is more transparent in automated scripts.

Proactive configuration: Rather than relying on `FLUSH HOSTS` reactively, set `max_connect_errors` to a higher value (e.g., `10000`) or set `host_cache_size = 0` to disable the host cache entirely on trusted internal networks.

5. FLUSH STATUS

“`sql

FLUSH STATUS;

“`

Resets most session and global status variables to zero. This includes counters like `Com_select`, `Handler_read_rows`, `Innodb_buffer_pool_reads`, `Threads_connected`, and hundreds of others exposed via `SHOW STATUS` or `performance_schema`.

When to use:

• Immediately before a controlled benchmark to establish a clean measurement baseline
• After a configuration change (e.g., adjusting `innodb_buffer_pool_size`) to isolate the effect on I/O metrics
• When scripting performance regression tests that compare before/after counter deltas

Important limitation: `FLUSH STATUS` does not reset all counters. Variables like `Uptime`, `Uptime_since_flush_status`, and certain InnoDB internal metrics are not affected. For comprehensive monitoring, use `performance_schema` tables directly, which offer per-thread and per-event granularity that `FLUSH STATUS` cannot provide.

6. FLUSH LOGS

“`sql

FLUSH LOGS;

FLUSH BINARY LOGS;

FLUSH ERROR LOGS;

FLUSH GENERAL LOGS;

FLUSH SLOW LOGS;

FLUSH RELAY LOGS;

“`

`FLUSH LOGS` closes and reopens all server log files. MySQL 5.7.2+ introduced the ability to flush specific log types individually, which is far preferable in production.

When to use:

• As part of a `logrotate` post-rotate script to signal MySQL to open a new log file after the old one has been rotated
• To force a new binary log file (equivalent to `FLUSH BINARY LOGS`), which increments the binary log sequence number
• Before archiving old logs to ensure all pending writes are flushed to disk

Binary log specifics: `FLUSH BINARY LOGS` creates a new binary log file and writes a `Rotate_event` to the old file. This is the correct way to segment binary logs for point-in-time recovery (PITR) archival. The current binary log file and position can be confirmed with `SHOW MASTER STATUS` (MySQL 5.7) or `SHOW BINARY LOG STATUS` (MySQL 8.4+).

logrotate integration example:

“`bash

/etc/logrotate.d/mysql

/var/log/mysql/mysql-slow.log {

daily

rotate 7

missingok

compress

postrotate

mysqladmin -u root -p flush-logs

endscript

}

“`

7. FLUSH QUERY CACHE

“`sql

FLUSH QUERY CACHE;

RESET QUERY CACHE;

“`

Deprecation warning: The MySQL query cache was deprecated in MySQL 5.7.20 and removed entirely in MySQL 8.0. If you are running MySQL 8.0 or later, this command does not exist.

For MySQL 5.6/5.7 environments where the query cache is still active:

• `FLUSH QUERY CACHE` defragments the query cache memory without removing cached results
• `RESET QUERY CACHE` removes all cached query results entirely

When to use (MySQL 5.6/5.7 only):

• After a large batch data modification that invalidates a significant portion of cached results
• When `Qcache_free_blocks` is high relative to `Qcache_total_blocks`, indicating fragmentation
• Before disabling the query cache (`SET GLOBAL query_cache_size = 0`) to release memory cleanly

Modern alternative: On MySQL 8.0+, use InnoDB buffer pool warming (`innodb_buffer_pool_dump_at_shutdown`, `innodb_buffer_pool_load_at_startup`) and `performance_schema` for query-level analysis instead.

8. FLUSH USER_RESOURCES

“`sql

FLUSH USER_RESOURCES;

“`

Resets per-user resource counters tracked by MySQL's built-in rate limiting. These counters enforce limits defined in `CREATE USER` or `GRANT` statements:

• `MAX_QUERIES_PER_HOUR`
• `MAX_UPDATES_PER_HOUR`
• `MAX_CONNECTIONS_PER_HOUR`
• `MAX_USER_CONNECTIONS`

When to use:

• When a user has exhausted their hourly query quota and needs immediate access restored before the counter resets naturally at the next hour boundary
• After increasing a user's resource limits and wanting the new limits to take effect immediately
• During development/testing to reset quotas between test runs

Note: This command resets counters for all users simultaneously. There is no per-user granularity at the `FLUSH` level. If you need to reset a single user's counters, the only option is to modify their account with `ALTER USER` and then issue `FLUSH USER_RESOURCES`.

9. FLUSH ENGINE LOGS

“`sql

FLUSH ENGINE LOGS;

“`

Forces all storage engines to flush their pending write buffers to their respective log files. For InnoDB, this means flushing the redo log buffer (`innodb_log_buffer_size`) to the InnoDB redo log files on disk.

When to use:

• Before taking a cold backup of InnoDB data files to ensure redo log consistency
• During storage engine troubleshooting to rule out buffer-related data inconsistencies
• As part of a pre-maintenance checklist before stopping the MySQL service

InnoDB durability context: InnoDB's `innodb_flush_log_at_trx_commit` setting controls how aggressively the redo log is flushed on each transaction commit. `FLUSH ENGINE LOGS` is a manual override that forces a flush regardless of that setting. This is useful in scenarios where you need a guaranteed durability checkpoint without committing a transaction.

10. FLUSH DES_KEY_FILE

“`sql

FLUSH DES_KEY_FILE;

“`

Reloads the DES encryption key file specified by the `–des-key-file` server startup option. This key file was used by the `DES_ENCRYPT()` and `DES_DECRYPT()` functions.

Deprecation warning: The `DES_ENCRYPT()` and `DES_DECRYPT()` functions were deprecated in MySQL 5.7.6 and removed in MySQL 8.0. This command is therefore only relevant on legacy MySQL 5.6/5.7 installations.

Modern encryption alternative: Use MySQL's native data-at-rest encryption (InnoDB tablespace encryption via `ALTER TABLE … ENCRYPTION='Y'`) combined with MySQL Keyring plugins (`keyring_file`, `keyring_okv`, `keyring_aws`) for production encryption requirements.

FLUSH Command Comparison Table

Replication and FLUSH: What Gets Replicated

Not all `FLUSH` commands are replicated to replica servers. Understanding this distinction is critical in HA and replication topologies:

Replicated by default:

• `FLUSH PRIVILEGES`
• `FLUSH LOGS` (written as a `Rotate_event` in the binary log)
• `FLUSH USER_RESOURCES`

Not replicated (session-local or explicitly excluded):

• `FLUSH TABLES WITH READ LOCK` — never written to the binary log
• `FLUSH STATUS` — affects only the local server's counters
• `FLUSH HOSTS` — local host cache only
• `FLUSH ENGINE LOGS` — local engine state only

To prevent a specific `FLUSH` command from being replicated even when it normally would be, use the `LOCAL` or `NO_WRITE_TO_BINLOG` modifier:

“`sql

FLUSH NO_WRITE_TO_BINLOG PRIVILEGES;

FLUSH LOCAL PRIVILEGES; — equivalent shorthand

“`

This is useful when managing privileges independently on a replica (e.g., adding monitoring users that should not exist on the primary).

Automating FLUSH Operations with mysqladmin

Many `FLUSH` operations can be triggered from the shell without opening a MySQL client session, which is useful in cron jobs and maintenance scripts:

“`bash

Flush binary logs

mysqladmin -u root -p flush-logs

Flush privileges

mysqladmin -u root -p flush-privileges

Flush host cache

mysqladmin -u root -p flush-hosts

Flush status counters

mysqladmin -u root -p flush-status

“`

For production environments, store credentials in `~/.my.cnf` with `chmod 600` rather than passing `-p` interactively, or use MySQL's `–login-path` mechanism with `mysql_config_editor`.

Hosting Environment Considerations

The ability to execute `FLUSH` commands depends heavily on the hosting environment and the level of database access granted. On a VPS Hosting plan, you typically have full root access to the MySQL instance, meaning you can execute any `FLUSH` variant, modify `my.cnf`, and manage log rotation directly. This is the minimum recommended environment for any serious database administration work.

On Shared Web Hosting, database access is usually restricted to a non-privileged user without the `RELOAD` privilege, making most `FLUSH` commands unavailable. If your application requires privilege management, log rotation control, or backup-consistent snapshots, a shared environment will be a hard blocker.

For high-throughput database workloads — particularly those involving frequent `FLUSH ENGINE LOGS` operations or large InnoDB buffer pools — Dedicated Servers provide the I/O throughput and memory bandwidth needed to make these operations non-disruptive. A `FLUSH TABLES WITH READ LOCK` on a server with 256 GB of buffer pool data takes measurably longer than on a server with fast NVMe storage and dedicated I/O channels.

If you are managing a MySQL instance alongside a web control panel, VPS with cPanel provides a managed environment where some `FLUSH` operations (particularly log rotation and privilege reloads) are handled automatically by the control panel's database management layer, reducing the need for manual intervention.

For database-backed applications requiring a full control panel ecosystem, reviewing available VPS Control Panels will help identify which panel best integrates with your MySQL administration workflow.

Key Takeaway Checklist

Use this decision matrix before executing any `FLUSH` command in production:

• Before `FLUSH TABLES WITH READ LOCK`: Confirm no long-running transactions are active (`SHOW PROCESSLIST`). Verify whether your database is InnoDB-only — if so, use `–single-transaction` instead.
• Before `FLUSH PRIVILEGES`: Confirm you are using raw DML on grant tables. If you used `GRANT`/`REVOKE`, this command is redundant.
• Before `FLUSH LOGS`: Ensure your log rotation script has already moved/renamed the old log file before signaling MySQL to reopen it.
• Before `FLUSH HOSTS`: Identify the root cause of connection failures first. Flushing the host cache without fixing the underlying issue will result in the host being blocked again.
• On MySQL 8.0+: Remove any `FLUSH QUERY CACHE` or `FLUSH DES_KEY_FILE` calls from scripts — these commands do not exist and will cause errors.
• In replication topologies: Use `FLUSH LOCAL` or `FLUSH NO_WRITE_TO_BINLOG` when the operation should not propagate to replicas.
• For automation: Use `mysqladmin flush-*` commands in scripts rather than opening full MySQL client sessions.
• Privilege auditing: Prefer MySQL 8.0 dynamic privileges (`FLUSH_STATUS`, `FLUSH_TABLES`, etc.) over the broad `RELOAD` privilege for monitoring and backup accounts.

Frequently Asked Questions

Does FLUSH PRIVILEGES need to be run after every GRANT or REVOKE statement?

No. `GRANT`, `REVOKE`, `CREATE USER`, and `DROP USER` automatically reload the grant tables in memory. `FLUSH PRIVILEGES` is only necessary after direct DML modifications to the `mysql` system tables (e.g., `UPDATE mysql.user SET …`).

Can FLUSH TABLES WITH READ LOCK cause application downtime?

Yes. It acquires a global write lock that blocks all `INSERT`, `UPDATE`, `DELETE`, and DDL operations across every database on the server. On a busy OLTP system, even a few seconds of `FTWRL` can exhaust connection pools and cause cascading application errors. For InnoDB-only databases, use `mysqldump –single-transaction` to avoid this entirely.

Is FLUSH STATUS the same as restarting the MySQL server for benchmarking purposes?

No. `FLUSH STATUS` resets most status counters but does not clear the InnoDB buffer pool, reset connection states, or affect `performance_schema` accumulated statistics. For a true clean-slate benchmark, a server restart combined with buffer pool clearing is more accurate, though impractical in production.

Why does FLUSH HOSTS not exist as a standalone command in some MySQL 8.0 documentation?

`FLUSH HOSTS` still works in MySQL 8.0, but the preferred method is `TRUNCATE TABLE performance_schema.host_cache`, which is more explicit and can be executed without the `RELOAD` privilege if the user has `DELETE` on `performance_schema`. Both achieve the same result.

What happens if FLUSH ENGINE LOGS is executed during peak write load on InnoDB?

It forces a synchronous flush of the InnoDB log buffer to disk, which can cause a brief write stall if the redo log files are on slow storage. On NVMe-backed servers, the impact is typically sub-millisecond. On spinning disk or heavily loaded SAN storage, it can cause noticeable latency spikes. Schedule this operation during low-traffic windows when possible.