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
- 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
Replaceyour_wp_dbwith your WordPress database name. Restart MySQL:sudo systemctl restart mysql. - 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;
Replacestrong_passwordwith a secure password. - Get Primary Status
Still in MySQL, runSHOW MASTER STATUS;. Note theFileandPositionvalues – you will need them for the replica. - 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 usingscp. 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. - Configure the Replica Server
Edit the replica's MySQL config file. Add:server-id = 2
Restart MySQL:sudo systemctl restart mysql. - 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 runSTART SLAVE;. - Verify Replication
On the replica, runSHOW SLAVE STATUS\G;. Check thatSlave_IO_RunningandSlave_SQL_Runningare bothYes. If they are, replication is active. - 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. - Configure the Plugin
For HyperDB, editwp-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.
- 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-heartbeator by checkingSeconds_Behind_MasterinSHOW SLAVE STATUS. - Keep the replica read-only with
read_only = 1in 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\Gand look at theLast_Errorfield. Common issues include duplicate key errors (often from writing directly to the replica) or network timeouts. Fix the cause, then runSTOP 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 -pfrom the web server. - Replica is lagging behind – If
Seconds_Behind_Masteris 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' => 1and'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
- Microsoft Surface Duo: The Foldable Phone Story
- Samsung SM-T813 Hard Reset: Step-by-Step Guide
- Mafia Clash Codes: Real Meanings & Usage
- Remove Programs from Mac: Full Uninstall Guide
- Project Sentinel Morning Digest: Data Stack Failure Alerts
- Best KVM VPS Hosting: Top Picks & Guide