Explore commands to clear internal caches. Learn to use FLUSH to reload privileges, clear the query cache, or close open tables.
Defragment the query cache to optimize memory usage. This command reorganizes the cache to eliminate fragmentation without removing existing cached queries.
You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Prepare individual tables for binary backup. This command flushes changes to disk and locks tables, allowing safe copying of .ibd files while the server runs.
FLUSH TABLES ... FOR EXPORT flushes changes to the specified tables to disk so that binary copies can be made while the server is still running. This works for , , , , and tables.
The table is read locked until you issue .
If a storage engine does not support FLUSH TABLES FOR EXPORT, a 1031 error ( 'HY000') is produced.
If FLUSH TABLES ... FOR EXPORT is in effect in the session, the following statements will produce an error if attempted:
FLUSH TABLES WITH READ LOCK
FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT
If any of the following statements is in effect in the session, attempting FLUSH TABLES ... FOR EXPORT will produce an error.
FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT
LOCK TABLES ... READ
FLUSH FOR EXPORT is not written to the .
This statement requires the and the privileges.
If one of the specified tables cannot be locked, none of the tables will be locked.
If a table does not exist, an error like the following will be produced:
If a table is a view, an error like the following will be produced:
For a full description, please see .
- Compressing the MyISAM data file for easier distribution.
This page is licensed: CC BY-SA / Gnu FDL
FLUSH TABLE[S] table_name [, table_name] FOR EXPORTLOCK TABLES ... WRITEaria_pack - Compressing the Aria data file for easier distribution
ERROR 1146 (42S02): Table 'test.xxx' doesn't existERROR 1347 (HY000): 'test.v' is not BASE TABLEFLUSH TABLES test.t1 FOR EXPORT;
# Copy files related to the table (see below)
UNLOCK TABLES;Reload internal caches and clear buffers. Learn how to use the FLUSH statement to reset logs, privileges, hosts, and other server states without restarting.
or when flushing tables:
where table_list is a list of tables separated by , (comma).
The FLUSH statement clears or reloads various internal caches used by MariaDB. To execute FLUSH, you must have the RELOAD privilege. See .
The RESET statement is similar to FLUSH. See .
You cannot issue a FLUSH statement from within a or a . Doing so within a stored procedure is permitted, as long as it is not called by a stored function or trigger. See , and .
If a listed table is a view, an error like the following will be produced:
By default, FLUSH statements are written to the and will be . 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:
You can also use the client to flush things. Use mariadb-admin --help to examine what flush commands it supports.
FLUSH RELAY LOGSThe FOR CHANNEL keyword was added for MySQL compatibility. This is identical to using the channel_name directly after the FLUSH command. For example, one can now use:
FOR CHANNEL isn't available.
can be reset by executing the following:
This statement requires the privilege.
Specify FLUSH GLOBAL or FLUSH SESSION. Flushing of global status variables has been moved to FLUSH GLOBAL STATUS which is a synonym for FLUSH STATUS.
You can use old-mode=OLD_FLUSH_STATUS to restore the old behavior of the FLUSH STATUS statement.
The variables flushed are mainly session, but some are global. Not all session (or global) variables are flushed - the decision was made per variable.
FLUSH STATUSNot all global status variables support being reset by FLUSH STATUS. Currently, the following is an incomplete list of status variables are reset by FLUSH GLOBAL STATUS in 11.5 or FLUSH STATUS in earlier versions:
MariaDB starting with
FLUSH TABLES doesn't cause to be reloaded or recalculated. , however, triggers a reload of the statistics.
FLUSH TABLES causes to be reloaded or recalculated.
FLUSH TABLES causes to be reloaded or recalculated.
The purpose of FLUSH TABLES is to clean up the open table cache and table definition cache of tables that are not in use. This frees up memory and file descriptors. Normally this is not needed as the caches works on a first-in, first-out basis, but can be useful if the server seems to use too much memory for some reason.
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 is blocked and all tables are marked as
'properly closed' on disk. The tables can still be used for read operations.
FLUSH TABLES table_list is useful if you want to copy a table object or 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 .
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 with most tables, except InnoDB as InnoDB may do background purges on the table even while it's write locked.
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.
Free memory and file descriptors not in use
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 .
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.
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.
This is basically the same behavior as in older MariaDB versions if you first lock the tables, then do FLUSH TABLES. The tables will be copyable until you issue UNLOCK TABLES.
The FLUSH SSL command can be used to dynamically reinitialize the server's 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 :
These are not dynamic, so their values can not be changed without restarting the server.
If you want to dynamically reinitialize the server's context, then you need to change the certificate and key files at the relevant paths defined by these , without actually changing the values of the variables. See for more information.
To flush some of the global caches that take up memory, you could execute the following command:
This page is licensed: GPLv2, originally from
FLUSH [NO_WRITE_TO_BINLOG | LOCAL]
flush_option [, flush_option] ...FLUSH [NO_WRITE_TO_BINLOG | LOCAL] TABLES [table_list] [table_flush_option]BINARY LOGS
FLUSH BINARY LOGS rotates the current .
BINARY LOGS DELETE_DOMAIN_ID=(list-of-domains)
FLUSH BINARY LOGS DELETE_DOMAIN_ID can be used to discard obsolete domains from the server's state. In order for this to be successful, no event group from the listed domains can be present in existing 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 .
MASTER
Deprecated option, use 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.
Defragment the to better utilize its memory. If you want to reset the query cache, you can do it with .
QUERY_RESPONSE_TIME
See the plugin.
QUERY_RESPONSE_TIME_READ
See the plugin. From .
QUERY_RESPONSE_TIME_READ_WRITE
See the plugin. From .
QUERY_RESPONSE_TIME_WRITE
See the plugin. From .
SLAVE
Deprecated option, use instead.
SSL
Used to dynamically reinitialize the server's context by reloading the files defined by several . See for more information.
[ GLOBAL
SESSION ] STATUS
TABLE[S]
Close tables given as options or all open tables if no table list was used. 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, 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.
For InnoDB tables, flushes table changes to disk to permit binary table copies while the server is running. See for more.
TABLE[S] WITH READ LOCK
Closes all open tables. New tables are only allowed to be opened with read locks until an is given.
TABLE[S] 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 ).
USER_RESOURCES
Resets all per hour . This enables clients that have exhausted their resources to connect again.
USER_STATISTICS
Reset user statistics (see ).
USER_VARIABLES
Reset user variables (see ).
No changes to these tables allowed until UNLOCK TABLES.
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
Closes and reopens the error log file to which the server is writing.
ERROR 1347 (HY000): 'test.v' is not BASE TABLEFLUSH RELAY LOGS 'connection_name'FLUSH RELAY LOGS FOR CHANNEL 'connection_name';FLUSH STATUSFLUSH LOCAL HOSTS,
QUERY CACHE,
TABLE_STATISTICS,
INDEX_STATISTICS,
USER_STATISTICS;