How-to Guides

Scope

Task recipes for MySQL Community Server 8.4 LTS, 9.7 LTS and 26.x Innovation: running a server, upgrading off 8.0, building an InnoDB Cluster, replication, backup and recovery, tuning, security setup and troubleshooting. Version and default tables are in Reference. Background is in Explanation.

Commands assume 8.4 or later

8.4 removed the MASTER/SLAVE statements (SHOW SLAVE STATUS, CHANGE MASTER TO, START SLAVE, RESET MASTER). Use the REPLICA/SOURCE forms below. The full mapping is in Reference: Removed Replication Statements.

Run MySQL in a Container

The Docker Official Image tags track the three supported lines: 8.4 (LTS), lts / 9.7 (LTS), and latest / innovation / 26.7 (Innovation). 8.0 is no longer maintained in the official image.

# LTS 9.7 with a data volume
docker run -d --name mysql97 \
  -e MYSQL_ROOT_PASSWORD='change-me' \
  -v mysql97-data:/var/lib/mysql \
  -p 3306:3306 \
  mysql:9.7

# Connect with the bundled client
docker exec -it mysql97 mysql -uroot -p

# Check version and track
docker exec mysql97 mysql -uroot -p -e "SELECT @@version, @@version_comment;"

For production on Linux, use Oracle's APT/YUM repositories (choose the 8.4 LTS, 9.7 LTS or Innovation channel) and pin the series you validated.

Upgrade from 8.0 (End of Life)

8.0 reached end of life on 2026-04-30. There is no direct 8.0 to 9.x upgrade. Go 8.0.37+ to 8.4, then optionally 8.4 to 9.7 LTS.

flowchart TD
    A["Running 8.0.x"] --> B{"8.0.37 or later?"}
    B -->|"No"| C["Patch to 8.0.46 first"]
    C --> D
    B -->|"Yes"| D["Run util.checkForServerUpgrade<br/>(target 8.4)"]
    D --> E{"Errors?"}
    E -->|"Yes"| F["Fix: native_password accounts,<br/>removed variables, SLAVE statements,<br/>reserved words"]
    F --> D
    E -->|"No"| G["Upgrade replicas first,<br/>then switch over the primary"]
    G --> H["On 8.4 LTS (supported to 2032)"]
    H --> I{"Need 9.x features?"}
    I -->|"Yes"| J["Repeat checker with target 9.7,<br/>upgrade to 9.7 LTS"]
    I -->|"No"| K["Stay on 8.4, apply quarterly patches"]

# 1. Pre-upgrade check with MySQL Shell (use the Shell version of the target)
mysqlsh -- util check-for-server-upgrade root@db1:3306 --target-version=8.4.11 --output-format=JSON

# 2. Find accounts that still use mysql_native_password
mysql -e "SELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password';"

-- 3. Move each account to caching_sha2_password (needs the password or a reset)
ALTER USER 'app'@'10.0.%' IDENTIFIED WITH caching_sha2_password BY 'new-strong-password';

# 4. my.cnf renames needed before starting 8.4
[mysqld]
# expire_logs_days = 7          # removed in 8.4
binlog_expire_logs_seconds = 604800
# default_authentication_plugin # removed in 8.4, use authentication_policy
# innodb_log_file_size = 2G     # deprecated, use innodb_redo_log_capacity
innodb_redo_log_capacity = 4G

Then replace SHOW SLAVE STATUS and friends in scripts and monitoring (for example Prometheus mysqld_exporter, Grafana dashboards, backup scripts). A temporary escape hatch on 8.4 only is mysql_native_password=ON in [mysqld]. It does not work on 9.x.

Build an InnoDB Cluster with MySQL Shell

InnoDB Cluster (Group Replication + MySQL Router + Shell AdminAPI) is the recommended HA setup. Three members tolerate one failure.

// mysqlsh --js, run against each instance first
dba.configureInstance('clusteradmin@db1:3306')   // sets GTID, server_id, creates admin account if asked
dba.configureInstance('clusteradmin@db2:3306')
dba.configureInstance('clusteradmin@db3:3306')

// Connect to the future primary and create the cluster
\connect clusteradmin@db1:3306
var cluster = dba.createCluster('prodCluster')
cluster.addInstance('clusteradmin@db2:3306', {recoveryMethod: 'clone'})
cluster.addInstance('clusteradmin@db3:3306', {recoveryMethod: 'clone'})
cluster.status()

# Bootstrap MySQL Router on each application host (reads cluster metadata)
mysqlrouter --bootstrap clusteradmin@db1:3306 --user=mysqlrouter --directory /opt/router
/opt/router/start.sh
# Applications connect to 6446 (read/write) and 6447 (read-only)

Useful day-2 AdminAPI calls: cluster.setPrimaryInstance('db2:3306') (planned switchover), cluster.rescan(), cluster.rejoinInstance('db3:3306'), dba.rebootClusterFromCompleteOutage() (after all members stopped), and cluster.createClusterSet('prodCS') followed by clusterset.createReplicaCluster(...) for a DR region.

Communication stack in 26.7+

From 26.7.0 new groups default to the MYSQL communication stack. Every member of a group must use the same stack. Check with cluster.status({extended: 1}) before mixing 9.7 and 26.x members.

Set Up Group Replication Manually

Use this only when you cannot use MySQL Shell. Configure every member first:

[mysqld]
server_id = 1                      # unique per member
gtid_mode = ON
enforce_gtid_consistency = ON
plugin_load_add = group_replication.so
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"   # any UUID, same on all members
group_replication_start_on_boot = OFF
group_replication_local_address = "db1:33061"
group_replication_group_seeds = "db1:33061,db2:33061,db3:33061"

-- On each member: recovery user (not written to the binlog)
SET SQL_LOG_BIN = 0;
CREATE USER 'rpl_user'@'%' IDENTIFIED BY 'change-me';
GRANT REPLICATION SLAVE, CONNECTION_ADMIN, BACKUP_ADMIN, GROUP_REPLICATION_STREAM ON *.* TO 'rpl_user'@'%';
SET SQL_LOG_BIN = 1;

-- Bootstrap the group on the first member only
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION USER = 'rpl_user', PASSWORD = 'change-me';
SET GLOBAL group_replication_bootstrap_group = OFF;

-- Join the other members
START GROUP_REPLICATION USER = 'rpl_user', PASSWORD = 'change-me';

-- Verify
SELECT member_host, member_state, member_role FROM performance_schema.replication_group_members;

Set Up Asynchronous GTID Replication

-- On the source
CREATE USER 'repl'@'10.0.%' IDENTIFIED BY 'change-me' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';

-- On the replica (after restoring a consistent copy, for example with CLONE or xtrabackup)
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'db1.example.internal',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'change-me',
    SOURCE_AUTO_POSITION = 1,
    SOURCE_SSL = 1,
    SOURCE_SSL_CA = '/etc/mysql/ca.pem',
    SOURCE_SSL_VERIFY_SERVER_CERT = 1;
START REPLICA;
SHOW REPLICA STATUS\G

Provision a replica quickly with the clone plugin (8.0.17+):

INSTALL PLUGIN clone SONAME 'mysql_clone.so';          -- on donor and recipient
SET GLOBAL clone_valid_donor_list = 'db1.example.internal:3306';
CLONE INSTANCE FROM 'clone_user'@'db1.example.internal':3306 IDENTIFIED BY 'change-me';

Backup and Recovery

# Logical, parallel dump and load with MySQL Shell (preferred over mysqldump for large data)
mysqlsh root@db1:3306 -- util dump-instance /backup/2026-09-25 --threads=8
mysqlsh root@db2:3306 -- util load-dump /backup/2026-09-25 --threads=8

# Classic logical dump (single-threaded, consistent for InnoDB)
mysqldump --single-transaction --source-data=2 --routines --triggers --events --all-databases > full.sql

# Physical hot backup with Percona XtraBackup (use the XtraBackup series matching the server: 8.4 for 8.4)
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full

# Point-in-time recovery: restore the backup, then replay binlogs up to just before the incident
mysqlbinlog --start-datetime="2026-09-24 10:00:00" --stop-datetime="2026-09-24 11:59:59" binlog.000042 binlog.000043 | mysql -uroot -p

mysqlpump was removed in 8.4. MySQL Enterprise Backup is the Oracle-supported physical backup tool. Percona XtraBackup 8.4 only backs up 8.4 servers. For 9.7, Percona XtraBackup 9.7 was still at 9.7.1-rc1 (2026-07-15) on 2026-09-28, and there is no 26.x Innovation build, so check its status before upgrading.

Tune InnoDB

Start from the 8.4 defaults (already SSD-oriented) and change only what you can measure. Example for a dedicated 32 GiB server:

[mysqld]
innodb_buffer_pool_size = 24G           # about 75% of RAM on a dedicated host
innodb_redo_log_capacity = 8G           # enough for about 1 hour of peak redo
innodb_flush_log_at_trx_commit = 1      # durable. 2 trades up to ~1 s of commits on OS crash for speed
sync_binlog = 1
innodb_flush_method = O_DIRECT          # 8.4 default on Linux
innodb_io_capacity = 10000              # 8.4 default. Lower (for example 200-2000) on HDD or throttled cloud disks
max_connections = 500

Size the redo log from the observed write rate:

-- Redo bytes written per minute (sample twice, 60 s apart)
SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_redo_log_current_lsn';
-- Resize online (8.0.30+)
SET PERSIST innodb_redo_log_capacity = 8 * 1024 * 1024 * 1024;

Check the buffer pool hit ratio:

SELECT ROUND(100 * (1 - r.variable_value / rr.variable_value), 2) AS hit_ratio_pct
FROM performance_schema.global_status r
JOIN performance_schema.global_status rr
  ON r.variable_name = 'Innodb_buffer_pool_reads'
 AND rr.variable_name = 'Innodb_buffer_pool_read_requests';

Try the hypergraph optimizer (9.7+) on a session before enabling it globally:

SET SESSION optimizer_switch = 'hypergraph_optimizer=on';
EXPLAIN FORMAT=TREE SELECT ...;

Find Slow Queries

-- Top statements by total latency (performance_schema digests)
SELECT digest_text, count_star, ROUND(sum_timer_wait / 1e12, 1) AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Same through the sys schema
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- Actual execution with timings
EXPLAIN ANALYZE SELECT ...;

# Slow query log
[mysqld]
slow_query_log = ON
long_query_time = 0.5
log_queries_not_using_indexes = OFF

Security Setup

Create Accounts and Roles

-- Application account with TLS, password expiry and a query cap
CREATE USER 'app_reader'@'10.0.%'
    IDENTIFIED BY 'reader-pass'
    REQUIRE SSL
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1
    WITH MAX_QUERIES_PER_HOUR 1000;

-- Roles
CREATE ROLE 'app_readonly';
GRANT SELECT ON appdb.* TO 'app_readonly';
GRANT 'app_readonly' TO 'analyst_user'@'10.0.%', 'reporting_user'@'10.0.%';
SET DEFAULT ROLE 'app_readonly' TO 'analyst_user'@'10.0.%';

-- Column-level grant
GRANT SELECT (id, name, email), UPDATE (name, email) ON appdb.customers TO 'support_agent'@'10.0.%';

-- Least privilege for a web app: no DELETE, DROP or GRANT OPTION
CREATE USER 'web_app'@'10.0.%' IDENTIFIED BY 'app-pass';
GRANT SELECT, INSERT, UPDATE ON appdb.orders TO 'web_app'@'10.0.%';

-- Lock an unused account
ALTER USER 'deprecated_user'@'localhost' ACCOUNT LOCK;

-- Enable password validation
INSTALL COMPONENT 'file://component_validate_password';
SET PERSIST validate_password.policy = 'MEDIUM';

Enforce TLS

[mysqld]
ssl_ca   = /etc/mysql/ca.pem
ssl_cert = /etc/mysql/server-cert.pem
ssl_key  = /etc/mysql/server-key.pem
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3

-- Require a client certificate, or a specific subject
CREATE USER 'secure_app'@'10.0.%' IDENTIFIED BY 'change-me' REQUIRE X509;
CREATE USER 'strict_app'@'10.0.%' IDENTIFIED BY 'change-me'
    REQUIRE SUBJECT '/CN=app.example.com/O=MyOrg/C=US';

-- Reload rotated certificates without restart
ALTER INSTANCE RELOAD TLS;

# Verify the server identity from the client side
mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/ca.pem -h db1.example.internal -u app -p

Encrypt Data at Rest

In 8.4+ load the keyring as a component through a global manifest file next to the mysqld binary, plus a component config file:

Global manifest mysqld.my, in the same directory as the mysqld binary:

{ "components": "file://component_keyring_file" }

Global component config component_keyring_file.cnf, in the plugin directory:

{ "path": "/var/lib/mysql-keyring/component_keyring_file", "read_only": false }

-- Check the keyring is active
SELECT * FROM performance_schema.keyring_component_status;

-- Encrypt tables
CREATE TABLE sensitive_data (id BIGINT PRIMARY KEY, ssn VARCHAR(11)) ENCRYPTION = 'Y';
ALTER TABLE customers ENCRYPTION = 'Y';

-- Encrypt the mysql system schema tablespace, redo, undo and binlogs (8.0.16+ for the mysql tablespace)
ALTER TABLESPACE mysql ENCRYPTION = 'Y';
SET PERSIST innodb_redo_log_encrypt = ON;
SET PERSIST innodb_undo_log_encrypt = ON;
SET PERSIST binlog_encryption = ON;

-- Rotate the master key (re-encrypts tablespace keys only)
ALTER INSTANCE ROTATE INNODB MASTER KEY;

Back up the keyring

Lose the keyring file and the encrypted tablespaces cannot be read. The file-based keyring is for development. Use an external KMS keyring (Enterprise) in production.

Configure Audit Logging (Enterprise)

[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_rotate_on_size = 100M

-- Rule-based filtering (Enterprise Audit 8.0.x+): log everything for one account
SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('app_user@%', 'log_all');

On Community Server use the Percona audit log filter component or the MariaDB audit plugin instead.

Troubleshoot Common Issues

Issue	Diagnose	Fix
Replication lag	SHOW REPLICA STATUS\G (Seconds_Behind_Source), performance_schema.replication_applier_status_by_worker	Check receiver vs applier. Raise replica_parallel_workers, add primary keys, split big transactions.
Replication stopped on error	SHOW REPLICA STATUS\G (Last_SQL_Error)	Fix the data drift. Skip only with an empty transaction for that GTID, never blindly.
Lock waits	SELECT * FROM sys.innodb_lock_waits\G	Kill the blocker, shorten transactions, add indexes.
Deadlocks	SHOW ENGINE INNODB STATUS\G (LATEST DETECTED DEADLOCK)	Access rows in a consistent order, add indexes, retry in the app.
History list length grows	SHOW ENGINE INNODB STATUS\G (History list length)	Find long transactions in information_schema.innodb_trx and end them.
Buffer pool thrashing	Hit ratio query above	Increase innodb_buffer_pool_size or reduce the working set.
Disk full from binlogs	SHOW BINARY LOGS	PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY. Set binlog_expire_logs_seconds.
Plugin 'mysql_native_password' is not loaded	Error on connect after upgrading to 8.4/9.x	Migrate the account to caching_sha2_password and upgrade the driver.
GR member stuck in RECOVERING or ERROR	performance_schema.replication_group_members, error log	Check recovery user privileges and TLS. Use cluster.rejoinInstance() or clone-based recovery.

Commands & Recipes

Connection & Basics

# Connect
mysql -h 127.0.0.1 -P 3306 -u root -p

# Import and export a database
mysql -u root -p mydb < dump.sql
mysqldump -u root -p --single-transaction mydb > backup.sql

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Essential Queries

-- Database sizes
SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC;

-- Active sessions and killing a query
SELECT * FROM sys.processlist WHERE command <> 'Sleep';
KILL QUERY 12345;

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Unused and redundant indexes
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

-- Binlog and GTID position (8.4+ syntax)
SHOW BINARY LOG STATUS;
SELECT @@global.gtid_executed;

Sources

• MySQL 8.4 Reference Manual
• MySQL 9.7 Upgrade Paths
• MySQL Shell 26.7: Group Replication communication stack
• MySQL 8.4 Native Pluggable Authentication
• Percona Server 8.4: breaking changes
• Docker Official Images: mysql library file