Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

WordPress Performance Optimization with MySQL Replication

What will you learn in this guide?​

In this guide, you will learn how to improve the database performance of your WordPress site.
The aim is to meet high traffic more smoothly by distributing the reading load.

🧠 Technical Summary​

Main Technical Topic:
MySQL Master–Slave replication and HyperDB integration.

Solved Problem:
A single database server is insufficient for heavy read traffic.

Steps Followed:

  • Slave MySQL server installation
  • Master replication settings
  • Data synchronization
  • Read/write separation with HyperDB

1️⃣ Install MySQL Slave Server​

Create a new database server (example: tr1-db02) and install MySQL:

sudo apt-get update
sudo apt-get install mysql-server
  • These commands install the MySQL server that will take on the Slave role.

2️⃣ Set Up Master Server for Replication​

  • Open the configuration file on the master server:

sudo vi /etc/mysql/my.cnf
  • This file contains MySQL's network and replication settings.

Activate the following lines:


bind-address = MASTER_OZEL_IP
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
  • These settings enable the Master server to replicate.
  • Restart MySQL:

sudo service mysql restart
  • Enables changes to become active.

Create the replication user:


CREATE USER 'repl'@'%' IDENTIFIED BY 'guclu_sifre';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  • Defines authority for slave servers to connect to the Master.

3️⃣ Synchronize Data from Master to Slave​

  • Lock tables for data consistency on master:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
  • Freezes the database and shows the log location.

Back up data and transfer to Slave server:


mysqldump --lock-all-tables -u root -p --all-databases > masterdump.sql
scp masterdump.sql user@SLAVE_OZEL_IP:/tmp
  • Moves existing data to the Slave server safely.

4️⃣ Connect Slave Server to Master​

  1. Import the backup on the slave server:

mysql -u root -p < /tmp/masterdump.sql
  • Slave synchronizes the server with the Master.

Define the replication link:


CHANGE MASTER TO
MASTER_HOST='MASTER_OZEL_IP',
MASTER_USER='repl',
MASTER_PASSWORD='guclu_sifre',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=408;
  • Slave introduces Master information to the server.

Start replication:


START SLAVE;
  • Slave now starts listening to the Master.
  • Check the status:

SHOW SLAVE STATUS\G
  • Verifies that replication is running smoothly.

5️⃣ HyperDB Configuration for WordPress​

  1. Download and extract the HyperDB plugin:

wget http://downloads.wordpress.org/plugin/hyperdb.zip
unzip hyperdb.zip
  • Downloads HyperDB files.

Define the slave database:


define('DB_SLAVE_1', 'SLAVE_OZEL_IP');
  • Informs WordPress about the second database source.
  1. Activate db.php file:

cp hyperdb/db.php /var/www/ornek.com/wp-content/
chmod a-w /var/www/ornek.com/wp-content/db.php
  • Replaces WordPress's default database class with HyperDB.

❓ Frequently Asked Questions (FAQ)​

1. What happens if the master server crashes? Write operations stop, read operations can continue through the Slave.

2. Can more than one Slave be added? Yes. Scaling can be done by repeating the same steps.

3. Is it mandatory to use HyperDB? It is not mandatory, but it is the most stable solution for WordPress.

4. Who is this structure suitable for? Ideal for WordPress sites with high reading traffic.

🚀 Result​

Thanks to the MySQL Master-Slave architecture, your WordPress site can easily accommodate more users. This structure provides a solid and scalable foundation for growing projects. For high-performance infrastructures, you can try it now on the GenixNode platform.