# MySQL and MariaDB

> Operate MySQL 8.4 and MariaDB 11.4: client, users and grants, InnoDB, EXPLAIN, slow log, indexes, dumps and mariadb-backup, replication, my.cnf and lock diagnosis.

Canonical: https://www.wiki.jodisand.me/mysql/
Reviewed: 2026-09-24
Related: [SQL](https://www.wiki.jodisand.me/sql/index.md), [PostgreSQL](https://www.wiki.jodisand.me/postgresql/index.md), [SQLite](https://www.wiki.jodisand.me/sqlite/index.md), [Linux performance](https://www.wiki.jodisand.me/linux-performance/index.md), [systemd](https://www.wiki.jodisand.me/systemd/index.md)


## Cheatsheet

| Task | Command |
| --- | --- |
| Connect over TLS | `mysql -h db.example.com -u app -p --ssl-mode=REQUIRED my_db` (`mariadb --ssl` on MariaDB) |
| Connect as root over the local socket | `sudo mysql` (MariaDB `unix_socket` auth) or `mysql -u root -p` |
| Vertical output for wide rows | `SELECT ... \G` |
| Who is connected and doing what | `SHOW FULL PROCESSLIST;` |
| Kill a query, keep the connection | `KILL QUERY 1234;` |
| Kill the connection | `KILL 1234;` |
| Current InnoDB state, deadlocks, waits | `SHOW ENGINE INNODB STATUS\G` |
| Blocked and blocking transactions (MySQL) | `SELECT * FROM sys.innodb_lock_waits\G` |
| Blocked and blocking transactions (MariaDB) | `SELECT * FROM information_schema.INNODB_LOCK_WAITS;` and `INNODB_TRX` |
| Grants of a user | `SHOW GRANTS FOR 'app'@'10.0.%';` |
| Create a user | `CREATE USER 'app'@'10.0.%' IDENTIFIED BY '...' REQUIRE SSL;` |
| Grant on a database | `GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO 'app'@'10.0.%';` |
| Table definition | `SHOW CREATE TABLE my_db.orders\G` |
| Plan with real row counts | `EXPLAIN ANALYZE SELECT ...` (MySQL 8.0.18+); `ANALYZE SELECT ...` (MariaDB) |
| Turn on the slow log live | `SET GLOBAL slow_query_log = ON, long_query_time = 1;` |
| Server variable, live | `SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';` |
| Set and persist a variable (MySQL) | `SET PERSIST max_connections = 500;` |
| Consistent logical backup | `mysqldump --single-transaction --routines --triggers --events my_db > my_db.sql` |
| Physical backup (MariaDB) | `mariadb-backup --backup --target-dir=/backup/full` |
| Replication health | `SHOW REPLICA STATUS\G` |
| Binary logs on disk | `SHOW BINARY LOGS;` |
| Validate a config file without starting | `mysqld --validate-config` (MySQL 8.0.16+) |

Behaviour below is MySQL 8.4 LTS and MariaDB 11.4 LTS on Fedora or RHEL 9 with InnoDB. The two forks agree on almost everything on this page; differences are marked. References: [MySQL 8.4 reference manual](https://dev.mysql.com/doc/refman/8.4/en/), [MariaDB server documentation](https://mariadb.com/docs/server/). SQL itself lives on the [SQL page](https://www.wiki.jodisand.me/sql/).

## First look at a struggling server

```sh
mysqladmin -u root -p status                                 # uptime, threads, questions, slow queries, QPS
mysql -e 'SHOW FULL PROCESSLIST' | grep -v Sleep              # active statements with their State and Time
mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections','Aborted_connects','Innodb_row_lock_waits','Innodb_buffer_pool_wait_free','Created_tmp_disk_tables','Slow_queries')"
mysql -e 'SHOW ENGINE INNODB STATUS\G' | sed -n '/LATEST DETECTED DEADLOCK/,/TRANSACTIONS/p'
journalctl -u mysqld --since -1h                              # mariadb.service on MariaDB
df -h /var/lib/mysql; sudo du -sh /var/lib/mysql/*
```

`Threads_running` in the tens on a machine with a handful of cores means queries are queuing, and `State` in the process list says where: `Waiting for table metadata lock` is DDL blocked by an open transaction, `Sending data` is a scan, `Copying to tmp table` is a `GROUP BY` or `ORDER BY` spilling, `Waiting for row lock` (MariaDB) or a long `updating` is contention. `SHOW ENGINE INNODB STATUS` is the single most informative page: transactions and what they wait for, the buffer pool hit rate, and the most recent deadlock with both statements.

## The client

The MySQL client is `mysql`; MariaDB ships `mariadb` with `mysql` as a symlink that prints a deprecation notice. Options are the same. Credentials on the command line appear in `ps` and shell history; use `~/.my.cnf` (mode 600), `mysql_config_editor` (MySQL, encrypted login path) or the `MYSQL_PWD` environment variable, in that order of preference.

```ini
# ~/.my.cnf
[client]
user = app
host = db.example.com
ssl-mode = REQUIRED          # MariaDB: ssl = on, ssl-verify-server-cert = on
# password in a login path or prompted with -p; never here on a shared host

[mysql]
prompt = "\\u@\\h [\\d]> "
pager = less -SFX            # horizontal scrolling for wide result sets
auto-rehash                  # tab completion of table and column names
```

```sh
mysql_config_editor set --login-path=prod --host=db.example.com --user=app --password   # prompts; stored encrypted in ~/.mylogin.cnf
mysql --login-path=prod my_db
mysql -h db.example.com -u app -p -e 'SELECT count(*) FROM orders' my_db                # one statement
mysql -N -B -e 'SELECT id FROM orders WHERE status = "stale"' my_db > ids.txt           # -N no header, -B tab separated
mysql --table my_db < report.sql                                                        # ASCII table output for a script
mysql my_db < schema.sql                                                                # run a file; add -f to continue past errors
mysqldump ... | mysql -h other.example.com my_db                                        # pipe a dump straight into another server
```

Inside the client: `\G` ends a statement with vertical output, `\s` prints status including TLS cipher and character set, `\P less -S` sets a pager, `\. file.sql` sources a file, `\e` edits the current statement in `$EDITOR`, `\c` cancels. `--safe-updates` (or `\U`) refuses `UPDATE` and `DELETE` without a key in `WHERE`; put `safe-updates` in the `[mysql]` section on production accounts.

## Users, authentication and grants

An account is `'user'@'host'`; `'app'@'10.0.%'` and `'app'@'localhost'` are different accounts with separate passwords and grants, and the server picks the most specific host match for a connecting client. `'app'@'localhost'` matches only socket and loopback connections; a client connecting to `127.0.0.1` over TCP still matches `localhost`. Hostnames are resolved unless `skip_name_resolve` is set, which you want on any server with more than a few connections per second.

```sql
CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'use-a-generated-secret' REQUIRE SSL
  WITH MAX_USER_CONNECTIONS 200
  PASSWORD EXPIRE INTERVAL 180 DAY;                         -- MySQL; MariaDB: PASSWORD EXPIRE INTERVAL 180 DAY also works
GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO 'app'@'10.0.%';
GRANT EXECUTE ON my_db.* TO 'app'@'10.0.%';                 -- stored procedures
CREATE USER 'readonly'@'%' IDENTIFIED BY '...';
GRANT SELECT, SHOW VIEW ON my_db.* TO 'readonly'@'%';
CREATE USER 'repl'@'10.0.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';           -- REPLICATION REPLICA is accepted from MySQL 8.0.26
CREATE USER 'backup'@'localhost' IDENTIFIED BY '...';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, BACKUP_ADMIN ON *.* TO 'backup'@'localhost';   -- MySQL; MariaDB 10.5+: GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
GRANT SELECT ON performance_schema.* TO 'backup'@'localhost';

CREATE ROLE app_rw; GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO app_rw;
GRANT app_rw TO 'app'@'10.0.%'; SET DEFAULT ROLE app_rw TO 'app'@'10.0.%';   -- roles: MySQL 8, MariaDB 10.0.5+
SHOW GRANTS FOR 'app'@'10.0.%';
SHOW GRANTS FOR CURRENT_USER();                             -- what the account you actually matched has
SELECT user, host, plugin, account_locked, password_expired FROM mysql.user;
ALTER USER 'app'@'10.0.%' IDENTIFIED BY 'new-secret';
REVOKE DELETE ON my_db.* FROM 'app'@'10.0.%';
RENAME USER 'app'@'10.0.%' TO 'app'@'10.1.%';
DROP USER 'old'@'%';
```

`FLUSH PRIVILEGES` is needed only after editing the `mysql.*` tables directly; `CREATE USER`, `GRANT` and `ALTER USER` take effect immediately for new connections. Existing connections keep the privileges they logged in with.

MySQL 8 defaults to `caching_sha2_password`, and 8.4 disables the old `mysql_native_password` plugin by default (removed in 9.0); an old client or driver that fails with `Authentication plugin 'caching_sha2_password' cannot be loaded` needs upgrading, or the server needs `mysql_native_password=ON` while you migrate. MariaDB defaults to `mysql_native_password` and uses `unix_socket` for `root@localhost` on distribution packages, which is why `sudo mysql` works without a password and `mysql -u root -p` does not. MariaDB 10.4+ also lets an account carry several authentication methods: `ALTER USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('...')`.

## Databases, tables and character sets

```sql
CREATE DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- MySQL 8 default collation
CREATE DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; -- MariaDB 10.10+; utf8mb4_unicode_ci on older
SHOW CREATE DATABASE my_db;
SELECT table_name, engine, table_rows, round((data_length + index_length)/1024/1024) AS mib, table_collation
FROM information_schema.tables WHERE table_schema = 'my_db' ORDER BY mib DESC;
SHOW CREATE TABLE my_db.orders\G
SHOW INDEX FROM my_db.orders;
SHOW TABLE STATUS FROM my_db LIKE 'orders'\G                             -- Auto_increment, Data_free, Update_time
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- rewrites the table
ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;           -- MySQL 8.0.12+/MariaDB 10.3+: metadata only
SELECT @@character_set_server, @@collation_server, @@character_set_connection;
```

`utf8` in MySQL and MariaDB means `utf8mb3`, three bytes per character, which rejects emoji and some CJK characters with `Incorrect string value`; always spell out `utf8mb4`. Every string column carries a collation, comparisons between different collations fail with `Illegal mix of collations`, and a collation ending in `_ci` is case-insensitive, `_bin` binary. InnoDB index keys are limited to 3072 bytes, so a `VARCHAR(255)` in `utf8mb4` (1020 bytes) fits but a composite of four does not; use prefix indexes (`INDEX (col(64))`) or shorter columns. Identifiers are case-sensitive on Linux by default (`lower_case_table_names=0`), so `Orders` and `orders` are different tables; set `lower_case_table_names=1` before initialising a server that must interoperate with Windows or macOS developers, because it cannot be changed afterwards.

## InnoDB essentials

InnoDB is the default and only sensible engine for general use. The table is stored as a B-tree clustered on the primary key: rows physically live in primary-key order, and every secondary index stores the primary key as the row pointer. So a long primary key (a UUID string) inflates every index, an insert with random keys splits pages everywhere, and a query through a secondary index does two lookups. Use a compact, increasing primary key (`BIGINT AUTO_INCREMENT`, or a time-ordered UUIDv7 stored as `BINARY(16)`) and always define one: without it InnoDB picks the first `UNIQUE NOT NULL` index or generates a hidden 6-byte key that replicas then cannot use for row lookups.

The buffer pool caches data and index pages and is the single most important setting: `innodb_buffer_pool_size` should hold the working set, typically 50 to 75% of RAM on a dedicated host. Writes go to the redo log (`innodb_redo_log_capacity`, MySQL 8.0.30+; `innodb_log_file_size` on MariaDB) first and are flushed to tablespaces later; `innodb_flush_log_at_trx_commit=1` fsyncs the redo log at every commit (durable, the default), `2` writes it and fsyncs once a second (loses up to a second on power loss, not on a process crash). Undo logs hold old row versions for MVCC and grow while a transaction stays open. Each table is its own `.ibd` file (`innodb_file_per_table=ON`), so a `DROP` or `OPTIMIZE` returns space to the filesystem; the shared `ibdata1` only ever grows.

```sql
SHOW ENGINE INNODB STATUS\G                    -- sections: SEMAPHORES, LATEST DETECTED DEADLOCK, TRANSACTIONS, BUFFER POOL AND MEMORY, ROW OPERATIONS
SELECT * FROM information_schema.INNODB_TRX WHERE trx_started < now() - INTERVAL 60 SECOND\G   -- long transactions
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';   -- read_requests vs reads: hit rate; reads should be a small fraction
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%'; -- free 0 and high 'dirty' means the pool is too small or checkpointing lags
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';           -- waits, time_avg, time_max
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;   -- online resize, MySQL 5.7+ and MariaDB 10.2+
OPTIMIZE TABLE my_db.orders;                    -- rebuilds the table online (ALGORITHM=INPLACE); reclaims Data_free after mass deletes
```

InnoDB's default isolation is `REPEATABLE READ`, implemented with a consistent snapshot for plain `SELECT` and with next-key locks (row plus the gap before it) for `UPDATE`, `DELETE`, `SELECT ... FOR UPDATE` and unique-key inserts. Gap locks are why an `UPDATE ... WHERE created_at < ?` on an unindexed column locks the whole table for the transaction's duration, and why range updates deadlock with inserts. Add the index, or run `READ COMMITTED`, which drops gap locking for most statements and is the level most application frameworks expect.

## EXPLAIN

```sql
EXPLAIN SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid' ORDER BY o.created_at DESC LIMIT 20;
EXPLAIN FORMAT=TREE SELECT ...;         -- MySQL 8.0.16+: the actual plan shape, nested
EXPLAIN ANALYZE SELECT ...;             -- MySQL 8.0.18+: runs the query; actual rows and time per node
ANALYZE FORMAT=JSON SELECT ...;         -- MariaDB: runs the query; r_rows and r_filtered are the measured values
EXPLAIN FOR CONNECTION 1234;            -- MySQL: plan of a statement currently running in another session
```

The tabular output, one row per table access in execution order:

| Column | Read it as |
| --- | --- |
| `type` | Access method, best to worst: `system`, `const` (one row by PK or unique), `eq_ref` (one row per outer row via unique key), `ref` (index equality, several rows), `range` (index range), `index` (full index scan), `ALL` (full table scan) |
| `possible_keys` / `key` | Indexes considered and the one chosen; `NULL` key with `ALL` on a large table is the problem row |
| `key_len` | Bytes of the index used: tells you how many columns of a composite index the predicate actually reached |
| `rows` | Estimated rows examined per outer row; multiply down the join |
| `filtered` | Estimated percentage of those rows that survive the `WHERE` after the index |
| `Extra` | `Using index` (covering: no table lookup), `Using where` (post-filter), `Using filesort` (sort not from an index), `Using temporary` (materialised intermediate), `Using index condition` (ICP), `Using join buffer` (no usable index on the inner table) |

`Using filesort` does not mean a file: it is any sort, in memory up to `sort_buffer_size`. It becomes a problem when `rows` is large, and the fix is an index that delivers the `ORDER BY` order, usually a composite of the equality columns followed by the sort column. Estimates come from index statistics, refreshed by `ANALYZE TABLE orders` (fast, sampled: `innodb_stats_persistent_sample_pages`), and a plan that changes between servers usually means the statistics differ. The optimiser can be steered with `FORCE INDEX (idx)` or MySQL 8 hints such as `/*+ INDEX(o orders_status_created) */`; treat that as a last resort that hides the missing index.

## Slow query log

```ini
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log     # directory must exist and be writable by mysql; SELinux label mysqld_log_t
long_query_time = 1                               # seconds, fractional allowed; 0 logs everything
log_queries_not_using_indexes = ON                # noisy on small tables; pair with min_examined_row_limit
min_examined_row_limit = 1000
log_slow_admin_statements = ON                    # ALTER, ANALYZE, OPTIMIZE
log_slow_verbosity = query_plan,explain           # MariaDB: adds the plan to each entry
```

```sh
mysql -e 'SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5;'   # live, until restart (SET PERSIST on MySQL to keep it)
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log       # top 10 by total time, literals normalised to N and 'S'
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log       # top 10 by count
pt-query-digest /var/log/mysql/slow.log | head -60     # Percona Toolkit: fingerprinted ranking with percentiles
mysql -e "SET GLOBAL log_output = 'TABLE'"             # log to mysql.slow_log instead of a file; queryable, slower
```

The log records statements after they finish, with `Query_time`, `Lock_time`, `Rows_sent` and `Rows_examined`; a high examined-to-sent ratio is the signature of a missing index. Rotate the file with `logrotate` and `FLUSH SLOW LOGS` (or `mysqladmin flush-logs slow`) in the `postrotate` script. On MySQL the performance schema digest table gives the same ranking without a file; see [Performance schema and process list](#performance-schema-and-process-list).

## Indexes

Index rules are those of every B-tree engine ([SQL: indexes](https://www.wiki.jodisand.me/sql/#indexes-and-reading-explain)): leftmost-prefix, equality columns before range columns, covering indexes avoid the row lookup. InnoDB specifics: every secondary index implicitly ends with the primary key, so `INDEX (customer_id)` already serves `WHERE customer_id = ? ORDER BY id`; and online DDL means most index changes do not block writes.

```sql
ALTER TABLE orders ADD INDEX orders_status_created (status, created_at), ALGORITHM=INPLACE, LOCK=NONE;   -- fails loudly if it cannot be done online
CREATE INDEX orders_customer ON orders (customer_id);                    -- same thing, defaults to online where possible
ALTER TABLE orders ADD UNIQUE INDEX orders_ref (reference);              -- duplicates present: ERROR 1062 with the offending value
ALTER TABLE orders ADD INDEX orders_note_prefix (note(32));              -- prefix index for long text
ALTER TABLE orders ADD INDEX orders_lower_email ((lower(email)));        -- MySQL 8.0.13+ functional index; MariaDB: index a generated column
ALTER TABLE orders ALTER INDEX orders_customer INVISIBLE;                -- MySQL 8: optimiser ignores it; watch for regressions, then DROP
ALTER TABLE orders ALTER INDEX orders_customer IGNORED;                  -- MariaDB 10.6+ equivalent
ALTER TABLE orders DROP INDEX orders_customer;
ANALYZE TABLE orders;                                                    -- refresh cardinality estimates

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'my_db';   -- MySQL and MariaDB 10.6+: no reads since server start
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'my_db'; -- indexes that are prefixes of others
SELECT object_name, index_name, count_read, count_write FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'my_db' ORDER BY count_read DESC;
```

`SHOW INDEX` reports `Cardinality`, the estimated distinct values; an index whose cardinality is close to 1 (a boolean flag) is rarely worth having on its own but often useful as the leading column of a composite with a range column. Foreign keys create an index automatically if none exists. Adding an index to a large table still copies it in the background and needs free disk space of the table's size in `tmpdir` or the data directory.

## Backups

`mysqldump` (MariaDB: `mariadb-dump`) writes SQL text. With `--single-transaction` it reads a consistent InnoDB snapshot without locking, which makes it safe on a live server, and it is the right tool for anything up to a few tens of gigabytes and for moving between versions and forks. Restores are slow because they replay every insert and rebuild every index.

```sh
# --set-gtid-purged=ON and --source-data=2 (MySQL) record the GTID set and binlog position as comments for seeding a replica; MariaDB: --gtid --master-data=2
mysqldump --single-transaction --quick --routines --triggers --events --set-gtid-purged=ON --source-data=2 my_db | gzip > "my_db-$(date +%F).sql.gz"
mysqldump --single-transaction --all-databases --routines --triggers --events | gzip > all.sql.gz
mysqldump --no-data my_db > schema.sql                      # DDL only
mysqldump --single-transaction my_db orders --where='created_at >= "2026-01-01"' > orders-2026.sql
gunzip -c my_db-2026-09-24.sql.gz | mysql my_db             # restore; create the database first for a single-database dump
mysql -e 'SET GLOBAL innodb_flush_log_at_trx_commit = 2'    # speeds up a large restore; set back to 1 afterwards
```

MySQL Shell's `util.dumpInstance()` and `util.loadDump()` parallelise both directions and are the supported path for large MySQL instances; Percona XtraBackup gives MySQL a physical backup. MariaDB ships `mariadb-backup` (formerly `mariabackup`), an XtraBackup fork that copies the InnoDB files while tracking redo, then applies the redo in a prepare step so the result is a consistent data directory. Physical backups restore in the time it takes to copy the files, which is what you want above a hundred gigabytes.

```sh
sudo mariadb-backup --backup --target-dir=/backup/full --user=backup --password="$BACKUP_PASSWORD"     # live; needs RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
sudo mariadb-backup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=backup --password="$BACKUP_PASSWORD"
sudo mariadb-backup --prepare --target-dir=/backup/full                                              # apply redo; makes the copy consistent
sudo mariadb-backup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1               # roll the incremental into the full
cat /backup/full/xtrabackup_binlog_info                                                              # binlog file, position and GTID at backup time

# restore: the server must be stopped and the data directory empty
sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.old                    # keep it until the restored server is verified
sudo mkdir /var/lib/mysql
sudo mariadb-backup --copy-back --target-dir=/backup/full    # --move-back to avoid a second copy
sudo chown -R mysql:mysql /var/lib/mysql && sudo restorecon -Rv /var/lib/mysql
sudo systemctl start mariadb
```

Store `--password` in a `[mariabackup]` or `[client]` section of a mode-600 option file rather than on the command line. A backup you have not restored is a hypothesis; the [Scripts](#scripts) section has a dump-and-verify job. Binary logs are the third component: with `binlog_expire_logs_seconds` long enough to bridge two backups, point-in-time recovery is a restore followed by `mysqlbinlog --start-position=... --stop-datetime='2026-09-24 03:12:00' binlog.000123 | mysql`.

> [!WARNING] mysqldump without --single-transaction
> Without it the default `--lock-tables` takes read locks table by table, so a large dump blocks writes for its duration and is still not consistent across tables. With MyISAM tables there is no alternative; with InnoDB there is no reason to omit it.

## Replication

Replication ships the primary's binary log to replicas, which replay it. Row-based logging (`binlog_format=ROW`, the default) replicates the changed rows; global transaction identifiers (GTIDs) let a replica find its position by transaction set rather than by file and offset, which makes failover and re-pointing safe. MySQL and MariaDB both have GTIDs, implemented differently and not interoperable: a MariaDB replica cannot follow a MySQL 8 primary or vice versa.

```ini
# primary, [mysqld]
server_id = 1                                # unique per server in the topology
log_bin = binlog
binlog_format = ROW
binlog_expire_logs_seconds = 604800          # 7 days; MariaDB: expire_logs_days = 7
gtid_mode = ON                               # MySQL only; MariaDB GTIDs are always on with log_bin
enforce_gtid_consistency = ON                # MySQL only
binlog_row_image = FULL                      # MINIMAL saves space but replicas then need a primary key on every table
sync_binlog = 1                              # durable binlog at every commit; pairs with innodb_flush_log_at_trx_commit = 1

# replica, [mysqld]
server_id = 2
log_bin = binlog                             # so the replica can itself be promoted or chained
log_replica_updates = ON                     # MySQL; MariaDB: log_slave_updates = ON
read_only = ON
super_read_only = ON                         # MySQL: also blocks users with SUPER; MariaDB has read_only only
replica_parallel_workers = 4                 # MySQL; MariaDB: slave_parallel_threads = 4, slave_parallel_mode = optimistic
gtid_mode = ON
enforce_gtid_consistency = ON
```

Seed the replica from a backup of the primary (a `mysqldump --source-data=2 --set-gtid-purged=ON` or a `mariadb-backup` copy), then point it at the primary:

```sql
-- MySQL 8.0.23+ (older releases: CHANGE MASTER TO MASTER_HOST=..., MASTER_AUTO_POSITION=1; START SLAVE)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'db1.example.com', SOURCE_PORT = 3306,
  SOURCE_USER = 'repl', SOURCE_PASSWORD = '...',
  SOURCE_SSL = 1,
  SOURCE_AUTO_POSITION = 1;                  -- GTID positioning; the dump's SET @@GLOBAL.gtid_purged line told the replica what it already has
START REPLICA;
SHOW REPLICA STATUS\G

-- MariaDB (10.5.1+ accepts REPLICA in place of SLAVE)
SET GLOBAL gtid_slave_pos = '0-1-123456';    -- from the dump header or mariadb-backup's xtrabackup_binlog_info
CHANGE MASTER TO
  master_host = 'db1.example.com', master_port = 3306,
  master_user = 'repl', master_password = '...',
  master_ssl = 1,
  master_use_gtid = slave_pos;
START REPLICA;
SHOW REPLICA STATUS\G
```

Fields that matter in `SHOW REPLICA STATUS`: `Replica_IO_Running` and `Replica_SQL_Running` both `Yes` (MariaDB still prints `Slave_IO_Running`); `Seconds_Behind_Source` (`Seconds_Behind_Master`), which is 0 when idle and `NULL` when a thread is stopped, not a reliable lag metric under continuous load because it measures the age of the event being applied; `Last_IO_Error` and `Last_SQL_Error`; `Retrieved_Gtid_Set` versus `Executed_Gtid_Set` on MySQL, `Gtid_IO_Pos` versus `gtid_slave_pos` on MariaDB, whose difference is the true backlog. `SHOW PROCESSLIST` on the primary lists one `Binlog Dump` thread per connected replica.

```sql
STOP REPLICA; START REPLICA;                              -- restart both threads
STOP REPLICA SQL_THREAD;                                  -- keep fetching, stop applying: freeze a replica for a report
SHOW BINARY LOGS; SHOW BINARY LOG STATUS;                 -- on the primary; SHOW MASTER STATUS before MySQL 8.4 / on MariaDB
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;          -- frees disk; every replica must already be past that point
SELECT * FROM performance_schema.replication_applier_status_by_worker\G   -- MySQL: per-worker errors and last applied transaction
RESET REPLICA ALL;                                        -- forget the primary entirely: on promotion of this replica
```

For a promotion: stop writes on the old primary (`SET GLOBAL super_read_only = ON`), wait until the replica's executed GTID set equals the primary's, `STOP REPLICA; RESET REPLICA ALL; SET GLOBAL read_only = OFF` on the new primary, then re-point the remaining replicas with a new `CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION = 1`. Tooling (Orchestrator, MySQL Shell's InnoDB ReplicaSet, MariaDB MaxScale) automates that dance and detects failure; hand-rolled failover scripts tend to promote two primaries.

## my.cnf essentials

Option files are read in order: `/etc/my.cnf`, `/etc/my.cnf.d/*.cnf` (Fedora and RHEL), `~/.my.cnf`; a later value wins. `mysqld --help --verbose | head -30` prints the exact search order for your build, `mysqld --validate-config` (MySQL) parses without starting, and `SELECT @@global.x` shows what the running server settled on. MySQL's `SET PERSIST` writes to `mysqld-auto.cnf` in the data directory and survives restarts; MariaDB has no equivalent, so every live change needs a matching edit in the file.

```ini
[mysqld]
bind_address = 10.0.0.5                     # not 0.0.0.0 unless the firewall does the work; MariaDB accepts a comma list from 10.11
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
skip_name_resolve = ON                      # grants must then use IPs or wildcards, not hostnames
max_connections = 500                       # each thread costs memory; pool in the application instead of raising this
max_allowed_packet = 64M                    # largest single statement or row; also set on the client
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci       # MariaDB: utf8mb4_uca1400_ai_ci
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
transaction_isolation = READ-COMMITTED      # fewer gap locks; REPEATABLE-READ is the default

innodb_buffer_pool_size = 12G               # 50-75% of RAM on a dedicated host
innodb_redo_log_capacity = 2G               # MySQL 8.0.30+; MariaDB: innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1          # 2 trades a second of durability for throughput
innodb_flush_method = O_DIRECT              # avoid double buffering through the page cache
innodb_io_capacity = 2000                   # background flushing rate; SSD-class values 1000-4000
innodb_io_capacity_max = 4000
innodb_print_all_deadlocks = ON             # every deadlock to the error log, not only the latest

tmp_table_size = 64M                        # in-memory temp table limit; both must be raised together
max_heap_table_size = 64M
table_open_cache = 4000
thread_cache_size = 50

log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

[client]
socket = /var/lib/mysql/mysql.sock
default_character_set = utf8mb4
```

`SET GLOBAL` changes apply to new sessions; `SET SESSION` (or plain `SET`) to the current one. Some variables are read-only at runtime (`datadir`, `innodb_page_size`, `lower_case_table_names`) and a few need `innodb_fast_shutdown=0` and a clean restart. The `systemd` unit (`mysqld.service` or `mariadb.service`) sets `LimitNOFILE`, which caps `open_files_limit` and therefore `table_open_cache`; raise it with a drop-in, see [systemd](https://www.wiki.jodisand.me/systemd/#overriding-a-packaged-unit).

## Performance schema and process list

`performance_schema` collects instrumentation in memory; the `sys` schema (MySQL 5.7+, MariaDB 10.6+) wraps it in readable views. Enabled by default on MySQL and off by default on MariaDB (`performance_schema = ON` in `my.cnf`, then restart). Statement digests normalise literals, so `sys.statement_analysis` is a slow-log ranking without the log.

```sql
SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, full_scan
FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;        -- since server start or last TRUNCATE
SELECT query, exec_count, rows_examined_avg FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;
SELECT * FROM sys.schema_table_statistics WHERE table_schema = 'my_db' LIMIT 10;   -- I/O per table
SELECT * FROM sys.user_summary;                                                    -- statements, latency and connections per user
SELECT * FROM sys.host_summary;
SELECT * FROM sys.session ORDER BY statement_latency DESC LIMIT 20;                -- richer PROCESSLIST with current statement and lock info
SELECT * FROM sys.innodb_lock_waits\G                                              -- MySQL: waiting_query, blocking_pid, sql_kill_blocking_query
SELECT * FROM performance_schema.data_locks WHERE object_name = 'orders';         -- MySQL 8: every lock InnoDB holds right now
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;                         -- MySQL: where memory goes
TRUNCATE performance_schema.events_statements_summary_by_digest;                  -- reset the statement ranking for a fresh window
```

```sql
SHOW FULL PROCESSLIST;                                                             -- Id, User, Host, db, Command, Time, State, Info
SELECT id, user, host, db, command, time, state, left(info, 100) AS query
FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC;
SELECT user, count(*) FROM information_schema.processlist GROUP BY user;           -- who is holding connections
KILL QUERY 1234;                                                                   -- stop the statement, keep the session
KILL 1234;                                                                         -- drop the session; the client sees a lost connection
KILL CONNECTION 1234;                                                              -- same; MariaDB also has KILL HARD / SOFT and KILL USER 'app'@'%'
```

Statement `Time` in the process list is seconds in the current state, not the total for the connection, and a `Sleep` connection with a high `Time` is an idle pooled connection, harmless unless there are thousands of them or it still holds an open transaction (`information_schema.INNODB_TRX` shows `trx_mysql_thread_id`). Metrics for dashboards come from `SHOW GLOBAL STATUS` through `mysqld_exporter` and the [Prometheus](https://www.wiki.jodisand.me/prometheus/) stack.

## Oneliners

```sh
# Server version, fork and uptime
mysql -e 'SELECT version(), @@version_comment; SHOW GLOBAL STATUS LIKE "Uptime"'

# Connections now versus the limit and the high-water mark
mysql -N -e "SELECT concat(v.Variable_value, ' running, max_used ', s.Variable_value, ', limit ', @@max_connections) FROM performance_schema.global_status v JOIN performance_schema.global_status s ON s.Variable_name='Max_used_connections' WHERE v.Variable_name='Threads_connected'"

# Active queries older than 10 seconds
mysql -e "SELECT id, user, time, state, left(info, 120) q FROM information_schema.processlist WHERE command <> 'Sleep' AND time > 10 ORDER BY time DESC"

# Kill every query of one user that has run longer than 5 minutes (prints the KILL statements first; pipe to mysql to run them)
mysql -N -e "SELECT concat('KILL QUERY ', id, ';') FROM information_schema.processlist WHERE user = 'report' AND time > 300 AND command <> 'Sleep'"

# Transactions open longer than a minute and their thread ids
mysql -e "SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state, trx_rows_locked, left(trx_query, 100) q FROM information_schema.INNODB_TRX WHERE trx_started < now() - INTERVAL 60 SECOND"

# The most recent deadlock
mysql -e 'SHOW ENGINE INNODB STATUS\G' | sed -n '/LATEST DETECTED DEADLOCK/,/^TRANSACTIONS/p'

# Buffer pool hit rate
mysql -N -e "SELECT round(100 - 100 * r.Variable_value / q.Variable_value, 2) FROM performance_schema.global_status r JOIN performance_schema.global_status q ON q.Variable_name = 'Innodb_buffer_pool_read_requests' WHERE r.Variable_name = 'Innodb_buffer_pool_reads'"

# Largest tables with fragmentation (Data_free) in MiB
mysql -e "SELECT table_schema, table_name, round((data_length+index_length)/1048576) mib, round(data_free/1048576) free_mib FROM information_schema.tables WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema') ORDER BY mib DESC LIMIT 15"

# Tables without a primary key (replication and locking hazard)
mysql -e "SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY' WHERE t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL AND t.table_schema NOT IN ('mysql','sys','performance_schema','information_schema')"

# Columns still using utf8mb3
mysql -e "SELECT table_schema, table_name, column_name, character_set_name FROM information_schema.columns WHERE character_set_name = 'utf8mb3' AND table_schema NOT IN ('mysql','sys','performance_schema','information_schema')"

# Top statements by total time since the counters were reset (MySQL, MariaDB 10.6+ with performance_schema on)
mysql -e "SELECT left(query, 90) q, exec_count, total_latency, avg_latency FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10"

# Unused indexes
mysql -e "SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN ('mysql','sys')"

# Replication lag and thread state, one line
mysql -e 'SHOW REPLICA STATUS\G' | grep -E 'Replica_(IO|SQL)_Running:|Seconds_Behind|Last_(IO|SQL)_Error'

# Binary log disk usage and retention
mysql -e 'SHOW BINARY LOGS' | awk 'NR>1 {s+=$2} END {printf "%.1f GiB in %d files\n", s/1073741824, NR-1}'; mysql -N -e 'SELECT @@binlog_expire_logs_seconds/86400'

# All effective server variables to a file for diffing between hosts
mysql -N -e 'SHOW GLOBAL VARIABLES' | sort > "vars-$(hostname).txt"

# Dump one table's schema and data, compressed, consistently
mysqldump --single-transaction --quick my_db orders | zstd -o orders.sql.zst

# Copy a database to another server without touching disk
mysqldump --single-transaction --routines --triggers my_db | mysql -h db2.example.com my_db

# Import a CSV (server-side file; needs FILE privilege and secure_file_priv to allow the path)
mysql -e "LOAD DATA INFILE '/var/lib/mysql-files/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES" my_db

# Import a CSV from the client machine instead
mysql --local-infile=1 -e "LOAD DATA LOCAL INFILE 'orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES" my_db

# Test a login exactly as the application would
mysql -h db.example.com -P 3306 -u app -p --ssl-mode=REQUIRED -e 'SELECT current_user(), @@hostname' my_db

# Accounts with no password or expired passwords
mysql -e "SELECT user, host, plugin, password_expired, account_locked FROM mysql.user WHERE authentication_string = '' OR password_expired = 'Y'"

# Grants of every account, for an audit file
mysql -N -e "SELECT concat('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user" | mysql -N | sed 's/$/;/' > grants.sql

# Reopen the slow log after logrotate renamed it
mysql -e 'FLUSH SLOW LOGS'
```

## Scripts

Dump every database to its own compressed file, verify each file parses by loading it into a scratch schema, and prune old dumps. Drops and recreates the scratch database `_verify` on every run.

```sh
#!/usr/bin/env bash
# usage: mysql-dump-verify.sh BACKUP_DIR [KEEP_DAYS]   credentials from ~/.my.cnf or --login-path via MYSQL_LOGIN_PATH
set -euo pipefail
dir=${1:?backup dir}; keep=${2:-14}
opts=(${MYSQL_LOGIN_PATH:+--login-path="$MYSQL_LOGIN_PATH"})
mkdir -p "$dir"; stamp=$(date +%Y%m%dT%H%M)

mapfile -t dbs < <(mysql "${opts[@]}" -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys|_verify)$')
for db in "${dbs[@]}"; do
  out="$dir/$db-$stamp.sql.zst"
  mysqldump "${opts[@]}" --single-transaction --quick --routines --triggers --events "$db" | zstd -q -o "$out"
  mysql "${opts[@]}" -e "DROP DATABASE IF EXISTS _verify; CREATE DATABASE _verify"
  if zstd -dc "$out" | mysql "${opts[@]}" _verify; then
    tables=$(mysql "${opts[@]}" -N -e "SELECT count(*) FROM information_schema.tables WHERE table_schema = '_verify'")
    printf '%-24s %8s  %s tables ok\n' "$db" "$(du -h "$out" | cut -f1)" "$tables"
  else
    printf '%-24s FAILED to load: %s\n' "$db" "$out" >&2; exit 1
  fi
done
mysql "${opts[@]}" -e "DROP DATABASE IF EXISTS _verify"
find "$dir" -name '*.sql.zst' -mtime +"$keep" -print -delete
```

Replication health check for a timer or monitoring hook: exits 1 when a thread is down, 2 when lag or GTID backlog exceeds a threshold, and prints the error text when present. Works on MySQL 8 and MariaDB.

```sh
#!/usr/bin/env bash
# usage: replica-check.sh [MAX_LAG_SECONDS]   default 30
set -euo pipefail
max=${1:-30}
status=$(mysql -e 'SHOW REPLICA STATUS\G' 2>/dev/null) || { echo 'cannot query replica status'; exit 1; }
[[ -n $status ]] || { echo 'not a replica'; exit 0; }
field() { awk -v k="$1" -F': ' '$1 ~ "^ *"k"$" {print $2; exit}' <<<"$status"; }

io=$(field 'Replica_IO_Running'); [[ -n $io ]] || io=$(field 'Slave_IO_Running')
sql=$(field 'Replica_SQL_Running'); [[ -n $sql ]] || sql=$(field 'Slave_SQL_Running')
lag=$(field 'Seconds_Behind_Source'); [[ -n $lag ]] || lag=$(field 'Seconds_Behind_Master')
err=$(field 'Last_SQL_Error'); [[ -n $err ]] || err=$(field 'Last_IO_Error')

src=$(field 'Source_Host'); [[ -n $src ]] || src=$(field 'Master_Host')

printf 'io=%s sql=%s lag=%s source=%s\n' "$io" "$sql" "${lag:-NULL}" "$src"
if [[ $io != Yes || $sql != Yes ]]; then
  printf 'replication thread down: %s\n' "${err:-no error text}" >&2; exit 1
fi
if [[ $lag == NULL || $lag -gt $max ]]; then
  printf 'lag %s exceeds %s seconds\n' "$lag" "$max" >&2; exit 2
fi
```

Report the blocking chain for lock waits so the on-call person can see who to kill. Read-only; prints `KILL` statements but does not run them.

```sh
#!/usr/bin/env bash
# usage: lock-waits.sh   (MySQL 8; on MariaDB use information_schema.INNODB_LOCK_WAITS joined to INNODB_TRX)
set -euo pipefail
mysql --table -e "
SELECT w.wait_age, w.locked_table, w.locked_type,
       w.waiting_pid, left(w.waiting_query, 60)  AS waiting_query,
       w.blocking_pid, w.blocking_trx_age, left(w.blocking_query, 60) AS blocking_query,
       w.sql_kill_blocking_connection
FROM sys.innodb_lock_waits w
ORDER BY w.wait_age DESC"
echo
echo 'transactions idle with an open transaction (blockers are often here, with no current query):'
mysql --table -e "
SELECT t.trx_mysql_thread_id AS pid, t.trx_started, timestampdiff(SECOND, t.trx_started, now()) AS age_s,
       t.trx_rows_locked, t.trx_rows_modified, p.user, p.host, p.command
FROM information_schema.INNODB_TRX t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
WHERE p.command = 'Sleep' AND t.trx_started < now() - INTERVAL 30 SECOND
ORDER BY t.trx_started"
```

## Troubleshooting

| Symptom | Cause | Fix |
| --- | --- | --- |
| `ERROR 1040: Too many connections` | Application pool leak, or `max_connections` too low for the pool sizes in play | Connect as an account with `CONNECTION_ADMIN` (MySQL reserves one extra slot; `admin_port` / MariaDB `extra_port` for emergencies); `SELECT user, host, count(*) FROM information_schema.processlist GROUP BY 1, 2`; fix the pool, then `SET GLOBAL max_connections` |
| `ERROR 1205: Lock wait timeout exceeded` | Another transaction holds the row (or gap) longer than `innodb_lock_wait_timeout` (50 s) | `sys.innodb_lock_waits` or `INNODB_LOCK_WAITS`; find the sleeping connection with an open transaction and fix the code that forgot to commit |
| `ERROR 1213: Deadlock found` | Two transactions locked rows in opposite order, or gap locks under `REPEATABLE READ` | Read `LATEST DETECTED DEADLOCK`; lock in a fixed order, shorten transactions, add the missing index, consider `READ-COMMITTED`; the application must retry |
| `Waiting for table metadata lock` piling up | DDL or `FLUSH TABLES` waiting behind an open transaction that touched the table | `INNODB_TRX` for the old transaction; kill it or wait; run DDL with `lock_wait_timeout` set low so it fails instead of blocking everyone |
| `ERROR 1045: Access denied for user` | Wrong password, wrong host part of the account, or a client that cannot do `caching_sha2_password` | `SHOW GRANTS FOR CURRENT_USER()` from a working session; `SELECT user, host, plugin FROM mysql.user`; check whether the client resolves to `localhost` or an IP; upgrade the driver |
| `ERROR 1698: Access denied for user 'root'@'localhost'` (MariaDB) | `unix_socket` authentication | `sudo mysql`, or grant a password auth method as well |
| `ERROR 2002: Can't connect to local server through socket` | Server down, or client and server disagree on the socket path | `systemctl status mariadb mysqld`; `SHOW VARIABLES LIKE 'socket'` versus `[client] socket` |
| `ERROR 2003: Can't connect to MySQL server on host` | `bind_address`, firewall, or the account only exists for `localhost` | `ss -ltnp \| grep 3306`; `firewall-cmd --list-all`; `SELECT host FROM mysql.user WHERE user='app'` |
| Disk full; server refuses writes or crashes | Binary logs, undo tablespaces, `ibtmp1`, the slow log or a runaway table | `du -sh /var/lib/mysql/*`; `PURGE BINARY LOGS BEFORE ...` (only past every replica's position); lower `binlog_expire_logs_seconds`; `OPTIMIZE TABLE` a table with large `Data_free`; `ibtmp1` shrinks only on restart |
| `ERROR 1114: The table is full` | `tmpdir` filesystem full during a sort or `ALTER`, or a MEMORY table hit `max_heap_table_size` | `df -h $(mysql -N -e 'SELECT @@tmpdir')`; move `tmpdir`; raise `tmp_table_size` and `max_heap_table_size` together |
| Replica lag grows and never recovers | Single-threaded apply, large transactions, tables without a primary key, slower replica hardware | `replica_parallel_workers` / `slave_parallel_threads`; add primary keys; split big `DELETE`s into batches; check `SHOW PROCESSLIST` on the replica for the applier's state |
| `Last_IO_Error: Got fatal error 1236 ... Could not find first log file name` | Primary purged binlogs the replica still needed | Re-seed the replica from a fresh backup; raise binlog retention |
| `Last_SQL_Error: ... Duplicate entry` on a replica | Writes went to the replica, or a reseed from an inconsistent dump | `super_read_only = ON`; if a single bad event, inject an empty transaction for that GTID (MySQL) or `SET GLOBAL sql_slave_skip_counter = 1` (non-GTID); otherwise re-seed |
| `Seconds_Behind_Source` is `NULL` | A replication thread is stopped | `SHOW REPLICA STATUS\G` for `Last_*_Error`; `START REPLICA` after fixing it |
| `ERROR 1071: Specified key was too long; max key length is 3072 bytes` | Composite index over long `utf8mb4` columns | Shorter columns, prefix index, or a hash column |
| `Illegal mix of collations` | Join or comparison between columns with different collations | Convert the columns, or `COLLATE` in the expression |
| `Incorrect string value: '\xF0\x9F...'` | Column or connection is `utf8mb3` | `CONVERT TO CHARACTER SET utf8mb4`; `SET NAMES utf8mb4` or `default_character_set` in the client |
| `The total number of locks exceeds the lock table size` | Enormous transaction with a tiny buffer pool | Batch the statement; raise `innodb_buffer_pool_size` |
| Server slow after restart | Cold buffer pool | `innodb_buffer_pool_dump_at_shutdown` and `innodb_buffer_pool_load_at_startup` (both default ON on MySQL 8) |
| `mysqld: Can't create/write to file` at start with SELinux enforcing | Non-default `datadir`, log or socket path without the `mysqld_db_t` label | `semanage fcontext -a -t mysqld_db_t '/srv/mysql(/.*)?'; restorecon -Rv /srv/mysql`; `semanage port -a -t mysqld_port_t -p tcp 3307` for a custom port; see [SELinux](https://www.wiki.jodisand.me/users/#selinux) |

For host-level pressure (I/O wait, swap, CPU steal) go to [Linux performance](https://www.wiki.jodisand.me/linux-performance/#the-first-minute) first; a database that was fine yesterday and is slow today with unchanged queries is usually an evicted buffer pool, a full disk or a noisy neighbour.

## Further reading

- [MySQL 8.4 Reference Manual](https://dev.mysql.com/doc/refman/8.4/en/)
- [MySQL: InnoDB locking and transaction model](https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-transaction-model.html)
- [MySQL: replication with GTIDs](https://dev.mysql.com/doc/refman/8.4/en/replication-gtids.html)
- [MariaDB Server documentation](https://mariadb.com/docs/server/)
- [MariaDB: mariadb-backup](https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup)
- [MariaDB: standard replication and GTIDs](https://mariadb.com/docs/server/ha-and-performance/standard-replication/gtid)