MENU


Server Commands

This page covers the statements used to control server resources and connections, and to administer a running instance: resource and connection-compression controls, session character set commands, ALTER INSTANCE, RESOURCE GROUP, CLONE, FLUSH, RESET, RESET PERSIST, KILL, RESTART, and SHUTDOWN.

Resource Allocation Control

The amount of memory used for queries issued by all regular users can be seen by checking the Global_connection_memory status variable. This total excludes resources used by system users such as the MySQL root account, and excludes any memory taken by the InnoDB buffer pool.

To enable updates of Global_connection_memory, set global_connection_memory_tracking = 1 (it is off by default). How often it is updated is controlled by connection_memory_chunk_size.

Memory usage limits for normal users can be set at the session level, the global level, or both:

Neither limit applies to system processes or administrative accounts.

Connection Compression Control

Connections to the server can compress traffic between client and server to reduce the number of bytes sent. By default connections are uncompressed, but compression is used if the server and client agree on a mutually permitted algorithm. Compression originates on the client side but affects CPU load on both sides, since both perform compression and decompression; because it decreases performance, its benefits occur mainly under low network bandwidth, when transfer time dominates the cost of compression, and when result sets are large.

SET PERSIST protocol_compression_algorithms='zlib,zstd,uncompressed';

Configures which compression algorithms the server permits for incoming connections.

mysql --compression-algorithms=zlib,uncompressed

Permits the mysql client to initiate zlib or uncompressed connections.

CHANGE REPLICATION SOURCE TO
  SOURCE_COMPRESSION_ALGORITHMS = 'zlib,zstd',
  SOURCE_ZSTD_COMPRESSION_LEVEL = 7;

Configures a replica to connect to the source using zlib or zstd, at zstd compression level 7. See also Replication.

SET CHARSET / SET NAMES

These statements map strings sent between the server and the current client to a given character set.

SET {CHARACTER SET | CHARSET} {'charset_name' | DEFAULT}

Sets the session character set (and derived collation).

SET NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT}

SET NAMES sets the character set and collation for the current connection. The character set determines how characters are encoded and decoded; the collation determines how strings are compared. As of MySQL 8.0, the server default character set is utf8mb4.

SELECT CCSA.CHARACTER_SET_NAME
FROM information_schema.TABLES T,
    information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = 'db'
  AND T.table_name = 'User';

Looks up the character set in effect for a table via INFORMATION_SCHEMA. A column's character set can be found similarly, by querying information_schema.COLUMNS and filtering on column_name.

ALTER INSTANCE

ALTER INSTANCE defines actions applicable to a MySQL server instance.

ALTER INSTANCE {ENABLE | DISABLE} INNODB REDO_LOG

Enables or disables InnoDB redo logging.

The total capacity of the redo log – how much redo log data is retained on disk, spread across a fixed number of same-sized files – is a separate concern from whether logging is on. Since MySQL 8.0.30, this is sized with the single innodb_redo_log_capacity system variable, which can be changed dynamically with SET GLOBAL/SET PERSIST and takes effect without a restart; it supersedes the older pair of variables, innodb_log_file_size and innodb_log_files_in_group, which required a slower, restart-based procedure to resize. The old variables still exist for backward compatibility, but innodb_redo_log_capacity is now the standard way to size the redo log in MySQL 8.4/9.x.

ALTER INSTANCE ROTATE INNODB MASTER KEY

Rotates the master encryption key used for InnoDB tablespace encryption.

ALTER INSTANCE ROTATE BINLOG MASTER KEY

Rotates the binary log master key used for binary log encryption.

ALTER INSTANCE RELOAD TLS
   [FOR CHANNEL {mysql_main | mysql_admin}]
   [NO ROLLBACK ON ERROR]

Reconfigures a TLS context from the current values of the system variables that define it.

ALTER INSTANCE RELOAD KEYRING

If a keyring component is installed, tells it to re-read its configuration file and reinitialize any in-memory keyring data.

RESOURCE GROUP

A resource group is a collection of threads that share resources such as CPU time and memory. Resource groups isolate different workloads and give them different priority levels – for example, a high-priority group for time-sensitive queries.

CREATE RESOURCE GROUP group_name
    TYPE = {SYSTEM|USER}
    [VCPU [=] vcpu_spec [, vcpu_spec] ...]
    [THREAD_PRIORITY [=] N]
    [ENABLE|DISABLE]

vcpu_spec:
   N | M-N

SET RESOURCE GROUP group_name [FOR thread_id [, thread_id] ...]

ALTER RESOURCE GROUP group_name
    [VCPU [=] vcpu_spec [, vcpu_spec] ...]
    [THREAD_PRIORITY [=] N]
    [ENABLE|DISABLE [FORCE]]

DROP RESOURCE GROUP group_name [FORCE]

Creating, assigning to, altering, and dropping resource groups.

SYSTEM resource groups are used for system threads (queries and connections). USER resource groups are used for user threads, such as those running stored procedures and functions. If VCPU is omitted, the group has no CPU affinity and can use all available CPUs; a range is written M-N where M <= N. ENABLE/DISABLE set the group's initial state (enabled by default); a disabled group cannot have threads assigned to it. Without FORCE, dropping or disabling a group that has threads assigned raises an error.

CLONE

CLONE clones data locally or from a remote MySQL server instance; the clone plugin must be installed.

CLONE clone_action

clone_action:
    LOCAL DATA DIRECTORY [=] 'clone_dir'
  | INSTANCE FROM 'user'@'host':port
    IDENTIFIED BY 'password'
    [DATA DIRECTORY [=] 'clone_dir']
    [REQUIRE [NO] SSL]
Cloning locally copies the local data directory to another directory on the same server or node. Cloning remotely transfers data from a remote donor instance to the recipient instance where cloning was initiated.

FLUSH

FLUSH clears or resets various server caches, buffers, and tables, typically to optimize performance or ensure data is safely written to disk. It causes an implicit commit.

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

flush_option:
    BINARY LOGS | ENGINE LOGS | ERROR LOGS | GENERAL LOGS
  | HOSTS | LOGS | PRIVILEGES | OPTIMIZER_COSTS
  | RELAY LOGS [FOR CHANNEL channel] | SLOW LOGS
  | STATUS | USER_RESOURCES

tables_option:
    TABLES
  | TABLES tbl_name [, tbl_name] ...
  | TABLES WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... FOR EXPORT

The full FLUSH syntax. See also Logging.

RESET

RESET clears the state of various server operations; it acts as a stronger version of FLUSH.

RESET reset_option [, reset_option] ...

reset_option:
    MASTER | REPLICA | SLAVE
MASTER deletes all binary logs listed in the index file, resets the binary log index file to empty, and creates a new binary log file. REPLICA/SLAVE makes the replica forget its replication position in the source's binary logs, and resets the relay log by deleting existing relay log files and starting a new one.

RESET PERSIST

RESET PERSIST removes persisted global system variable settings from the mysqld-auto.cnf option file in the data directory. Removing a persisted variable means it is no longer initialized from that file at server startup.

RESET PERSIST [[IF EXISTS] system_var_name]
With no argument, clears all persisted variables; with a name, clears just that one; IF EXISTS suppresses the error if it was not persisted.

KILL

Each connection to mysqld runs in a separate thread, which can be killed with KILL processlist_id.

KILL [CONNECTION | QUERY] processlist_id
KILL CONNECTION (the default with no modifier) terminates the connection after terminating any statement it is executing. KILL QUERY terminates only the statement currently executing, leaving the connection intact.
Thread processlist identifiers can be found in the ID column of the information_schema.PROCESSLIST table, the Id column of SHOW PROCESSLIST output, or the PROCESSLIST_ID column of the Performance Schema threads table; the current thread's ID is returned by CONNECTION_ID(). Killing threads and statements requires the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). See also Privileges.

RESTART

RESTART

Stops and restarts the MySQL server. Requires the SHUTDOWN privilege.

SHUTDOWN

SHUTDOWN

Stops the MySQL server. Requires the SHUTDOWN privilege.


-- Resource and connection-compression control
SET GLOBAL global_connection_memory_tracking = 1;
SET PERSIST connection_memory_limit = 4294967296; -- 4GB per connection
SET PERSIST protocol_compression_algorithms = 'zlib,zstd,uncompressed';

-- Session character set / collation
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';
SHOW CHARACTER SET;
SHOW COLLATION LIKE 'latin1%';

-- ALTER INSTANCE
ALTER INSTANCE ROTATE INNODB MASTER KEY;
ALTER INSTANCE RELOAD TLS;

-- Size the redo log dynamically (MySQL 8.0.30+; supersedes
-- innodb_log_file_size / innodb_log_files_in_group)
SET PERSIST innodb_redo_log_capacity = 8589934592; -- 8GB, no restart required

-- RESOURCE GROUP: isolate high-priority work on specific CPUs
CREATE RESOURCE GROUP rg1
    TYPE = USER
    VCPU = 0
    THREAD_PRIORITY = 19;

CREATE RESOURCE GROUP rg2
    TYPE = SYSTEM
    THREAD_PRIORITY = -20
    DISABLE;

SET RESOURCE GROUP rg1;            -- assigns the current session thread
SET RESOURCE GROUP rg2 FOR 14, 78, 4;

-- CLONE: provision a new instance from a donor
CLONE INSTANCE FROM 'clone_user'@'donor.example.com':3306
    IDENTIFIED BY 'password'
    DATA DIRECTORY = '/var/lib/mysql-clone';

-- FLUSH / RESET / RESET PERSIST
FLUSH TABLES WITH READ LOCK;
FLUSH PRIVILEGES;
UNLOCK TABLES;
RESET PERSIST protocol_compression_algorithms;

-- KILL a runaway query without dropping the connection
KILL QUERY 482;
KILL CONNECTION 482;