FLUSH
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]
Contents
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 | 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) |
INDEX_STATISTICS | Reset index statistics (see SHOW INDEX_STATISTICS). |
| Close and reopen the specified log type, or all log types if none are specified. FLUSH 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 10.1.30 FLUSH BINARY LOGS DELETE_DOMAIN_ID=(list-of-domains) allows to discard obsolete gtid domains from the (master) server binlog state. The successful removal requires no event group from the listed domains be present in existing binlog files. That is those files, if existed, must have been purged prior to this one invocation. The successful command also rotates 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 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. Introduced in MariaDB 10.0.8. 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 AND DISABLE CHECKPOINT | As TABLES WITH READ LOCK but also disable all 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). |
You can also use the mysqladmin client to flush things. Use mysqladmin --help to examine what flush commands it supports.
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_clientsAborted_connectsAria_pagecache_blocks_not_flushedAria_pagecache_blocks_unusedAria_pagecache_blocks_usedBinlog_cache_disk_useBinlog_cache_useBinlog_stmt_cache_disk_useBinlog_stmt_cache_useConnection_errors_acceptConnection_errors_internalConnection_errors_max_connectionsConnection_errors_peer_addressConnection_errors_selectConnection_errors_tcpwrapCreated_tmp_filesDelayed_errorsDelayed_writesFeature_check_constraintFeature_delay_key_writeMax_used_connectionsOpened_plugin_librariesPerformance_schema_accounts_lostPerformance_schema_cond_instances_lostPerformance_schema_digest_lostPerformance_schema_file_handles_lostPerformance_schema_file_instances_lostPerformance_schema_hosts_lostPerformance_schema_locker_lostPerformance_schema_mutex_instances_lostPerformance_schema_rwlock_instances_lostPerformance_schema_session_connect_attrs_lostPerformance_schema_socket_instances_lostPerformance_schema_stage_classes_lostPerformance_schema_statement_classes_lostPerformance_schema_table_handles_lostPerformance_schema_table_instances_lostPerformance_schema_thread_instances_lostPerformance_schema_users_lostQcache_hitsQcache_insertsQcache_lowmem_prunesQcache_not_cachedRpl_semi_sync_master_no_timesRpl_semi_sync_master_no_txRpl_semi_sync_master_timefunc_failuresRpl_semi_sync_master_wait_pos_backtraverseRpl_semi_sync_master_yes_txRpl_transactions_multi_engineServer_audit_writes_failedSlave_retried_transactionsSlow_launch_threadsSsl_accept_renegotiatesSsl_acceptsSsl_callback_cache_hitsSsl_client_connectsSsl_connect_renegotiatesSsl_ctx_verify_depthSsl_ctx_verify_modeSsl_finished_acceptsSsl_finished_connectsSsl_session_cache_hitsSsl_session_cache_missesSsl_session_cache_overflowsSsl_session_cache_sizeSsl_session_cache_timeoutsSsl_sessions_reusedSsl_used_session_cache_entriesSubquery_cache_hitSubquery_cache_missTable_locks_immediateTable_locks_waitedTc_log_max_pages_usedTc_log_page_waitsTransactions_gtid_foreign_engineTransactions_multi_engine
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:
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;
