MySQL guide

What MySQL and MariaDB do in production

MySQL stores relational data in databases with InnoDB as the default transactional storage engine. Applications connect over TCP (port 3306) using the mysql client or drivers. MariaDB maintains high compatibility with MySQL — most commands, replication concepts, and troubleshooting steps apply to both. On Debian/Ubuntu, mariadb-server is often the default package; RHEL and cloud images may ship Oracle MySQL or MariaDB.

How a client connection works

When an application connects, the typical workflow is:

1. TCP connect — client reaches host:3306
2. Authentication — server validates user/host against the mysql.user privilege tables
3. Session — a thread is assigned; SQL mode and charset are negotiated
4. Query execution — SQL is parsed, optimized, executed; InnoDB handles transactions and row locks
5. Result — result set returns to the client; connection may stay open in a pool

Key files and configuration

• Config — /etc/mysql/my.cnf, /etc/my.cnf, or /etc/mysql/mariadb.conf.d/
• Data directory — /var/lib/mysql/
• Logs — error log (often /var/log/mysql/error.log), slow query log if enabled
• Socket — /var/run/mysqld/mysqld.sock for local connections

Users, privileges, and storage

Access is granted per user@host (e.g. 'app'@'10.%'). Use least privilege — avoid GRANT ALL for application accounts. InnoDB supports ACID transactions, row-level locking, and crash recovery via the redo log. Monitor SHOW ENGINE INNODB STATUS for deep diagnostics.

Monitoring essentials

• SHOW PROCESSLIST / performance_schema — active queries
• SHOW ENGINE INNODB STATUS — locks, deadlocks, buffer pool
• information_schema — metadata; table sizes via information_schema.tables
• Slow query log — queries exceeding long_query_time

Replication

MySQL/MariaDB replication is primarily binlog-based: the primary writes changes to the binary log; replicas connect as slaves and replay events. MariaDB additionally offers Galera synchronous multi-primary clustering (WSREP) for a different HA model.

Asynchronous replication (traditional)

One primary (source) accepts writes; one or more replicas apply binlog events read-only. Replication is asynchronous by default — the primary does not wait for replicas to confirm.

Basic setup (primary):

• Unique server-id on each server
• log_bin enabled; binlog_format=ROW recommended
• Replication user: CREATE USER 'repl'@'%' IDENTIFIED BY '...'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
• Note coordinates: SHOW MASTER STATUS (file + position) or enable GTID

Basic setup (replica): seed with backup (mysqldump --source-data or Percona XtraBackup), then: CHANGE REPLICATION SOURCE TO SOURCE_HOST='...', SOURCE_USER='repl', ...; (older syntax: CHANGE MASTER TO ...). Start with START REPLICA; (START SLAVE; on older versions).

Advantages: mature, well understood; read scaling from replicas; simple primary/replica topology; works across MySQL and MariaDB with care.
Disadvantages: asynchronous lag and potential data loss on failover; replication errors can stop the replica thread; DDL can be tricky with statement-based binlog.

GTID replication

Global Transaction IDs tag every transaction. Failover and replica setup are simpler — no manual file/position matching. Enable with gtid_mode=ON and enforce_gtid_consistency=ON on all nodes.

Advantages: easier failover and replica provisioning; clearer tracking of applied transactions.
Disadvantages: all nodes must support GTID; some edge cases with non-transactional engines or certain admin operations.

Semi-synchronous replication

Primary waits for at least one replica to acknowledge receipt of the binlog event before committing (plugin: rpl_semi_sync_source). Reduces data loss risk vs pure async; adds latency.

Group Replication and Galera (brief)

MySQL Group Replication — multi-node consensus cluster (InnoDB Cluster); automatic membership and failover; more complex to operate.
MariaDB Galera Cluster — synchronous multi-primary via WSREP; writes propagate to all nodes before commit; good for HA but requires odd node counts and attention to schema design (primary keys required).

Monitor async/GTID replicas with SHOW REPLICA STATUS\G — watch Replica_IO_Running, Replica_SQL_Running, and Seconds_Behind_Source.

Backups

Replicas protect availability but not logical mistakes — a DROP DATABASE on the primary propagates to replicas. Maintain independent backups and test restores.

Logical backup (mysqldump) — exports SQL statements for schemas and data. For InnoDB, use --single-transaction for a consistent snapshot without locking tables. Simple and portable; slow on very large databases. Restore with mysql dbname < dump.sql.

Physical hot backup — mariabackup (MariaDB) or Percona XtraBackup (MySQL) copy InnoDB data files while the server runs, with redo log consistency. Faster for large datasets; required for some replication seeding workflows.

Point-in-time recovery (PITR) — enable log_bin and retain binlog files. Restore a full backup, then replay binlogs to a target time with mysqlbinlog before the accidental event.

Do not copy /var/lib/mysql/ while mysqld is writing without a hot-backup tool — partial pages produce corruption. Schedule backups during low traffic or use hot-backup utilities; verify with a test restore.

Learning resources

• MySQL documentation — dev.mysql.com/doc
• MariaDB documentation — mariadb.com/kb
• Replication — dev.mysql.com — replication
• GTID — dev.mysql.com — GTIDs
• Backup and recovery — dev.mysql.com — backup and recovery