MySQL and MariaDB database administration for web application

MySQL/MariaDB Database Administration for Web Applications

MySQL and MariaDB are the most common stack for PHP-based web applications. LAMP projects, WordPress, Magento, Laravel with MySQL — all require systematic maintenance. Without it: fragmented MyISAM tables, overflowing binary logs, slow queries without indexes, unoptimized InnoDB buffer pool.

Initial Audit

-- Version and default engine
SELECT VERSION();
SHOW ENGINES;

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

-- Table fragmentation (Data_free > 0 — can be optimized)
SELECT table_schema, table_name, engine,
       ROUND(data_length / 1024 / 1024, 1)  AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       ROUND(data_free / 1024 / 1024, 1)    AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
  AND data_free > 0
ORDER BY data_free DESC
LIMIT 20;

InnoDB: Key Parameters

# /etc/mysql/conf.d/optimized.cnf
[mysqld]
# Buffer pool — allocate 70-80% RAM on dedicated server
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4  # one per gigabyte

# Log file size — larger = less frequent checkpoints, higher write performance
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M

# Synchronization: 2 = safe on power loss, but O_DSYNC
innodb_flush_log_at_trx_commit = 1  # 1 — full ACID, 2 — slightly faster

# File per table — convenient for backups and OPTIMIZE
innodb_file_per_table = ON

# Parallelism
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_io_capacity      = 2000  # for SSD
innodb_io_capacity_max  = 4000

Managing Binary Log

Binary log is needed for replication and PITR. Without rotation it fills disk space:

[mysqld]
log_bin            = /var/log/mysql/mysql-bin
binlog_format      = ROW       # ROW is safer than STATEMENT for replication
expire_logs_days   = 7         # MySQL 5.7
binlog_expire_logs_seconds = 604800  # MySQL 8.0+
max_binlog_size    = 100M

Manual cleanup:

-- View current binlog files
SHOW BINARY LOGS;

-- Delete older than specific date
PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00';
-- or
PURGE BINARY LOGS TO 'mysql-bin.000150';

Backup

mysqldump — for most projects:

# Hot backup of InnoDB with consistent snapshot
mysqldump \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --flush-logs \
  -u backup -p'password' mydb \
  | gzip > /backups/mydb_$(date +%Y%m%d_%H%M).sql.gz

--single-transaction opens a transaction for InnoDB — backup without table locks. For MyISAM requires --lock-tables.

Percona XtraBackup — physical backup for large databases (>10 GB) without performance impact:

# Full backup
xtrabackup --backup \
  --user=backup --password='password' \
  --target-dir=/backups/full_$(date +%Y%m%d)

# Prepare for recovery
xtrabackup --prepare --target-dir=/backups/full_20250101

# Incremental (from last full backup)
xtrabackup --backup --incremental-basedir=/backups/full_20250101 \
  --target-dir=/backups/incr_$(date +%Y%m%d)

Master-Replica Replication

# master: my.cnf
[mysqld]
server-id = 1
log_bin   = /var/log/mysql/mysql-bin

-- On master: create user for replication
CREATE USER 'replication'@'10.0.0.2' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.0.0.2';
FLUSH PRIVILEGES;

-- Take snapshot for replica initialization
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; -- remember File and Position
-- (in another session execute mysqldump)
UNLOCK TABLES;

# replica: my.cnf
[mysqld]
server-id        = 2
relay_log        = /var/log/mysql/mysql-relay
read_only        = ON
super_read_only  = ON  # MySQL 5.7+

-- On replica after dump restore
CHANGE MASTER TO
  MASTER_HOST     = '10.0.0.1',
  MASTER_USER     = 'replication',
  MASTER_PASSWORD = 'strong_password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS  = 12345;
START SLAVE;
SHOW SLAVE STATUS\G

ProxySQL: Connection Management and Routing

ProxySQL sits between application and MySQL: separates READ/WRITE queries, limits connection pool:

-- In ProxySQL console (port 6032)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (1, '10.0.0.1', 3306, 1000),  -- hostgroup 1 = writer
  (2, '10.0.0.2', 3306, 1000);  -- hostgroup 2 = reader

-- Rules: SELECT to reader, everything else to writer
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup) VALUES
  (1, 1, '^SELECT.*FOR UPDATE',  1),
  (2, 1, '^SELECT',               2);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

OPTIMIZE TABLE and Defragmentation

-- Defragment specific table (locks, run during maintenance window)
OPTIMIZE TABLE orders;

-- Check and repair for MyISAM
CHECK TABLE old_table;
REPAIR TABLE old_table;

For InnoDB OPTIMIZE TABLE recreates the table — similar to VACUUM FULL in PostgreSQL. Takes long on large tables. Alternative without locking:

# pt-online-schema-change from Percona Toolkit
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --execute \
  D=mydb,t=orders \
  u=root,p=password,h=localhost

Monitoring via performance_schema

-- Top slow queries
SELECT digest_text,
       count_star,
       ROUND(avg_timer_wait / 1e12, 3)  AS avg_sec,
       ROUND(sum_timer_wait / 1e12, 3)  AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'mydb'
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Current locks
SELECT waiting_trx_id, blocking_trx_id,
       waiting_query, blocking_query
FROM sys.innodb_lock_waits;

Regular Tasks