Page 6 - MySQL

Setting up a new replica for a large Magento database used to mean either an hours-long mysqldump import or reaching for an external tool like xtrabackup. Since MySQL 8.0.17, the built-in clone plugin handles this task directly inside the server: a physical, page-based copy process that turns hours into minutes on databases hundreds of gigabytes in size.

Log tables like report_event, customer_visitor, and catalog_compare_item log visitor behavior and grow with every page view, often faster than any other table group in the store. Once you configure built-in log cleaning correctly and build custom scripts for tables without automatic cleanup, you keep the database lean without giving up important reporting data.

A Docker Compose project with a PHP container, MySQL, Redis, and Nginx is up and running quickly, but fully integrating PHPStorm so that the interpreter, debugger, database data source, and Composer all work from within the IDE requires targeted configuration in several places.

EXPLAIN shows what the optimizer plans, EXPLAIN ANALYZE shows what actually happened. Anyone who correctly interprets type, key, rows and Extra, and compares estimated rows against real execution statistics, finds full table scans and filesorts before they become a problem in production.

Native online DDL does not solve every problem on large, write-heavy tables. Once MySQL's internal row log hits its capacity limit, or an operation requires a full table copy anyway, an external tool is often the only option left. pt-online-schema-change from the Percona Toolkit solves exactly this problem with a trigger-based shadow table approach that has been running in production databases hundreds of gigabytes in size for many years.

A server change, a hosting provider switch, or consolidating multiple Docker hosts eventually requires moving a running database container from one machine to another. Depending on data volume, downtime tolerance, and network connectivity between hosts, logical dumps, raw volume copies via rsync, or a replication cutover each suit different scenarios, with their own pitfalls around consistency and integrity.

A good Magento workflow depends heavily on using the right commands. In a Mark Shust setup, the wrappers in the `bin/` directory are the clean way to handle Magento, Composer, logs, debugging, the database and code quality.

At some point, even a well-tuned single MySQL server runs into limits, whether in data volume, write load, or concurrent connection count. Vitess addresses this by placing a proxy layer in front of the actual MySQL instances that hides sharding transparently: the application keeps connecting as usual over the MySQL protocol and, ideally, never notices that data is spread across multiple shards. Whether this approach makes sense depends heavily on actual data size and traffic scale, because the complexity Vitess brings is considerable.

The character set utf8 in MySQL is a historical trap that allows at most three bytes per character, silently dropping or rejecting emoji and many rare scripts. utf8mb4 stores full Unicode completely but requires the right collation choice and a carefully planned migration so index lengths and sort orders do not break.

The Performance Schema measures what actually happens inside a running MySQL instance: which statements run how often and for how long, which waits they get stuck on, and which stage consumes their time. Anyone who wants to find expensive query patterns in a Magento store systematically instead of by guesswork cannot avoid this instrumentation.