{"id":12821,"date":"2025-08-08T10:41:40","date_gmt":"2025-08-08T04:56:40","guid":{"rendered":"https:\/\/nestnepal.com\/blog\/?p=12821"},"modified":"2025-08-13T13:41:00","modified_gmt":"2025-08-13T07:56:00","slug":"managing-wordpress-databases-wp-config-mysql","status":"publish","type":"post","link":"https:\/\/nestnepal.com\/blog\/managing-wordpress-databases-wp-config-mysql\/","title":{"rendered":"Managing WordPress Databases: Optimizing wp-config and MySQL Settings"},"content":{"rendered":"\n

Your WordPress<\/a> database is the heart of your site\u00a0 every post, comment, user, and plugin setting lives there. Yet most developers treat database optimization as an afterthought, only addressing it when things start breaking. By then, you’re playing catch-up instead of preventing problems.<\/p>\n\n\n\n

\"wordpress<\/figure>\n\n\n\n

Let’s dive into the nuts and bolts of WordPress database management, from wp-config tweaks that actually matter to MySQL settings that can dramatically improve performance.<\/p>\n\n\n\n

Understanding WordPress Database Architecture<\/strong><\/h2>\n\n\n\n

Core WordPress Tables<\/strong><\/h3>\n\n\n\n

WordPress uses a lean database structure with just 12 core tables in a fresh installation:<\/p>\n\n\n\n

Table<\/strong><\/td>Purpose<\/strong><\/td>Optimization Priority<\/strong><\/td><\/tr>
wp_posts<\/td>Posts, pages, revisions<\/td>High – gets huge fast<\/td><\/tr>
wp_postmeta<\/td>Custom fields, metadata<\/td>High – can explode with plugins<\/td><\/tr>
wp_users<\/td>User accounts<\/td>Medium<\/td><\/tr>
wp_usermeta<\/td>User metadata<\/td>Medium – grows with user activity<\/td><\/tr>
wp_options<\/td>Site settings, plugin data<\/td>Critical – frequently queried<\/td><\/tr>
wp_comments<\/td>Comments and trackbacks<\/td>Medium<\/td><\/tr>
wp_commentmeta<\/td>Comment metadata<\/td>Low<\/td><\/tr>
wp_terms<\/td>Categories, tags<\/td>Low<\/td><\/tr>
wp_term_taxonomy<\/td>Term relationships<\/td>Low<\/td><\/tr>
wp_term_relationships<\/td>Post-term connections<\/td>Medium<\/td><\/tr>
wp_links<\/td>Blogroll links (rarely used)<\/td>Low<\/td><\/tr>
wp_termmeta<\/td>Term metadata<\/td>Low<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The Big Three<\/strong> that need your attention: wp_posts, wp_postmeta, and wp_options. These tables can single-handedly kill your site’s performance if not properly managed.<\/p>\n\n\n\n

Plugin-Created Tables<\/strong><\/h3>\n\n\n\n

Many plugins create their own tables. Common culprits:<\/p>\n\n\n\n

    \n
  • WooCommerce<\/strong>: Adds 10+ tables for orders, products, etc.<\/li>\n\n\n\n
  • Event Calendar<\/strong>: Creates event-specific tables<\/li>\n\n\n\n
  • Form builders<\/strong>: Store submissions in custom tables<\/li>\n\n\n\n
  • SEO plugins<\/strong>: Cache and metadata tables<\/li>\n\n\n\n
  • Security plugins<\/strong>: Log tables that grow rapidly<\/li>\n<\/ul>\n\n\n\n

    wp-config.php Database Optimization<\/strong><\/h2>\n\n\n\n

    Essential Database Constants<\/strong><\/h3>\n\n\n\n

    Here’s what actually moves the needle in wp-config.php:<\/p>\n\n\n\n

    <?php<\/p>\n\n\n\n

    \/\/ Basic database connection<\/p>\n\n\n\n

    define(‘DB_NAME’, ‘your_database’);<\/p>\n\n\n\n

    define(‘DB_USER’, ‘your_username’);<\/p>\n\n\n\n

    define(‘DB_PASSWORD’, ‘your_strong_password’);<\/p>\n\n\n\n

    define(‘DB_HOST’, ‘localhost:3306’); \/\/ Specify port for clarity<\/p>\n\n\n\n

    define(‘DB_CHARSET’, ‘utf8mb4’);<\/p>\n\n\n\n

    define(‘DB_COLLATE’, ‘utf8mb4_unicode_ci’);<\/p>\n\n\n\n

    \/\/ Performance-critical settings<\/p>\n\n\n\n

    define(‘WP_MEMORY_LIMIT’, ‘256M’); \/\/ Increase from default 40M<\/p>\n\n\n\n

    define(‘WP_MAX_MEMORY_LIMIT’, ‘512M’); \/\/ For admin tasks<\/p>\n\n\n\n

    \/\/ Database connection optimization<\/p>\n\n\n\n

    define(‘DB_COLLATE’, ”); \/\/ Let MySQL decide for better performance<\/p>\n\n\n\n

    \/\/ Disable file editing from admin<\/p>\n\n\n\n

    define(‘DISALLOW_FILE_EDIT’, true);<\/p>\n\n\n\n

    \/\/ Control revisions (huge performance impact)<\/p>\n\n\n\n

    define(‘WP_POST_REVISIONS’, 3); \/\/ Limit to 3 revisions<\/p>\n\n\n\n

    define(‘AUTOSAVE_INTERVAL’, 300); \/\/ Autosave every 5 minutes instead of 60 seconds<\/p>\n\n\n\n

    \/\/ Trash and spam cleanup<\/p>\n\n\n\n

    define(‘EMPTY_TRASH_DAYS’, 7); \/\/ Delete trash after 7 days instead of 30<\/p>\n\n\n\n

    define(‘WP_CRON_LOCK_TIMEOUT’, 60); \/\/ Prevent cron overlap issues<\/p>\n\n\n\n

    Advanced wp-config Database Settings<\/strong><\/h3>\n\n\n\n

    \/\/ Database connection tweaks<\/p>\n\n\n\n

    ini_set(‘mysql.connect_timeout’, 300); \/\/ 5 minute connection timeout<\/p>\n\n\n\n

    ini_set(‘default_socket_timeout’, 300); \/\/ Match socket timeout<\/p>\n\n\n\n

    \/\/ Enable database error logging<\/p>\n\n\n\n

    define(‘WP_DEBUG’, false); \/\/ Keep false in production<\/p>\n\n\n\n

    define(‘WP_DEBUG_LOG’, true); \/\/ Log errors to \/wp-content\/debug.log<\/p>\n\n\n\n

    define(‘WP_DEBUG_DISPLAY’, false); \/\/ Don’t show errors to visitors<\/p>\n\n\n\n

    \/\/ Database repair and optimization<\/p>\n\n\n\n

    define(‘WP_ALLOW_REPAIR’, true); \/\/ Enable in emergency only, then disable<\/p>\n\n\n\n

    \/\/ Object caching (if using Redis\/Memcached)<\/p>\n\n\n\n

    define(‘WP_CACHE’, true);<\/p>\n\n\n\n

    define(‘WP_CACHE_KEY_SALT’, ‘your-unique-prefix-here’);<\/p>\n\n\n\n

    \/\/ Multisite-specific (if applicable)<\/p>\n\n\n\n

    define(‘WP_ALLOW_MULTISITE’, true);<\/p>\n\n\n\n

    define(‘MULTISITE’, true);<\/p>\n\n\n\n

    define(‘SUBDOMAIN_INSTALL’, false);<\/p>\n\n\n\n

    define(‘DOMAIN_CURRENT_SITE’, ‘yourdomain.com’);<\/p>\n\n\n\n

    define(‘PATH_CURRENT_SITE’, ‘\/’);<\/p>\n\n\n\n

    define(‘SITE_ID_CURRENT_SITE’, 1);<\/p>\n\n\n\n

    define(‘BLOG_ID_CURRENT_SITE’, 1);<\/p>\n\n\n\n

    Custom Database Table Prefix Security<\/strong><\/h3>\n\n\n\n

    \/\/ Change default table prefix for security<\/p>\n\n\n\n

    $table_prefix = ‘xyz_’; \/\/ Use random 3-4 characters<\/p>\n\n\n\n

    \/\/ Alternative approach for existing sites (requires migration)<\/p>\n\n\n\n

    \/\/ Don’t change this on live sites without proper migration!<\/p>\n\n\n\n

    Migration script for changing table prefix<\/strong> (use with extreme caution):<\/p>\n\n\n\n

    — Backup first! This is irreversible.<\/p>\n\n\n\n

    — Replace ‘wp_’ with old prefix, ‘new_’ with new prefix<\/p>\n\n\n\n

    RENAME TABLE wp_posts TO new_posts;<\/p>\n\n\n\n

    RENAME TABLE wp_postmeta TO new_postmeta;<\/p>\n\n\n\n

    RENAME TABLE wp_users TO new_users;<\/p>\n\n\n\n

    RENAME TABLE wp_usermeta TO new_usermeta;<\/p>\n\n\n\n

    RENAME TABLE wp_options TO new_options;<\/p>\n\n\n\n

    RENAME TABLE wp_comments TO new_comments;<\/p>\n\n\n\n

    RENAME TABLE wp_commentmeta TO new_commentmeta;<\/p>\n\n\n\n

    RENAME TABLE wp_terms TO new_terms;<\/p>\n\n\n\n

    RENAME TABLE wp_term_taxonomy TO new_term_taxonomy;<\/p>\n\n\n\n

    RENAME TABLE wp_term_relationships TO new_term_relationships;<\/p>\n\n\n\n

    RENAME TABLE wp_links TO new_links;<\/p>\n\n\n\n

    RENAME TABLE wp_termmeta TO new_termmeta;<\/p>\n\n\n\n

    — Update options table references<\/p>\n\n\n\n

    UPDATE new_options SET option_name = REPLACE(option_name, ‘wp_’, ‘new_’);<\/p>\n\n\n\n

    — Update usermeta table references<\/p>\n\n\n\n

    UPDATE new_usermeta SET meta_key = REPLACE(meta_key, ‘wp_’, ‘new_’);<\/p>\n\n\n\n

    MySQL Configuration Optimization<\/strong><\/h2>\n\n\n\n

    Key MySQL Variables for WordPress<\/strong><\/h3>\n\n\n\n

    Memory Settings<\/strong> (most critical for performance):<\/p>\n\n\n\n

    — Check current values first<\/p>\n\n\n\n

    SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;<\/p>\n\n\n\n

    SHOW VARIABLES LIKE ‘query_cache_size’;<\/p>\n\n\n\n

    SHOW VARIABLES LIKE ‘tmp_table_size’;<\/p>\n\n\n\n

    — Optimal settings for WordPress (adjust based on available RAM)<\/p>\n\n\n\n

    SET GLOBAL innodb_buffer_pool_size = 1073741824; — 1GB for sites with 4GB+ RAM<\/p>\n\n\n\n

    SET GLOBAL query_cache_size = 67108864; — 64MB<\/p>\n\n\n\n

    SET GLOBAL query_cache_limit = 2097152; — 2MB<\/p>\n\n\n\n

    SET GLOBAL tmp_table_size = 134217728; — 128MB<\/p>\n\n\n\n

    SET GLOBAL max_heap_table_size = 134217728; — Match tmp_table_size<\/p>\n\n\n\n

    Connection and Threading<\/strong>:<\/p>\n\n\n\n

    — Handle WordPress’s connection patterns<\/p>\n\n\n\n

    SET GLOBAL max_connections = 200; — Increase from default 151<\/p>\n\n\n\n

    SET GLOBAL connect_timeout = 10; — Quick connection timeout<\/p>\n\n\n\n

    SET GLOBAL interactive_timeout = 300; — 5 minutes for admin sessions<\/p>\n\n\n\n

    SET GLOBAL wait_timeout = 300; — Match interactive timeout<\/p>\n\n\n\n

    SET GLOBAL thread_cache_size = 50; — Cache threads for reuse<\/p>\n\n\n\n

    InnoDB Optimization<\/strong> (WordPress default storage engine):<\/p>\n\n\n\n

    — InnoDB performance tuning<\/p>\n\n\n\n

    SET GLOBAL innodb_log_file_size = 268435456; — 256MB<\/p>\n\n\n\n

    SET GLOBAL innodb_log_buffer_size = 16777216; — 16MB<\/p>\n\n\n\n

    SET GLOBAL innodb_flush_log_at_trx_commit = 2; — Better performance, slight durability risk<\/p>\n\n\n\n

    SET GLOBAL innodb_file_per_table = 1; — Separate files per table<\/p>\n\n\n\n

    SET GLOBAL innodb_open_files = 300; — Handle more concurrent files<\/p>\n\n\n\n

    my.cnf Configuration File<\/strong><\/h3>\n\n\n\n

    Create or edit \/etc\/mysql\/my.cnf (location varies by system):<\/p>\n\n\n\n

    [mysqld]<\/p>\n\n\n\n

    # Basic settings<\/p>\n\n\n\n

    port = 3306<\/p>\n\n\n\n

    socket = \/var\/run\/mysqld\/mysqld.sock<\/p>\n\n\n\n

    datadir = \/var\/lib\/mysql<\/p>\n\n\n\n

    log-error = \/var\/log\/mysql\/error.log<\/p>\n\n\n\n

    # Memory allocation (adjust based on available RAM)<\/p>\n\n\n\n

    innodb_buffer_pool_size = 1G # 70-80% of available RAM for database-only servers<\/p>\n\n\n\n

    query_cache_size = 64M<\/p>\n\n\n\n

    query_cache_limit = 2M<\/p>\n\n\n\n

    tmp_table_size = 128M<\/p>\n\n\n\n

    max_heap_table_size = 128M<\/p>\n\n\n\n

    # Connection limits<\/p>\n\n\n\n

    max_connections = 200<\/p>\n\n\n\n

    connect_timeout = 10<\/p>\n\n\n\n

    interactive_timeout = 300<\/p>\n\n\n\n

    wait_timeout = 300<\/p>\n\n\n\n

    thread_cache_size = 50<\/p>\n\n\n\n

    # InnoDB settings<\/p>\n\n\n\n

    innodb_log_file_size = 256M<\/p>\n\n\n\n

    innodb_log_buffer_size = 16M<\/p>\n\n\n\n

    innodb_flush_log_at_trx_commit = 2<\/p>\n\n\n\n

    innodb_file_per_table = 1<\/p>\n\n\n\n

    innodb_open_files = 300<\/p>\n\n\n\n

    # Query cache (disable if using object caching)<\/p>\n\n\n\n

    query_cache_type = 1<\/p>\n\n\n\n

    query_cache_size = 64M<\/p>\n\n\n\n

    # Logging (disable in production for performance)<\/p>\n\n\n\n

    slow_query_log = 1<\/p>\n\n\n\n

    slow_query_log_file = \/var\/log\/mysql\/mysql-slow.log<\/p>\n\n\n\n

    long_query_time = 2<\/p>\n\n\n\n

    # Character set<\/p>\n\n\n\n

    character-set-server = utf8mb4<\/p>\n\n\n\n

    collation-server = utf8mb4_unicode_ci<\/p>\n\n\n\n

    # Binary logging (for replication\/backups)<\/p>\n\n\n\n

    log-bin = \/var\/log\/mysql\/mysql-bin.log<\/p>\n\n\n\n

    expire_logs_days = 7<\/p>\n\n\n\n

    max_binlog_size = 100M<\/p>\n\n\n\n

    [mysql]<\/p>\n\n\n\n

    default-character-set = utf8mb4<\/p>\n\n\n\n

    [client]<\/p>\n\n\n\n

    default-character-set = utf8mb4<\/p>\n\n\n\n

    Monitoring MySQL Performance<\/strong><\/h3>\n\n\n\n

    Essential Performance Queries<\/strong>:<\/p>\n\n\n\n

    — Check slow queries<\/p>\n\n\n\n

    SELECT * FROM information_schema.processlist WHERE time > 5;<\/p>\n\n\n\n

    — Monitor query cache hit rate<\/p>\n\n\n\n

    SHOW STATUS LIKE ‘Qcache_hits’;<\/p>\n\n\n\n

    SHOW STATUS LIKE ‘Qcache_inserts’;<\/p>\n\n\n\n

    — Hit rate = Qcache_hits \/ (Qcache_hits + Qcache_inserts) * 100<\/p>\n\n\n\n

    — Check InnoDB buffer pool efficiency<\/p>\n\n\n\n

    SHOW STATUS LIKE ‘Innodb_buffer_pool_read_requests’;<\/p>\n\n\n\n

    SHOW STATUS LIKE ‘Innodb_buffer_pool_reads’;<\/p>\n\n\n\n

    — Hit rate = (1 – (Innodb_buffer_pool_reads \/ Innodb_buffer_pool_read_requests)) * 100<\/p>\n\n\n\n

    — Identify problematic tables<\/p>\n\n\n\n

    SELECT table_schema, table_name, <\/p>\n\n\n\n

           ROUND(data_length\/1024\/1024,2) AS data_mb,<\/p>\n\n\n\n

           ROUND(index_length\/1024\/1024,2) AS index_mb,<\/p>\n\n\n\n

           ROUND((data_length + index_length)\/1024\/1024,2) AS total_mb<\/p>\n\n\n\n

    FROM information_schema.tables <\/p>\n\n\n\n

    WHERE table_schema = ‘your_database_name’<\/p>\n\n\n\n

    ORDER BY (data_length + index_length) DESC;<\/p>\n\n\n\n

    WordPress-Specific Database Optimization<\/strong><\/h2>\n\n\n\n

    Cleaning Up wp_options Table<\/strong><\/h3>\n\n\n\n

    The wp_options table is WordPress’s Achilles heel. It’s queried on every page load and can become bloated with plugin data:<\/p>\n\n\n\n

    \/\/ Find autoloaded options consuming memory<\/p>\n\n\n\n

    SELECT option_name, LENGTH(option_value) as value_length <\/p>\n\n\n\n

    FROM wp_options <\/p>\n\n\n\n

    WHERE autoload = ‘yes’ <\/p>\n\n\n\n

    ORDER BY value_length DESC <\/p>\n\n\n\n

    LIMIT 20;<\/p>\n\n\n\n

    Common wp_options Cleanup<\/strong>:<\/p>\n\n\n\n

    — Remove expired transients (cached data)<\/p>\n\n\n\n

    DELETE FROM wp_options WHERE option_name LIKE ‘_transient_timeout_%’ <\/p>\n\n\n\n

    AND option_value < UNIX_TIMESTAMP();<\/p>\n\n\n\n

    DELETE FROM wp_options WHERE option_name LIKE ‘_transient_%’ <\/p>\n\n\n\n

    AND option_name NOT LIKE ‘_transient_timeout_%’ <\/p>\n\n\n\n

    AND option_name NOT IN (<\/p>\n\n\n\n

        SELECT DISTINCT REPLACE(option_name, ‘_transient_timeout_’, ‘_transient_’) <\/p>\n\n\n\n

        FROM wp_options WHERE option_name LIKE ‘_transient_timeout_%’<\/p>\n\n\n\n

    );<\/p>\n\n\n\n

    — Remove orphaned post meta<\/p>\n\n\n\n

    DELETE pm FROM wp_postmeta pm<\/p>\n\n\n\n

    LEFT JOIN wp_posts p ON pm.post_id = p.ID<\/p>\n\n\n\n

    WHERE p.ID IS NULL;<\/p>\n\n\n\n

    — Clean up spam and trash comments<\/p>\n\n\n\n

    DELETE FROM wp_comments WHERE comment_approved = ‘spam’;<\/p>\n\n\n\n

    DELETE FROM wp_comments WHERE comment_approved = ‘trash’;<\/p>\n\n\n\n

    — Remove orphaned comment meta<\/p>\n\n\n\n

    DELETE cm FROM wp_commentmeta cm<\/p>\n\n\n\n

    LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID<\/p>\n\n\n\n

    WHERE c.comment_ID IS NULL;<\/p>\n\n\n\n

    Post Revisions Management<\/strong><\/h3>\n\n\n\n

    Post revisions can bloat your database quickly:<\/p>\n\n\n\n

    \/\/ In wp-config.php – limit revisions going forward<\/p>\n\n\n\n

    define(‘WP_POST_REVISIONS’, 3);<\/p>\n\n\n\n

    \/\/ Clean up existing revisions (SQL approach)<\/p>\n\n\n\n

    DELETE FROM wp_posts WHERE post_type = ‘revision’;<\/p>\n\n\n\n

    \/\/ Or use WP-CLI (safer method)<\/p>\n\n\n\n

    wp post delete $(wp post list –post_type=’revision’ –format=ids) –force<\/p>\n\n\n\n

    Optimizing Database Queries<\/strong><\/h3>\n\n\n\n

    Add Missing Indexes<\/strong> for common WordPress queries:<\/p>\n\n\n\n

    — Add index for post queries with meta<\/p>\n\n\n\n

    ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key, meta_value(191));<\/p>\n\n\n\n

    — Add index for user queries<\/p>\n\n\n\n

    ALTER TABLE wp_usermeta ADD INDEX meta_key_value (meta_key, meta_value(191));<\/p>\n\n\n\n

    — Add index for comment queries<\/p>\n\n\n\n

    ALTER TABLE wp_comments ADD INDEX comment_date_gmt (comment_date_gmt);<\/p>\n\n\n\n

    ALTER TABLE wp_comments ADD INDEX comment_approved_date_gmt (comment_approved, comment_date_gmt);<\/p>\n\n\n\n

    — Add index for term relationships<\/p>\n\n\n\n

    ALTER TABLE wp_term_relationships ADD INDEX term_taxonomy_id (term_taxonomy_id);<\/p>\n\n\n\n

    Query Optimization Examples<\/strong>:<\/p>\n\n\n\n

    \/\/ Inefficient query (loads all post meta)<\/p>\n\n\n\n

    $post_meta = get_post_meta($post_id);<\/p>\n\n\n\n

    \/\/ Efficient query (loads specific meta key)<\/p>\n\n\n\n

    $specific_meta = get_post_meta($post_id, ‘specific_key’, true);<\/p>\n\n\n\n

    \/\/ Inefficient query (no limits)<\/p>\n\n\n\n

    $posts = get_posts(array(‘post_type’ => ‘product’));<\/p>\n\n\n\n

    \/\/ Efficient query (with limits and specific fields)<\/p>\n\n\n\n

    $posts = get_posts(array(<\/p>\n\n\n\n

        ‘post_type’ => ‘product’,<\/p>\n\n\n\n

        ‘posts_per_page’ => 10,<\/p>\n\n\n\n

        ‘fields’ => ‘ids’, \/\/ Only get IDs if that’s all you need<\/p>\n\n\n\n

        ‘meta_query’ => array(<\/p>\n\n\n\n

            array(<\/p>\n\n\n\n

                ‘key’ => ‘featured’,<\/p>\n\n\n\n

                ‘value’ => ‘yes’,<\/p>\n\n\n\n

                ‘compare’ => ‘=’<\/p>\n\n\n\n

            )<\/p>\n\n\n\n

        )<\/p>\n\n\n\n

    ));<\/p>\n\n\n\n

    Database Maintenance and Monitoring<\/strong><\/h2>\n\n\n\n

    Automated Database Maintenance<\/strong><\/h3>\n\n\n\n

    Daily Maintenance Script<\/strong>:<\/p>\n\n\n\n

    #!\/bin\/bash<\/p>\n\n\n\n

    # Save as \/usr\/local\/bin\/wp-db-maintenance.sh<\/p>\n\n\n\n

    DB_NAME=”your_wordpress_db”<\/p>\n\n\n\n

    DB_USER=”your_db_user”<\/p>\n\n\n\n

    DB_PASS=”your_db_password”<\/p>\n\n\n\n

    LOG_FILE=”\/var\/log\/wp-db-maintenance.log”<\/p>\n\n\n\n

    echo “$(date): Starting WordPress database maintenance” >> $LOG_FILE<\/p>\n\n\n\n

    # Optimize all tables<\/p>\n\n\n\n

    mysql -u$DB_USER -p$DB_PASS $DB_NAME -e “<\/p>\n\n\n\n

        DELETE FROM wp_options WHERE option_name LIKE ‘_transient_timeout_%’ AND option_value < UNIX_TIMESTAMP();<\/p>\n\n\n\n

        DELETE FROM wp_options WHERE option_name LIKE ‘_transient_%’ AND option_name NOT LIKE ‘_transient_timeout_%’;<\/p>\n\n\n\n

        OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta;<\/p>\n\n\n\n

    ” 2>> $LOG_FILE<\/p>\n\n\n\n

    echo “$(date): Database maintenance completed” >> $LOG_FILE<\/p>\n\n\n\n

    # Add to crontab: 0 2 * * * \/usr\/local\/bin\/wp-db-maintenance.sh<\/p>\n\n\n\n

    Monitoring Database Health<\/strong><\/h3>\n\n\n\n

    Performance Monitoring Queries<\/strong>:<\/p>\n\n\n\n

    — Check table sizes and growth<\/p>\n\n\n\n

    SELECT <\/p>\n\n\n\n

        table_name,<\/p>\n\n\n\n

        ROUND(((data_length + index_length) \/ 1024 \/ 1024), 2) AS “Size (MB)”,<\/p>\n\n\n\n

        table_rows<\/p>\n\n\n\n

    FROM information_schema.TABLES <\/p>\n\n\n\n

    WHERE table_schema = ‘your_database_name’<\/p>\n\n\n\n

    ORDER BY (data_length + index_length) DESC;<\/p>\n\n\n\n

    — Monitor slow queries<\/p>\n\n\n\n

    SELECT <\/p>\n\n\n\n

        query_time,<\/p>\n\n\n\n

        lock_time,<\/p>\n\n\n\n

        rows_sent,<\/p>\n\n\n\n

        rows_examined,<\/p>\n\n\n\n

        sql_text<\/p>\n\n\n\n

    FROM mysql.slow_log<\/p>\n\n\n\n

    WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 DAY)<\/p>\n\n\n\n

    ORDER BY query_time DESC<\/p>\n\n\n\n

    LIMIT 10;<\/p>\n\n\n\n

    — Check for missing indexes<\/p>\n\n\n\n

    SELECT <\/p>\n\n\n\n

        object_schema,<\/p>\n\n\n\n

        object_name,<\/p>\n\n\n\n

        index_name,<\/p>\n\n\n\n

        count_star,<\/p>\n\n\n\n

        sum_timer_wait<\/p>\n\n\n\n

    FROM performance_schema.table_io_waits_summary_by_index_usage<\/p>\n\n\n\n

    WHERE index_name IS NULL<\/p>\n\n\n\n

    ORDER BY count_star DESC;<\/p>\n\n\n\n

    Backup Strategies<\/strong><\/h3>\n\n\n\n

    Automated Backup Script<\/strong>:<\/p>\n\n\n\n

    #!\/bin\/bash<\/p>\n\n\n\n

    # WordPress database backup with rotation<\/p>\n\n\n\n

    DB_NAME=”your_wordpress_db”<\/p>\n\n\n\n

    DB_USER=”your_db_user”<\/p>\n\n\n\n

    DB_PASS=”your_db_password”<\/p>\n\n\n\n

    BACKUP_DIR=”\/backups\/mysql”<\/p>\n\n\n\n

    DATE=$(date +%Y%m%d_%H%M%S)<\/p>\n\n\n\n

    BACKUP_FILE=”$BACKUP_DIR\/${DB_NAME}_$DATE.sql.gz”<\/p>\n\n\n\n

    # Create backup directory<\/p>\n\n\n\n

    mkdir -p $BACKUP_DIR<\/p>\n\n\n\n

    # Create compressed backup<\/p>\n\n\n\n

    mysqldump -u$DB_USER -p$DB_PASS –single-transaction –routines –triggers $DB_NAME | gzip > $BACKUP_FILE<\/p>\n\n\n\n

    # Keep only last 7 days of backups<\/p>\n\n\n\n

    find $BACKUP_DIR -name “${DB_NAME}_*.sql.gz” -mtime +7 -delete<\/p>\n\n\n\n

    # Verify backup integrity<\/p>\n\n\n\n

    if [ -f “$BACKUP_FILE” ] && [ -s “$BACKUP_FILE” ]; then<\/p>\n\n\n\n

        echo “$(date): Backup successful – $BACKUP_FILE” >> \/var\/log\/wp-backup.log<\/p>\n\n\n\n

    else<\/p>\n\n\n\n

        echo “$(date): Backup failed – $BACKUP_FILE” >> \/var\/log\/wp-backup.log<\/p>\n\n\n\n

        exit 1<\/p>\n\n\n\n

    fi<\/p>\n\n\n\n

    Advanced Database Optimization Techniques<\/strong><\/h2>\n\n\n\n

    Database Partitioning<\/strong><\/h3>\n\n\n\n

    For high-traffic sites, consider partitioning large tables:<\/p>\n\n\n\n

    — Partition wp_posts by year (for sites with lots of content)<\/p>\n\n\n\n

    ALTER TABLE wp_posts PARTITION BY RANGE (YEAR(post_date)) (<\/p>\n\n\n\n

        PARTITION p2020 VALUES LESS THAN (2021),<\/p>\n\n\n\n

        PARTITION p2021 VALUES LESS THAN (2022),<\/p>\n\n\n\n

        PARTITION p2022 VALUES LESS THAN (2023),<\/p>\n\n\n\n

        PARTITION p2023 VALUES LESS THAN (2024),<\/p>\n\n\n\n

        PARTITION p2024 VALUES LESS THAN (2025),<\/p>\n\n\n\n

        PARTITION pmax VALUES LESS THAN MAXVALUE<\/p>\n\n\n\n

    );<\/p>\n\n\n\n

    Read\/Write Splitting<\/strong><\/h3>\n\n\n\n

    For high-traffic sites, implement read\/write splitting:<\/p>\n\n\n\n

    \/\/ wp-config.php – Database splitting configuration<\/p>\n\n\n\n

    define(‘DB_HOST’, ‘master-db-server.com’); \/\/ Write operations<\/p>\n\n\n\n

    define(‘DB_HOST_SLAVE’, ‘slave-db-server.com’); \/\/ Read operations<\/p>\n\n\n\n

    \/\/ Custom db.php drop-in for read\/write splitting<\/p>\n\n\n\n

    class wp_db_split extends wpdb {<\/p>\n\n\n\n

        public function __construct($dbuser, $dbpassword, $dbname, $dbhost) {<\/p>\n\n\n\n

            parent::__construct($dbuser, $dbpassword, $dbname, $dbhost);<\/p>\n\n\n\n

            \/\/ Add slave connection logic here<\/p>\n\n\n\n

            if (defined(‘DB_HOST_SLAVE’)) {<\/p>\n\n\n\n

                $this->setup_slave_connection();<\/p>\n\n\n\n

            }<\/p>\n\n\n\n

        }<\/p>\n\n\n\n

        private function setup_slave_connection() {<\/p>\n\n\n\n

            \/\/ Implementation for slave database connection<\/p>\n\n\n\n

            \/\/ Route SELECT queries to slave, everything else to master<\/p>\n\n\n\n

        }<\/p>\n\n\n\n

    }<\/p>\n\n\n\n

    Object Caching Integration<\/strong><\/h3>\n\n\n\n

    Redis Configuration for WordPress<\/strong>:<\/p>\n\n\n\n

    \/\/ wp-config.php Redis settings<\/p>\n\n\n\n

    define(‘WP_REDIS_HOST’, ‘127.0.0.1’);<\/p>\n\n\n\n

    define(‘WP_REDIS_PORT’, 6379);<\/p>\n\n\n\n

    define(‘WP_REDIS_TIMEOUT’, 1);<\/p>\n\n\n\n

    define(‘WP_REDIS_READ_TIMEOUT’, 1);<\/p>\n\n\n\n

    define(‘WP_REDIS_DATABASE’, 0);<\/p>\n\n\n\n

    \/\/ Reduce database load with object caching<\/p>\n\n\n\n

    define(‘WP_CACHE’, true);<\/p>\n\n\n\n

    define(‘WP_CACHE_KEY_SALT’, ‘your-unique-salt-here’);<\/p>\n\n\n\n

    Troubleshooting Common Database Issues<\/strong><\/h2>\n\n\n\n

    Connection Problems<\/strong><\/h3>\n\n\n\n

    “Error establishing database connection”<\/strong> troubleshooting:<\/p>\n\n\n\n

    \/\/ Test database connection independently<\/p>\n\n\n\n

    <?php<\/p>\n\n\n\n

    $connection = mysql_connect(‘DB_HOST’, ‘DB_USER’, ‘DB_PASSWORD’);<\/p>\n\n\n\n

    if (!$connection) {<\/p>\n\n\n\n

        die(‘Connection failed: ‘ . mysql_error());<\/p>\n\n\n\n

    }<\/p>\n\n\n\n

    echo ‘Connected successfully’;<\/p>\n\n\n\n

    mysql_close($connection);<\/p>\n\n\n\n

    ?><\/p>\n\n\n\n

    Common fixes<\/strong>:<\/p>\n\n\n\n

      \n
    1. Check database credentials in wp-config.php<\/li>\n\n\n\n
    2. Verify MySQL service is running: systemctl status mysql<\/li>\n\n\n\n
    3. Check available connections: SHOW PROCESSLIST;<\/li>\n\n\n\n
    4. Verify disk space isn’t full: df -h<\/li>\n<\/ol>\n\n\n\n

      Performance Issues<\/strong><\/h3>\n\n\n\n

      Database locks and deadlocks<\/strong>:<\/p>\n\n\n\n

      — Check for locked tables<\/p>\n\n\n\n

      SHOW OPEN TABLES WHERE In_use > 0;<\/p>\n\n\n\n

      — Check for deadlocks<\/p>\n\n\n\n

      SHOW ENGINE INNODB STATUS;<\/p>\n\n\n\n

      — Kill problematic queries<\/p>\n\n\n\n

      SHOW PROCESSLIST;<\/p>\n\n\n\n

      KILL [process_id];<\/p>\n\n\n\n

      Corruption Recovery<\/strong><\/h3>\n\n\n\n

      Table repair commands<\/strong>:<\/p>\n\n\n\n

      — Check table integrity<\/p>\n\n\n\n

      CHECK TABLE wp_posts;<\/p>\n\n\n\n

      — Repair corrupted tables<\/p>\n\n\n\n

      REPAIR TABLE wp_posts;<\/p>\n\n\n\n

      — For severe corruption, use myisamchk (MyISAM only)<\/p>\n\n\n\n

      — myisamchk –recover \/path\/to\/table.MYI<\/p>\n\n\n\n

      Migration and Scaling Strategies<\/strong><\/h2>\n\n\n\n

      Database Migration Best Practices<\/strong><\/h3>\n\n\n\n

      Pre-migration checklist<\/strong>:<\/p>\n\n\n\n

        \n
      1. Full database backup<\/li>\n\n\n\n
      2. Test migration on staging environment<\/li>\n\n\n\n
      3. Document current performance metrics<\/li>\n\n\n\n
      4. Plan for downtime window<\/li>\n\n\n\n
      5. Prepare rollback procedure<\/li>\n<\/ol>\n\n\n\n

        Migration script template<\/strong>:<\/p>\n\n\n\n

        #!\/bin\/bash<\/p>\n\n\n\n

        # WordPress database migration script<\/p>\n\n\n\n

        SOURCE_DB=”old_wordpress_db”<\/p>\n\n\n\n

        TARGET_DB=”new_wordpress_db”<\/p>\n\n\n\n

        OLD_URL=”http:\/\/oldsite.com”<\/p>\n\n\n\n

        NEW_URL=”https:\/\/newsite.com”<\/p>\n\n\n\n

        # Export source database<\/p>\n\n\n\n

        mysqldump -u$DB_USER -p$DB_PASS $SOURCE_DB > migration_backup.sql<\/p>\n\n\n\n

        # Import to target database<\/p>\n\n\n\n

        mysql -u$DB_USER -p$DB_PASS $TARGET_DB < migration_backup.sql<\/p>\n\n\n\n

        # Update URLs in database<\/p>\n\n\n\n

        mysql -u$DB_USER -p$DB_PASS $TARGET_DB -e “<\/p>\n\n\n\n

            UPDATE wp_options SET option_value = replace(option_value, ‘$OLD_URL’, ‘$NEW_URL’);<\/p>\n\n\n\n

            UPDATE wp_posts SET post_content = replace(post_content, ‘$OLD_URL’, ‘$NEW_URL’);<\/p>\n\n\n\n

            UPDATE wp_postmeta SET meta_value = replace(meta_value, ‘$OLD_URL’, ‘$NEW_URL’);<\/p>\n\n\n\n

            UPDATE wp_comments SET comment_content = replace(comment_content, ‘$OLD_URL’, ‘$NEW_URL’);<\/p>\n\n\n\n

        “<\/p>\n\n\n\n

        echo “Migration completed. Don’t forget to update wp-config.php!”<\/p>\n\n\n\n

        Scaling Database Architecture<\/strong><\/h3>\n\n\n\n

        Vertical Scaling Checklist<\/strong>:<\/p>\n\n\n\n