Skip to main content
🗄

MySQL / MariaDB

24 articles in this section

This section is about running MySQL and MariaDB on a real server: where to start tuning my.cnf for the memory you actually have, how indexes work and why a query without one falls back to a full table scan, and what the difference between InnoDB and MyISAM means in practice, not just in theory.

Backups get separate treatment: mysqldump for smaller databases, Percona XtraBackup for a hot copy that doesn't stop the server, point-in-time recovery from the binary log, and moving data when migrating from MySQL to MariaDB or upgrading from 5.7 to 8.0.

There's plenty on diagnostics too: the slow query log and EXPLAIN for slow queries, reading InnoDB deadlocks, the Too many connections error and connection leaks, and a growing ibdata1 file with ways to free up disk space.

  • Tuning my.cnf, indexes and Performance Schema
  • Backups: mysqldump, XtraBackup, binlog point-in-time recovery
  • Replication: Master-Slave, GTID and Galera Cluster
  • Diagnostics: slow queries, deadlocks, connection limits
MySQL/MariaDB Optimization: Tuning my.cnf my.cnf/my.ini configuration for MySQL and MariaDB optimization: innodb_buffer_pool_size, max_connections, thread settings. RAM-based calculation formulas. mysqldump: MySQL Database Backup and Restore Guide Complete mysqldump guide: backup single DB, all databases, structure only. Cron automation, gzip compression, restore from dump. Commands and examples. MySQL/MariaDB User Management and Access Privileges MySQL user management: CREATE USER, GRANT, REVOKE, DROP USER. Database, table and column-level permissions. View privileges with SHOW GRANTS. Reset MySQL/MariaDB Root Password: Step-by-Step Guide How to reset MySQL 8.0, MySQL 5.7 and MariaDB root password via --skip-grant-tables, ALTER USER, mysqladmin. Fix Access denied for user root error. MySQL Slow Query Log: Enable and Analyze Slow Queries Enable and analyze MySQL Slow Query Log: long_query_time, mysqldumpslow, pt-query-digest. Find and optimize slow SQL queries on your VPS. MySQL Indexes: Types, Creation and Query Optimization MySQL index types: PRIMARY KEY, UNIQUE, INDEX, FULLTEXT, COMPOSITE. Create indexes, analyze with EXPLAIN, optimize queries. When indexes hurt performance. MySQL Master-Slave Replication: Setup and Monitoring MySQL Master-Slave replication setup: binlog, server-id, CHANGE MASTER TO. Monitor with SHOW SLAVE STATUS. Step-by-step VPS replication guide. Import Large MySQL Database: Optimization and Speed Tips Speed up large MySQL dump import: max_allowed_packet, innodb_buffer_pool_size, disable autocommit, mydumper/myloader. Import large .sql.gz file on VPS. Migrate from MySQL to MariaDB: Step-by-Step Guide Migrate from MySQL to MariaDB: export data, install MariaDB, import, verify compatibility. MySQL 8.0 vs MariaDB 10.x differences on VPS. MariaDB Galera Cluster: 3-Node High-Availability Setup MariaDB Galera Cluster 3-node setup: wsrep_cluster_address, bootstrap with --wsrep-new-cluster, monitor with wsrep_cluster_size. MySQL utf8mb4 Charset: Full Unicode and Emoji Support Setup Configure utf8mb4 charset in MySQL and MariaDB: change database, table and column charset/collation. Enable emoji and full 4-byte Unicode support in MySQL. Hot MySQL Backup with Percona XtraBackup Create hot MySQL backups without stopping the server using Percona XtraBackup. Full and incremental backups, restore procedures. MySQL Performance Schema: Monitoring Server Bottlenecks Using MySQL Performance Schema to find slow queries, analyze locks, and monitor memory and I/O usage without external tools. MySQL JSON Fields: Storing and Querying JSON Data Working with MySQL 5.7+ JSON data type. Creating fields, ->, ->>, JSON_EXTRACT operators, JSON indexes, JSON_SET and JSON_ARRAY functions. InnoDB vs MyISAM in MySQL: differences and choice InnoDB vs MyISAM compared: transactions, row locks, crash recovery. A differences table and commands to switch a table storage engine. EXPLAIN and EXPLAIN ANALYZE: reading a MySQL query plan A guide to MySQL EXPLAIN columns: type, key, rows, Extra. How EXPLAIN ANALYZE differs and the signs of a slow query inside the execution plan. MySQL table partitioning: when it actually helps MySQL RANGE and HASH partitioning explained: when it speeds queries up and when it only adds complexity. Examples of creating and dropping partitions. MySQL binlog: point-in-time recovery step by step How to enable MySQL binlog and restore a database to the moment before a failure: a full backup plus mysqlbinlog, positions, and timestamps. MySQL GTID replication: setup and master failover MySQL GTID replication explained: how it differs from binlog coordinates, how to configure it, and how to fail a replica over to a new master. ProxySQL: Splitting Reads and Writes Across Servers How ProxySQL splits reads and writes between MySQL servers: installation, hostgroups, query rules, and checking the balancing. InnoDB Deadlocks: Diagnosing and Fixing Lock Conflicts Why InnoDB deadlocks happen, how to read SHOW ENGINE INNODB STATUS, and which coding rules reduce the number of lock conflicts. Too Many Connections in MySQL: Limits and Connection Leaks What the Too many connections error in MySQL means, how to check the max_connections limit, and find connection leaks in code. Upgrading MySQL 5.7 to 8.0: Steps to Take and Pitfalls How to upgrade MySQL 5.7 to version 8.0 without losing data: compatibility checks, command order, and common upgrade pitfalls. ibdata1 Growing? How to Free Up Disk Space in MySQL Why the ibdata1 file grows in MySQL, how to check what is taking up space, and how to safely shrink the shared tablespace file.