18 Must‑Know MySQL 8.0 Management Features to Boost Your DBA Skills

1. Persistent Global Variables

Variables marked as persistent are saved in the mysqld-auto.cnf file under the data directory.

# Persist variable and modify its in‑memory value
SET PERSIST max_connections = 2000;
# Persist only without changing the in‑memory value (read‑only parameter)
SET PERSIST_ONLY back_log = 2000;
# Delete all persisted variables
RESET PERSIST;
# Delete a specific variable (error if it does not exist)
RESET PERSIST system_var_name;
# Delete a specific variable (warning if it does not exist)
RESET PERSIST IF EXISTS system_var_name;

Persisted variables can also be inspected via performance_schema.persisted_variables.

2. Configurable Admin IP and Port

The admin interface address is set with admin_address and the port with admin_port. Connections are unlimited but require the SERVICE_CONNECTION_ADMIN privilege. The admin listener thread can be enabled by setting create_admin_listener_thread=ON.

3. Minimal Installation Packages

Since MySQL 8.0.16 a minimal generic binary package removes debug binaries. For example, the regular 8.0.31 package is 576.8 MB while the minimal version is only 57.4 MB (≈9.95 % of the original size). ARM‑64 glibc 2.17 packages are also available.

4. Resource Groups

Resource groups allow control over thread CPU priority and affinity.

# Create a user‑type resource group named Batch
CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 0-1 THREAD_PRIORITY = 10;

# Assign specific threads to the group
SET RESOURCE GROUP Batch FOR 702,703;   # 702 and 703 are THREAD_ID values from performance_schema.threads

# Assign the current session’s thread to the group
SET RESOURCE GROUP Batch;

# Use the group in a query hint
SELECT /*+ RESOURCE_GROUP(Batch) */ COUNT(*) FROM sbtest.sbtest1;

5. ALTER DATABASE … READ ONLY

Databases can be placed in read‑only mode, which blocks all write operations—useful for migration scenarios.

# Enable read‑only mode for database mydb
ALTER DATABASE mydb READ ONLY = 1;
# Disable read‑only mode
ALTER DATABASE mydb READ ONLY = 0;

6. SHOW PROCESSLIST Implementation

By default SHOW PROCESSLIST reads from the thread manager and holds a global mutex, impacting performance. Setting performance_schema_show_processlist=ON (available from MySQL 8.0.22) switches to the lock‑free performance_schema.processlist view.

7. DDL Thread and Buffer Improvements

MySQL 8.0.27 introduces innodb_ddl_threads and innodb_ddl_buffer_size to accelerate index creation.

8. Instant ADD COLUMN

Since MySQL 8.0.12 the INSTANT algorithm allows column addition by only updating metadata, completing instantly. Prior to 8.0.29 columns could be added only at the end of a table; from 8.0.29 the restriction is removed and columns can be added anywhere. DROP COLUMN also supports INSTANT from 8.0.29.

9. Faster DROP TABLE, TRUNCATE TABLE, DROP TABLESPACE

MySQL 8.0.23 makes these operations asynchronous, avoiding full Buffer Pool scans and reducing lock contention. Blocking time depends on Buffer Pool size, not on table size.

10. OS‑Level Thread Names

From MySQL 8.0.27 the ps command can display MySQL thread names directly.

# Example output of "ps -p 22307 -H -o \"pid tid cmd comm\""
PID   TID CMD                     COMMAND
22307 22307 /usr/local/mysql/bin/mysqld mysqld
22307 22316 /usr/local/mysql/bin/mysqld ib_io_ibuf
...

11. Connection Memory Limits

Parameters connection_memory_limit (per‑connection) and global_connection_memory_limit (overall) were added in MySQL 8.0.28. Exceeding the per‑connection limit raises error 4082. The limits do not include the InnoDB Buffer Pool.

12. Enhanced Slow‑Log

The log_slow_extra option (MySQL 8.0.14) adds richer information to the slow‑log, including the statement start timestamp (previously the end timestamp). Example logs before and after enabling the option are shown.

13. mysqldump Long‑Query‑Time Option

MySQL 8.0.30 adds --mysqld-long-query-time to let users set a custom long_query_time for the dump session, preventing backup‑related queries from being recorded in the slow‑log.

14. Clone Plugin

The Clone Plugin, introduced in MySQL 8.0.17, enables a single command to create a new node for both Group Replication and traditional primary‑replica setups. Detailed usage is described in the official MySQL documentation.

15. Backup Locks

Backup locks were added to block DDL during backup, not to replace the global read lock. XtraBackup 8.0 and MySQL Enterprise Backup no longer acquire a global read lock because of performance_schema.log_status.

16. In‑Place Upgrade

Database upgrades no longer require running mysql_upgrade; the upgrade logic is now built into the server startup. Before upgrading, util.checkForServerUpgrade() in MySQL Shell can verify upgrade readiness.

17. mysql Client Binary‑As‑Hex

The client now defaults to --binary-as-hex, displaying binary data in hexadecimal form. To revert to the old behavior, use --skip-binary-as-hex.

18. RESTART Command

If the server is managed by mysqld_safe or systemctl, the RESTART statement can be used to restart the instance. Attempting to restart an unmanaged server yields error 3707.