keepcalculator

06 Numbers and Powers

WordPress MySQL Replication Performance Guide

Card No. 06·70742 · Updated · Standard formula, shown below

This guide walks you through setting up MySQL replication to boost WordPress performance. By offloading read queries to a replica server, you reduce load on the primary database, speeding up page loads and improving scalability. You will configure a primary and replica MySQL server, adjust WordPress to use both, and verify everything works.

What You’ll Need

  • Two Linux servers (or VPS) with root access – one for the primary MySQL, one for the replica.
  • MySQL 5.7 or 8.0 installed on both servers.
  • WordPress installed on a separate web server (or on the primary server, but separate is better).
  • Ability to edit configuration files via SSH and a text editor (nano or vim).
  • Basic familiarity with the command line.

Step-by-Step

  1. Configure the Primary Server
    Edit /etc/mysql/mysql.conf.d/mysqld.cnf (or /etc/my.cnf) on the primary. Add or uncomment these lines under [mysqld]:
    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    binlog_do_db = your_wp_db

    Replace your_wp_db with your WordPress database name. Restart MySQL: sudo systemctl restart mysql.
  2. Create a Replication User
    On the primary, log into MySQL: mysql -u root -p. Run:
    CREATE USER 'replica'@'%' IDENTIFIED BY 'strong_password';
    GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
    FLUSH PRIVILEGES;
    Replace strong_password with a secure password.
  3. Get Primary Status
    Still in MySQL, run SHOW MASTER STATUS;. Note the File and Position values – you will need them for the replica.
  4. Export and Import the Database
    On the primary, create a snapshot: mysqldump -u root -p your_wp_db > db_backup.sql. Copy this file to the replica server using scp. On the replica, create the database: mysql -u root -p -e 'CREATE DATABASE your_wp_db;' then import: mysql -u root -p your_wp_db < db_backup.sql.
  5. Configure the Replica Server
    Edit the replica's MySQL config file. Add:
    server-id = 2
    Restart MySQL: sudo systemctl restart mysql.
  6. Start Replication
    On the replica, log into MySQL and run:
    CHANGE MASTER TO MASTER_HOST='primary_server_ip', MASTER_USER='replica', MASTER_PASSWORD='strong_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
    Use the file and position from Step 3. Then run START SLAVE;.
  7. Verify Replication
    On the replica, run SHOW SLAVE STATUS\G;. Check that Slave_IO_Running and Slave_SQL_Running are both Yes. If they are, replication is active.
  8. Install a WordPress Plugin for Read/Write Splitting
    On your WordPress site, install and activate a plugin like HyperDB or LudicrousDB. These plugins allow you to define multiple database connections.
  9. Configure the Plugin
    For HyperDB, edit wp-content/db.php (or the plugin’s config file) with the following structure:
    $wpdb = new hyperdb();
    $wpdb->add_database(array(
      'host' => 'primary_server_ip',
      'user' => 'wp_user',
      'password' => 'wp_password',
      'name' => 'your_wp_db',
      'write' => 1,
      'read' => 1,
    ));
    $wpdb->add_database(array(
      'host' => 'replica_server_ip',
      'user' => 'wp_user',
      'password' => 'wp_password',
      'name' => 'your_wp_db',
      'write' => 0,
      'read' => 1,
    ));
    

    Adjust the IPs and credentials. This tells WordPress to use the primary for writes and the replica for reads.

  10. Test Your Setup
    Visit your WordPress site and run a few actions (post a comment, edit a page). Then check the replica’s MySQL logs to see if SELECT queries are arriving. You can also use the Query Monitor plugin to see which database each query hits.

Common Mistakes

  • Ignoring the binlog_do_db setting – If you don't set this, all databases on the primary are replicated, which can cause permission issues or unnecessary load.
  • Using the same server-id – Both servers must have unique IDs, or replication will fail.
  • Forgetting to open firewall ports – The replica needs to reach the primary on port 3306. Check your firewall rules.
  • Not using a plugin – Simply having a replica does nothing for WordPress unless you configure read/write splitting.
  • Pointing both servers to the same WordPress files – The replica should only handle database queries, not serve web requests.

Tips

  • Use a dedicated MySQL user for WordPress on both servers – do not use root.
  • Monitor replication lag with tools like pt-heartbeat or by checking Seconds_Behind_Master in SHOW SLAVE STATUS.
  • Keep the replica read-only with read_only = 1 in its config to prevent accidental writes.
  • Back up your WordPress files and database before starting – replication is not a backup.
  • If you use a caching plugin, make sure it doesn’t cache the database connection settings.

Troubleshooting

  • Replication stops with an error – Run SHOW SLAVE STATUS\G and look at the Last_Error field. Common issues include duplicate key errors (often from writing directly to the replica) or network timeouts. Fix the cause, then run STOP SLAVE; START SLAVE;.
  • WordPress shows “Error establishing a database connection” – Check that the credentials in your plugin config are correct and that the servers can reach each other. Test with mysql -h replica_ip -u wp_user -p from the web server.
  • Replica is lagging behind – If Seconds_Behind_Master is high, the replica may be overloaded. Consider adding an index to large tables or upgrading the replica’s hardware.
  • Read queries still go to the primary – Ensure your plugin is properly installed and that you’ve defined the replica with 'read' => 1 and 'write' => 0. Also, some plugins or themes may use direct database calls that bypass the split.

Frequently Asked Questions

Do I need a separate server for the replica?

Yes, ideally. Running both on the same server defeats the purpose of load distribution and increases the risk of data loss if the server fails.

Can I use MySQL replication with managed WordPress hosting?

Usually not. Managed hosts control the database layer, so you cannot configure replication. This guide is for self-hosted or VPS setups.

Will replication slow down my WordPress writes?

Writes go to the primary only, so they are unaffected. Reads are offloaded, so overall performance should improve.

What if my WordPress database has multiple tables?

Replication works at the database level, so all tables are replicated. The plugin configuration applies to the whole database.

Is replication a substitute for backups?

No. Replication protects against server failure, but not against accidental deletions or corrupted data. Always maintain regular backups.

Can I use more than one replica?

Yes, you can add multiple replicas and configure WordPress to distribute reads among them. This further scales performance.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →