MySQL & MariaDB

01InnoDB: how MySQL stores data

InnoDB is the default storage engine and the only one you should use for application data. It stores each table as a clustered index: rows are physically ordered by the primary key inside a B-tree. Secondary indexes store the indexed columns plus the primary key, and use it to find the full row. Two consequences follow. A short, ever-increasing primary key (bigint) keeps every index small and inserts cheap, while random UUIDs cause page splits and bloat. And a lookup through a secondary index costs two searches unless the index covers all needed columns. InnoDB also keeps a redo log for crash recovery, an undo log for rollbacks and consistent reads, and a buffer pool that caches pages in memory.

• innodb_buffer_pool_size: about 50 to 70% of RAM on a dedicated database server.
• Every table needs an explicit primary key.
• Use utf8mb4 everywhere; the old utf8 cannot store emojis or some scripts.

02Indexes and EXPLAIN

MySQL indexes are B-trees, and the leftmost-prefix rule applies: an index on (status, created_at) helps WHERE status = 'paid' and WHERE status = 'paid' AND created_at > ..., but not created_at alone. A covering index contains every column the query needs, so MySQL never touches the table; EXPLAIN shows 'Using index'. Functions on columns, like WHERE DATE(created_at) = ..., prevent index use; rewrite them as ranges. Leading wildcards (LIKE '%term') cannot use a B-tree, so use FULLTEXT or a search engine. Use EXPLAIN to see the plan and EXPLAIN ANALYZE (MySQL 8) to see real timings. Watch the type column: const and ref are good, ALL means a full table scan.

1ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
2 
3EXPLAIN ANALYZE
4SELECT id, total_minor
5FROM orders
6WHERE status = 'paid'
7  AND created_at >= '2026-10-01'
8  AND created_at <  '2026-11-01';

03Transactions and locking

A transaction groups statements so they all succeed or all fail (ACID: atomic, consistent, isolated, durable). InnoDB's default isolation level is REPEATABLE READ: within a transaction you see a consistent snapshot. Plain SELECTs never block writers, thanks to MVCC (multi-version concurrency control). When you need to read a row and then update it safely, for example stock or a wallet balance, lock it with SELECT ... FOR UPDATE, or update it atomically in one statement with a condition. Keep transactions short: long ones hold locks, block others and grow the undo log. Deadlocks happen when two transactions lock rows in opposite order; InnoDB detects them and rolls one back, so your code should retry.

1START TRANSACTION;
2 
3SELECT stock FROM variants WHERE id = 42 FOR UPDATE;
4 
5UPDATE variants SET stock = stock - 2
6WHERE id = 42 AND stock >= 2;   -- 0 rows affected = out of stock
7 
8INSERT INTO order_items (order_id, variant_id, qty, unit_price_minor)
9VALUES (1001, 42, 2, 2490);
10 
11COMMIT;

04Users and privileges

MySQL accounts are user plus host: 'app'@'localhost' and 'app'@'%' are different accounts. Follow least privilege. The application user gets only data rights (SELECT, INSERT, UPDATE, DELETE) on its own database. A separate migration user gets schema rights (CREATE, ALTER, INDEX, DROP), and backups get their own read-only account. Never let applications connect as root. Since MySQL 8.4 the old mysql_native_password plugin is disabled by default; caching_sha2_password is the default and works with current drivers. Bind the server to 127.0.0.1 or a private network address with bind-address, and keep port 3306 closed in the firewall. Use TLS if connections cross a network.

1CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'use-a-long-random-secret';
2GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
3 
4CREATE USER 'shop_migrate'@'localhost' IDENTIFIED BY 'another-long-secret';
5GRANT ALL PRIVILEGES ON shop.* TO 'shop_migrate'@'localhost';
6 
7SHOW GRANTS FOR 'shop_app'@'localhost';

05The slow query log

The slow query log records every statement that takes longer than long_query_time seconds. It is the fastest way to find what actually hurts in production, instead of guessing. Turn it on with a threshold of around half a second to one second, optionally log queries that do not use indexes on a staging copy, and then summarise the file. mysqldumpslow ships with MySQL; pt-query-digest from Percona Toolkit gives a better report grouped by query fingerprint, showing which queries consume the most total time. Fix the top few, usually with an index or a rewritten query, then repeat. The performance_schema and sys schema offer the same insight live, for example sys.statements_with_runtimes_in_95th_percentile.

1# /etc/mysql/mysql.conf.d/mysqld.cnf
2[mysqld]
3slow_query_log      = ON
4slow_query_log_file = /var/log/mysql/slow.log
5long_query_time     = 0.5
6 
7# then summarise:
8# sudo pt-query-digest /var/log/mysql/slow.log | less

06Replication basics

Replication copies changes from a source server to one or more replicas. The source writes every change to its binary log (binlog); each replica pulls the binlog and replays it. Use row-based format (the default) and GTIDs (global transaction ids), which make failover and re-pointing replicas much simpler. Replication is asynchronous by default, so a replica can lag behind; reads from it may be slightly stale, which matters for 'read your own write' flows like showing an order right after checkout. Replicas are useful for read scaling, reporting and as a warm standby. Replication is not a backup: a DROP TABLE or bad UPDATE is replicated instantly. Since MySQL 8.4 the terms are source and replica, with commands like SHOW REPLICA STATUS.

07Backups, and MySQL vs MariaDB

There are two kinds of backup. Logical backups, made with mysqldump or MySQL Shell's dump utilities, export SQL or data files; they are portable across versions and easy to restore partially, but slow for large databases. Use --single-transaction for a consistent InnoDB snapshot without locking. Physical backups copy the data files themselves, using Percona XtraBackup for MySQL or mariadb-backup for MariaDB; they are much faster to take and restore for big databases and support incremental backups. Keep binlogs to allow point-in-time recovery, store copies off the server, and test restores regularly. MariaDB forked from MySQL in 2009. It is still close for everyday SQL, but the two have diverged: JSON, authentication, replication and optimiser features differ, so treat them as separate products and test before switching.

1# consistent logical backup, compressed, off to another location
2mysqldump --single-transaction --routines --triggers --events \
3  -u backup_user -p shop | gzip > shop-$(date +%F).sql.gz
4 
5# restore into a test database to prove the backup works
6gunzip < shop-2026-11-05.sql.gz | mysql -u root -p shop_restore_test

08Memory and durability: buffer pool, redo log and flush settings

Most MySQL tuning comes down to a few InnoDB settings. The buffer pool caches data and index pages; on a dedicated database server it usually gets 50 to 75 percent of RAM, and the goal is that the hot working set fits so reads rarely touch disk. innodb_dedicated_server = ON sizes the buffer pool and redo log automatically from the machine's memory, which is a good default on a host that runs only MySQL. The redo log records changes before data pages are written; since MySQL 8.0.30 its size is one setting, innodb_redo_log_capacity, and a log that is too small forces constant flushing under write load. Durability is a trade-off you choose explicitly. innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 flush on every commit and survive power loss without losing committed transactions; setting them to 2 and 0 is faster but can lose about a second of commits in a crash. Keep 1 and 1 for anything involving money.

1# /etc/mysql/mysql.conf.d/mysqld.cnf  (dedicated DB host, 16 GB RAM)
2[mysqld]
3innodb_buffer_pool_size        = 10G
4innodb_redo_log_capacity       = 4G
5innodb_flush_log_at_trx_commit = 1     # full durability
6sync_binlog                    = 1
7max_connections                = 300   # each connection also uses memory
8binlog_expire_logs_seconds     = 604800  # keep 7 days for point-in-time recovery
9 
10# check how often reads miss the buffer pool:
11# SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

09Online schema changes: INSTANT, INPLACE and metadata locks

ALTER TABLE in MySQL 8 has three algorithms. INSTANT changes only metadata and finishes in milliseconds regardless of table size; since 8.0.29 it can add or drop columns at any position, and it can also rename columns and change defaults. INPLACE rebuilds or modifies the table without blocking reads and writes for many operations, such as adding a secondary index with LOCK=NONE, but it still takes time and I/O, and on replicas the change is replayed afterwards, causing lag. COPY rebuilds the whole table and blocks writes. State the algorithm explicitly; MySQL then refuses instead of silently falling back to something slower. The hidden danger is the metadata lock. Every ALTER needs a brief exclusive metadata lock at start and end. If a long transaction is open on the table, the ALTER waits, and every new query on that table queues behind the ALTER: the site appears frozen. Set a short lock_wait_timeout and retry. For huge tables or risky changes, gh-ost or pt-online-schema-change copy the table in the background in throttled chunks.

1SET SESSION lock_wait_timeout = 5;   -- give up quickly instead of freezing traffic
2 
3ALTER TABLE orders ADD COLUMN gift_note VARCHAR(255) NULL, ALGORITHM=INSTANT;
4 
5ALTER TABLE orders ADD INDEX idx_orders_customer (customer_id, created_at),
6  ALGORITHM=INPLACE, LOCK=NONE;
7 
8-- who is holding or waiting for metadata locks?
9SELECT * FROM sys.schema_table_lock_waits\G

10Locking internals and deadlock analysis

InnoDB locks index records, not rows in the abstract, and that explains most surprises. A locking read or an UPDATE locks every index record it scans, not just the ones it changes, so an UPDATE whose WHERE clause cannot use an index can lock a large part of the table. Under REPEATABLE READ, InnoDB also takes gap and next-key locks on ranges to prevent phantoms, which means an INSERT can wait on a range another transaction scanned. Practical rules: make sure UPDATE and DELETE statements filter on an index; touch rows in a consistent order, for example sorted by primary key, so two transactions cannot lock in opposite orders; and keep transactions short. When deadlocks happen, InnoDB rolls back the cheaper transaction with error 1213, and the application should simply retry the whole transaction. To understand one, read the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS, or set innodb_print_all_deadlocks = ON to log every deadlock. Live waits are visible in performance_schema.data_locks and sys.innodb_lock_waits.

1-- who blocks whom right now
2SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age
3FROM sys.innodb_lock_waits;
4 
5-- last deadlock, with both transactions and the locks they held
6SHOW ENGINE INNODB STATUS\G
7 
8-- log every deadlock to the error log
9SET PERSIST innodb_print_all_deadlocks = ON;

11High availability: parallel replication, semi-sync and clusters

Basic asynchronous replication can lose the last transactions if the source dies, and a replica can fall behind under heavy writes. Several features address this. Multi-threaded replication applies transactions in parallel on the replica; replica_parallel_workers defaults to 4 since 8.0.27 and replica_preserve_commit_order keeps the commit order safe. Semi-synchronous replication makes the source wait until at least one replica has received each transaction before confirming the commit, trading a little latency for not losing acknowledged writes on failover. For automatic failover, MySQL InnoDB Cluster combines Group Replication, a consensus protocol across three or more nodes, with MySQL Router, which sends connections to the current primary. MariaDB users often choose Galera Cluster instead. Whatever you choose, monitor lag (SHOW REPLICA STATUS, or better a heartbeat table), avoid huge single transactions that replicas must replay serially, and route read-your-own-write requests to the primary.

• Batch big UPDATE or DELETE jobs into chunks of a few thousand rows to keep replicas close.
• A primary key on every table is required for Group Replication and good for row-based replication.
• Rehearse failover; a cluster nobody has failed over is an untested assumption.