Syntax
------

FLUSH [NO_WRITE_TO_BINLOG | LOCAL]
  flush_option [, flush_option] ...

or when flushing tables:

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] TABLES [table_list]  [table_flush_option]

where table_list is a list of tables separated by , (comma).

Description
-----------

The FLUSH statement clears or reloads various internal caches used by MariaDB.
To execute FLUSH, you must have the RELOAD privilege. See GRANT.

The RESET statement is similar to FLUSH. See RESET.

You cannot issue a FLUSH statement from within a stored function or a trigger.
Doing so within a stored procedure is permitted, as long as it is not called
by a stored function or trigger. See Stored Routine Limitations, Stored
Function Limitations and Trigger Limitations.

If a listed table is a view, an error like the following will be produced:

ERROR 1347 (HY000): 'test.v' is not BASE TABLE

By default, FLUSH statements are written to the binary log and will be
replicated. The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the
statement is not written to the binary log.

The different flush options are:

+---------------------------+------------------------------------------------+
| Option                    | Description                                    |
+---------------------------+------------------------------------------------+
| CHANGED_PAGE_BITMAPS      | XtraDB only. Internal command used for backup  |
|                           | purposes. See the Information Schema           |
|                           | CHANGED_PAGE_BITMAPS Table.                    |
+---------------------------+------------------------------------------------+
| CLIENT_STATISTICS         | Reset client statistics (see SHOW              |
|                           | CLIENT_STATISTICS).                            |
+---------------------------+------------------------------------------------+
| DES_KEY_FILE              | Reloads the DES key file (Specified with the   |
|                           | --des-key-file startup option).                |
+---------------------------+------------------------------------------------+
| HOSTS                     | Flush the hostname cache (used for converting  |
|                           | ip to host names and for unblocking blocked    |
|                           | hosts. See max_connect_errors and              |
|                           | performance_schema.host_cache                  |
+---------------------------+------------------------------------------------+
| INDEX_STATISTICS          | Reset index statistics (see SHOW               |
|                           | INDEX_STATISTICS).                             |
+---------------------------+------------------------------------------------+
| [ERROR | ENGINE |         | Close and reopen the specified log type, or    |
| GENERAL | SLOW | BINARY   | all log types if none are specified. FLUSH     |
| | RELAY] LOGS             | RELAY LOGS [connection-name] can be used to    |
|                           | flush the relay logs for a specific            |
|                           | connection. Only one connection can be         |
|                           | specified per FLUSH command. See Multi-source  |
|                           | replication. FLUSH ENGINE LOGS will delete     |
|                           | all unneeded Aria redo logs. Since MariaDB     |
|                           | 10.1.30 and MariaDB 10.2.11, FLUSH BINARY      |
|                           | LOGS DELETE_DOMAIN_ID=(list-of-domains) can    |
|                           | be used to discard obsolete GTID domains from  |
|                           | the server's binary log state. In order for    |
|                           | this to be successful, no event group from     |
|                           | the listed GTID domains can be present in      |
|                           | existing binary log files. If some still       |
|                           | exist, then they must be purged prior to       |
|                           | executing this command. If the command         |
|                           | completes successfully, then it also rotates   |
|                           | the binary log.                                |
+---------------------------+------------------------------------------------+
| MASTER                    | Deprecated option, use RESET MASTER instead.   |
+---------------------------+------------------------------------------------+
| PRIVILEGES                | Reload all privileges from the privilege       |
|                           | tables in the mysql database. If the server    |
|                           | is started with --skip-grant-table option,     |
|                           | this will activate the privilege tables again. |
+---------------------------+------------------------------------------------+
| QUERY CACHE               | Defragment the query cache to better utilize   |
|                           | its memory. If you want to reset the query     |
|                           | cache, you can do it with RESET QUERY CACHE.   |
+---------------------------+------------------------------------------------+
| QUERY_RESPONSE_TIME       | See the QUERY_RESPONSE_TIME plugin.            |
+---------------------------+------------------------------------------------+
| SLAVE                     | Deprecated option, use RESET REPLICA or RESET  |
|                           | SLAVE instead.                                 |
+---------------------------+------------------------------------------------+
| SSL                       | Used to dynamically reinitialize the server's  |
|                           | TLS context by reloading the files defined by  |
|                           | several TLS system variables. See FLUSH SSL    |
|                           | for more information. This command was first   |
|                           | added in MariaDB 10.4.1.                       |
+---------------------------+------------------------------------------------+
| STATUS                    | Resets all server status variables that can    |
|                           | be reset to 0. Not all global status           |
|                           | variables support this, so not all global      |
|                           | values are reset. See FLUSH STATUS for more    |
|                           | information.                                   |
+---------------------------+------------------------------------------------+
| TABLE                     | Close tables given as options or all open      |
|                           | tables if no table list was used. From         |
|                           | MariaDB 10.4.1, using without any table list   |
|                           | will only close tables not in use, and tables  |
|                           | not locked by the FLUSH TABLES connection. If  |
|                           | there are no locked tables, FLUSH TABLES will  |
|                           | be instant and will not cause any waits, as    |
|                           | it no longer waits for tables in use. When a   |
|                           | table list is provided, from MariaDB 10.4.1,   |
|                           | the server will wait for the end of any        |
|                           | transactions that are using the tables.        |
|                           | Previously, FLUSH TABLES only waited for the   |
|                           | statements to complete.                        |
+---------------------------+------------------------------------------------+
| TABLES                    | Same as FLUSH TABLE.                           |
+---------------------------+------------------------------------------------+
| TABLES ... FOR EXPORT     | For InnoDB tables, flushes table changes to    |
|                           | disk to permit binary table copies while the   |
|                           | server is running. See FLUSH TABLES ... FOR    |
|                           | EXPORT for more.                               |
+---------------------------+------------------------------------------------+
| TABLES WITH READ LOCK     | Closes all open tables. New tables are only    |
|                           | allowed to be opened with read locks until an  |
|                           | UNLOCK TABLES is given.                        |
+---------------------------+------------------------------------------------+
| TABLES WITH READ LOCK     | As TABLES WITH READ LOCK but also disable all  |
| AND DISABLE CHECKPOINT    | checkpoint writes by transactional table       |
|                           | engines. This is useful when doing a disk      |
|                           | snapshot of all tables.                        |
+---------------------------+------------------------------------------------+
| TABLE_STATISTICS          | Reset table statistics (see SHOW               |
|                           | TABLE_STATISTICS).                             |
+---------------------------+------------------------------------------------+
| USER_RESOURCES            | Resets all per hour user resources. This       |
|                           | enables clients that have exhausted their      |
|                           | resources to connect again.                    |
+---------------------------+------------------------------------------------+
| USER_STATISTICS           | Reset user statistics (see SHOW                |
|                           | USER_STATISTICS).                              |
+---------------------------+------------------------------------------------+
| USER_VARIABLES            | Reset user variables (see User-defined         |
|                           | variables).                                    |
+---------------------------+------------------------------------------------+

You can also use the mysqladmin client to flush things. Use mysqladmin --help
to examine what flush commands it supports.

FLUSH RELAY LOGS
----------------

FLUSH RELAY LOGS 'connection_name';

Compatibility with MySQL
------------------------

MariaDB starting with 10.7.0
----------------------------
The FOR CHANNEL keyword was added for MySQL compatibility. This is identical
as using the channel_name directly after the FLUSH command.

For example, one can now use:

FLUSH RELAY LOGS FOR CHANNEL 'connection_name';

FLUSH STATUS
------------

Server status variables can be reset by executing the following:

FLUSH STATUS;

Global Status Variables that Support FLUSH STATUS
-------------------------------------------------

Not all global status variables support being reset by FLUSH STATUS.
Currently, the following status variables are reset by FLUSH STATUS:

* Aborted_clients
* Aborted_connects
* Binlog_cache_disk_use
* Binlog_cache_use
* Binlog_stmt_cache_disk_use
* Binlog_stmt_cache_use
* Connection_errors_accept
* Connection_errors_internal
* Connection_errors_max_connections
* Connection_errors_peer_address
* Connection_errors_select
* Connection_errors_tcpwrap
* Created_tmp_files
* Delayed_errors
* Delayed_writes
* Feature_check_constraint
* Feature_delay_key_write
* Max_used_connections
* Opened_plugin_libraries
* Performance_schema_accounts_lost
* Performance_schema_cond_instances_lost
* Performance_schema_digest_lost
* Performance_schema_file_handles_lost
* Performance_schema_file_instances_lost
* Performance_schema_hosts_lost
* Performance_schema_locker_lost
* Performance_schema_mutex_instances_lost
* Performance_schema_rwlock_instances_lost
* Performance_schema_session_connect_attrs_lost
* Performance_schema_socket_instances_lost
* Performance_schema_stage_classes_lost
* Performance_schema_statement_classes_lost
* Performance_schema_table_handles_lost
* Performance_schema_table_instances_lost
* Performance_schema_thread_instances_lost
* Performance_schema_users_lost
* Qcache_hits
* Qcache_inserts
* Qcache_lowmem_prunes
* Qcache_not_cached
* Rpl_semi_sync_master_no_times
* Rpl_semi_sync_master_no_tx
* Rpl_semi_sync_master_timefunc_failures
* Rpl_semi_sync_master_wait_pos_backtraverse
* Rpl_semi_sync_master_yes_tx
* Rpl_transactions_multi_engine
* Server_audit_writes_failed
* Slave_retried_transactions
* Slow_launch_threads
* Ssl_accept_renegotiates
* Ssl_accepts
* Ssl_callback_cache_hits
* Ssl_client_connects
* Ssl_connect_renegotiates
* Ssl_ctx_verify_depth
* Ssl_ctx_verify_mode
* Ssl_finished_accepts
* Ssl_finished_connects
* Ssl_session_cache_hits
* Ssl_session_cache_misses
* Ssl_session_cache_overflows
* Ssl_session_cache_size
* Ssl_session_cache_timeouts
* Ssl_sessions_reused
* Ssl_used_session_cache_entries
* Subquery_cache_hit
* Subquery_cache_miss
* Table_locks_immediate
* Table_locks_waited
* Tc_log_max_pages_used
* Tc_log_page_waits
* Transactions_gtid_foreign_engine
* Transactions_multi_engine

The different usage of FLUSH TABLES
-----------------------------------

The purpose of FLUSH TABLES
---------------------------

The purpose of FLUSH TABLES is to clean up the open table cache and table
definition cache from not in use tables. This frees up memory and file
descriptors. Normally this is not needed as the caches works on a FIFO bases,
but can be useful if the server seams to use up to much memory for some reason.

The purpose of FLUSH TABLES WITH READ LOCK 
-------------------------------------------

FLUSH TABLES WITH READ LOCK is useful if you want to take a backup of some
tables. When FLUSH TABLES WITH READ LOCK returns, all write access to tables
are blocked and all tables are marked as 'properly closed' on disk. The tables
can still be used for read operations.

The purpose of FLUSH TABLES table_list
--------------------------------------

FLUSH TABLES table_list is useful if you want to copy a table object/files to
or from the server. This command puts a lock that stops new users of the table
and will wait until everyone has stopped using the table. The table is then
removed from the table definition and table cache.

Note that it's up to the user to ensure that no one is accessing the table
between FLUSH TABLES and the table is copied to or from the server. This can
be secured by using LOCK TABLES.

If there are any tables locked by the connection that is using FLUSH TABLES
all the locked tables will be closed as part of the flush and reopened and
relocked before FLUSH TABLES returns. This allows one to copy the table after
FLUSH TABLES returns without having any writes on the table. For now this
works works with most tables, except InnoDB as InnoDB may do background purges
on the table even while it's write locked.

The purpose of FLUSH TABLES table_list WITH READ LOCK
-----------------------------------------------------

FLUSH TABLES table_list WITH READ LOCK should work as FLUSH TABLES WITH READ
LOCK, but only those tables that are listed will be properly closed. However
in practice this works exactly like FLUSH TABLES WITH READ LOCK as the FLUSH
command has anyway to wait for all WRITE operations to end because we are
depending on a global read lock for this code. In the future we should
consider fixing this to instead use meta data locks.

Implementation of FLUSH TABLES commands in MariaDB 10.4.8 and above
-------------------------------------------------------------------

Implementation of FLUSH TABLES
------------------------------

* Free memory and file descriptors not in use

Implementation of FLUSH TABLES WITH READ LOCK
---------------------------------------------

* Lock all tables read only for simple old style backup.
* All background writes are suspended and tables are marked as closed.
* No statement requiring table changes are allowed for any user until UNLOCK
TABLES.

Instead of using FLUSH TABLE WITH READ LOCK one should in most cases instead
use BACKUP STAGE BLOCK_COMMIT.

Implementation of FLUSH TABLES table_list
-----------------------------------------

* Free memory and file descriptors for tables not in use from table list.
* Lock given tables as read only.
* Wait until all translations has ended that uses any of the given tables.
* Wait until all background writes are suspended and tables are marked as
closed.

Implementation of FLUSH TABLES table_list FOR EXPORT
----------------------------------------------------

* Free memory and file descriptors for tables not in use from table list
* Lock given tables as read.
* Wait until all background writes are suspended and tables are marked as
closed.
* Check that all tables supports FOR EXPORT
* No changes to these tables allowed until UNLOCK TABLES

This is basically the same behavior as in old MariaDB version if one first
lock the tables, then do FLUSH TABLES. The tables will be copyable until

FLUSH SSL
---------

MariaDB starting with 10.4
--------------------------
The FLUSH SSL command was first added in MariaDB 10.4.

In MariaDB 10.4 and later, the FLUSH SSL command can be used to dynamically
reinitialize the server's TLS context. This is most useful if you need to
replace a certificate that is about to expire without restarting the server.

This operation is performed by reloading the files defined by the following
TLS system variables:

* ssl_cert
* ssl_key
* ssl_ca
* ssl_capath
* ssl_crl
* ssl_crlpath

These TLS system variables are not dynamic, so their values can not be changed
without restarting the server.

If you want to dynamically reinitialize the server's TLS context, then you
need to change the certificate and key files at the relevant paths defined by
these TLS system variables, without actually changing the values of the
variables. See MDEV-19341 for more information.

Reducing Memory Usage
---------------------

To flush some of the global caches that take up memory, you could execute the
following command:

FLUSH LOCAL HOSTS,
 QUERY CACHE,
 TABLE_STATISTICS,
 INDEX_STATISTICS,
 USER_STATISTICS;

URL: https://mariadb.com/kb/en/flush/