MySQL FLUSH Commands

In the world of hosting, MySQL remains one of the most critical components for powering dynamic websites, e-commerce stores, content management systems (CMS), and SaaS platforms. Whether you’re managing a shared hosting cluster, a VPS infrastructure, or dedicated servers for enterprise clients, having control over MySQL’s internal operations is essential.

One often-overlooked but powerful tool is the FLUSH command — a collection of administrative SQL statements that allow you to reload privileges, clear logs, reset states, and ensure consistency without restarting the server.

What is FLUSH in MySQL?

The FLUSH statement is used to:

• Clear internal caches

• Reload configuration files (like privilege tables)

• Reset status information

• Force MySQL to write buffered data to disk

Only users with the RELOAD privilege (or higher, like SUPER) can execute most FLUSH operations.

Common FLUSH Commands

1. 🔐 FLUSH PRIVILEGES

Reloads user privileges from the grant tables in the mysql database:

When to use:

• After manually editing mysql.user or other grant tables.

• When you change user access via direct UPDATE statements instead of GRANT.

2.  FLUSH TABLES

Closes all open tables and forces them to be re-opened. Useful in file system maintenance or backups.

You can also flush specific tables:

When to use:

• Before performing backups with external tools (e.g., mysqldump –single-transaction).

• After large-scale writes that may overload the table cache.

3.  FLUSH QUERY CACHE

Removes all query results from the query cache (if enabled):

Note: Query cache is deprecated and removed in MySQL 8.0.

4.  FLUSH LOGS

Closes and reopens all log files (general log, error log, binary log, relay log).

Or flush a specific type:

When to use:

• After log rotation

• When logs are moved or archived

• To reset logs for debugging or compliance

5. FLUSH HOSTS

Resets the host cache of failed client connections.

When to use:

• When receiving the error:
  Host 'xxx' is blocked because of many connection errors

6. FLUSH STATUS

Resets most status variables to zero (does not affect global counters like Uptime).

When to use:

• Before benchmarking or performance testing.

• To get clean metrics with SHOW STATUS.

7. FLUSH TABLES WITH READ LOCK

Flushes and locks all tables, preventing writes from other sessions.

When to use:

• For consistent snapshot backups (e.g., with LVM or file-copying tools).

• Important: Always unlock manually with UNLOCK TABLES;

8. FLUSH DES_KEY_FILE

Reloads DES key files used in old DES_ENCRYPT()/DES_DECRYPT() (legacy).

9.  FLUSH USER_RESOURCES

Resets per-user resource limits (like MAX_QUERIES_PER_HOUR) counters.

When to use:

• Mid-cycle reset for resource-limited users.

Deprecated/Removed Commands

• FLUSH QUERY CACHE is removed in MySQL 8.0.

• FLUSH DES_KEY_FILE is legacy.

• Consider newer alternatives in MySQL 8.0 like RESET PERSIST.

Permissions Required

Most FLUSH operations require:

• RELOAD privilege

• SUPER or SYSTEM_VARIABLES_ADMIN (for sensitive operations in MySQL 8+)

Example:

Conclusion

The FLUSH command family is essential for administrative control in MySQL. Whether you’re managing privileges, logs, or cache states, understanding how and when to use FLUSH helps maintain optimal server performance and reliability without requiring a restart. Use it wisely with proper privileges, especially in production environments.