MySQL in Magento

Why InnoDB?

Magento requires the InnoDB storage engine for all database tables. InnoDB provides ACID compliance, row-level locking, and foreign key support — all critical for an e-commerce platform.

-- Magento's core tables use InnoDB
CREATE TABLE catalog_product_entity (
    entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
    attribute_set_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
    type_id VARCHAR(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
    sku VARCHAR(255) DEFAULT NULL COMMENT 'SKU',
    has_options SMALLINT NOT NULL COMMENT 'Has Options',
    required_options SMALLINT NOT NULL COMMENT 'Required Options',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
    PRIMARY KEY (entity_id),
    INDEX IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID (attribute_set_id),
    INDEX IDX_SKU (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Catalog Product Entity';

Key InnoDB features Magento relies on:

• ACID transactions: Order placement, inventory updates
• Row-level locking: Concurrent product updates without table locks
• Foreign keys: Referential integrity between tables
• Crash recovery: Automatic recovery from server crashes

Character Set Configuration

Magento 2 requires utf8mb4 character set for full Unicode support including emoji:

-- Check current character set settings
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Magento database should use utf8mb4
CREATE DATABASE magento_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Table-level character set
CREATE TABLE customer_entity (
    entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (entity_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

Collation Choices

<!-- env.php database configuration -->
<?php
return [
    'db' => [
        'table_prefix' => '',
        'connection' => [
            'default' => [
                'host' => 'localhost',
                'dbname' => 'magento_db',
                'username' => 'magento_user',
                'password' => 'password',
                'active' => '1',
                'driver_options' => [
                    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
                ]
            ]
        ]
    ]
];

The SET NAMES utf8mb4 command ensures proper character encoding between PHP and MySQL.

Recommended MySQL Settings

my.cnf Configuration

[mysqld]
# InnoDB settings (critical for Magento)
innodb_buffer_pool_size = 1G          # 50-70% of available RAM
innodb_log_file_size = 256M            # Reduces checkpoint flushing
innodb_flush_log_at_trx_commit = 1     # Full ACID compliance
innodb_flush_method = O_DIRECT         # Bypass OS cache
innodb_file_per_table = ON             # Separate tablespaces

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Connection settings
max_connections = 200
max_allowed_packet = 64M
wait_timeout = 600

# Query cache (MySQL 5.7 only, disabled in 8.0)
query_cache_type = 1
query_cache_size = 64M

# Temp tables
tmp_table_size = 64M
max_heap_table_size = 64M

Magento System Requirements

# Check MySQL version
mysql --version
# Required: MySQL 5.7+ or MySQL 8.0+

# Check required extensions
php -m | grep -i pdo
php -m | grep -i mysql

# Required PHP extensions
# - ext-pdo_mysql
# - ext-intl (for Magento 2.4+)

Performance Monitoring

-- Check InnoDB buffer pool hit rate
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Hit rate should be > 99%

-- Check slow queries
SHOW STATUS LIKE 'Slow_queries';

-- Check table sizes
SELECT table_name, 
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables 
WHERE table_schema = 'magento_db'
ORDER BY data_length DESC;

Connection Pooling with Hyperdrive

Magento can benefit from connection pooling to reduce MySQL connection overhead:

// Database connection configuration
'connection' => [
    'default' => [
        'host' => 'localhost',
        'dbname' => 'magento_db',
        'username' => 'magento_user',
        'password' => 'password',
        'active' => '1',
        'persistent' => [], // Use persistent connections
    ]
]

Table Prefixes

Magento supports table prefixes to install multiple instances on one database:

// env.php
return [
    'db' => [
        'table_prefix' => 'm2_',
        'connection' => [
            'default' => [
                'host' => 'localhost',
                'dbname' => 'magento_db',
                'username' => 'root',
                'password' => '',
            ]
        ]
    ]
];

All tables will be prefixed: m2_catalog_product_entity, m2_customer_entity, etc.

Schema Comparison

# Compare database schema
bin/magento setup:db-declaration:compare-whitelist

# Generate whitelist of declarative schema tables
bin/magento setup:db-declaration:generate-whitelist

# Check database status
bin/magento setup:db:status

Backup Strategies

# Full database backup
mysqldump -u root -p magento_db > magento_backup.sql

# Backup with table structure only
mysqldump -u root -p --no-data magento_db > schema_only.sql

# Backup specific tables
mysqldump -u root -p magento_db catalog_product_entity catalog_category_entity > catalog_backup.sql

# Restore from backup
mysql -u root -p magento_db < magento_backup.sql

Common MySQL Issues in Magento